Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Summing Some Rows

How do I Sum some records?

I have a report which shows data something like this:

Customer  Job_No  Vendor  Vendor_Cost  Sale_Amt
12345678  123456  ABCDEF  10.50        123.45
12345678  123456  UVWXYZ  20.92        123.45
87654321  123456  LMNOPQ  12.45        645.23 

This happens because of a join in the query. There was no problem, until we started adding more than one vendor per job.

The issue is that there is a summary of Sale_Amt. When we had only a single vendor, the sum was correct. Now Sum is adding the Sale_Amt even if it's a duplicate (same customer and job_no).

At one point I also had an issue with counting jobs. I was doing "Count({view1.Job_No})", but was getting three (from the example above). I learned that I could do DistinctCount() which solved that problem. Now I need something akin to a DistinctSum().

Currently I suppress the Sale_Amt if it's a duplicate. Is there anyway to Sum only on non-suppressed fields?

Finally, our reports have the GT values in the report headers so that the customer can see right up front the important values. I tried a global variable, but it does not allow me to put the result in the report header (well, it allows me to put it there, but it shows 0.00).

I do understand that I could use a subreport to give me the values. However I would still have the issue with the duplicate sales amounts. Yes, I could create a seperate query trimming out the vendor infor, but I'd rather not have the added resources that I have to track and grant/restrict permissions. It ios an option, but the last one.

How do I fix this?

TIA

Former Member
replied

Hi Clif001,

I had a similar challenge to your request. I solved this by creating a Running Total Field that summarizes Sale_Amt on change of Customer.

If you have another group level like me, you can reset this "counter" on change on the higher level field.

Hope this helps.

Kind regards, Harry

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question