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