Conditional cell formatting on SSRS pivot table

Question

I created a pivot table in SQL that has report names along the left side, and hours (00:00, 00:01, etc.) along the top. The values in the table are the number of times each report has been used during that hour over the past three months. I've imported the table into SSRS, and I'm trying to create a heat map of sorts. I want to color the cells darker or lighter across the row based on the number in each cell compared to the value of cells across the row (cell that has the highest value will be the darkest colored). I've tried following this guide to color the cells, but here the entire row is one field, while I have separate fields for each column. Is there a way to achieve this?

EDIT: Added picture of table design, and preview where coloring is done incorrectly

The entire row isn't one field in the link youposted. Each row is shaded a different base colour and each cell is shaded lighter or darker based on the value. You shoud be able to apply it your report. Just select all the cells you want to affect and then set the background color property to same expression. you may need to edit it if you want the same colour on each row but that's all. — Alan Schofield
I've been trying this but I can't get it to work correctly. I tried setting the color expression for all fields to [=Code.ColorDWB(sum(Fields!ID00.Value),Max(Fields!ID00.Value, "DataSet2"),Min(Fields!Report_Name.Value, "DataSet2"), "#2322EE")], but this just compares the values in the 00 field for each row and colors the entire row based on that. I'm not sure how to change the function to make it color each cell separately based on all the other cells in the row. — HDuck
Also added a picture to show the table design and preview when I tried using the code from my last comment. In the example, the two fields he had were [Vehicle] and [sum(Sales)], and he applied the code to [sum(Sales)] and used sum(Sales) for values in the function. I tried applying the code to fields [ID00] - [ID23] but I'm not sure what values to plug into the function, because when I plug in [ID00] it colors the rows based only on the values in the 00 field. — HDuck

Alan Schofield Alan Schofield · Accepted Answer · 2017-07-05T19:38:46

I understand your problem better now...The function uses the min and max values of a column to determine the range from lightest to darkest, then it probably looks at what fraction of the range your actual value is. In your case where you have each column's data coming from a different cell it'll be a pain unless your columns are fixed and even then it's more trouble than it needs to be.

I would suggest the following.

DON'T PIVOT your data in SQL, we can do that really easily in SSRS, your dataset will be simpler too something like

ReportName   Hour   UsageCount
ReportA       0     8
ReportA       1     4
ReportC       22    18

and so on...

Create a new report and add a matrix with reportName as the row group and hour as the column group. The data values will be UsageCount.

That's it for the report design, then just set the cells back ground based on your function but this time you can pass in Max(Fields!UsageCount.Value) etc as per the sample.

I've rushed this a bit so it if not clear, let me know and I'll post a clearer solution.

Conditional cell formatting on SSRS pivot table

1 Answers