Querying MDX SET - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

Querying MDX SET

Author

Topic

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2013-08-06 : 08:47:24

Hi,

I have create a set by joining two dimension members say customer with his location country.

WITH
SET [COUNTRYLIST] AS
(([GEOGRAPHY].[COUNTRY].MEMBERS) * ([CUSTOMER].[NAME].[NAME].MEMBERS))

now I want to display in columns only the country with another measure from the cube.

What expression should I use to retrieve the country names from the set?

Thanks in advance.

- Varalakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 09:25:44

[code]
WITH
SET [COUNTRYLIST] AS
(([GEOGRAPHY].[COUNTRY].MEMBERS) * ([CUSTOMER].[NAME].[NAME].MEMBERS))

SELECT [Measures].[YourMeasure] ON 0,
[COUNTRYLIST] ON 1
FROM [CubeName]
[/code]
use NONEMPTY if you've to filter out empty rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2013-08-06 : 09:32:23

I want to display the country alone without customer from the above set. I used [COUNTRYLIST].item(0) but it brings only the first value of the customername.
I want to bring in all the countries here

- Varalakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 01:08:01

quote:
Originally posted by varalakshmi

I want to display the country alone without customer from the above set. I used [COUNTRYLIST].item(0) but it brings only the first value of the customername.
I want to bring in all the countries here

- Varalakshmi

then why do the cross join? wont this be enough?


SELECT [Measures].[YourMeasure] ON 0,
[GEOGRAPHY].[COUNTRY].MEMBERS ON 1
FROM [CubeName]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources