Could not create dynamic subset

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

Could not create dynamic subset

Post by gtonkin » Mon Jan 15, 2018 6:49 pm

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 317 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: 80
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 » Mon Jan 15, 2018 7:07 pm

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: 563
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Could not create dynamic subset

Post by gtonkin » 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

tm123
Posts: 80
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 » Mon Jan 15, 2018 7:45 pm

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: 563
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Could not create dynamic subset

Post by gtonkin » 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.

lotsaram
MVP
Posts: 3023
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Could not create dynamic subset

Post by lotsaram » 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 );
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

tm123
Posts: 80
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 » Tue Jan 16, 2018 12:55 pm

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: 80
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 » Tue Jan 16, 2018 1:52 pm

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: 3023
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Could not create dynamic subset

Post by lotsaram » 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

tm123
Posts: 80
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 » Tue Jan 16, 2018 4:44 pm

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