Report with Monthly rows, Yearly columns & 3 pieces of data per mo

[magicdds]

I have a query with the following results:

Year Month AmountDue Fee Payments
2007 1
2007 2
2007 3
2007 4
2007 5 $800.00
2007 6 $1,080.00
2007 7 $150.00
2007 8 $350.00
2007 9 $350.00
2007 10 $350.00
2007 11 $350.00
2007 12 $350.00 $200.00
2008 1 $150.00
2008 2 $150.00 $200.00
2008 3 $150.00
2008 4 $150.00 $780.00
2008 5 $5,835.00 $1,792.00 $1,295.00
2008 6 $717.95 $52.95 $337.50
2008 7
2008 8
2008 9
2008 10
2008 11
2008 12

I want to use this query to create a report that shows:

Month 2007 2008

1 Amount Due 150
Fee
Payments
-------------------------------------------------
2 Amount Due 150
Fee
Payments 200
-------------------------------------------------
3 Amount Due 150
Fee
Payments
------------------------------------------------
4 Amount Due 150
Fee
Payments 780
------------------------------------------------
5 Amount Due 800 5835
Fee 1792
Payments 1295
------------------------------------------------
6 Amount Due 1080 717.95
Fee 52.95
Payments 337.50
------------------------------------------------
7 Amount Due 150
Fee
Payments
------------------------------------------------
etc........

But whatever I have tried, I get a report that prints out each month's data,
twice. I also can't get the data into two columns, one for 2007 and a second
column for 2008. I tried to make a 2007 and a 2008 subreport, which gave me 2
columns but printed each month twice.

Any ideas on how to accomplish the layout I'm looking for?

Thanks,
Mark

 

[Allen Browne]

A crosstab is the most obvious way to get each year into a column of its
own, but the issue here is that you want 3 values in the matrix: AmountDue,
Fee, and Payments.

For just the AmountDue, you would set up a crosstab with:
- Year as Column Heading (group by)
- Month as Row Heading (group by)
- AmountDue as Value (sum)

There is a technique for getting multiple value columns in a crosstab. See:
http://allenbrowne.com/ser-67.html#MultipleValues

Ultimately, you can then lay a report out the way you indicated based on
those 3 values per record.

 

[magicdds]

This is a little tricky but works really well.
Thanks for your help.

Mark