Hi,

How can i display member properties instead of members.

For example,

Select {[Measures].[Price]} On Columns,

{[Product].AllMembers} On Rows

From [Products]

displays the product names in columns. But i want to display "Product
Description" which is a member property of Product INSTEAD of name.

Can this be done?


Any help will be greatly appreciated.

Thanks
Mahesh


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

Hi Mahesh,

Many MS OLAP client tools (including Excel 2003) can optionally display
Member Properties, using the "PROPERTIES" option in the axis
specification of an MDX query:

http://msdn2.microsoft.com/en-us/library/ms144780.aspx
Using the PROPERTIES Keyword

The PROPERTIES keyword specifies the member properties that are to be
used for a given axis dimension. The PROPERTIES keyword is buried within
the <axis specification> clause of the MDX SELECT statement:

SELECT [<axis_specification>
[, <axis_specification>...]]
FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

The <axis_specification> clause includes an optional <dim_props> clause,
as shown in the following syntax:

<axis_specification> ::= <set> [<dim_props>] ON <axis_name>
...
Another option is to create a calculated measure, like:

'[Product].Properties("ProductDescription")'

Select {[Measures].[ProductDescr],
[Measures].[Price]} On Columns,
{[Product].Members} On Rows
From [Products]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Just to add onto Deepak's answer, if you need to create a set from your
member properties, you can always use the undocumented function:

CREATEPROPERTYSET(<<Member>>,<<Level>>,<<Property> >)

e.g.
CREATEPROPERTYSET([Stores].[Stats].[Production],[Stores].[Stats].[Production].Children,[Stores].CurrentMember.Properties("Ref
No"))

should build you a set from all the different entries under the member
property...

Hi Deepak and Unknown user,

Thanks for the response.

I am still trying to make the fist method work.

The second method (using calcualted measures) works but does not sove my
problem. It shows the product description IN ADDITION to product name,
but i want to show only product description and no product name.

Thanks
Mahesh

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

Hi,

The results from following two MDX is same: both of them are not showing
member properties.

1.
SELECT {[Measures].[Sales]} ON COLUMNS,
[Products].Members DIMENSION PROPERTIES [ProductsDate].[Description] ON
ROWS ......

2.
SELECT {[Measures].[Sales]} ON COLUMNS,
{[Products].Members} ON ROWS ......

The first MDX is not giving any error message but not showing
Description.

Any help will be greatly appreciated.

Thanks
Mahesh

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

I have seen the same... Interestingly it works in query builder that
comes with Reporting Services SSRS 2005.

Mahesh Shrestha wrote:

Hi Mahesh,

I often display member properties both on rows and on columns in MDX
statements. Here is the way to do it.

MEMBER [Measures].[ProductDescr] AS
'[Product].CurrentMember.Properties("ProductDescription")'

Now you can put this measure anywhere you wish to. I hope this helps!



"Mahesh Shrestha" wrote:

I have seen the same... Interestingly it works in query builder that
comes with Reporting Services SSRS 2005.

Mahesh Shrestha wrote:

Unfortunately it is upto the individual client tool as to how they treat
dimension properties. If you are using the default MDX sample
application, you will not see them. Earlier versions of Excel would not
display them either.

I think that even when they do display they will normally display in
addition to the member name as they are "attached" to the member. I
don't know of any tools that will let you display a property instead of
the member name.

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

In article <eWHBTnA$FHA.2036 (AT) TK2MSFTNGP14 (DOT) phx.gbl>, maheshnp (AT) hotmail (DOT) com
says...