Displaying value from another dimension

Hi.
I'm relatively new to MDX and have what should be a simple problem to
solve.
I have a Hotel dimension and a hierarchial Geography
(Region/Market/SubMarket) dimension. There's a join between the two on
Geography_ID.
All I'm trying to do is display a few measures for the Hotel as well as
its Market value. I can't get it to display the Market.
SELECT
{[Measures].[CountUserReview], NonEmptyCrossJoin({[Hot
el]},
{[Geography]})} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
I've tried several different things with the NonEmptyCrossJoin (among
others), please help. A dimension can only appear on one axis, so I suspect your sample is
giving you a syntax error.
The following should give you the top 10 hotels in the rows, and the
markets in the columns, with the CountUserReview in each cell. Is this
the sort of result you are after? If not it would help to have a sample
of how you would like the output to appear.
SELECT
NON EMPTY {[Geography].Market.Members} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE
(
[Measures].[CountUserReview]
)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1129766281.949669.289920@.g43g2000cwa.googlegroups.com>,
query_squidier@.speakeasy.net says...
> Hi.
> I'm relatively new to MDX and have what should be a simple problem to
> solve.
> I have a Hotel dimension and a hierarchial Geography
> (Region/Market/SubMarket) dimension. There's a join between the two on
> Geography_ID.
> All I'm trying to do is display a few measures for the Hotel as well as
> its Market value. I can't get it to display the Market.
> SELECT
> {[Measures].[CountUserReview], NonEmptyCrossJoin({[H
otel]},
> {[Geography]})} ON COLUMNS,
> {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[C
ountUserReview],
> BDESC), 10, [Measures].[CountUserReview])} ON ROWS
> FROM [UserReview]
>
> I've tried several different things with the NonEmptyCrossJoin (among
> others), please help.
>|||Thanks for the response Darren and yes I was getting an error.
Each Hotel has only one Market value.
I'm hoping to display each Hotel name ON ROWS and the following ON
COLUMNS:
HotelID, HotelName, Market, and Count.
The trouble I'm having is figuring out how to display stuff from the
Hotel dimension and the Geography dimension both ON COLUMNS.
Thanks for your help, this is very very different than T-SQL. |||This is my latest attempt:
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
--MEMBER [Measures].[Market] AS '[Geography].[Market].Curren
tMember'
--SET [Market] AS '[Geography].[Market].MEMBERS'
--MEMBER [Measures].[Market] AS '[Market].CurrentMember'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview], [Geography].[Market].CurrentMe
mber} ON
COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])|||The problem is that your geography dimension needs to be given a context
by being placed on one of the axis or in the slicer (where clause)
The other thing you were doing was mixing members of different
dimensionality in a set on the columns.
In order to get the market for each hotel I have crossjoined the
geography dimension (using the * operator). This will generate every
market for every hotel and I then exclude the empty ones with the NON
EMPTY clause.
Note: I would have thought that the HotelName and Market measures were
redundant as these names should appear in the rows of the returned
cellset. As such I have commented them out of the query below, if the
names of your dimension members are different you can remove the comment
dashes to include these measures again.
[vbcol=seagreen]
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentM
ember.Name'
SELECT
{[Measures].[Hotel_ID]
--, [Measures].[HotelName]
, [Measures].[CountUserReview]
--, [Measures].[Market]
} ON COLUMNS,
NON EMPTY {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures
].
[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} * {[Geography].
[Market].Members} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])[vbcol=seagreen]
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Thank you very much, Darren. This helps bunches.
The only thing, though, I'm getting a syntax error near axis
definition: " * {[Geography]. [Market].Members}".
Thoughts?|||Got it.
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview]} ON COLUMNS,
{ NonEmptyCrossJoin ({TOPCOUNT(ORDER([Hotel].[Name].MEMBER
S,
[Measures]. [CountUserReview], BDESC), 10,
[Measures].[CountUserReview])}, {[Geography].[Market].M
embers})} ON
ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
The only other question I might have is if I did want to show the
Market value ON COLUMNS, how to do that. It didn't recognize
[Geography].[Market].CurrentMember.Name and [Geography].[Mar
ket].Name
just returns "Market".
Thanks so much, Darren, I've got lightbulbs going off all over.|||> Thanks so much, Darren, I've got lightbulbs going off all over.
That's great to hear, glad I could help.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell