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.

Answer 2

If you just need a total for those top 10 and not a running total, you can filter your table by top N ProductCategory and sort your ProductCategory group by SalesVolume Z to A.

For example, I have a table of sales orders and subtotals. I'm showing the top 10 highest total

I sorted by SalesOrderID group descending by my value (TotalDue). Then I filtered my table so it shows only top 10 SalesOrderID.

If you have a lot of data, you may have to see how this performs since I think the table filter happens at runtime.