RSS

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

26 Apr

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

 

Tags: , , ,

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

  1. Adi

    April 26, 2012 at 12:53 pm

    Good one Khilit

    Regards,
    Adi
    AdiCodes.com

     
  2. Niko Holmen

    May 26, 2012 at 4:40 am

    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.

     
  3. vikas

    May 31, 2012 at 8:06 pm

    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

     
    • Khilitchandra Prajapati

      May 31, 2012 at 11:03 pm

      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

       
  4. Timon Ron

    June 12, 2012 at 12:52 am

    Very nice post and article. Great. Thank ou very much.
    Tim

     
  5. Hiral Prajapati

    July 6, 2012 at 8:07 pm

    Good demonstration Khilit.

    Keep it up.
    Hiral

     
  6. Vivek Banafar

    October 7, 2013 at 5:48 pm

    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

     
  7. Tim

    November 19, 2013 at 11:17 pm

    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?

     
    • oms024

      February 21, 2014 at 9:31 pm

      I am also having the same problem.

       
  8. r.bhupesh

    April 3, 2014 at 6:59 pm

    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

     

Leave a Reply

 
%d bloggers like this: