# Sum Distinct in DataSet

### Question

•  Hello, How to calculate the formula Sum([PRODUCT_VAL] ) Distinct( [ID]) in SSRS? ( [PRODUCT_VAL] and [ID] - are two columns in the dataset ) I think I need to iterate through rows of DataSet in custom reports code, to find Distinct - [ID] and make SUM for [PRODUCT_VAL] , but is it possible? Or is it a better solution? Thanks
Wednesday, June 12, 2013 1:54 PM

### All replies

• Hi,

you can simple use group on your table to achieve this resultset.

So to add a group on table have a look on this link:

http://msdn.microsoft.com/en-us/library/ms170712.aspx

Live life with joy and happiness! Avanish

Wednesday, June 12, 2013 2:35 PM

You can to calculate the total fo [Product_Val] for distinct [ID], right? If in this case, which [Product_Val] value should be added to the total if there have multiple [Product_Val] for the same [ID]?

In you scenario, you can use a matrix to dispaly your data. Add [ID] to the first column, in this case, it is in a row group, then distinct [ID] will be displayed.

Then use the custom code to calculate the total value.
Public Shared Value as Integer=0
Public Shared Function GetValue(Item as Integer) as Integer
value= value + Item
return Item
End Function
Public Shared Function GetTotal()
return value
End Function

In the second use the expression below. (You can calculate the first value of last value of Product_Val for duplicate ID base on you condition)
=Code.GetValue(First(Fields.Product_Val.Value))

And use the expression to get the total
=Code.GetTotal()

Regards,

Charlie Liao
TechNet Community Support

Thursday, June 13, 2013 3:36 AM
•

Hello Charlie,

If in this case, which [Product_Val] value should be added to the total if there have multiple [Product_Val] for the same [ID]?   First value.

I already have a table in the report that displays multiple columns of the dataset.

Sum([PRODUCT_VAL] ) Distinct( [ID])  - It is placed at the bottom of the table.

If I will make the group on table for the SUM of the values ​​for Distinct IDs, I'll ruin the table.

Thanks

Thursday, June 13, 2013 9:13 AM