Use function to add member to set

[RajChinna]

Hi,
I have created the following MDX query that queries the Foodmart 2000 Sales Cube:

WITH
SET [Test1] as' ({[Gender].[All Gender].[F],[Gender].[All Gender].[M],[Gender].[All Gender].[F]}*{[Marital Status].members})'
SELECT
{[Measures].[Unit Sales]} on COLUMNS,
{[Test1]} on ROWS
FROM [Sales]

I would like to add the member "[Gender].[All Gender]" to the set [Test1] set WITHOUT ACTUALLY EDITING/MODIFYING the set notation in the with clause. This should require use of a some MDX function.

Please note that:

1. We know prior to executing query what dimensions are included in the set [Test1] -- Gender and Marital Status

2. We need to preserve the repeating member ("[Gender].[All Gender].[F]") in the Gender dimension.

We tried using the union and extract functions (see syntax below) but we were unable to preserve the repeating member present in the original query:

WITH
SET [Test1] as' ({[Gender].[All Gender].[F],[Gender].[All Gender].[M],[Gender].[All Gender].[F]}*{[Marital Status].members})'
SELECT
{[Measures].[Unit Sales]} on COLUMNS,
(Union({Extract({[Test1]},[Gender])},{[Gender].[All Gender]}) * {Extract({[Test1]},[Marital Status])}) on ROWS
FROM [Sales]

ANY HELP ON THIS MATTER WOULD BE GREATLY APPRECIATED!!

Thanks,
Raj C.

 

[justins57]

Raj C.

I think this does what you are looking for:

WITH
SET [Test1] as' {[Gender].[All Gender].[F],[Gender].[All Gender].[M],[Gender].[All Gender].[F]}'
SELECT
{[Measures].[Unit Sales]} on COLUMNS,
CROSSJOIN(UNION({[Gender].[All Gender]},Test1,ALL),{[Marital Status].Members}) on ROWS
FROM [Sales]

Justin