top n after adding group in tablix

Question

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??

Any suggestion.....

---

j

Answer 1

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

Answer 2

 

Hi There

You can put any expression like sum etc. inside top n on group.

I have attached the screenshot for your help

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 3

Hi J_aer,

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:

1. Right-click on the “Category” group in the Row Groups pane, open the “Group Properties…” dialog.
2. Click the “Filters” tab, add a filter like the one below:
   Expression: Sum(Fields!SalesAmount.Value)  Interger
   Operator: Top N
   Value: 10

The screenshot below is for your reference:

If you have any questions, please feel free to let me know.

Regards,
Mike Yin