Create Dynamic Subset with User Input
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
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