I have a most basic question: how to correctly access properties (attributes) of dimension members that are at a different level. Here's a simple query from AdventureWorks to illustrate:
Code Snippet
select
[Customer].[Customer].members
dimension properties
[customer].[customer].[postal code],
[customer].[postal code].[city]
on 1,
[Measures].[Internet Sales Amount]
on 0
from [Adventure Works]
This query works, but the city property is left out of the results (postal code is provided). How can I get City, State-Province, Country, etc, as properties in a query like this? (As the relationships are all correctly defined, and the levels for these attributes/properties are less restrictive than the query level, I'm totally missing why this doesn't work in either the form given -- but do I have the name perhaps wrong?)
Seperately, I have read (somewhere) that property access is to be discouraged in SSAS05, instead prefering direct attribute access via UDM; is there a better way to write these sorts of queries, say including City, etc in the output grid area?
Thanks as always!
To somewhat answer my own query, I have found that:
Code Snippet
with member [measures].[cc] as [customer].[city].MemberValue + ', ' + [customer].[state-province].MemberValue
select
[Customer].[Customer].members
dimension properties
[customer].[customer].[postal code],
[customer].[postal code].[city]
on 1,
{[measures].[cc], [Measures].[Internet Sales Amount]}
on 0
from [Adventure Works]
properly returns the customer "city, state" in the output grid area. But I am still interested in how to obtain the city and state-province instead as properties of the customer members on axis(1).....
Thanks again!
No comments:
Post a Comment