I want to get properties, but I get an error:

SELECT {[Subs Count]} On Columns, {[Zone Id].properties("Zone Name")} On
Rows
from [Consumer]


I guess the syntax is wrong

ok, i got it. You gotta do something like this:

WITH SET CardTypes AS 'CreatePropertySet([Gender], [Zone Id].[Zone
Id].Members,
[Zone Id].CurrentMember.Properties
("Zone Name"))'
SELECT {[Subs Count]} ON COLUMNS,
CardTypes ON ROWS
from [Consumer]


Any easier way?



"Cindy Lee" <cindylee (AT) hotmail (DOT) com> wrote

Depends on what you want to do with the properties.

Your syntax with the CreatePropertySet() is a very powerfull, undocumented
one that creates a set based on all distinct values for a member property,
and maybe this is exactly what you want.

If you simply want to display your property's values for each member in a
report, you can use a calculated member such as :

WITH Member Measures.ZoneName AS '[Zone Id].CurrentMember.Properties("Zone
Name")'
SELECT {Measures.[ZoneName], Measures.[Subs Count]} On Columns, {[Zone
Id].[Zone Id].Members} On Rows
from [Consumer]

Also, notice that member properties are not sent to the client by default
when executing a query; but you can force them by explicitly specifying the
properties you want in your result set, using the [DIMENSION] PROPERTIES
keyword on the axis specification:

SELECT {[Subs Count]} ON COLUMNS,
{[Zone Id].[Zone Id].Members} PROPERTIES [Zone Id].[Zone Id].[Zone Name] ON
ROWS
from [Consumer]

The way the properties values are displayed in this case depends on the
client tool you are using; in MDX sample app, you can view the properties'
values by double clicking on the row headers after executing the query,
whereas they can be displayed directly as part of a report in MS Excel
2K2/2K3 PivotTables.

HTH

Olivier.


"Cindy Lee" <cindylee (AT) hotmail (DOT) com> wrote

Look at Russ Whitney's SQL Server Magazine article on Member Properties from
last August:
http://www.winnetmag.com/Article/Art...384/39384.html

It is one of my favorites. If he doesn't do something like this directly,
I'd bet that you can derive a hint on how to do it from one of his examples.


--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Olivier Matrat" <olivier.matrat_nospam (AT) winsight (DOT) fr> wrote