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).

Wednesday, December 05, 2012

SSRS Pie Chart displaying 0

Was given a problem with one of the reports showing data in the pie chart when it was 0.
The orignial report seperated each of the series values into the Values section, which at first seemed counter-intuitive -- but the reason behind this was so that certain colors and a specific ordering of stages took place in the legend.

So, going back to the drawing board and fresh eyes (me) -- I set the column used to create the seperate values into one series group. This then gets rid of the 0 line value, but to get the coloring corrected I added an expression to the Values field to set the color for specific series types:

=IIF(Fields!TypeName.Value = "Referral Received to 1st Legal", "LimeGreen",
IIF(Fields!TypeName.Value = "1st Legal to Service Complete", "Purple",
IIF(Fields!TypeName.Value = "Service Complete to Judgment Entered", "SlateBlue",
IIF(Fields!TypeName.Value = "Judgment Entered to Sale Held", "Turquoise",
IIF(Fields!TypeName.Value = "Referral Received to Sale Held", "Tomato","White")))))

Once this was setup, I then was left with the last issue of sorting. In the series group properties under sorting, I added the field SortOrderNumber which is coming in from the stored procedure. This then gives me the final clean product: