Page 1 of 1

Could not create dynamic subset

Posted: Mon Jan 15, 2018 6:49 pm
by gtonkin
With 10.2.2 FP4 and above you can get around the error thrown by SubsetCreateByMDX when the MDX returns a blank result.
SubsetCreateByMDX.PNG
SubsetCreateByMDX.PNG (6.81 KiB) Viewed 20541 times
Traditionally you would code something like:

Code: Select all

sDim='Versions';
sSubsetName='CurrentVersion';
sMDX='{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Versions] )}, "2018")}';

IF(SubsetExists(sDim,sSubsetName)=1);
SubsetDestroy(sDim,sSubsetName);
ENDIF;
SubsetCreatebyMDX(sSubsetName,sMDX);
Try using the following code which creates the subset first then sets the MDX:

Code: Select all

sDim='Versions';
sSubsetName='CurrentVersion';
sMDX='{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Versions] )}, "2018")}';

IF(SubsetExists(sDim,sSubsetName)=0);
SubsetCreate(sDim,sSubsetName);
ELSE;
SubsetDeleteAllElements(sDim,sSubsetName);
ENDIF;

SubsetMDXSet(sDim,sSubsetName,sMDX);
Don't forget to follow the SubsetMDXSet with :

Code: Select all

SubsetMDXSet(sDim,sSubsetName,'');
To convert from dynamic to static when required. Also consider the new flag (last parameter) to allow temporary objects i.e.:

Code: Select all

SubsetCreate(sDim,sSubsetName,1);

Re: Could not create dynamic subset

Posted: Mon Jan 15, 2018 7:07 pm
by tm123
I use a different approach:

SubsetCreatebyMDX(sSubsetName,sMDX,sDimName);

So if you provide The name of dimension as a third parameter, then TM1 will not throw an error if MDX returns empty subset

Re: Could not create dynamic subset

Posted: Mon Jan 15, 2018 7:18 pm
by gtonkin
Have not tested this in 10.2.2FP4 as 3rd parameter is now for temporary object-will have to test if 4th works as third did previously.

Edit: thanks for the reminder TM123

Re: Could not create dynamic subset

Posted: Mon Jan 15, 2018 7:45 pm
by tm123
gtonkin wrote: Mon Jan 15, 2018 7:18 pm Have not tested this in 10.2.2FP4 as 3rd parameter is now for temporary object-will have to test if 4th works as third did previously.

Edit: thanks for the reminder TM123
Still works, but the DimName has to be the 4th Argument

Re: Could not create dynamic subset

Posted: Tue Jan 16, 2018 5:53 am
by gtonkin
tm123 wrote: Mon Jan 15, 2018 7:45 pm ...Still works, but the DimName has to be the 4th Argument
Thanks for testing and confirming TM123 - appreciated.
I am still cautious of using undocumented features as they could potentially stop working with any future upgrade. They also make it a bit confusing when handing over to another resource who is not aware of them.

Hope this at least gives others options on dealing with the issue.

Re: Could not create dynamic subset

Posted: Tue Jan 16, 2018 8:57 am
by lotsaram
DimName argument is also the approach that I have used for some time to prevent empty set errors.
tm123 wrote: Mon Jan 15, 2018 7:45 pm Still works, but the DimName has to be the 4th Argument
Not quite true. The bTemporary flag is optional. If creating a temporary subset then the flag must be the 3rd argument and the optional dimension name becomes the 4th. However if the bTemporary flag is omitted (and therefore has default value of 0) then dimension name remains the 3rd argument. To implement otherwise would have broken backwards compatibility.

Hence there are actually 3 valid ways to call SubsetCreatebyMDX function depending if using 2, 3, or 4 arguments.
SubsetCreatebyMDX( sSubName, sMDX );
SubsetCreatebyMDX( sSubName, sMDX, sDimName );
SubsetCreatebyMDX( sSubName, sMDX, bTemporary, sDimName );

Re: Could not create dynamic subset

