SubsetCreateByMDX in TI

[Willi]

Hello,

I have a prolem with creating a subset by TI. I use the following Subset:

{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {[Produkt].[Alle Produkte]}, ALL, RECURSIVE )}, "*/ 0011 /*")}

wich is perfectly working on the Subset_Editor and Returns not an empty subset. But if I try to execute the TI I get the message that the subset could not be created! I've no idea where to look at.

Oh, almost forgot: I also tried the Version with single Quotation-marks entered by "CHAR(39)" in the formula. Same result and the MDX copied from the log to the subset-Editor gave me the wanted correct result!

Thanks in advance,
Willi

[tomok]

Can't really help without the actual TI code.........

[Willi]

That's true. Sorry. I forgot to post the complete code.

This is in the Prolog-Tab:

vDimTmp = 'Produkt';
if (SubsetExists (vDimTmp, vSubTmp)=1, SubsetDestroy(vDimTmp, vSubTmp), 0);

And this is in the Data-Tab:

vAttrCognos = AttrS(vDim4,Produkt_MPL,'Cognos');
SubsetCreatebyMDX (vSubTmp, '{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {['|vDimTmp|'].[Alle Produkte]}, ALL, RECURSIVE )},
"*/ '|vAttrCognos|' /*")}');

The Variable "vAttrCognos" is correctly filled wich is proofed by the Error-Log.

[tomok]

You need to move your SubsetCreateByMDX code to the Metadata tab.

[Willi]

There it was before. Same result.

[declanr]

Use:

Code: Select all

SubsetCreatebyMDX (vSubTmp, '{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {['|vDimTmp|'].[Alle Produkte]}, ALL, RECURSIVE )},
"*/ '|vAttrCognos|' /*")}', vDimTmp);

To find out whether the MDX statement really does return any elements.

[Michel Zijlema]

SubsetCreateByMDX is a function that typically should not be used on either the MetaData or Data tab, which are executed per record/cell in the datasource.
So I would move the statement to either the Prolog or Epilog (if dependent on metadata changes) tab.

Michel

(Of course, if you really need to create multiple subsets, one per record/cell in the datasource, then the data tab should be OK (the vSubTmp variable should then be changing per record/cell too). In my statement above I was assuming this is not the case.)

[Willi]

Michel:

Thx, but I Need this Statement for every cell returned by the Datasource. So I Need to put it in the Metadata or Data Tab. The process is not run interactively by the user. So time is not the highest priority.

The subset is deleted after it's used in the TI.

declan:

Thx. This is not the soution but pointed me to my (!!!) error. I tested the MDX in the Subset-Editor with an Alias activated. This changes the filter dramatically. I now have to find the solution how to use the alias in aa MDX or Change the filter to use the Original Element-Names.

Thx to all.

[Wim Gielis]

I now have to find the solution how to use the alias in aa MDX or Change the filter to use the Original Element-Names.

The function SubsetAliasSet maybe?

[Willi]

Hm, but if I understand the documentation correctly I have to specify an already existing subset for this function. I want to create a new one.

[declanr]

Use the 3 parameter SubsetCreateByMDX function I put above so it creates your mdx subset although it will be empty. Then use subsetaliasset on that.

[rmackenzie]

Hm, but if I understand the documentation correctly I have to specify an already existing subset for this function. I want to create a new one.

Then just create the 'Alle Produkte' drilldown subset in the Prolog and refer to it in the Data tab, e.g.:

Code: Select all

# Prolog
# create 'base' subset
SubsetCreatebyMDX ( 'base_subset', ' {TM1DRILLDOWNMEMBER( {[Produkt].[Alle Produkte]}, ALL, RECURSIVE )}' );
# apply alias
SubsetAliasSet ( 'Produkt', 'base_subset', AttrS(vDim4,Produkt_MPL,'Cognos') );

# Data
vSubTmp = 'SOME NEW SUBSET NAME';
SubsetCreatebyMDX (vSubTmp, '{TM1FILTERBYPATTERN( {[Produkt].[base_subset]}, "*/ '|vAttrCognos|' /*")}');

Try it and see?

[Willi]

Thx for all you effort. It works!