could not create dynamic subet Error

[maps]

Hello everyone,

I'm struggeling with creating a subset based on a MDX statement using a TI process. My process runs based on the elements of a dimension. For each element I like to create two different subsetsbymdx for differernt dimensions.

But when I run the process I get the error "..:Could not create dynamic subset" The error occurs when the second subset is created.

Code: Select all

newSubsetName = CellGetS( cCubeElementAttr, variableOfDim, 'Long Name (ID)');
#cDimName is Name of the first Dim I like to create a subset for
IF(SubsetExists(cDimName, newSubsetName) = 1);
  SubsetDestroy(cDimName, newSubsetName);
ENDIF;
#Create Subset for Kat4
tmpMDX = '{[Kat4].[' |  variableOfDim| '].Children}';

SubsetCreatebyMDX( newSubsetName, tmpMDX );

cName_2 is the Name of the first Dim I like to create a subset for
IF(SubsetExists(cDimName_2, newSubsetName) = 1);
 SubsetDestroy(cDimName, newSubsetName);
ENDIF;

# 34 = "
#39 = '
quoteChar = Char(34);

tmpMDX = '{FILTER( {TM1FILTERBYPATTERN( {[Kat2].[All Kat2].Children},' |  quoteChar | 'Kat2_EX*' | quoteChar  | ')}, [Kat2].[Cost Center] = ' | quoteChar  | 'Kat7_' | vKat4 | quoteChar | ')}';

Asciioutput( 'c:\temp\createsubsets.txt', getprocessName(), tmpMDX);
SubsetCreatebyMDX(newSubsetName , tmpMDX);

First I thought the mdx statement is wrong. So I did an asciioutput and tested the output in the expression window of the subset. Works fine (no error)...

Then I thought probably I should use a single quote instead of a double so I changed the quoteChar variable to 39. I also tested the mdx statement with single quotes in the dimension expression view. It also worked. So single and double quotes shouldn't make a difference. Still get the same error.

Then I was wondering that when I use the subsetcreate statement I have to add the dimension name as a parameter but the subsetcreatebymdx doesn't use this parameter. Where does tm1 know to which dimension the subset has to be applied? That's why I changed the name of the second subset. But I still get the same error.

I really hope that I just missed something... because I don't know how to proceed. Has anyone a idea?

[Duncan P]

TM1 executes the MDX to get a set of elements and from that discovers the dimension to which to apply the subset. If however the MDX returns no elements then it has nothing from which to get the information.

All you can do is put an element in the dimension which you know will be returned by the MDX. You can do this dynamically before creating the subset and then remove it afterwards if you don't want it. Once the subset has been created it doesn't matter if it is empty or not.

To check whether this is what is causing your problem go into the subset editor on your dimension and enter the MDX in the expression window. If there are no elements showing then that is your problem.

[lotsaram]

I guess TM1 just knows the dimension that the subset is for by virtue of the dimension referred to in the MDX ...

On your error, if you are sure there is no syntax error what about does the expression actually return any members? TM1 won't let you create a null subset (although once the subset is created if it subsequently evaluates as having no members this doesn't seem to cause an issue, except in views where the subset might be used ...) If this is the cause of your error then the trick is to insert an element into the dimension that you know will pass the filter criteria in the MDX, then create the subset which will work as it is guaranteed to have at least one member, then delete the artificial element just created. It is a bit of an ugly work around but one that works.

[beaten to it]

[maps]

Thank you sooooooo much for your help. this was exactly the problem. The subset of the mdx statement was empty so the process aborted! You guys are awesome