Create Dynamic Subset with User Input

Post Reply
jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Create Dynamic Subset with User Input

Post by jrock »

Hi-

I am trying to export the YTD results from a cube with Periods defined as "2018-01", "2018-02", etc. with a input from the user of the Year desired. For example, the user would input "2018", and the export would be for all months of 2018.

As a part of my TI code, I know that the following works when I hardcode 2018:
sDim = 'Periods';
SubsetCreatebyMDX( srcSub, ' {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Periods] )}, 0)}, "2018*")} ') ;
ViewSubsetAssign( srcCube, srcView, sDim, srcSub );

I am attempting the following to create a dynamic subset of all the months of the year defined, but it errors out:
pPeriod = 2018 (as defined by the user)

SubsetCreatebyMDX( srcSub, ' {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Periods] )}, 0)}, {SUBST( [Periods], 1, 4) @= pPeriod)} } ') ;

I've searched the forum and downloaded the MDX primer, but it doesn't seem to give me what I need. Any help would be greatly appreciated.
Thanks,
J
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Create Dynamic Subset with User Input

Post by Wim Gielis »

Hello,

If the periods rollup into the year, this could work:

Code: Select all

sDim = 'Periods';
SubsetCreatebyMDX( srcSub, '{[' | sDim | '].[' | pPeriod | '].Children}') ;
ViewSubsetAssign( srcCube, srcView, sDim, srcSub );
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Create Dynamic Subset with User Input

Post by PavoGa »

jrock wrote: Thu Jun 28, 2018 11:46 pm Hi-

I am trying to export the YTD results from a cube with Periods defined as "2018-01", "2018-02", etc. with a input from the user of the Year desired. For example, the user would input "2018", and the export would be for all months of 2018.

As a part of my TI code, I know that the following works when I hardcode 2018:
sDim = 'Periods';
SubsetCreatebyMDX( srcSub, ' {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Periods] )}, 0)}, "2018*")} ') ;
ViewSubsetAssign( srcCube, srcView, sDim, srcSub );

I am attempting the following to create a dynamic subset of all the months of the year defined, but it errors out:
pPeriod = 2018 (as defined by the user)

SubsetCreatebyMDX( srcSub, ' {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Periods] )}, 0)}, {SUBST( [Periods], 1, 4) @= pPeriod)} } ') ;

I've searched the forum and downloaded the MDX primer, but it doesn't seem to give me what I need. Any help would be greatly appreciated.
Thanks,
J
This should work as well:

Code: Select all

sDim = 'Periods';
sMDX = EXPAND(' TM1FILTERBYPATTERN( TM1FILTERBYLEVEL( TM1SUBSETALL( [Periods] ), 0), "%pPeriod%*")');
  SubsetCreatebyMDX( srcSub, sMDX) ;
  ViewSubsetAssign( srcCube, srcView, sDim, srcSub );
Just a note: if the MDX function returns a set, the braces are not required. Personal choice, but I exclude them because I think the code looks cleaner. The braces are required if needing a set and the function or code returns a member.
Ty
Cleveland, TN
jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Re: Create Dynamic Subset with User Input

Post by jrock »

This worked! Thanks for the help!
Post Reply