How are you doing?
Today I would show you; how you can highlight minimum and maximum values in an each row of matrix report in SSRS.
Again, “there are lots of ways“, but that depends on what is your solution framework. If you are using standard T-SQL queries for reporting, then it’s easier to write this min-max logic in query itself.
But if you are using SSAS cube then you have to write custom MDX (i.e. calculated column/”Member” in Cube’s term) and do comparison in SSRS report in each row.
I can show you even simpler way where you don’t have to write MDX to achieve this functionality and implementation will be easier, faster & fun
Ok, let’s begin step by step.
1. Prepare your matrix report up to this point as shown below.
2. Insert a column outside the group.
3. Insert one more column on right side.
4. Rename columns “Min” and “Max” respectively for better reference. And use the SUM() to aggregate the whole row values.
5. Use out-of-the-box feature to summarize the data to display minimum value.
6. Do it for setting the maximum value.
7. Preview your report for verification of values. It should look-like following:
8. Set “Name” property for minimum value textbox as shown below.
9. Set “Name” property for maximum value textbox too.
10. Select the main value textbox and go to properties. Open the “BackgroundColor” property’s expression dialog box for editing its expression.
11. Set the expression as shown below and hit “OK”.
12. Select “Min” and “Max” columns and set their “Hidden” property to “True” in order to hide these columns.
13. View your report & enjoy
I know these are lots of steps but once you do it you will feel like 1 min job. And that’s cool.
I hope you find this useful and interesting.