CODEWITHSUNDEEP
sql-serverssasmdx
whytheq
whytheq

Reputation: 35557

Return custom date members Month

If I run the following: 

WITH MEMBER [Date].[Date - Calendar Month].[Last Completed Month] AS
     IIF( Day(Now()) = 1,
         TAIL([Date].[Date - Calendar Month].[Calendar Month],1)(0),
         TAIL([Date].[Date - Calendar Month].[Calendar Month],2)(0)
    )
SELECT
       NON EMPTY
          [Date].[Date - Calendar Month].[Last Completed Month]
       ON COLUMNS
FROM  [OurCube]
WHERE ([Measures].[NumUsers])

It returns this:

enter image description here

Is it possible to change the script so that instead of using "Last Completed Month" as the column header it uses the actual month "June 2013" ?

Upvotes: 2

Views: 118

Answers (2)

revoua
revoua

Reputation: 2059

declare @cur_date   varchar(20), @linked_server varchar(20) = 'YourLinkedServer'
set @cur_date=datename(M,GETDATE()) + ' '+ datename(YYYY,GETDATE())
declare @mdx_query varchar(max) = 
'WITH MEMBER [Date].[Date - Calendar Month].[Last Completed Month] AS
     IIF( Day(Now()) = 1,
         TAIL([Date].[Date - Calendar Month].[Calendar Month],1)(0),
         TAIL([Date].[Date - Calendar Month].[Calendar Month],2)(0)
    )
SELECT
       NON EMPTY
   [Date].[Date - Calendar Month].[Last Completed Month] as ''' + @cur_date + '''
       ON COLUMNS
FROM  [OurCube]
WHERE ([Measures].[NumUsers])'

set @mdx_query='Select * from openrowset('''+@linked_server+''','''+@mdx_query+''')'
EXEC sp_executesql @mdx_query

As for pure MDX solution, there is a way.

Upvotes: 0

Marc Polizzi
Marc Polizzi

Reputation: 9375

Do not think this is possible; the CAPTION property does not seem to accept an MDX expression.

WITH MEMBER XX as 42, CAPTION = 'Answer'
SELECT XX ...

Depending on how the MDX is used you can add a calculated member displaying the name

WITH 
  MEMBER [LCM Value] as  (...)
  MEMBER [LCM Name ] as  (...).name

SELECT
 { [LCM Value], [LCM Name] } on 0
...

Upvotes: 1

Related Questions