CODEWITHSUNDEEP
ssrs-2008ssrs-2008-r2
maddy
maddy

Reputation: 50

SSRS sum of distinct values

I want sum of only unique values in SSRS report.Is there any logic to achieve this. this is what something like this sum(distinct value)

Thanks

Upvotes: 0

Views: 16536

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Supposed you have a column named value in your query, you could do the following:

Add an additional column to the query of the dataset:

ROW_NUMBER() OVER (PARTITION BY [value] ORDER BY [value]) AS valueNr

Then, if you already have created a Sum field in the table, change the expression of the textbox to

=Sum(Iif(Fields!valueNr.Value=1, Fields!value.Value, 0))

Repeat this for every "distinct sum" calculation.

Upvotes: 3

Lucky
Lucky

Reputation: 4493

Yes. You use groups. At the top click the insert menu, then table, then Table Wizard. Pick your dataset and hit next. Now drag the column for the different types of items you want a distinct sum of into the Row Groups section. Drag your count column into the Values section. This should automatically turn it into Sum(ColumnName). You can click the down arrow to change the aggregate type (if desired). Press next and next and finish. Viola. You have a distinct sum for each specified field.

Upvotes: 0

Related Questions