Hello,

does anybody know how to retrieve the list of possible values for a
given property at a specified level ?

One of the problem I'm facing is that you can't use any of the
available Set function of the MDX language as you're dealing with
property values and not members !
After many attempts, I came up with the following syntax (based on
Foodmart that, I think, in theory should, work, but it actually
doesn't :

Any help is welcome, thank you...

Jean
-------------------------------------------------------------------------------

WITH
SET CUST_LIST1 AS 'Order( Head([Customers].[Name].Members, 20),
[Customers].CurrentMember.Properties ("Member Card"), BASC)'

MEMBER [Measures].Card as
'[Customers].CurrentMember.properties("Member Card")', SOLVE_ORDER =
20

MEMBER [Measures].PreviousInList as 'Rank( [Customers].CurrentMember,
CUST_LIST1)-2', SOLVE_ORDER = 30 MEMBER [Measures].LALA as 'SubSet(
CUST_LIST1, PreviousInList, 1).Item(0).properties("Name")',
SOLVE_ORDER = 40
MEMBER [Measures].PreviousInList_Card as 'SubSet( CUST_LIST1,
PreviousInList, 1).Item(0).properties("Member Card")', SOLVE_ORDER =
50
MEMBER [Measures].YesOrNo as 'Cstr([Measures].Card) <>
Cstr([Measures].PreviousInList_Card)' , SOLVE_ORDER = 60

SET CUST_LIST2 as 'filter(CUST_LIST1, ([Customers].CurrentMember,
[Measures].LALA) = 1)'

SELECT
{[Unit Sales], Card, PreviousInList, LALA, PreviousInList_Card,
YesOrNo } ON COLUMNS,
{ CUST_LIST2 } ON ROWS
FROM Sales

http://groups.google.com/groups?hl=e...ehI9EHEHA.3196
%40tk2msftngp13.phx.gbl
Subject: Re: Set of Member Properties
View: Complete Thread (3 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-04-06 20:22:05 PST

Maybe the (undocumented?) MDX function: CreateProptySet() can help you.
Here is a SQL Server Mag article:

http://www.winnetmag.com/SQLServer/A...302/16302.html
the real power of CreatePropertySet() is its ability to determine the
unique values of a member property within a group of dimension members.
If you needed to determine the unique member-property values and you
didn't have the CreatePropertySet() function, you would have to retrieve
the member-property values for all the relevant dimension members and
eliminate all the duplicates. This process can be very slow if you have
many dimension members. CreatePropertySet() quickly determines this list
of unique properties, but the rest of the execution can be
time-consuming. To take advantage of the function's speed, you need to
understand how Analysis Services processes MDX queries.
..

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thank you for your answer.

I'm familiar with the CreatePropertySet() function. However, I can't
use it for 2 reasons :
- CreatePropertySet( can only create up to 957 members !
- For performance reason, I could issue a "prepare" statement in order
to only retrieve the possible values for a property and avoid
aggregating data. The problem is that this will not be possible that
with XML for Analysis.

Jean-Christophe

In that case, here's a version of your query that runs:

SET CUST_LIST1 AS 'Order( Head([Customers].[Name].Members, 20),
[Customers].CurrentMember.Properties ("Member Card"), BASC)'

MEMBER [Measures].Card as
'[Customers].CurrentMember.properties("Member Card")'
MEMBER [Measures].PreviousInList as 'Rank( [Customers].CurrentMember,
CUST_LIST1)-2'
MEMBER [Measures].PreviousInList_Card as 'iif(PreviousInList < 0,
"None",
SubSet( CUST_LIST1,PreviousInList, 1).Item(0).properties("Member
Card"))'
MEMBER [Measures].YesOrNo as 'iif(PreviousInList < 0, 1,
iif(CStr([Measures].Card) <> Cstr([Measures].PreviousInList_Card), 1,
0))'

SET CUST_LIST2 as 'filter(CUST_LIST1, ([Customers].CurrentMember,
[Measures].YesOrNo) = 1)'

SELECT
{[Unit Sales], Card, PreviousInList, PreviousInList_Card,
YesOrNo } ON COLUMNS,
{ CUST_LIST2 } ON ROWS
FROM Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!