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 · bigator8 Comments
bigatorTags: , , ,  Â· Posted in: Business Intelligence, MDX, Quick Tips, SQL Server, SSAS, SSRS, Tutorial

8 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.

Leave a Reply


bigator