sqlsql-server-2008-r2reporting-servicesbids

Grand Total value doesn't match with Top N Filtered values in SSRS


I have a report in reporting services. In this report, I am displaying the Top N values. But my Grand Total is displaying the sum of all the values.

Right now I am getting something like this.Here N = 2

 +-------+------+-------------+
| Area   |ID   |  Count      |
+-------+------+-------------+
| - A   |      |   4         | 
|       |  a1  |   1         | 
|       |  b1  |   1         | 
|       |  c1  |   1         | 
|       |  d1  |   1         | 
|       |      |             |
| - B   |      |   3         | 
|       |  a2  |   1         | 
|       |  b2  |   1         | 
|       |  c2  |   1         |  
|       |      |             | 
|Grand  |      |  10         | 
|Total  |      |             | 
+-------+------+-------------+

The correct Grand Total should be 7 instead of 10. A and B are toggle items(You can expand and contract)

How can I display the correct Grand Total using Top N filter?

I also want to use the filter in the report and not in the SQL query.


Solution

  • I found a way to solve my question. As Ido said I worked on the dataset. I am using Analysis Cube. So in this cube I created a Named Set Calculation.

    In this set I used the TopCount() function. It filters out the TOP N values where N can be integer according to your choice.

    So the final Named Set in this case is :-

    TopCount([Dim Area].[Area].[Area], 2, ([Measures].[Count]))
    

    This will give you Grand total of Top N filtered values.