Using parameters in FilterByPattern

Post Reply
vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Using parameters in FilterByPattern

Post by vasek1192 » Thu Feb 11, 2021 7:41 pm

Hi All,

I am attempting to use parameter value (pVersion) in filters to create a dynamic subsets in temporary view. I have no idea how to do this. This is my meager attempt, that as expected did not work. Any advice would be appreciated.

Code: Select all

sVersionFilter ='{TM1FILTERBYPATTERN( {TM1SUBSETALL( [V_Version]   )}, pVersion)}';
For broader context: I have a cube consisting of 3 dimensions: V_Version (v1 - v12), T_Cas_M (Months 1-12) and M_Sales. I am attempting to export specific version using AsciiOutput. The idea is to prompt users to enter parametr pVersion and then use that value to filter source view. The whole code is as follows:

Prolog

Code: Select all

sSourceCube = 'Sales';
sSourceView = 'TMP_Sales';
sDimVersion = 'V_Version';
sDimCas = 'T_Cas_M';
sDimSales = 'M_Sales';
nTempFlag = 1;

OutputFile1 = 'D:\TM1SHARE\08_Outputs\07_ECP2_KONSOLIDACE\Sales.txt';
OutputFile2 = 'D:\TM1SHARE\08_Outputs\07_ECP2_KONSOLIDACE\Sales_Months_Inc.txt';

#___________________Zdrojové_View_________________;
# Vytvoří filtery pro subsety - leaf elements
sVersionFilter ='{TM1FILTERBYPATTERN( {TM1SUBSETALL( [V_Version]   )}, "pVersion")}';
sCasFilter ='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [T_Cas_M]   )}, 0)}';
sSalesFilter ='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [M_Sales]   )}, 0)}';

# Vytvoří subsety pro view pomocí vytvořených filterů
SubsetCreateByMDX(sSourceView, sVersionFilter, nTempFlag);
SubsetCreateByMDX(sSourceView, sCasFilter, nTempFlag);
SubsetCreateByMDX(sSourceView, sSalesFilter, nTempFlag);

# Vytvoří Zdrojové View
ViewCreate(sSourceCube, sSourceView, nTempFlag);
ViewSubsetAssign(sSourceCube, sSourceView, sDimVersion, sSourceView);
ViewSubsetAssign(sSourceCube, sSourceView, sDimCas, sSourceView);
ViewSubsetAssign(sSourceCube, sSourceView, sDimSales, sSourceView);

# Přiřadí pravidla pro View
ViewExtractSkipZeroesSet(sSourceCube, sSourceView, 1);
ViewExtractSkipCalcsSet(sSourceCube, sSourceView, 1);
ViewExtractSkipRuleValuesSet( sSourceCube, sSourceView, 1 );

DataSourceNameForServer = sSourceCube;
DataSourceNameForClient = sSourceCube;
DatasourceCubeview = sSourceView;
DataSourceType = 'VIEW';
Data:

Code: Select all

AsciiOutput(OutputFile1, vsVersion, vsSales, Value);
AsciiOutput(OutputFile2, vsVersion, vsMonth, vsSales, Value);

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Using parameters in FilterByPattern

Post by Wim Gielis » Thu Feb 11, 2021 8:27 pm

Hello,

You have 2 options:

Code: Select all

sVersionFilter = Expand( '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [V_Version]   )}, "%pVersion%")}');
or:

Code: Select all

sVersionFilter = '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [V_Version]   )}, "' | pVersion | '")}';
Expand, with variables between % signs.
Or chopping up the string in various pieces, and concatenating again. Avoid single quotes around variables or they will become fixed text instead of variable.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

burnstripe
Posts: 15
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Using parameters in FilterByPattern

Post by burnstripe » Thu Feb 11, 2021 9:03 pm

Also you could avoid the need the speech marks entirely and just have this.

sVersionFilter = '{[V_Version].[' | pVersion | ']}';

No need to for filter by pattern if you already have the full name and it's just a single element you want.

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Using parameters in FilterByPattern

Post by Wim Gielis » Thu Feb 11, 2021 9:06 pm

burnstripe wrote:
Thu Feb 11, 2021 9:03 pm
Also you could avoid the need the speech marks entirely and just have this.

sVersionFilter = '{[V_Version].[' | pVersion | ']}';

No need to for filter by pattern if you already have the full name and it's just a single element you want.
Exactly !
Or simply, SubsetCreate( ... ) and SubsetElementInsert( ... ) since it's only 1 element (although it's variable, it's still 1 element).

Vasek1992: can I ask to go through other topics first ? This kind of syntax can be found in other topics too and you learn how to search for the answer rather than getting it delivered to you on a plate.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Using parameters in FilterByPattern

Post by vasek1192 » Thu Feb 11, 2021 10:33 pm

Thank you very much, it worked perfectly. I did try to find the solution in existing topics and through google too, but did not succeed. I used keyword: filterbypattern, parameters, variables with no luck. But I do admit, I gave it about 30 minutes and then moved to the other tasks at hand. It is 23:32 pm here and still not bloody finished.

Post Reply