Page 1 of 1
SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 2:06 pm
by 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
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 2:47 pm
by tomok
Can't really help without the actual TI code.........
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 3:12 pm
by 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.
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 3:16 pm
by tomok
You need to move your SubsetCreateByMDX code to the Metadata tab.
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 3:20 pm
by Willi
There it was before. Same result.
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 3:24 pm
by 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.
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 3:25 pm
by 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.)
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 3:32 pm
by 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.
Re: SubsetCreateByMDX in TI
Posted: Mon Aug 11, 2014 4:26 pm
by Wim Gielis
Willi wrote: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?
Re: SubsetCreateByMDX in TI
Posted: Tue Aug 12, 2014 7:14 am
by 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.
Re: SubsetCreateByMDX in TI
Posted: Tue Aug 12, 2014 7:45 am
by 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.
Re: SubsetCreateByMDX in TI
Posted: Tue Aug 12, 2014 8:01 am
by rmackenzie
Willi wrote: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?
Re: SubsetCreateByMDX in TI
Posted: Wed Aug 13, 2014 5:21 am
by Willi
Thx for all you effort. It works!