none
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
  • Hi Adi,

    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()

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, June 13, 2013 3:36 AM
    Moderator
  •          

         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