9

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.

KrazzyNefarious
  • 3,202
  • 3
  • 20
  • 32

3 Answers3

17

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.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • Hi Ian, Thank you so much for the response. Your post is really helpful. Just one thing, what happens in those cases where the top 10 actually has only 5 categories? I have a parameter vendor and I want top 10 categories sold by them. What in case if the vendor sold only 5 categories? What should be in the row visibility expression. – KrazzyNefarious Jun 25 '13 at 05:57
  • Hi, @BhupeshChouhan, I've added some more detail. Again, since I don't know your data it's more representative than anything, but hopefully you see what I'm trying to explain. – Ian Preston Jun 25 '13 at 08:21
  • @Ian, I have similar problem, but I need to get a totals for each Month in my report. So I have RowGroup and ColumnGroup: http://stackoverflow.com/questions/39154250/total-sum-in-ssrs-doesnt-display-corrent-number – Serdia Sep 02 '16 at 19:14
  • @IanPreston Thank you. But to show the top 2, wouldn't the visibility formula have to be: =IIf(RunningValue(Fields!grp.Value, CountDistinct, Nothing) <= 2, false, true) ? That's what I had to do. – Emil Rehhnberg Jul 26 '17 at 03:20
11

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
mmarie
  • 5,598
  • 1
  • 18
  • 33
  • The total in my solution is the sum of the top 10 amounts. A quick check will a calculator will prove this out. The sum of my total data set was over 1 million, whereas the sum of the top 10 was 768. If you filter the group rather than the table, you will get the total for the entire dataset. But if you filter at the tablix level as I described, you will get the desired total. – mmarie Jun 24 '13 at 21:19
  • Hi mmarie, your post actually gave me just one record. I recreated the entire report just to make sure nothing was wrong in the definition, but seems that it just lands up with one data. Thanks anyways for looking into this. – KrazzyNefarious Jun 25 '13 at 05:59
  • I would guess that is due to a difference in our data or groups. My source query was SELECT SalesOrderID, TotalDue FROM Sales.SalesOrderHeader (using the AdventureWorks db). I had two groups: Details and SalesOrderID. – mmarie Jun 25 '13 at 15:17
0

I think I found an easy way to do that. I had many "Sums" in my report and I couldn't understand the way you answered. The way i found was to create a parent group of the Details group and add a total row outside the Details. Then I hided the Detais group and the total group just did the Sums and in the group properties just needed to filter the final Sum top N rows, and sort by Z to A the Sum. All of this worked fine and was done in the Group Properties! In the tablix poperties only showed 3 or 4 rows...

ALourenço
  • 100
  • 4