Hi,

I'm using Analysis Services on a SQLServer 2000 on Windows 2003.

My dimension 'accounts' has more than 250.000 members, so it contains
automatically created member groups (intervals) of accounts. In one of my
cubes, I've got a calculated measure which returns a boolean/bit value. This
measure is intended as an indicator and works fine so far.

Now I'd like to find the accounts where this indicator measure is TRUE/1. My
MDX works, but returns the affected account groups. That's no help; I need
the distinct account numbers where the indicator measure ist set. How can I
'expand' the groups into the distinct accounts that have an indicator value
of 1?

My MDX:
SELECT
{[Measures].[Amount]} ON COLUMNS,
{Filter(Descendants([Accounts].AllMembers,0,LEAVES), [Measures].[Indicator]
= 1)} ON ROWS
FROM
MyCube
WHERE
[Date].[All Dates].[2006-01-01 00:00:00]

Thx
Martin

I think your issue revolves around the way you are calling the
descedants function.

Instead of:

, [Measures].[Indicator] = 1)}
I think the following will give you the results that you are after:

, [Measures].[Indicator] = 1)}
What is happening here is that you are passing in the AllMembers
function which returns every member from every level and when you pass
in an integer in the second argument it returns the member(s) from the
first argument. Using the top level "All" member as the first argument
and leaving the second argument blank should give you the results you
are after.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <D9FBCF8B-6B8A-41BC-B2C8-261083680F0C (AT) microsoft (DOT) com>,
Atrus2711 (AT) discussions (DOT) microsoft.com says...

Thank you Darren,

it works, you made my day! :-)

Regards
Martin