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
Create Dynamic Subset with User Input
-
- MVP
- Posts: 3128
- 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
Hello,
If the periods rollup into the year, this could work:
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
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
- PavoGa
- MVP
- Posts: 618
- 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
This should work as well: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
Code: Select all
sDim = 'Periods';
sMDX = EXPAND(' TM1FILTERBYPATTERN( TM1FILTERBYLEVEL( TM1SUBSETALL( [Periods] ), 0), "%pPeriod%*")');
SubsetCreatebyMDX( srcSub, sMDX) ;
ViewSubsetAssign( srcCube, srcView, sDim, srcSub );
Ty
Cleveland, TN
Cleveland, TN
-
- 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
This worked! Thanks for the help!