Search code examples
sql-server-2008reporting-servicesssrs-2008ssrs-tablix

SSRS display top n but have SUM include all values

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

Solution

  • 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