Crystal 9 Crosstab - Can't get rid of a column 2

[shaleen7]

I created a crosstab in Crystal 9:
Select Statement:
My Select statement asks for all issued applications that are still active. So we could have issued applications as old as the year 1984.

Grouping Crosstab
I then created a crosstab in which I've created 4 groups. Every app falls into one of the groups.


In my crosstab, the 1st quarter total represents all apps issued to date and each month shows how many apps have been issued for that specific month.
Current Results:
04-Jan 04-Feb 04-Mar Others 1st Quar. Total
TV 1104 411 408 4506 6429
DVD 659 368 345 3796 5168
VCR 377 185 286 2868 3716
CD 494 381 334 2814 4023
Total 2634 1345 1373 13984 19336

The problem:
However I keep getting this "others" column. The other column seems to represents all the issued apps that don't fall into the months of Jan, Feb or Mar 2004. When I discard the column my 1st Quarter Totals change and just adds up the first 3 months. Is there anyway I can hide this "others" column but maintain my first quarter totals.

Desired Results:
04-Jan 04-Feb 04-Mar 1st Quar. Total
TV 1104 411 408 6429
DVD 659 368 345 5168
VCR 377 185 286 3716
CD 494 381 334 4023
Total 2634 1345 1373 19336

 

[shaleen7]

I tried to suppress the data down to the first three months so I used the following formula.
If Month ({VTABBRE.datmaj}) =1 and Year({VTABBRE.datmaj}) =2004 then "JAN-04" else
If Month ({VTABBRE.datmaj}) =2 and Year({VTABBRE.datmaj}) =2004 then "FEB-04" else
If Month ({VTABBRE.datmaj}) =3 and Year({VTABBRE.datmaj}) =2004 then "MAR-04" else ""

However still have that extra column that I want to suppress yet I need the data from that column for the final total.

I'm stumped.

 

[lbass]

Jan04, Feb04, Mar04 ARE the only members of the 1st Qtr, so why would you want other dates to appear in the total for that quarter? Don't you really want the total to reflect the sum of the first three columns?

If so, go to group options and choose specified order on your column formula, and after entering each of the three months, choose the "Others" tab and check "discard all others".

I'm second guessing you here, so I might be off-base, but then you should explain what the total column should represent if NOT the sum of the first three columns.

-LB

 

[shaleen7]

I'm trying to get a running total for each month and finally come up with a total at the end.


Current Results:
04-Jan 04-Feb 04-Mar Others 1st Quar. Total
TV 1104 411 408 4506 6429
DVD 659 368 345 3796 5168
VCR 377 185 286 2868 3716
CD 494 381 334 2814 4023
Total 2634 1345 1373 13984 19336


Desired Results:
04-Jan 04-Feb 04-Mar 1st Quar. Total
TV 1104 411 408 6429
DVD 659 368 345 5168
VCR 377 185 286 3716
CD 494 381 334 4023
Total 2634 1345 1373 19336

If I discard "others" the values decrease. At this point my values are right. It's just that I don't want the other column to show.

Any suggestions?

 

[lbass]

Please note that if you were using your earlier formula, there would BE no other values in the first quarter--so you must have left out some other conditional criteria?

Anyway, to suppress the column, first go into format crosstab->customize style->select the column field->and then in the section "Alias for Formulas" enter a name for the field, e.g., "Month." Then, in the main report select the summary field (sum of {table.amt}), right click on it->format field->common->suppress->x+2 and enter:

GridRowColumnValue ("Month")) = "Other"

Then select the "Other" column heading->format field->common->suppress->x+2 and enter:

CurrentFieldValue = "Other"

This assumes that the formula you are using as your column field has "Other" as the default.

-LB

 

[shaleen7]

It worked!!!! Thanks.The values are gone.

One more question??? Now I have an empty grid space between the last month and the Total column. Is there anyway to reduce the width of the Other column.

 

[lbass]

I'm not aware of a way to do that in 8.0, but I'm not familiar with higher versions. I think you might have to do a manual crosstab to get rid of the space. You would group on {table.electronicequipment} (the field containing these products), and then create three formulas:

//{@Jan-04} to be placed in the detail section:
If Month ({VTABBRE.datmaj}) =1 and Year({VTABBRE.datmaj}) =2004 then {table.amt}//or if this is a count, replace {table.amt} with 1

//{@Feb-04}:
If Month ({VTABBRE.datmaj}) = 2 and Year({VTABBRE.datmaj}) = 2004 then {table.amt}

//{@Mar-04}:
If Month ({VTABBRE.datmaj}) = 3 and Year({VTABBRE.datmaj}) = 2004 then {table.amt}

If you have a record selection formula limiting records to the first quarter, then for the total, you would just place the field {table.amt} in the details section.

Then you would right click on each formula and the {table.amt} field and insert a summary(SUM) on each. If you are doing counts, then for the formulas, you would still insert a SUM (not a count), but you would insert a count on the field in the total column.

Then suppress the details section.

-LB

 

[shaleen7]

Thanks for your help.