TM1 Forum

Posted: **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

Posted: **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 );

Posted: **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.

Posted: **Fri Jun 29, 2018 5:52 pm**

This worked! Thanks for the help!

TM1 Forum

Create Dynamic Subset with User Input

Create Dynamic Subset with User Input

Re: Create Dynamic Subset with User Input

Re: Create Dynamic Subset with User Input

Re: Create Dynamic Subset with User Input