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?
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:
Live life with joy and happiness! Avanish
- Proposed as answer by Akhila Vasishta Thursday, June 13, 2013 3:31 AM
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
Public Shared Function GetTotal()
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)
And use the expression to get the total
If you have any questions, please feel free to ask.
TechNet Community Support
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.