SSRS get colors from database
I need to get value for cell background color from a database table. The color needs to be from a table like shown here: Cell RangeStart RangeEnd Color Column1 0 89.99 Red Column1 90 99.99 Yellow Column1 100 1000 Green Column2 0 49.99 Red Column2 50 89.99 Yellow Column2 90 1000 Green Since there can be many columns, I was thinking of maybe having one dataset that reads data. I tried using Lookup function, but it doesn’t support BETWEEN functionality. Any Ideas on how I can implement this in SSRS? Thanks, Ajay
June 22nd, 2011 4:54pm

You would need to establish the relationship between your data query and your color table in SQL Server - I would not recommend doing it in SSRS.Please 'Mark as Answer' if found helpful - Chris@tier-1-support
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2011 9:13pm

I cannot do the join, my data is coming from Analysis services.
June 23rd, 2011 10:36am

I have found a solution to the problem that seems to be working. I had to write a Custom Code in the report to get the color. Public Function GetColor(ByVal Ranges As Object, ByVal Num As Single, ByVal ColorType As String) As String Dim RangeStart As Single Dim RangeEnd As Single Dim BackgroundColor As String Dim FontColor As String Dim KPI() As String For Each Range As Object In Ranges KPI = Split(Range, ";") RangeStart = Val(KPI(1)) RangeEnd = Val(KPI(2)) BackgroundColor = KPI(3) FontColor = KPI(4) If Num >= RangeStart And Num <= RangeEnd Then If ColorType = "BackgroundColor" Then Return BackgroundColor Else Return FontColor End If End If Next Return "Transparent" End Function And then for the Background property, I set this expression: =Code.GetColor(LOOKUPSET("KPIName" ,Fields!KPI_Name.Value ,Replace(Fields!KPI_Name.Value, ";", "_") + ";" + cStr(Fields!RangeStart.Value) + ";" + cStr(Fields!RangeEnd.Value) + ";" + Fields!BackgroundColor.Value + ";" + Fields!FontColor.Value , "KPI_Colors") , Fields!Hour_Var_pct.Value ,"BackgroundColor") Since the LookupSet function can return only one column, I an concatenating the values and using split in the code to separate them out. Thanks, Ajay
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2011 4:52pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics