Search code examples
ssasmdxolap

Using GENERATE to concatenate Rank and Member Name

The following is from MSDN: http://msdn.microsoft.com/en-us/library/ms144726.aspx

WITH 
SET OrderedCities AS Order
   ([Geography].[City].[City].members
   , [Measures].[Reseller Sales Amount], BDESC
   )
MEMBER [Measures].[City Rank] AS Rank
   ([Geography].[City].CurrentMember, OrderedCities)
SELECT {[Measures].[City Rank],[Measures].[Reseller Sales Amount]}  ON 0 
,Order
   ([Geography].[City].[City].MEMBERS
   ,[City Rank], ASC)
    ON 1
FROM [Adventure Works]

Would it be possible to use the GENERATE function to concatenate the rank and the city into a single column. So results would look like the following:

enter image description here

EDIT

So the following must be getting closer:

WITH 
SET OrderedCities AS Order
   ([Geography].[City].[City].members
   , [Measures].[Reseller Sales Amount], BDESC
   )
MEMBER [Measures].[City Rank] AS Rank
   ([Geography].[City].CurrentMember, OrderedCities)

MEMBER [Measures].[memberName] AS
    '[Geography].[City].CurrentMember.name'
MEMBER [Measures].[memberValue] AS
    '[Measures].[City Rank].value'
MEMBER [Measures].[concat] AS  
//  [Measures].[memberName] + [Measures].[memberValue]//<<this errors
//  '[Measures].[memberName] & [Measures].[memberValue]'//<<this errors
    '[Measures].[memberName] + [Measures].[memberValue]'//<<this errors

SELECT 
    {[Measures].[City Rank],
    [Measures].[memberName],
    [Measures].[memberValue],
    [Measures].[concat],
    [Measures].[Reseller Sales Amount]}  ON 0 
,Order
   ([Geography].[City].[City].MEMBERS
   ,[City Rank], ASC)
    ON 1
FROM [Adventure Works]
Solution

  • the following should work:

      MEMBER [Measures].[concat] AS 
    cstr(RANK( [Geography].[City].currentmember, orderedcities)) +'.'+ 
    [Geography].[City].currentmember.name

    You will still have to use the presentation programm to get rid of the first redundant column.

    Philip,