Cross tab report formatting help

[danikuper]

Hello!

I need to create a report that compares 2002, 2003 and 2004 data by month by week (defined as groupings of 7 days).

I've created a cross-tab query with the data I need but am having trouble with the report... can someone help?

See attached what I mean. The cross_tab_report.jpg is the end result I'm looking for while the cross_tab_query.jpg is the data I have.

I'm not even sure how to start... grouping levels, sorting, etc.?

Thanks so much in advance!

daniel

 

[Pat Hartman]

You need to order the crosstab by month, year rather than year, month to get it to look like the report sample. Then for the report just add a group by the month field.

 

[danikuper]

Thanks!

Easier than I thought it would be.

 

[danikuper]

Not yet done

Oops! It seems I got ahead of myself... another question related to this report:

Is there a way I can add yearly totals at the end of the report? something like:

Year...Total
2002...$$$$
2003...$$$$
2004...$$$$

If I use a calculated field and have in the control source =sum([total]) (where total is the sum for each month calculated in the cross tab query), I get a total $ for the three years combined, which is not what I want.

Is there any way around that? Would I have to redo my query?

thanks!
daniel

 

[danikuper]

Forget about it... I solved the problem by using a sub-report based on another query that gives me the totals per year.