Crosstab Display Columns but exclude from final total 2

[ReillyC]

I have a crosstab that works beautifully, ALMOST.
Here is the crosstab
US JP GB CA MX TOTAL

TV 2 9 0 3 0 14

DVD 3 12 5 0 3 23

VCR 0 6 4 2 0 12

I want to display CA and MX but include it in the totals.

US JP GB CA MX TOTAL

TV 2 9 0 3 0 14

DVD 3 12 5 0 3 20

VCR 0 6 4 2 0 12


Is there a way to change the crosstab or do I have to create a manual crosstab to accomplish what I need?

My problem is that I always have trouble with the alignment and sizing of the field boxes. My Manual crosstabs never look quite professional.

Any suggestions?

Thanks

 

[ReillyC]

Resubmitting post because I made an error in my first one.
I have a crosstab that works beautifully, ALMOST.
Here is the crosstab
US JP GB CA MX TOTAL

TV 2 9 0 3 0 14

DVD 3 12 5 0 3 23

VCR 0 6 4 2 0 12

I want to display CA and MX but exclude it in the totals.

US JP GB CA MX TOTAL

TV 2 9 0 3 0 14

DVD 3 12 5 0 3 20

VCR 0 6 4 2 0 12


Is there a way to change the crosstab or do I have to create a manual crosstab to accomplish what I need?

My problem is that I always have trouble with the alignment and sizing of the field boxes. My Manual crosstabs never look quite professional.

Any suggestions?

Thanks

 

[BobSuruncle]

One method would be to create a formula to separate the countries.
@Special Country
if {Customer.Country} in ['Canada','Mexico'] then "N/Am" else " Rest"
Use this formula as a column in the Cross-Tab Expert, making sure it's the Top Column.
Format the heading for this new column to suppress.
In the Customize Style tab of the Cross-Tab Expert, suppress row Grand Totals.
Format the Gridlines so that the gridlines surrounding the top columns are suppressed.

This is not quite what you have shown in your sample, as Canada and Mexico will show to the right of the Total column for USA, Japan, and Great Britain (and they'll have their own Total column), but I think it's the closest you'll come and still be able to use a real Cross-Tab.


Bob Suruncle

 

[ReillyC]

I tried your suggested solution. It didn't quite work the way I thought it would. This is what I got.
REST TOTAL
TV 10 10
DVD 35 35
VCR 20 20

Here is the formula I placed in the crosstab
if {VMIS_TABPAY.CODPAY} in ['EP','WO'] then "N/Am" else " Rest"

Unforunately, none of the countries were displayed not even Canada and Mexico at the end of the crosstab.



I expected to see the following based on your suggestion.
US JP GB TOTAL CA MX

TV 2 9 0 14 3 0

DVD 3 12 5 20 0 3

VCR 0 6 4 12 2 0

What did I do wrong?

 

[lbass]

You have to use the formula as your first column, but then you still have to add the country field as your second column.

-LB

 

[ReillyC]

Sorry for getting back so late!

Thank you lbass for the direction. It worked.
BobSuruncle, your solution is brilliant.

Cheers,
R