MDX filter based on element value

Post Reply
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

MDX filter based on element value

Post by kenship »

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: 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: MDX filter based on element value

Post by declanr »

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
)}
Declan Rodger
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: MDX filter based on element value

Post by kenship »

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