Chris Umbaugh (10/12/2011)
Are you looking for the sales ON the last day of each month or the sales for each month AS OF the last day of each month (including all of the days in that month)?
I know its a few months later, just revisiting this.
Thanks for pointing that out Chris.
If you want the sales ON the last day of the month then that is the query I've shown in the previous post.
If you want the sales for the whole month it's pretty much the most basic looking MDX query you could imagine.
SELECT
NON EMPTY
(
[Measures].[Sales Amount]
)
ON COLUMNS,
NON EMPTY
(
[Date].[Calendar].[Month]
)
ON ROWS
FROM [Adventure Works]
It's not going to give you end of month dates, it will just show the month. This will be the total for the month although if the cube includes the current month it will show the partial total to date for that current month.
If you want to display the date of the last day of the month and the total for the month in the other column I'm not sure how to do that. It doesn't really make sense from a cube point of view to do that anyway given how slicing & hierarchies work. A dataset like that is ambigous. It would imply that it's the sales ON the last day of the month.
If you really want month totals next to last day of month it's really just a matter of formatting and display which you could achieve in the reporting solution.