Using parameters in FilterByPattern

[vasek1192]

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]

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.

[burnstripe]

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]

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.

[vasek1192]

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.