locked
How to do SUM(Distinct(Field1)) in Visual Studion report designer?? RRS feed

  • Question

  • Hi,

    I am currently working on a report in which I need to do sum of only distinct values from a column.

    Lets say, Column1 have a number of repeatating records. I need to take the sum of the distinct values from them.

    I can do it in query, like: select Sum(Distinct(Column1) from Table1. And then create a dataset and display this in a table.

    But if I want to do it in Visual Studio report desinger, how Could i do this,as I could not find any built-in function like Distinct() ??

     

    Thanks

    Anupam

     

    Thursday, May 13, 2010 5:44 AM

Answers

  • Hello,

    The function should be like this...

    FUNCTION SUMDISTINCT(ByVal Field AS INTEGER) AS INTEGER
    Dim count As Integer = values.Count
    values.Sort()
     i = 0
      While ( i < count)
       If (values(i) <> values(i+1)) 
       values2.Add(values(i))
      End IF
     i = i+1
     End While

    count2 = values2.Count
     j = 0

     While ( j < count2)
      SumField = SumField + values2(j)
     End While

    RETURN SumField
    END FUNCTION

    In few minutes I will be away from MSDN forums, hope its helpfull...


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, May 13, 2010 12:24 PM

All replies

  • Hello Anupam,

    This can be done like this. Assume this is your query

    SELECT        CustomerKey, Title
    FROM           DimCustomer

    and your report has the same 2 columns.

    add a totalrow to the table on your report and put the following (VB) expression in one of its cells

     

    =CountDistinct(Fields!Title.Value)
    

    That should do it.

    r,

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Thursday, May 13, 2010 6:20 AM
  • Why not doing it in T-SQL within a stored procedure?
    Thursday, May 13, 2010 8:14 AM
  • Hello,

    You can achieve this using Custom Code. Below is the custom code which you need to place in the Report->Report Propeties->Code

    DIM Shared SumField AS INTEGER = 0
    DIM Shared PreField AS INTEGER = 0

    FUNCTION SUMDISTINCT(ByVal Field AS INTEGER) AS INTEGER
     IF (PreField = Field) THEN
      SumField = SumField + 0
     ELSE
      PreField = Field
      SumField = SumField + Field
     END IF
     RETURN SumField
    END FUNCTION

    In the report design where you need to call the function like below statement
    =CODE.SumDistinct(Fields!FieldName.Value)

    I need few more information as where you want to display this Sum Distinct value. Depending on the cell placing (Scope) the custom code will be giving the results.

    Hope its clear and helpful...


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, May 13, 2010 8:33 AM
  • Hi PavanKokkula,

     

    I used your thought, and used this snippet of code :

    DIM Shared SumField AS INTEGER = 0
    DIM Shared PreField AS INTEGER = 0
    Dim Shared i As INTEGER = 0
    Dim Shared j As INTEGER = 0

    Dim values As New System.Collections.ArrayList
    Dim values2 As New System.Collections.ArrayList
    DIM Shared Num AS INTEGER = 0
    DIM Shared count2 AS INTEGER = 0

    Function AddValue(NewValue As Decimal) As Decimal
     values.Add(newValue)
     AddValue = newValue
    End Function


    FUNCTION SUMDISTINCT(ByVal Field AS INTEGER) AS INTEGER
    Dim count As Integer = values.Count
    values.Sort()
     Do While ( i <= count)
      If (values(i) = values(i+1))  
       continue Do
      Else
       values2.Add(values(i)) 
      End IF
     i = i+1
     Loop

    count2 = values2.Count 
     
     While ( j <= count2)
      SumField = SumField + values2(j)
     End While

    RETURN SumField
    END FUNCTION

     

    Now from a hidden field I called "=Code.AddValue(Fields!salary.Value)",

     and from my SUM field I called "=Code.SUMDISTINCT(Fields!salary.Value)"

    So, AddValue function will put all my records of salary column in a ArrayList, and in SUMDISTINCT function I filter the distinct values and put them in another ArrayList. and then add them.

     

    but, sum field in showing #error........how to trace , why is this error occured ??

     

    Please refer to my table

     

    Name  ID  Salary  LastName
    A          1   10000     AA
    B          2    15000    BB
    C          3    10000    CC
    D         4     20000   DD
    E         5     15000   EE
    F         6     12990    FF
    G          7    10000   GG

     

    Now when I want to sum all the Salary records, then the output should be : 10000+15000+20000+12990 = 57990

     

    please help

     

    Thanks,

    Anupam

     

     

    Thursday, May 13, 2010 12:08 PM
  • Hello,

    The function should be like this...

    FUNCTION SUMDISTINCT(ByVal Field AS INTEGER) AS INTEGER
    Dim count As Integer = values.Count
    values.Sort()
     i = 0
      While ( i < count)
       If (values(i) <> values(i+1)) 
       values2.Add(values(i))
      End IF
     i = i+1
     End While

    count2 = values2.Count
     j = 0

     While ( j < count2)
      SumField = SumField + values2(j)
     End While

    RETURN SumField
    END FUNCTION

    In few minutes I will be away from MSDN forums, hope its helpfull...


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, May 13, 2010 12:24 PM