bigator

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

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

bigatorApril 26, 2012 · bigatorKhilitchandra Prajapati · bigator12 Comments
bigatorTags: , , ,  · Posted in: Business Intelligence, MDX, Quick Tips, SQL Server, SSAS, SSRS, Tutorial

12 Responses

  1. Adi - April 26, 2012

    Good one Khilit

    Regards,
    Adi
    AdiCodes.com

  2. Khilitchandra Prajapati - April 26, 2012

    Thanks Adi :)
    Khilit

  3. Niko Holmen - May 26, 2012

    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.

  4. vikas - May 31, 2012

    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

  5. Khilitchandra Prajapati - May 31, 2012

    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

  6. Timon Ron - June 12, 2012

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

  7. Hiral Prajapati - July 6, 2012

    Good demonstration Khilit.

    Keep it up.
    Hiral

  8. Khilitchandra Prajapati - July 7, 2012

    Thanks Hiral.

  9. Vivek Banafar - October 7, 2013

    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

  10. Tim - November 19, 2013

    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?

  11. oms024 - February 21, 2014

    I am also having the same problem.

  12. r.bhupesh - April 3, 2014

    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

Current day month ye@r *


%d bloggers like this:
bigator