Report: Calculating Sum with duplicate values in Dataset

hornster

Hi!
Got a bit of a problem in a report on nav 2009 r2
Got a report where 2 data items (customer ledg entry and g/l entry) are grouped. They are grouped on document no. from customer ledger entry.
Here I got a simplified version of the table structure I'm currently using:

grp_header.|Doc_no|......|Sales_LCY.|Amount_LCY.
detail.....|......|Acc_No|..........|...........
tablefooter|......|......|Sum(Sales)|Sum(Amount)

Now, what I want (like I got it in the classic report) is to sum up the distinct values of Sales LCY and Amount LCY from the group headers. In RTC, however, the sum is obviously calculated through the dataset, and there I got several duplicate entries, since I got the a doc no entry for each acc no entry, and so the sum is calculated with all those duplicates.

One solution I found here(Darentan's post)

basically, I put the table into a list and use a custom function:

Public sum_of_salesLCY As Integer
Public Function SumUp(ByVal Value As Integer)
  sum_of_salesLCY = sum_of_salesLCY + Value
End Function

In a text field next to sales/amount lcy in the group header I use this function, with the value of the textfield containing sales lcy as parameter.
I'd then use the value of the sum_of_salesLCY variable in the sum text box in the footer, but I just get 0 as value. After some experimenting I found out that it actually works, but the problem is that the sum would be displayed in the next "group" of the list.
list group 1 -> sum = 0
list group 2 -> sum = actual sum of list group 1

so... thats my problem... is there any way to get this method to work?
Any other methods to correctly display/calculate the sum?

I appreciate any help I can get
Sorry if this mess of a text is a bit confusing.

delaghetto

I don't know if this will help you, but let me try.

When something gets difficult using SSRS Layout designer, what I do as a last resource solution is that I calculate in this case the SUM you are looking for in NAV and store it in a global variable. Then I just add this global variable to a column in my dataset and then I can easily use it in my report layout.

hornster

I thought about that as well, but as you said - last resort. Thanks though

clausl

From a performance point of view it is also better to do the SUM in NAV.

But notice that you can always place the scope at the end of the expression i.e "Sum(Fields!Amount.Value, "Scope").
This way you can be very specific on what sum you show, but you cannot show a Child Group Sum in a Parent Group.
So to me it looks like you want a ChildGroup Sum in the ParentGroup, in your case Tablix footer which is the whole dataset if not filtered.

/Claus Lundstrøm