Evaluting result set using MDX

[RajChinna]

Hi,
I constructed a very basic MDX Query that generates a cellset/result set containing the member unique names for the 4 gender members ([Gender].[All Gender].[F],[Gender].[All Gender].[M],[Gender].[All Gender].[F],[Gender].[All Gender].[F]) in the Foodmart 2000 Sales cube:

WITH
Member [Measures].[Test] as'[Gender].currentmember.uniquename'
SELECT
{[Measures].[Test]} on COLUMNS,
{[Gender].[All Gender].[F],[Gender].[All Gender].[M],[Gender].[All Gender].[F],[Gender].[All Gender].[F]} on ROWS
FROM [Sales]

This query produces the following cellset/result set:

Test
F [Gender].[All Gender].[F]
M [Gender].[All Gender].[M]
F [Gender].[All Gender].[F]
F [Gender].[All Gender].[F]

Currently we are iterating through the cellset object to determine whether or not a fact data value in the [Measures].[Test] dimension is repeated (as you can see the 3rd row and 4th row values are repeated). Please assume that we don't know and therefore can't test the members of the Gender dimension prior to query execution.

Does anybody know of a MDX method that would allow us to reference the previous fact data value in the cellset/result set (as opposed to the cube - prevmember does not work here) and to determine whether it repeats without having to itereate through the entire cellset object?

For example, is there a MDX method (possibly using iif clause) to test whether the 2nd row fact data value ([Gender].[All Gender].[M]) = the 3rd row fact data value ([Gender].[All Gender].[F]) in the cellset/result set?

Any help on this matter would be GREATLY APPRECIATED!!

Thanks and regards,
Raj C.

 

[justins57]

Raj C.

I don't know if this is what you're looking for, but you can use the INTERSECT function like this to do it.

WITH
Member [Measures].[Test] as'[Gender].currentmember.uniquename'
SET TestSet As '{[Gender].[All Gender].[F],[Gender].[All Gender].[M],[Gender].[All Gender].[F],[Gender].[All Gender].[F]}'
SET RowSet As 'Intersect(TestSet, [Gender].[All Gender].Children)'
SELECT
{[Measures].[Test]} on COLUMNS,
RowSet on ROWS
FROM [Sales]

Justin