MDX Question

Question

MDX Question

Paul Ibison

SSCertifiable

Points: 5303
More actions
June 21, 2002 at 6:11 am

#151705

Is it possible to alias member names? I have the following MDX:
select {[Measures].[Sales Units]} on columns,
{[Time].[Calendar].[Year].&[2000].&[1],
[Time].[Calendar].[Year].&[2001].&[1]}
on rows
from sales
It returns :
Sales Units
839
Quarter 1
3978
Basically, I want to be able to distinguish between the quarters. Surely this is possible? Any help welcome.
Paul Ibison
Paul.Ibison@blueyonder.co.uk

Paul Ibison
Paul.Ibison@replicationanswers.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Scot J Reagin SSC-Addicted Points: 468 More actions · Answer 1

You might consider changing the member name property in the dim definition to reflect the year and quarter. This would resolve your need here as well as make the UI more readable when the user slices on a single time member (by showing the year in the drop down list).

In the meantime, you could use the 'with' clause to create a member in the query:

with member [Time].[Calendar].[Q1, 2000] as '([Time].[Calendar].[Year].&[2000].&[1])'

select {[Measures].[Sales Units]} on columns,

{[Time].[Calendar].[Q1, 2000],

[Time].[Calendar].[Year].&[2001].&[1]}

on rows

from sales

Scot J Reagin

sreagin@aspirity.com

Scot J Reagin
sreagin@hitachiconsulting.com

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 2

Thanks Scott - am a bit of a novice as far as this type of mdx goes, but what you posted does the trick. Have adapted it to have both rows labelled as :

with member [Time].[Calendar].[Q1, 2000] as '([Time].[Calendar].[Year].&[2000].&[1])'

member [Time].[Calendar].[Q1, 2001] as '([Time].[Calendar].[Year].&[2001].&[1])'

select {[Measures].[Sales Units]} on columns,

{[Time].[Calendar].[Q1, 2000],

[Time].[Calendar].[Q1, 2001]}

on rows

from sales

Paul Ibison

Paul.Ibison@blueyonder.co.uk

Paul Ibison
Paul.Ibison@replicationanswers.com

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 3

Related to the first question, is it possible to make the query invariant on years: ie can you make this query always use the last two years, so in this current year it would be 2001 and 2002? This is a common question from delegates, and it would be really useful.