Displaying value from another dimension

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

query_squidier@speakeasy.net - 20 Oct 2005 00:58 GMT

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({[Hotel]},
{[Geography]})} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]

I've tried several different things with the NonEmptyCrossJoin (among
others), please help. :)

Reply to this Message

Darren Gosbell - 20 Oct 2005 03:05 GMT

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].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE
(
[Measures].[CountUserReview]
)

Signature

Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

> Hi.
>
[quoted text clipped - 17 lines]
> I've tried several different things with the NonEmptyCrossJoin (among
> others), please help. :)

Reply to this Message

query_squidier@speakeasy.net - 20 Oct 2005 17:48 GMT

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. :)

Reply to this Message

query_squidier@speakeasy.net - 20 Oct 2005 18:06 GMT

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].CurrentMember'
--SET [Market] AS '[Geography].[Market].MEMBERS'
--MEMBER [Measures].[Market] AS '[Market].CurrentMember'

SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview], [Geography].[Market].CurrentMember} ON
COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])

Reply to this Message

Darren Gosbell - 24 Oct 2005 03:23 GMT

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.

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].CurrentMember.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])

Signature

Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Reply to this Message

query_squidier@speakeasy.net - 24 Oct 2005 19:53 GMT

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?

Reply to this Message

query_squidier@speakeasy.net - 24 Oct 2005 20:00 GMT

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].MEMBERS,
[Measures]. [CountUserReview], BDESC), 10,
[Measures].[CountUserReview])}, {[Geography].[Market].Members})} 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].[Market].Name
just returns "Market". :)

Thanks so much, Darren, I've got lightbulbs going off all over.

Reply to this Message

Darren Gosbell - 24 Oct 2005 23:48 GMT

> Thanks so much, Darren, I've got lightbulbs going off all over.

That's great to hear, glad I could help. :)

Signature

Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Reply to this Message