Tuesday, May 01, 2012 11:20 AM
I have report in which sum is calculated for each cateogary(group by cateogary).
I have to select top 10 cateogary who have max value in report.When i am using top10 filter it pick top 10 rows and so I am not gettin correct result as top n filter works before calculating sum in group.Do I need to use custom code for this??
Tuesday, May 01, 2012 11:40 AM
I think you'd be better off doing your aggregation and TOP 10 in SQL...
So, if you have a query at the moment which is:
SELECT column FROM Table
You'd change it to:
SELECT TOP 10 * FROM ( SELECT MAX(column) FROM Table ) T
Zach Stagers - http://www.scratchbox.co.uk
Tuesday, May 01, 2012 11:05 PMModerator
You can put any expression like sum etc. inside top n on group.
I have attached the screenshot for your help
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
- Proposed As Answer by Syed Qazafi AnjumMicrosoft Community Contributor, Moderator Tuesday, May 01, 2012 11:05 PM
Wednesday, May 02, 2012 3:13 AMModerator
From your description, you want the report to display TOP 10 Category values and the total value aggregates the entire category field rather than the top 10 Category values.
To achieve your goal, you can add the filter to the group level rather than the dataset level. Please refer to the steps below:
- Right-click on the “Category” group in the Row Groups pane, open the “Group Properties…” dialog.
- Click the “Filters” tab, add a filter like the one below:
Expression: Sum(Fields!SalesAmount.Value) Interger
Operator: Top N
The screenshot below is for your reference:
If you have any questions, please feel free to let me know.
- Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Sunday, May 06, 2012 2:44 PM