SQL crosstab query with multiple grouping levels

[greenythebeast]

I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.

The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)

The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query. I would appreciate any suggestions in how to construct the query. Please let me know if you need anymore information.

This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!

TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[plog]

Sub headings in your columns aren't possible with a Cross-Tab. My advice would be have this final report in Excel. You can do that with a pivot table.

 

[CJ_London]

or do two crosstab queries and then combine them - you would need to ensure both crosstabs have the same number of rows