Summing Some Rows

[Former Member]

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 · Accepted solution]

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

[Former Member]

Hi,

As you said you have GT (Group Totals), please insert a group on 'Customer' and insert all the fields on your group header, now it takes only one (frist) record in that group. This will eliminate duplicate records and duplicate amounts.

Now you try to insert a summary on the 1st group (other than 'Customer') to get group wise summary.

Also you said you want to show all summaries on Report Header, this is possible only when you insert a sub report

Thanks,

Sastry

[Former Member]

Sorry. By "GT" I meant "Grand Total"

[Former Member]

if you have duplicated values and place them in a group the summary wizard will still sum the dup values

in order to get an accurate value use the following formulas

and if you place the data in the group footer and surpress the header it will still display at the top level

MANUAL RUNNING TOTALS

RESET

The reset formula is placed in a group header report header to reset the summary to zero for each unique record it groups by.

whileprintingrecords;

Numbervar X := 0;

CALCULATION

The calculation is placed adjacent to the field or formula that is being calculated.

(if there are duplicate values; create a group on the field that is being calculated on. If there are not duplicate records, the detail section is used.

whileprintingrecords;

Numbervar X := x + ; ( or formula)

DISPLAY

The display is the sum of what is being calculated. This is placed in a group, page or report footer. (generally placed in the group footer of the group header where the reset is placed.)

whileprintingrecords;

Numbervar X;

X

[Former Member]

The problem with your solution (which I've already tried) is that the results are required to be put in the footers. I need a solution which will allow me to put the result in the header.

[former_member11696]

Because it's a total after the fact you'll need to create a subreport to get the data. Put the subreport in the header. Means you'll have to run the query twice but no way around it. You could try using a command object or possibly, and I don't recommend putting SELECT statement in SQL Expressions, but it is one option.

Don