Distinct Sum for my column

Hi,

Bonjour,

I want distinct sum for one of my column.But iam not able to do that.

I tried DISTINCTSUM function given inMSDN, but it always return ZERO.

My function call in FOOTER section is called first, before my DETAILS section function call.

please help me for this.

thanks and regards

Hemant

You need to add an expression to your detail cells in the column, that expression should call a custom (code) function that records all unique values into an array:

=Code.AddUniqueNumber(myField)

then in your total just call another function that sums the array you built:

=Code.SumUniqueNumbers()

this is roughly what your code should look like:

Code Snippet

dim myArray() as Integer

public function AddUniqueNumber(Byval newNumber as integer) as integer

AddUniqueNumber = newNumber

dim i as integer

for i = lbound(myArray) to ubound(myArray)

'if this array element equals the number then it isn't unique

if myArray(i) = newNumber then exit sub

next

'increase the size of the array

redim preserve myArray(ubound(myArray) + 1)

'add the new unique number to it

myArray(ubound(myArray)) = newNumber

end function

public function SumUniqueNumbers() as Integer

dim sum as integer

dim i as integer

for i = lbound(myArray) to ubound(myArray)

sum = sum + myArray(i)

next i

SumUniqueNumbers = sum

end function

Note that this code is purely of the top of my head, my VBA is rusty, and it is UNTESTED and will have syntax errors. But it gives you an indication of how to do it. You will also need to initialise your array, probably by passing the rownum in as a parameter as well, and if the rownum = 1 then reinitialise the array.

|||

hi,

thanks for the reply.

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

-thanks and rgeards

Hemant

|||

HemantC wrote:

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

Then you are doing something wrong.... the code concept does work, i have used it in the past.

The columns in a report are evaluated left to right, top to bottom, so if your array was zero then maybe you have one of these things wrong:

- you have not inserted a call to add a value to the array in the detail rows (or you put the call in the wrong place)

- you are making the call correctly but not adding the new value to the array like you should

- you are reinitialising the array on every call, instead of on just the first row of the table

- you are not looping through the array correctly to sum it

- there is an error in the code and you are showing a zero instead of #ERROR

What i have found helpful in the past is to write the code in the macro editor of Excel, along with a test function that calls it, then once it is performing correctly i insert the code into the report.

|||

Hi,

Thanks again.

I deleted my table and again created new one.But my footer function is called first and then my details section.

For debugging i just put a messagebox, which shows that first footer function is called.

thanks and regards

Hemant.

|||Do you mean the page footer or do you mean the subtotal on a table?

If it is the former, then try referencing the code from a hidden text box in the page body, and then refer to the hidden text box from the footer using the "reportitems" collection|||

Its in footer.

I also tried in hidden textbox.

But we cant access textbox of details section in Footer section.It gives error.

thanks

Hemant

|||

this is my code

Public orderIDs As System.Collections.Hashtable
Public total As Double

Public function CalculateSum(ByVal orderID As Object, ByVal freight As Object) As Double

If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
CalculateSum = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
CalculateSum = total
End If
End Function

Public function SumUniqueNumbers() as Integer
System.Windows.Forms.MessageBox.Show("toto")
dim sum as integer
Dim myDE As System.Collections. DictionaryEntry

For Each myDE In orderIDs

sum =sum +myDE.Value
Next myDE
SumUniqueNumbers = sum
end function

|||workaround for that error
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1903450&SiteID=17|||

hi,

i have tried this but we cannot acess the textbox present in details section in footer.

this is the error

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

hemant