Crosstab Query

[shaleen7]

I used the cross tab expert to obtain a running total but I want to suppress some of the data so that only the months in 2003 appear.

Right now I have this:

For each month since 1983, the number of cars sold by a dealership.

1/83 2/83 3/83 4/83 ........1/03 7/03 8/03 9/03
2 5 7 15 27 47 53 59

I want to have this instead:
1/03 7/03 8/03 9/03
27 47 53 59

Any suggestions? Can this be done without a formula? I'm new.

 

[lbass]

Why not just limit the dates in your record selection formula? You could use something like:

year({table.date}) = 2003

If you need the other years in your report for some reason, but only want to include 2003 within the crosstab, then you could use a formula like the following for your column:

if Month({table.date}) = 1 and Year({table.date}) = 2003 then "January 2003" else
|
|
|
if Month({table.date}) = 12 and Year({table.date}) = 2003
then "December 2003" else ""

Then click on this column field->group options->specified order and use the dropdown list to select the months. Then click on the "Others" tab and choose "discard all others."

Another approach would be to group on year({table.date}) in your main report, place the crosstab in the group footer or header and then suppress the section (format section->group footer->suppress->x+2 and enter: year({table.date}) <> 2003.

-LB

 

[shaleen7]

Brilliant the 2nd suggestion worked. Thanks!!!!!!!