Return custom date members Month

Question

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:

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" ?

Answer 1

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.