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 this in OLAP report?

I am using SQL 2005 Reporting Services. I need to create an OLAP report. The suitation is similiar to a sub-query in Store Procedure. I am not sure how to do this in an OLAP dataset. The OLAP cube has been prepared using SQL 2005 AS. I believe that this report requires two datasets and it is based on the same OLAP cube. The report itself is based on the second dataset. The first dataset require the user to input two parameters. The second dataset uses two of the fields from the first dataset to filter out the second dataset. I can create the two datasets but I dont know how to assign the two parameters on the second dataset which is depends on the result of the first dataset. Any samples or steps or procedures using SQL 2005 RS would be great. Thanks a million.

If I am not mistaken, the first dataset is nothing but a query which contains two fields from the dimension that you will use it as a parameter and the second dataset is a query that should be filtered based on the two parameters.

So, first create the dataset which retrieve the two fields

then, click the layout tab and go to the report parameter dialog box and create the parameters, and make sure that the avialable values comes from the query (to do this, from the Available values column select the "From query" radio button and select the dataset name that you have created earlier.)

Finally, when you are creating the second dataset, filter your query based on those parameters.

I hope this will help.

Sincerely,

--Amde