Hi,

I'm trying to get the following query working and its driving me mad.
Can someone please give me a hand.

Here is what I have which almost gives me what I want:

WITH
SET [Selection] AS '{[Product].[Product].Members}'
MEMBER [Product].[TOTAL] AS 'AGGREGATE([Selection])'
SET [ProductRecovery] AS '{
GENERATE(DESCENDANTS([Product].[ProductGroup].&[1],[Product].[Product]),{([RecoveryRate].[All
RecoveryRate],[RecoveryRateDiff].[100])}),
GENERATE(DESCENDANTS([Product].[ProductGroup].&[2],[Product].[Product]),{([RecoveryRate].[25],[RecoveryRateDiff].[All
RecoveryRateDiff])})}'
MEMBER [Measures].[Val1] AS '
SUM (
[ProductRecovery],[Measures].[Measure1]
)'

SELECT {Val1} ON COLUMNS,
NON EMPTY
CROSSJOIN({[Product].[TOTAL],[Selection]},[DEMeasure].[Measure].Members)
ON ROWS
FROM DefaultExposure
WHERE ([BusinessDate].[2005-07-04 00:00:00],
[IssuerIndustry].[Issuer].&[1380087])

The problem is the numbers it produces are wrong and I suspect that the
error is with the [ProductRecovery] set and how I use it.

Basically the intention is to select either a [recovery rate] or a
[recovery rate diff] (but not both) for each of the two productgroups
and then to sum over these values.

Unfortunately, it doesn't produce the right numbers so something is
going wrong.

In a nutshell my question is this:

If I have three separate dimensions (called A,B and X), how do I
aggregate in such a way that I either get [All A] and [a single Item
from B] or [a Single Item from A] and [All B] depending on
X.CurrentMember.

I expect this is really easy, but my background is SQL and I am still
getting my head round the MDX approach. Any help is really
appreciated.

If its not clear what I am getting at please tell me.

Thanks,
Seth

Hi Seth,

I can't quite map the MDX query to your (simpler) problem description,
so some sample data would help clarify this. My initial interpretation
is this MDX query:

SET [Selection] AS '{[Product].[Product].Members}'
MEMBER [Product].[TOTAL] AS 'SUM([Selection])',
SOLVE_ORDER = 5
MEMBER [Measures].[Val1] AS
'iif(IsAncestor(Product].[ProductGroup].&[1],
[Product].CurrentMember),
([RecoveryRate].[All RecoveryRate],
[RecoveryRateDiff].[100],
[ProductRecovery],[Measures].[Measure1]),
([RecoveryRate].[25],
[RecoveryRateDiff].[All RecoveryRateDiff],
[ProductRecovery],[Measures].[Measure1]))'

SELECT {Val1} ON COLUMNS,
NON EMPTY
CROSSJOIN({[Product].[TOTAL],[Selection]},[DEMeasure].[Measure].Members)
ON ROWS
FROM DefaultExposure
WHERE ([BusinessDate].[2005-07-04 00:00:00],
[IssuerIndustry].[Issuer].&[1380087])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***