How do i sort by a data field in a SSRS Matrix, plus show top N

Question

How do i sort by a data field in a SSRS Matrix, plus show top N

paul.beattie

Mr or Mrs. 500

Points: 540
More actions
October 18, 2010 at 9:51 pm

#227987

Hi
I have a matrix in SSRS 2005 feeding from a grouped SQL query, so the matrix isn't actually doing anything with the data except displaying the results in the correct format. The query returns data for the previous 12 months, summed by product.
The matrix shows the Products going down, the months showing as column headers across the page and the count of the product sales for 12 months as the data. I need to be able to sort the first data column - which is the current month - so we can see what the top item is, and what the trend has been for that item over the past 12 months. Next I want to show only the top N (call it 10 for this example) items for that month.
The matrix is refreshed each month and it is expected the top 10 will change so I can't define which items I want to show up and of course the months will move on.
So...matrix to look like below, sorted on the first data column (currently Sep 2010 (in this example, the previous month (Aug) would have shown Product A at the top, and the Jul matrix would have Product F at the top):
Product/Month Sep 2010 Aug 2010 Jul 2010 Jun 2010 May 2010 etc
Product D 100 94 92 98 95
Product A 95 95 101 98 105
Product F 80 92 104 101 94
Product Z 78 64 82 90 91
etc for 6 more products
So, my question is....how do I sort by the first data column and how do I only show the top N?
Many thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

I had a similar problem where I went back to the query to get the results I wanted. Using temp tables or common table expressions I built a top N result set/table then I used that as a filter for the final query for the report.

Something like

WHERE FieldToFilterBy IN (SELECT FieldToFilterBy FROM TopNSetOrTable)

I hope this helps.

VinceV Right there with Babe Points: 791 More actions · Answer 2

I think that this is best handled in your query but if you must do it in RS, there is indeed a solution. I didn't think it was possible at first but toyed around and was able to create a working POC in SSRS 2008. It should work in SSRS 2005 too but I don't know for certain.

There are two separate pieces to this. (1) Sort a particular data column in descending order. (2) Show only the matrix rows with the top N values.

This is what I did for the first piece:

1) In your matrix you should already have 1 row group (for product) and 1 column group (for dates). Remove any sort you had on the row group.

2) Add a sort at the matrix scope. In your matrix properties, you should have just 1 sort with the following expression: =iif(Fields!MTP_YEAR_MONTH.Value=Parameters!tp.Value, Fields!SalesCount.Value, Nothing) In my POC, MTP_YEAR_MONTH is the date in YYYYMM format and I compared it to a parameter so that I can choose which date column I want to sort by. Note that this code will only work when the intent is for a DESCENDING sort so make sure you set the order attribute accordingly in the sort line.

3) Test your report to make sure that it works before moving on to the next piece. I recommend that you take a parameter to let you choose which date column to sort by - for testing purposes at least.

The second piece:

Note that your query is pulling back more products you wanted to show. This is one reason why I would have preferred to handle on the query side...but again, if you must do it in RS...

The trick is to use the Row Visibility attribute to hide all rows except the first N (let's say 10 in this case). But how do we determine the first 10 rows of the matrix? It took a bit of toying around but ultimately this is the formula that would assign the row numbers to your matrix =RowNumber(Nothing) / CountRows("rowgrp") RowNumber(Nothing) assigns row numbers to your entire data set. CountRows("rowgrp") returns the number of rows in each of your product grouping. Of course, change "rowgrp" to whatever name you gave to your product grouping. The quotient would give you consecutive integers representing the matrix rows.

The last thing you need to do is to right-click on a row of the matrix and select "Row Visibility..." and insert the following code: =RowNumber(Nothing) / CountRows("rowgrp") > 10

That's all there is to it. Let me know if you need additional assistance.

--Vince

paul.beattie Mr or Mrs. 500 Points: 540 More actions · Answer 3

Hi Vince

Thank-you for your work - it's most appreciated. I understand where you're going but have two problems.

1. I got the sorting working as you suggested but ony by manually entering a parameter (or putting one in as a default). As the month will change every....well, month, I need the report to be able to know what the latest month is. I put an expression looking for the LAST month into the default parameter area (ie so it will always sort by the latest month) and then tried putting the same expression directly into the sort area but both times was told I wasn't allowed to have an aggregate function in those areas. Any suggestions?

2. I copied the Top N expression into the matrix row visibility area but the > was underlined in red as an unrecognised identifier, and when I ran the report anyway the error message read: [BC30451] Name 'gt' is not declared. What haven't I done?

Many thanks

Paul

VinceV Right there with Babe Points: 791 More actions · Answer 4

Paul -

For your first question, the easiest way is to set a default value for your date parameter. Set it so that the default value pulls from a query. That query should mirror your main query in terms of selection criteria but you would only pull back the max sales date. Something like MAX(SalesDate). This solution is workable as long as the query finishes pretty quickly. Otherwise, you might have to resort to a more complicated solution that involves calculating the Max date in RS and store it in a hash table. The sort expression will then retrieve the value back from the hash table. You might be better off not going there so give the first solution a try.

Regarding the second question, it's actually my fault. I neglected to double check... The forum code formatter mangled the expression that I posted. Below is what it should be.

=RowNumber(Nothing) / CountRows("rowgrp") >= 10

Let me know how it goes.

--Vince

paul.beattie Mr or Mrs. 500 Points: 540 More actions · Answer 5

Hi Vince

Thanks for coming back - I hope i'm not keeping you up. Some success, some failure - I can't block out a row and choose visibility (I wonder if it is a 2005 thing). I have to choose multiple cells. I did this and received the following message:

The Hidden expression for the textbox ‘Count’ has a scope parameter that is not valid for RunningValue, RowNumber or Previous. The scope parameter must be set to a string constant that is equal to the name of a containing group within the matrix ‘matrix1’.

I then put your expression into individual cells and it worked beautifully in the Product field that I'm grouping on - as expected only 10 product names were returned, but of course, with no visibility expression in the data cell, the totals for all the other 200 products were returned with blanks where their names would be. I then cut and pasted the expression to the data cell and the above error message was returned. Any ideas?

Paul

VinceV Right there with Babe Points: 791 More actions · Answer 6

Paul - I never worked with SSRS 2005 and don't have it installed so I am afraid I can't help you further. Did you give any thought to doing this on the query side? It certainly would be much less headache, in my opinion.

--Vince