# How to do SUM(Distinct(Field1)) in Visual Studion report designer?? • ### 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

• 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))
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.

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
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
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 ??

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

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