Subtotal - row level by text field Cross Tab

[jlr123]

I have a cross tab report where {AR_InvoiceHistoryHeader.CustomerNo}+" "+{AR_Customer.CustomerName}+ " "+{AR_InvoiceHistoryHeader.ShipToCity}+{AR_InvoiceHistoryHeader.ShipToState}+" - "+{AR_Customer.SalespersonNo} is my main row. I want to subtotal all columns (which is the summary of AR_InvoiceHistoryDetail.ExtensionAmt )by left ({AR_InvoiceHistoryHeader.CustomerNo},4)

Can you please tell me how to accomplish this? attaching view of cross tab.
Thanks.

 

[SkipVought]

Hi,

Assuming that {AR_InvoiceHistoryHeader.CustomerNo} is always numeric characters, if CustomerNo is a text field, then you'd do better using an Integer function rather than the 4 left-most characters, since, I noticed, that the first CustomerNo in your example has 5 Integer characters (10573.00).

I would venture to guess that CustomerNo in its database form in your ERP system, has no decimal values, and those have been added by this report to differentiate the CityState values within a CustomerNo in this report. If you have access to the data the report was using, then, I'd guess, you'd have CustomerNo alone to use to subtotal.

This is a drawback to using reports as a source for further reporting. These thing become much harder to extract as you get a report of a report of....ad nauseam.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

The customer no is numeric and the customer numbers in our ERP has decimal point because we have a Parent and sub accounts. I want to total the extension amount by the so called "parent" in the cross tab. Is that possible?

 

[lbass]

Add your formula: left(custno,4) as your first (higher order row in the crosstab.

-LB

 

[jlr123]

Wonderful. Thanks.