0

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

maddy
  • 50
  • 1
  • 1
  • 10
  • Google has the answer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3c2626d8-e835-4bba-b946-7d6796e9c617/sum-distinct?forum=sqlreportingservices – GandRalph Jul 19 '18 at 07:26
  • Thanks all for reply.Your answer's gave me different perspective. – maddy Jul 20 '18 at 17:00

2 Answers2

3

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.

Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
0

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.

Lucky
  • 4,443
  • 2
  • 8
  • 18