Create Dynamic Subset with User Input

Post Reply
jrock
Posts: 4
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 » 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

Wim Gielis
MVP
Posts: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Create Dynamic Subset with User Input

Post by Wim Gielis » Fri Jun 29, 2018 7:05 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
PavoGa
Community Contributor
Posts: 227
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Create Dynamic Subset with User Input

Post by PavoGa » Fri Jun 29, 2018 12:37 pm

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: 4
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 » Fri Jun 29, 2018 5:52 pm

This worked! Thanks for the help!

Post Reply