March 9, 2009 at 4:50 pm
Hi,
I am working on a matrix report (SSRS 2005). I am trying to accomplish the following:
1) Display top 10 rows for each group. Can I do this in SSRS ?
2) I would also like to toggle CategoryGrp to Show/Hide the details. I can hide Subcategory, but I cannot hide Sum(Defects) field.
I would like to display CategoryGrp and subtotal by month first, then toggle on Category to show the details for it.
My report looks like this:
| Month | Total
--------------------------------------------------------
CategoryGrp | SubcategoryGrp | Sum(defects)|
--------------------------------------------------------
|SubTotal |
Thank you !
March 10, 2009 at 6:14 am
I have not done it, but on the properties for the Group you can set a filter for Top N. Look up filters in BOL.
Here is a snippet from "How to: Add a Filter (Report Designer)":
To add a filter to a table group or matrix group
In Layout view, click the table or matrix so that column and row handles appear above and next to the table or matrix.
Right-click the corner handle of the table or matrix and then click Properties.
On the Groups tab, select the group to edit, and then click Edit.
Select the Filters tab and follow the steps below in To set a filter.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2009 at 6:18 am
Hey Jack,
I've already tried that, but RowNumber method cannot be used in the Filter Option. Any other suggestion?
March 10, 2009 at 6:55 am
I solved the second problem. To toggle the second group, set the visibility at group level, not at the cell level.
March 11, 2009 at 5:28 am
Not sure why you want to use the rownumber method in the Top N filtering, you shouldn't need to use this. Just set the filter to Top N and then the expression to =10 (for example, gives top 10) and it will automatically provide top 10 according to your own sorting.
Regards,
Nigel.
Nigel West
UK
March 11, 2009 at 7:48 am
It worked. Thanks a lot !
I didn't know about Top N.
March 18, 2009 at 10:34 pm
i have a scenario like i want to display Top 10 sum and Total for others,the situation is like below
ccc | clmngrp
-----------------
cc1 | 1
cc2 | 2
| 3
| .
| .
| .
| .
| 10
-----------------------
Top 10|
total |
method's like running value is not working,we cant also give sum(xxx,"clmngrp") because of the dynamically varying values in column group.Did anyone came across the same situation??any solution for this problem??
March 19, 2009 at 3:13 am
i have a scenario like i want to display Top 10 sum and Total for others,the situation is like below
I think I would probably look at using an aggregation in the original SQL Query that provides the dataset, let's assume you want to get the top 10 customers in terms of sales value and then others. The aggregation can provide a grouping level for you.
SELECT
CustomerName,
SalesValue,
RANK() OVER (ORDER BY SalesValue DESC) AS [Rank]
FROM dbo.CustomerSales
The above query simply gives you a ranking, however, if you add the following simple case statement to it........
SELECT
CustomerName,
SalesValue,
RANK() OVER (ORDER BY SalesValue DESC) AS [Rank],
CASE WHEN RANK() OVER (ORDER BY SalesValue DESC)<=10 THEN 1 ELSE 2 END AS GroupLevel
FROM dbo.CustomerSales
Then this could give you a grouplevelk field which you can use in your report to seperate the two groups of data (top 10 and others).
Good luck,
Nigel.
Nigel West
UK
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply