Dynamic Named Set member in Measure!

[Json]

Guys,

I have an interesting problem - I'm using SSAS 2008 and the Adventure
Works DW database. I'll like to create a result set like this:

Internet Sales Amount Product 1
Product 2
Australia $2,154,284.88 Red Bikes Blue
Bikes
Canada $621,602.38 Red Car #5 Honda
France $514,942.01 Pogo Stick Jump Rope
Germany $521,230.85 Chalk Soda
United Kingdom $591,586.85 Toyota Ferrari
United States $2,126,696.55 Coke Pepsi

This would be where I can drag a "calculated measure" on to the scene
that has the top #1 Product sold and the top #2 Product sold. But I
also need it to be SMART, so that I can use Customer Names instead of
Country, or any other context that interacts with Internet Sales
Amount. Make sense?

My MDX is below - unfortuantely, all I get for Product 1 and Product 2
is "All" every time, not the specific products. :(

CREATE DYNAMIC SET [Adventure Works].[Top 5 Products] AS
'TOPCOUNT(NONEMPTY([Product].[Product Categories].
[Product].MEMBERS), 5, [Measures].[Internet Sales Amount])';

WITH MEMBER [Product 1] AS
'[Top 5 Products].Item(0).Hierarchy.CurrentMember.Name'
MEMBER [Product 2] AS
'[Top 5 Products].Item(1).Hierarchy.CurrentMember.Name'
SELECT
{[Measures].[Internet Sales Amount], [Product 1], [Product 2]}
ON 0,
{[Customer].[Customer Geography].[Country]}
ON 1
FROM
[Adventure Works]
WHERE {[Date].[Calendar Year].&[2002]}

DROP SET [Adventure Works].[Top 5 Products]

Thanks so much for your help!

Sincerely,

J'son

[dpuri]

Using a Dynamic Named Set won't help here, because it isn't re-evaluated
for every cell in the result set. Directly evaluating the Top 2 Products
seems to work:

>>
With
MEMBER [Product 1] AS
TOPCOUNT(
[Product].[Product Categories].[Product].MEMBERS,
1, [Measures].[Internet Sales Amount]).Item(0).Name
MEMBER [Product 2] AS
Tail(TOPCOUNT(
[Product].[Product Categories].[Product].MEMBERS,
2, [Measures].[Internet Sales Amount])).Item(0).Name

SELECT
{[Measures].[Internet Sales Amount], [Product 1], [Product 2]}
ON 0,
{[Customer].[Customer Geography].[Country]}
ON 1
FROM
[Adventure Works]
WHERE {[Date].[Calendar Year].&[2002]}

----------------------------------------------

Internet Sales Amount Product 1 Product 2

Australia $2,154,284.88 Road-150 Red, 56 Road-150 Red, 48
Canada $621,602.38 Road-150 Red, 48 Road-150 Red, 52
France $514,942.01 Road-150 Red, 44 Road-150 Red, 52
Germany $521,230.85 Road-150 Red, 44 Road-150 Red, 62
United Kingdom $591,586.85 Road-150 Red, 48 Road-150 Red, 52
United States $2,126,696.55 Road-150 Red, 48 Road-150 Red, 62
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

[Json]

Deepak,

You rock! This was exactly what I was looking for. :)

Thanks!

Sincerely,

J'son