How to do this...in mdx query?

In a MDX query how to create a new member within the same dimension. The following is my MDX query:

OLAP cube: AP Statistics by Cancer Centre

Dimension: DIM_Fiscal_Year

Attribute: Fiscal Year

Attribute: Fiscal Year Full

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[FY] AS '[DIM_Fiscal_Year].[Fiscal Year Full].CURRENTMEMBER.MEMBER_CAPTION'

SELECT {[Measures].[FY], [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {ORDER({FILTER([DIM_Fiscal_Year].[Fiscal Year].MEMBERS,[Measures].[ParameterLevel]=1)},([Measures].[ParameterCaption]), DESC)} ON ROWS FROM [AP Statistics by Cancer Centre]

New member is in Red, It returns the value "All" for the entire "FY" column.

2008 All 2008 [DIM_Fiscal_Year].[Fiscal Year].&[2008] 1

2007 All 2007 [DIM_Fiscal_Year].[Fiscal Year].&[2007] 1

2006 All 2006 [DIM_Fiscal_Year].[Fiscal Year].&[2006] 1

Thanks

Could you explain the structure of [DIM_Fiscal_Year] with examples - and is any relationship defined between the [Fiscal Year] and [Fiscal Year Full] attributes? From the results above, it looks like [Fiscal Year Full] is not related to [Fiscal Year].

|||

The reason I have the "fiscal_year" and "fiscal_year_full" is because 'fiscal_year" is a 4 digit year of the fiscal year and was used to create the "Time" Dimension and fiscal_year_full is the full description of the fiscal year. The following is an example:

fiscal year.........2007

fiscal year full....2006-07

I use the fiscal year full to display in the report.

|||

In that case, if you relate "fiscal year full" to "fiscal year" via an attribute relationship (while removing "fiscal year full" from its existing attribute relationship), then the appropriate "fiscal year full" member should be selected when you select a "fiscal year" member. One way to do this would be to drag "fiscal year full" under "fiscal year", as described below:

SQL Server 2005 Books Online

Defining and Configuring an Attribute Relationship

...

You can create an attribute relationship between any two attributes in a dimension. With the Attributes pane of Dimension Designer set to tree view, drag the attribute that you want to relate to another attribute onto the <new attribute relationship> field under the attribute.

...

How to do QTD and MTD in MDX ?

I want to use the setup as used in the "A different approach to to Time Calculations"

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

But i still need Month to date and Quarter to date, as sepperate members in the Time calculation dimension (i know i can use the periods to date). As with the other members in the dimension this shall apply to every meassure that might be in the cube.

Can someone give an example of how to do it in MDX ?

I advise you to use AS built-in Time Intelligence Wizard. It uses Time calculation attribute rather than Time calculation dimension, but the expressions for MTD and YTD will be the same.|||

The YTD looks like this:

-- YTD CALCULATIONS

([Time Calculations].[YTD]=

Aggregate(

{[Time Calculations].&[Current Period]} *

PeriodsToDate([Time].[Calendar Hierarchy].[Year],

[Time].[Calendar Hierarchy].CurrentMember

)

));

But the MTD and QTD shall only be visible when time is on monthlevel or daylevel (for QTD) and daylevel (for MTD)

can you give an example ?

|||Hi,

I have QTD example, it work fine.

// Quarter to Date
(
[Time].[Current_QTD].[Quarter to Date],
[Time].[Quarter].[Quarter].Members,
[Time].[Date].Members
) =

Aggregate(
{ [Time].[Current_QTD].DefaultMember } *
PeriodsToDate(
[Time].[Year - Quarter - Month - Date].[Quarter],
[Time].[Year - Quarter - Month - Date].CurrentMember
)
);

Hope this work for you.