Returning attribute value for MDX statement

Post Reply
tm1dev
Posts: 4
Joined: Fri Aug 30, 2019 3:14 pm
OLAP Product: Planning Analytics
Version: Planning Analytics 2.x
Excel Version: 2016

Returning attribute value for MDX statement

Post by tm1dev »

Hello. I am trying to return my Time dimension's "Month Status" attribute value in the results of this MDX statement:

SELECT non empty {[Scenario].[Scenario].[Budget]} on 0,
non empty
{TM1FILTERBYLEVEL(TM1SUBSETALL([Supplements].[Supplements]) , 0)}
* {TM1FILTERBYLEVEL(TM1SUBSETALL([Time].[Time]) , 0)}
DIMENSION PROPERTIES [Time].[Time].[Month Status]
on 1
from [Programs] where ([Currency].[Currency].[USD], [Programs Measure].[Programs Measure].[Total Revenue])

However I only get 3 columns returning data for Supplements, Time and Scenario...I was expecting a 4th column for the Time dimension's Month Status string attribute. Is something wrong with my syntax?
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Returning attribute value for MDX statement

Post by declanr »

If your interface is PAW or PAX you could just right click on the time dimension in the view and select show attributes. That is the quick GUI friendly way to show what you want.

If you want it to be part of the view that will work in other interfaces also then I would do it by using a MEMBER calculation.

Code: Select all

WITH MEMBER [Scenario].[Scenario].[Month Status]
AS [Time].[Time].CurrentMember.Properties("Month Status")
SELECT non empty {UNION( {[Scenario].[Scenario].[Budget]}, {[Scenario].[Scenario].[Month Status]} )} on 0,
non empty
{TM1FILTERBYLEVEL(TM1SUBSETALL([Supplements].[Supplements]) , 0)}
* {TM1FILTERBYLEVEL(TM1SUBSETALL([Time].[Time]) , 0)} on 1
from [Programs] where ([Currency].[Currency].[USD], [Programs Measure].[Programs Measure].[Total Revenue])
Declan Rodger
tm1dev
Posts: 4
Joined: Fri Aug 30, 2019 3:14 pm
OLAP Product: Planning Analytics
Version: Planning Analytics 2.x
Excel Version: 2016

Re: Returning attribute value for MDX statement

Post by tm1dev »

Thank you declanr, that worked! I am using the Rest API to return data with this MDx statement.
Post Reply