Search code examples
reporting-servicesssrs-2008

SSRS - Total on Distinct Value

I have data returned like this from the query. basically Actual_Volume column is giving me individual items and Nomination column is giving total corresponding values for Actual_Volume. I want total of Actual_Volume column and distinct value of Nominations.

Nominations Actual_Volume
830800 65073.3885
830800 111853.9392
830800 139085.3661
830800 287052.0938
830800 311772.7324

830800 914837.52

I want Nomination should show as 830800 and Actual Volume as 914837.52. How can we do that in SSRS?

Solution

  • One of the strengths of SSRS is the grouping features. This allows you to aggregate numbers at different scopes in a pivot. In this case, it sounds like you're looking for a pretty simple layout, but these principles can be applied if you want to drill in to see a finer granularity.

    Using the Table Wizard, group by Nominations on the rows and use the volume on values. (You can also do this without the wizard, but it's a little easier to start with.)

    enter image description here

    Do not choose to include subtotals if prompted.

    The table design should look like this:

    enter image description here

    If you look at the group properties for the group in this table you will see that it is grouped by Nominations. This means that the results will have one row per distinct Nomination value. And the volume will be summed to that scope.

    Here are the results:

    enter image description here