SSRS display top n but have SUM include all values

Question

I currently have a tabllix in SSRS with multiple rows, but I wish to only display the top 5 rows, yet have the total column at the bottom include the values for however many rows are in the table.
Example:

NameID    /   Sales  /   % of Total
1         /    100    /
4         /    70    /
3        /     65    /
2        /     50   /
7        /     35    /

DO NOT DISPLAY THESE RECORDS:
6        /     25    /
5        /     10    /

TOTAL SALES:   355   (inclusive of all 7 records)

I need this total sales number to be accurate so that I can then use it for the % of Total column.

I don't believe a filter for top n on the dataset will work because that will not include the lower records in the dataset.
The only thing I can think to do is to make the row visibility so it only displays the first 5 rows, except I do not know how to do this.

Thanks

score 1 · Accepted Answer · answered Aug 06 '13 at 17:25

Add a rownumber column to your query (SQL)

ROW_NUMBER() OVER (partition BY FIeldName1 ORDER BY FieldName1 Asc) AS rownum

On the Report:

Right click on any Tablix column and select Tablix Properties
Click on Filters

Expression: rownum

Operator: <

Value: 6

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

You can use Top N to do this. Look 'How to get total of top 10 sales in SSRS 2012'

edited May 23 '17 at 12:30

Community

1
1

answered Nov 04 '13 at 14:06

Dev SSRS

1
1

SSRS display top n but have SUM include all values

2 Answers2

Linked