cancel
Showing results for 
Search instead for 
Did you mean: 

Crosstab Total formula help

0 Kudos

There are two tables involved in the correct Total column. Currently the column total uses a formula to

summarize the ExtAmount in the InvoiceDetail table. Formula: {AR_InvoiceHistoryDetail.ExtensionAmt}

What I need is to add is the SalesTax, Discount and Freight amount found in the InvoiceHeader table.

If I modify the formula as shown.

{AR_InvoiceHistoryDetail.ExtensionAmt} + {AR_InvoiceHistoryHeader.FreightAmt}+ {AR_InvoiceHistoryHeader.SalesTaxAmt}+ {AR_InvoiceHistoryHeader.DiscountAmt}

Results below.

It looks like the header freight, tax and discount values are summarizing incorrectly. They need to be summarized by client, and it appears the calculation is way off.

How can I summarize the total line item amounts by customer invoice, then add the freight, sales tax and discount?

Then total those values in the crosstab.

Possible?

Any comments would help

Thanks for looking.

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Ido,

The extended amount (from item_detail) summarizes fine. It's when I add the other values to the total ExtAmount

formula the total is way off.

Still playing with it.

ido_millet
Active Contributor
0 Kudos

One option is to Group the report on Order ID. Then, create a running total on Extended Amount and set it to evaluate once per Order ID group and reset on Order ID group. Then use that running total in the CrossTab.

Another option is to use a VIEW or SP that return a single row for each order, providing the Extended Amount, instead of joining to the Order Details table.