IF statement in Epilog

[tez]

Hi

I'm building a Project dim & creating the subsets in the Epilog tab.

Some subsets filters by the Customer code of the project/s & if the project has an active status. So my code looks like....

Code: Select all

SubsetCreatebyMDX('subsetname','{HIERARCHIZE({TM1SORT({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[Project].[Total Projects]}, ALL, RECURSIVE)},0,1)},[Project].[Customer Code] = "customercode")},[Project].[Active Status] = "Active")},ASC)})}' );

If there are no active projects, the TI process errors out saying it can't create the subset (which is correct). I was hoping I could use an IF statement so it controls if the subset is created or not & to allow the TI process to complete successfully...

Code: Select all

IF(vCustomerCode@='customercode' % vActiveStatus@='True');
SubsetCreatebyMDX('subsetname','{HIERARCHIZE({TM1SORT({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[Project].[Total Projects]}, ALL, RECURSIVE)},0,1)},[Project].[Customer Code] = "customercode")},[Project].[Active Status] = "Active")},ASC)})}' );
ENDIF;

but it seems that it totally ignores the subsetcreate if I include the IF statement.

I'm wondering if I am doing something wrong or if this isn't possible.

Many thanks
Terri

[Wim Gielis]

Hello

Just add the dimension name as a third (optional) parameter to the function:

Code: Select all

SubsetCreatebyMDX('subsetname','{HIERARCHIZE({TM1SORT({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[Project].[Total Projects]}, ALL, RECURSIVE)},0,1)},[Project].[Customer Code] = "customercode")},[Project].[Active Status] = "Active")},ASC)})}', 'Project' );

It will not error out. Use SubsetGetSize after wars to check the number of elements.

[tez]

Hi Wim

Great, thanks so much for that. I noticed that it then creates the subset with no elements, so using your suggestion of SubsetGetSize, I was able to then delete the subset if it was empty. My final code looks like....

Code: Select all

SubsetCreatebyMDX('subsetname','{HIERARCHIZE({TM1SORT({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[Project].[Total Projects]}, ALL, RECURSIVE)},0,1)},[Project].[Customer Code] = "customercode")},[Project].[Active Status] = "Active")},ASC)})}', 'Project' );
IF(SubsetGetSize('Project','subsetname')=0);
    SUBSETDESTROY('Project','subsetname');
ENDIF;

Many thanks
Terri

[Wim Gielis]

That's the way I would proceed too.