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