I've got a sales per working day spreadsheet that pentaho generates every morning for my boss, days of the month on the columns and branches on the rows.
The problem is that with the query he was using originally, the columns expanded a lot, like this sample on the steel-wheels database:
select Descendants([Time], [Time].[Months], SELF_AND_BEFORE) ON COLUMNS,
[Markets].Children ON ROWS
from [SteelWheels Sales] where [Measures].[Sales]
His original approach was to use 12 subreports - one for each month, but I'd personally rather stab myself than to go through there. I tried to solve going through another route, by "crosstabing" months and days (on my steelwheels sample, years and months).
I added a new "utility dimension" in my schema, containing only the months:
By using this dimension, I can modify my query to the following:
select [Time2].Children ON COLUMNS,And get a much more legible result, either to analyze on the pivot table or to put in a report
([Markets].Children * [Time].Children) ON ROWS
from [SteelWheelsSales] where [Measures].[Sales]