need some help on an cross tab report

22 Views

I am trying to create, what I think is a cross tab report. Here is the example I got from the client. It was done in Access 2000, using its reporting facility:

Page:  5               Yearly Reliable/Churchill Sales Summary
                               Report Date:12/19/2007

Product Name            Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sep    Oct    Nov    Dec     Total
_____________________ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______  ________
Year: 2006
*ELK RUN CHESAPK SUN    0.00   0.25   0.00   0.50   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00      0.75
ELK RUN ANNAPOLIS S     0.00   0.50   1.08   0.25   2.25   0.00   1.00   1.09   0.17   2.66   1.00   1.00     11.00
ELK RUN CAB FRANC       0.00   0.00   0.08   2.00   0.50   0.25   0.25   0.25   1.50   3.17   2.00   3.17     13.17
ELK RUN CHAMPAGNE       0.00   0.00   0.00   0.00   0.00   0.00   0.75   0.00   0.00   0.00   0.00   0.25      1.00
ELK RUN GEWURZTR        0.50   0.00   0.58   0.17   0.25   0.33   1.00   0.00   0.33   1.33   0.00   0.59      5.08
ELK RUN JBERG RIESLI    8.67   6.33   8.50   8.16  10.83   9.67   6.00  10.49   8.17  10.32  13.25   6.84    107.23
ELK RUN JUS GLACE(6)    0.33   0.00   0.00   0.17   1.17   0.00   1.33   0.00   0.34   0.67   0.00   1.66      5.67
ELK RUN KATH DESS CA    0.50   1.33   0.00   2.00   2.08   1.00   1.00   1.00   3.67   0.33   3.00   3.17     19.08
ELK RUN LORD PORT(6)    0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.50   0.00   0.00   1.00      1.50
ELK RUN MD CAB SAUV     0.00   2.00   1.08   0.25   0.33   0.50   3.58   2.00   1.00   3.58   0.17   0.50     14.99
ELK RUN MD CHARD        1.00   2.17   1.00   3.25   4.25   0.25   1.25   1.08   1.66   1.83   1.33   0.92     19.99
ELK RUN MD MERLOT       1.08   1.00   1.58   0.00   0.00   4.00   1.00   0.08   0.00   1.00   0.00   0.17      9.91
                      ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______  ________
      Type: O Total    12.08  13.58  13.90  16.75  21.66  16.00  17.16  15.99  17.34  24.89  20.75  19.27    209.37

*ELK RUN CHESAPK SUN    0.00   2.24   1.42   5.33   0.00   0.00   0.00   0.00   0.00   0.00  -0.17   0.00      8.82
ELK RUN ANNAPOLIS S     0.00   8.84   8.50  14.16  19.53   7.33  12.58  14.50  13.68  14.25  23.02  32.43    168.82
ELK RUN ANNAPOLIS SU   10.02   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00     10.02
ELK RUN CAB FRANC       0.76   0.34   1.84   0.50   0.34   1.58   0.75   1.00   0.51   3.76   1.51   3.34     16.23
ELK RUN CHAMPAGNE       0.00   0.00   0.00   0.00   0.00   0.17   0.00   0.00   0.00   0.00   0.00   0.00      0.17
ELK RUN CHESAPK SUNS    3.09   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00      3.09
ELK RUN GEWURZTR        0.59   1.08   0.83   3.34   4.76   2.41   3.17   1.01   3.60   7.17   6.69  11.69     46.34
ELK RUN JBERG RIESLI   14.17   9.27  11.91  15.24  12.26  11.44  11.84   9.58  14.76  15.92  28.55  30.85    185.79
ELK RUN JUS GLACE(6)   12.98  10.00   8.49   8.16   8.31   9.32   5.16   6.48   8.18  14.64  20.49  31.15    143.36
ELK RUN KATH DESS CA   26.86  57.46  40.52  48.95  43.20  33.88  39.63  44.76  34.53  50.08  85.44 111.68    616.99
ELK RUN LORD PORT(6)    0.00   2.00   0.67   1.16   1.17   2.50   0.33   1.00   1.33   2.50   0.83   7.15     20.64
ELK RUN MD CAB SAUV     4.76   2.17   1.33   1.93   2.10   3.34   1.09   1.23   3.01   2.76   4.51   6.26     34.49
ELK RUN MD CHARD        7.26   3.93   2.69   5.60  15.15   5.84   5.49   3.57   5.50   7.36  10.43  16.27     89.09
ELK RUN MD MERLOT       0.84   1.17   2.08   1.00   1.09   1.34   0.84   1.00   1.75   2.17   0.67   2.59     16.54
                      ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______  ________
      Type: R Total    81.33  98.50  80.28 105.37 107.91  79.15  80.88  84.13  86.85 120.61 181.97 253.41  1,360.39

Year: 2006 Total       93.41 112.08  94.18 122.12 129.57  95.15  98.04 100.12 104.19 145.50 202.72 272.68  1,569.76

What has to happen is that the data will be first grouped by years. Each year's worth of data will be displayed as shown above. Next, the data will be grouped by what is called LicenseType, which can be either "O", "F", "M", "A". Finally, the data is grouped by the product, which is shown on the left side of each line. Each month's data is actually a summary of all sales for that product, for that month. Now, I have tried using the cross tab reports and it does work correctly if I only select one year's worth of data. A Problem occurs when there is more than one year's data selected. When I run this report, I get everything that you see above, but, I also get the additional year's data being displayed across the top, mixed in with the other year's worth of data.

Product Name            May Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sep    Oct    Nov    Dec     Total
_____________________ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______  ________
Year: 2006
*ELK RUN CHESAPK SUN  1.23   0.00   0.25   0.00   0.50   0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00      0.75

That "May" data is really from the other year, not 2006 in this example. I need to have the report break on year and for each page, only that year's worth if data should be displayed.

In the cross tab expert, I have the following columns selected in the Rows list:

Sales.ReportPeriod (grouped by year)

Cust.LicenseType

Items.ProdDescr

In the summarized fields, I have the following columns selected:

Sum of Sales.CaseSales

finally in the Columns list I have the Sales.ReportPeriod selected and under the cross tab options dialog, I have the column will be printed for each month selected. I really need some help with this as I have a lots of this type of reports to create. Thank you.

Ed Cohen