About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, October 23, 2014

SSRS: Setting a value dynamically for an unknown count of distinct values -- AND -- having a legend outside of a chart


One of the tricks that I do, is removing the legend out of charts into a table. To do this, I need to explicitly set the colors and/or markers for each category. In this example, to do this dynamically, I use this basic statement with a list of colors -- where the number of possible results back maybe be up to 4 distinct values.

=Choose(RunningValue(Fields!Unknown.Value,CountDistinct,Nothing),
"#0000e7",
"#626297",
"CornflowerBlue",
"#2f52a6")

So, in this example I put a sparkline in the table with no category or series group -- forcing it to only display one color marker. The logic that I used in the chart's marker and fill color are then also used in the sparkline, giving a legend outside of the chart. 



If need be, you can just create a chart with the name and color/marker sparkline and move your legend anywhere you want.

No comments: