MDX filter based on element value

Post Reply
kenship
Posts: 111
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

MDX filter based on element value

Post by kenship » Wed May 30, 2018 12:24 pm

Trying to perform MDX with filter without success.

I have a year dimension ("cbm_year"): From 1996 to 2050 all rolled up to "All Years".

The MDX needs to filter out all years before 2012. I thought it should be really simple:

{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
,[cbm_year] > 2012
)}

But I keep receiving a syntax error at or near "2012".

Appreciate any thoughts on this.

Thanks.

declanr
MVP
Posts: 1588
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: MDX filter based on element value

Post by declanr » Wed May 30, 2018 12:53 pm

You have a couple of issues here:
1/ [cbm_year] in the filter is something that it doesn't know what to do with - you would need to specify that it is the name from the currentmember you are wanting to use.
2/ Even then you would be comparing string with a number

You could easily do it if you had a numeric attribute containing the year by replacing [cbm_year] with [cbm_year].[myNumericAttribute] e.g.

Code: Select all

{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
,[cbm_year].[myNumericAttribute] > 2012
)}
Or if you don't have an attribute just convert the name to a number e.g.

Code: Select all

{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
, StrToValue ( [cbm_year].currentmember.name )> 2012
)}

kenship
Posts: 111
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: MDX filter based on element value

Post by kenship » Wed May 30, 2018 2:38 pm

This is perfect! I didn't know about currentmember.name.

Thank you very much!

declanr wrote:
Wed May 30, 2018 12:53 pm
You have a couple of issues here:
1/ [cbm_year] in the filter is something that it doesn't know what to do with - you would need to specify that it is the name from the currentmember you are wanting to use.
2/ Even then you would be comparing string with a number

You could easily do it if you had a numeric attribute containing the year by replacing [cbm_year] with [cbm_year].[myNumericAttribute] e.g.

Code: Select all

{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
,[cbm_year].[myNumericAttribute] > 2012
)}
Or if you don't have an attribute just convert the name to a number e.g.

Code: Select all

{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
, StrToValue ( [cbm_year].currentmember.name )> 2012
)}

Post Reply