Move a formula up a level?!

[DeviousDi]

Hi,

I have a formula that I'm using in a group header, (customer number) to basically get the correct sum of a group of invoices. This is formula:

if Count ({invoices.invoice_number}, {invoices.invoice_number}) >= 2 then
Sum ({invoices.amount_open}, {@Customer}) / Count ({invoices.invoice_number}, {invoices.invoice_number}) else
Sum ({invoices.amount_open}, {@Customer})

I'm doing this because I can have duplicate, or more invoice numbers creating two or more lines of detail, and I only want to add the invoice amount once. I've basically grouped on the invoice number and copied the fields into to group header.

My grouping is like so:

G4 - Invoice number
G3 - Customer
G2 - Dept
G1 - Branch

My question is, how do I insert this formula into the next level, dept, so that I get correct 'sums' on the invoice amount for the dept group? It won't let me add my formula field, GF3AmountOpen because its summarised.

It coming out with some wonderful figures, but thats because of the duplicate lines for the invoice!!

Any help would be appreciated!

Thanks

Di

 

[IanWaterman]

You can use a Running Total which only evaluates once for each Invoice number.

However, RTs like variables can only be used in Report/Group Footers as they must evaluae data before displaying.

The other posssibility is to use a command and eliminate the duplicates in SQL, however, that will require you to redesign report as you cannot map from a report composed of tables to a single command object.

Ian

 

[DeviousDi]

Hi Ian,

Thanks, worked a treat!

Can I also ask, would this work that same in a cross tab? As in that only one invoice would be counted, summed etc at a time?

Thanks

Di

 

[IanWaterman]

Di

It is not possible to use RTs in a cross tab. You can do distinct counts but not Distinct Sums.

You will have to build a manual cross tab and use Rts in that.

If you are not familiar with Manual Cross Tabs just search this forum there have been many postings on the subject. They are not dynamic so you need to know column headers in advance.

Ian