How to get the total number of top 10 sales in SSRS 2012

Question

I accept the Top 10 sales grouped by product category in SSRS 2012. I need the total number of these top 10, but it shows the total amount. I cannot do this at the dataset level because I need a complete dataset for other parts of the report. I tried the solution mentioned in MSDNlink , but that didn't help either. Thanks in advance.

Answer 1

This approach really works very well.

You do not know what your data / metadata is, but the concepts can be explained with a simple example. Consider the following data:

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

Sorting groups by sum val, from highest to lowest:

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

To get a group rating, use:

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

To get the total usage:

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

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

For the second line of group details, use the following Row Visibility expression:

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

That is, only this line is shown when there were two groups, i.e. top 2. You could change the value as needed.

This shows us one common line if required:

You need to apply these concepts to your data. If you still have problems, I suggest trying to reproduce my results with the above data / code to make sure you understand all the concepts involved.

Edit after comment:

In situations where the number of groups is less than N, but you want to display the last total number, you need to add an additional check to the expression on the top line of the Row Visibility line , for example:

 =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 display for the 10th row, or if the total number of groups in the DataSet is less than 10, it will be displayed for the last group.

It is a bit more complicated, but in the past it worked for me; Depending on your data and reporting settings, you may need a little Scope to get it working in your environment.

Answer 2

If you just need the total amount for the top 10, and not the total amount, you can filter the table on top of N ProductCategory and sort your ProductCategory group using SalesVolume Z to A.

For example, I have a table of orders and subtotals of sales. I show the top 10 highest scores

I sorted by SalesOrderID group, dropping by my value (TotalDue). Then I filtered my table so that it showed only the top 10 SalesOrderID.

If you have a lot of data, you might have to see how it works, since I think the table filter happens at runtime.

Answer 3

I think I found an easy way to do this. I had a lot of “Sums” in my report, and I couldn’t understand how you answered. I found a way to create the parent group of the Details group and add the summary row outside the Details. Then I hid the Detais group, and the general group just did Sums in the group properties, just needed to filter the final lines of N from above, and sort by Z in Sum. All this worked perfectly and performed in the group properties! In tablix poperties, only 3 or 4 lines were shown ...