Skip to Content
Former Member
May 13, 2011 at 04:50 PM

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?