Posted: Tue Jan 16, 2018 12:55 pm
by tm123
lotsaram wrote: Tue Jan 16, 2018 8:57 am DimName argument is also the approach that I have used for some time to prevent empty set errors.
tm123 wrote: Mon Jan 15, 2018 7:45 pm Still works, but the DimName has to be the 4th Argument
Not quite true. The bTemporary flag is optional. If creating a temporary subset then the flag must be the 3rd argument and the optional dimension name becomes the 4th. However if the bTemporary flag is omitted (and therefore has default value of 0) then dimension name remains the 3rd argument. To implement otherwise would have broken backwards compatibility.

Hence there are actually 3 valid ways to call SubsetCreatebyMDX function depending if using 2, 3, or 4 arguments.
SubsetCreatebyMDX( sSubName, sMDX );
SubsetCreatebyMDX( sSubName, sMDX, sDimName );
SubsetCreatebyMDX( sSubName, sMDX, bTemporary, sDimName );
That is what I exactly said :) When Creating Temp Subsets, the DimName argument should be placed in 4th place, Like in your third example: SubsetCreatebyMDX( sSubName, sMDX, bTemporary, sDimName );

Re: Could not create dynamic subset

Posted: Tue Jan 16, 2018 1:52 pm
by tm123
gtonkin wrote: Tue Jan 16, 2018 5:53 am
tm123 wrote: Mon Jan 15, 2018 7:45 pm ...Still works, but the DimName has to be the 4th Argument
Thanks for testing and confirming TM123 - appreciated.
I am still cautious of using undocumented features as they could potentially stop working with any future upgrade. They also make it a bit confusing when handing over to another resource who is not aware of them.

Hope this at least gives others options on dealing with the issue.
That is true and I have that concern also! There are some good undocumented features such as specifying SubsetNames in CellGetN ( and also DBRW ) instead of ElementNames, and I currently use this feature a lot :)

Re: Could not create dynamic subset

Posted: Tue Jan 16, 2018 3:58 pm
by lotsaram
tm123 wrote: Tue Jan 16, 2018 12:55 pm That is what I exactly said :) When Creating Temp Subsets, the DimName argument should be placed in 4th place, Like in your third example: SubsetCreatebyMDX( sSubName, sMDX, bTemporary, sDimName );
I interpreted your answer as saying that sDimName had to be the 4th argument. That is, I thought you said if you don't want to create a temporary subset you would need to use ...
SubsetCreatebyMDX( sSubName, sMDX, 0, sDimName );
Which you could use (but why would you). As if you wanted to create a non-temporary object then the 3rd argument can just be omitted and use ...
SubsetCreatebyMDX( sSubName, sMDX, sDimName );

I was just making it clear that there is no need to re-factor any processes using SubsetCreatebyMDX with sDimName argument as they won't break on upgrade.

Re: Could not create dynamic subset

Posted: Tue Jan 16, 2018 4:44 pm
by tm123
lotsaram wrote: Tue Jan 16, 2018 3:58 pm
tm123 wrote: Tue Jan 16, 2018 12:55 pm That is what I exactly said :) When Creating Temp Subsets, the DimName argument should be placed in 4th place, Like in your third example: SubsetCreatebyMDX( sSubName, sMDX, bTemporary, sDimName );
I interpreted your answer as saying that sDimName had to be the 4th argument. That is, I thought you said if you don't want to create a temporary subset you would need to use ...
SubsetCreatebyMDX( sSubName, sMDX, 0, sDimName );
Which you could use (but why would you). As if you wanted to create a non-temporary object then the 3rd argument can just be omitted and use ...
SubsetCreatebyMDX( sSubName, sMDX, sDimName );

I was just making it clear that there is no need to re-factor any processes using SubsetCreatebyMDX with sDimName argument as they won't break on upgrade.
Exactly, I am not currently using the Temp subset (yet) so my code still works and that was the first thing when I saw the new feature (with bTemporary being the third parameter). I thought that will make all my TIs break since I was (and I still am ) applying sDimName as third parameter