Months on columns

Question

Hi, I want to create one report in which I want to show all the months on columns which I can do by using Descendants function but just consider if I pass 10th Nov 2011 then report should show months from Jan2011 till Nov2011 but in Nov column, it should show SUM (aggreation) of only 10 day data and DEC should show NULL. Can someone is aware how to write MDX for the same.

Answer 1

Not that I'm a big proponent of subselects but this is one of those cases where a subselect is useful. For example, here is how a query would be written to accomplish something similar in the Adventure Works database.

SELECT	Descendants (
		[Date].[Calendar].[Calendar Year].&[2007],
		[Date].[Calendar].[Month]
	) ON COLUMNS,
	{
		[Measures].[Internet Sales Amount] 
	} ON ROWS
FROM	(
		SELECT	{
				NULL : [Date].[Calendar].[Date].&[20071112]
			} ON COLUMNS
		FROM	[Adventure Works]
	)

HTH, Martin

---

http://martinmason.wordpress.com