Greetings,

This question is about how to achieve the desired labeling in the results of
an MDX query, via the ExecuteCellSet method

of AdomdCommand object (.NET v2) working against an Analysis Services 2005
cube (SP1).

I am asked to run an MDX query and then to export the results to an Excel
spreadsheet for further processing. Two

dimensions have been selected along the columns and the time dimension is
wanted down the rows, with a couple of measures

for data.

For the time dimension, although we have data down to date granularity, this
takes too long to compute, so we wish to get

just the year and month levels. For the measures of interest, the value for
the last day of the month is that for the

month as a whole.

Assuming just the one measure (M1), the layout of the resulting cellset
looks like:

All All All Africa Africa Africa Asia Asia Asia etc
All Male Female All Male Female All Male Female etc
M1 M1 M1 M1 M1 M1 M1 M1 M1 etc
2002
January
February
March
April
May
June
July
August
September
October
November
December
2003
January
February
March
April
May
June
etc

The syntax for the rows above is:

{[Dim Time].[Calendar Year - Month - Date].Members - [Dim Time].[Calendar
Year - Month - Date].[Date].Members} ON ROWS

What is wanted is:

All All All Africa Africa Africa Asia Asia Asia etc
All Male Female All Male Female All Male Female etc
M1 M1 M1 M1 M1 M1 M1 M1 M1 etc

31/01/2002
28/02/2002
31/03/2002
30/04/2002

....

31/12/2003
31/01/2003
28/02/2003

etc

... ie the row label is the value of the LastChild member name for the
month being presented.

I can easily avoid having the year in the first layout by using a simpler
syntax:

{[Dim Time].[Calendar Year - Month - Date].[Month].Members} ON ROWS

But I can't figure out how to get the member's LastChild name (eg
31/01/2002) and use that instead of the month name as a

row label. Is there a way to do this? Most grateful for any suggestions.

With thanks and regards

Sebastian Crewe

You could get the lastchild members by doing something like:

GENERATE({[Dim Time].[Calendar Year - Month - Date].[Month].Members},
{[Dim Time].[Calendar Year - Month - Date].CurrentMember.LastChild) ON
ROWS

But this would also get you the daily values, not the monthly values. To
get the month value you would need to create a calculated measure to
replace "M1" in order to get the value from the month level.

eg.

WITH
MEMBER Measures.M1Monthly AS (Measures.M1,[Dim Time].[Calendar Year -
Month - Date].CurrentMember.Parent)



--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <CAC2396A-B7A7-4327-876F-D10DBD82A9DB (AT) microsoft (DOT) com>,
SebastianCrewe (AT) discussions (DOT) microsoft.com says...

You are what's known in the trade as an Absolute Star. That is precisely
what I was looking for. I even had some documentation to hand about the
Generate function, but couldn't figure out the syntax to use for the second
set. This will be useful now and in the future.

In fact, since the month figure is the same as the last day's figure, in
this case, your first example does the job precisely. The second example
will be useful in other cases, I'm sure.

A million thanks for your very quick response and for taking care with the
question so as to respond so accurately and helpfully.

Best regards

Sebastian

"Darren Gosbell" wrote: