MDX Filter By Current Member Value Range

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 By Current Member Value Range

Post by kenship »

Hi,

I'm trying to write a MDX filter statement to return years from 2018 to upcoming budget year (say 2024 for now).

This one works.

Code: Select all

vIndexDim = 1;
WHILE(TABDIM(vCube, vIndexDim) @<> '');
	vDimName = TABDIM(vCube, vIndexDim);
	SubsetDestroy(vDimName,vSubset);

	IF( vDimName @='Year');
	vMDX= '	{FILTER
			(
			TM1FILTERBYLEVEL (
					      TM1DRILLDOWNMEMBER( {[' | vDimName| '].[(ALL YEARS)]}, ALL, RECURSIVE)
					    ,0
					     )
			,StrToValue ( [' | vDimName| '].currentmember.name )>2017
			 AND
			 StrToValue ( [' | vDimName| '].currentmember.name )<= 2024
			)
		}';
ELSE;
	vMDX = '{TM1FILTERBYLEVEL( TM1SUBSETALL( [' | vDimName | ']), 0)}'; 

               ENDIF;
SubsetCreateByMDX(vSubset, vMDX);

	ViewSubsetAssign(vCube, vView, vDimName, vSubset);
	vIndexDim = vIndexDim +1;
END;
What I want to do is to change 2024 to become a variable, and I added:

Code: Select all

pCurrentBudgetYear = StringToNumber(CellGetS('Control','Current Budget Year', 'svalue'));
What I would like to know is how to change the upper range statement to replace 2024.

Code: Select all

StrToValue ( [' | vDimName| '].currentmember.name )<= 2024
Thanks!
Kenneth
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX Filter By Current Member Value Range

Post by MarenC »

Hi,

You haven't exactly explained what your issue is?

Have you tried adding in pCurrentBudgetYear to your logic, so something like the following:

Code: Select all

StrToValue ( [' | vDimName| '].currentmember.name )<=' | NumberToString( pCurrentBudgetYear )
Maybe you could just reference the control cube in the MDX as follows:

Code: Select all

{FILTER
			(
			TM1FILTERBYLEVEL (
					      TM1DRILLDOWNMEMBER( {[' | vDimName| '].[(ALL YEARS)]}, ALL, RECURSIVE)
					    ,0
					     )
			,VAL ( [' | vDimName| '].currentmember.name )>2017
			 AND
			 VAL ( [' | vDimName| '].currentmember.name )<= VAL( [Control].([Name of dim].[Current Budget Year],[Name of dim].[svalue]) )
			)
		}
I prefer using VAL to STRTOVALUE is situations like this.

Maren
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 By Current Member Value Range

Post by kenship »

It works already!

My earlier attempt resulted in wrong expression or ambiguity issue with other dimensions.

Thanks a lot!!
Post Reply