How do I sort by one of the data columns in a Matrix, and show only Top N
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 months matrix (Aug) would have shown Product A at the top, and the Jul matrix would have Product F at the top): 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 N more products So, my question do I sort by the first data column and how do I only show the top N? Many thanks in advance
October 19th, 2010 7:11am

Hi Raglan, The matrix can't sort on one of the columns, only on the subtotal. If possible, i would suggest you manage to achieve these via writing T-SQL. thanks, Jerry
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2010 5:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics