CODEWITHSUNDEEP
sqlreporting-services
HDuck
HDuck

Reputation: 395

Conditional cell formatting on SSRS pivot table

enter image description hereI 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

Upvotes: 0

Views: 437

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21738

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.

Upvotes: 2

Related Questions