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
