Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get total of top 10 sales in SSRS 2012

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.

like image 434
KrazzyNefarious Avatar asked Jun 24 '13 12:06

KrazzyNefarious


2 Answers

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:

enter image description here

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

enter image description here

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

enter image description here

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:

enter image description here

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.

like image 125
Ian Preston Avatar answered Oct 17 '22 21:10

Ian Preston


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

top 10 sales

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

enter image description here

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

like image 41
mmarie Avatar answered Oct 17 '22 21:10

mmarie