↓
 
Home→SSAS→MDX→Spot/Highlight Minimum and Maximum value(s) in each row in Matrix report in SSRS :)

Spot/Highlight Minimum and Maximum value(s) in each row in Matrix report in SSRS :)

Khilitchandra's Business Intelligence Blog Posted on by

Hi all,
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.
Thanks, Khilit

Comments

Spot/Highlight Minimum and Maximum value(s) in each row in Matrix report in SSRS :) — 13 Comments

  2. I heard a couple of guys talking about this in the New York subway so I looked it up online and found your page. Thanks. I thought I was right and you confirmed my thoughts. Thanks for the work you’ve put into this. I’d love to save this and share with my friends.

    Reply ↓

  3. Hi Khilit,

    i have not seen the option of summarized by min and max in ssrs report. can u pls let me know which version of sql server your using.

    i am using sql server 2008.

    Thanks
    Vikas

    Reply ↓

    • hi vikas,
      how are you doing?

      well, i am using SQL Server 2008 R2 and i have used the same for this demo. i want you to take a look at point number 5. you will see that, i haven’t selected the cell, but i have selected the value (expression) in it. and when you right-click on that selected value (expression), you will sure see the “Summarize By” pop-up 🙂 there you go.

      i hope this helps.
      Thanks, Khilit

      Reply ↓

  6. Hi Khilit,

    thanks for your valuable and nice post. I was stuck in colour coding concept but finally I have implemented the concept through you article.

    thanks Vivek

    Reply ↓

  7. Came across this article, but ran into a couple issues when implementing it.

    I have a similar matrix setup in one of my reports. In my case, the row groups are Production Shifts, the Column groups are Production Machine Numbers, and the values are Avg(OperatorTime). What the table is showing is the average operator cycle time (OperatorTime), broken up by shift and machine, over a span of days. This means that for each combination of machine number and shift, the cell is displaying the average of roughly 1000 values.

    The problem I am having is that if I set up a “MaxValue” column like seen above, with the value set to =Max(Avg(OperatorTime)), it will just pull out the average of all data summarized there, instead of the max displayed value. Likewise, if I use just =Max(OperatorTime), it will just pull out the highest single point of data out of all the data that is being summarized.

    Any suggestions?

    Reply ↓

  8. how to find the maximum value for the below expression , ineed to do,
    Maximum (sum (value1)/Sum(value2))
    Becoz,I need to find the maximum value fo a chart series

    Reply ↓

  9. I like this….but unfortunately doesn’t solve what I wanted to do.

    My Columns are expressions calculating percentages (ie percent male/female), and when I click on the expression to Summarize, it’s greyed out sadly.

    I wanted to just highlight the highest value of the 2.

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

↑