TM1 Forum

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

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

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

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

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

TM1 Forum

Using parameters in FilterByPattern

Using parameters in FilterByPattern

Re: Using parameters in FilterByPattern

Re: Using parameters in FilterByPattern

Re: Using parameters in FilterByPattern

Re: Using parameters in FilterByPattern