Could not create dynamic subset

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Could not create dynamic subset

Post 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 20542 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);
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Could not create dynamic subset

Post 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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Could not create dynamic subset

Post 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
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Could not create dynamic subset

Post 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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Could not create dynamic subset

Post 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.
lotsaram
MVP
Posts: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Could not create dynamic subset

Post 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 );
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Could not create dynamic subset

Post 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 );
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Could not create dynamic subset

Post 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 :)
lotsaram
MVP
Posts: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Could not create dynamic subset

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Could not create dynamic subset

Post 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
Post Reply