How to get total of top 10 sales in SSRS 2012

Question

I am taking Top 10 of Sales Volume grouped by Product categories in SSRS 2012. I need the total of these top 10 but it shows the complete total. I cant do it on dataset level as I need the complete dataset for other parts in the report. I tried the solution as given in MSDNlink but that didn't help either. Thanks in advance.

Answer 1

This sort of approach does actually work very well.

You haven't given any idea of what your data/metadata is like, but the concepts can be explained with a simple example. Consider the following data:

We will make a simple report based on this, grouped by the grp column:

Sort the groups by total val, from highest to lowest:

To get the running rank and the running total, we use the RunningValue function.

To get the Group Rank, use:

=RunningValue(Fields!grp.Value, CountDistinct, Nothing)

To get the running total use:

=RunningValue(Fields!val.Value, Sum, Nothing)

Finally, we need to display a total for the Top N values; in this case I'm displaying the top 2.

For the second group detail row, use the following Row Visibility expression:

=IIf(RunningValue(Fields!grp.Value, CountDistinct, Nothing) = 2, false, true)

That is, only display this row when there have been two groups, i.e. top 2. You could change the value as required.

This shows us one total row as required:

You need to apply these concepts to your data. If you're still having issues, I suggest trying to replicate my results with the above data/code to make sure you understand all the concepts involved.

Edit after comment:

For situations where there are fewer than N groups but you still want to display the last total, you need to add an extra check to the Top N row Row Visibility expression, something like:

=IIf(RunningValue(Fields!grp.Value, CountDistinct, Nothing) = 10
    or (RunningValue(Fields!grp.Value, CountDistinct, Nothing) = CountDistinct(Fields!grp.Value, "DataSet1") and CountDistinct(Fields!grp.Value, "DataSet1") < 10)
  , false
  , true)

So now the expression will show the for the 10th row, or if the total number of groups in the DataSet are less than 10, it will show for the last group row.

It's a bit more complicated but it has worked for me in the past; depending on your data and report setup you might need to play around with the Scope a bit to get it working in your environment.