Page 1 of 1
MDX using TI Parameters
Posted: Mon Oct 30, 2017 11:37 am
by RonLat
using a MDX statement, it is possible to create a subset by filtering the values of a cube. However, it is necessary to explicitly name the dimensions and elements of the cube. In my case, the [Period] and [Config_Measure].[Source] are constants in the MDX statement below. I'd like to vary the elements [Year].[FY17] and [Scenario_Plan].[FC0517], because I need the process to create different subsets for [Period] in dependence of the selected elements in the dimension [Year] and [Scenario_Plan]. Is there any possibility to use TI Parameters as variables for the selection of the elements? If not, any idea how to solve this problem?
Code: Select all
SubsetCreateByMDX (SubNameSRC,
'{FILTER({TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Period] )}, 0)},
[Config_JPS].( [Year].[FY17], [Scenario_Plan].[FC0517], [Config_Measure].[Source]) > 0 )}'
);
Re: MDX using TI Parameters
Posted: Mon Oct 30, 2017 12:11 pm
by Steve Rowe
Yup, just concatenate the vars into the MDX string
Code: Select all
sMDX='{FILTER({TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Period] )}, 0)},
[Config_JPS].( [Year].[' | sYear |'], [Scenario_Plan].[' | sScenario |'], [Config_Measure].[Source]) > 0 )}'
SubsetCreateByMDX (SubNameSRC, sMDX);
Re: MDX using TI Parameters
Posted: Mon Oct 30, 2017 12:15 pm
by tm123
RonLat wrote: ↑Mon Oct 30, 2017 11:37 am
using a MDX statement, it is possible to create a subset by filtering the values of a cube. However, it is necessary to explicitly name the dimensions and elements of the cube. In my case, the [Period] and [Config_Measure].[Source] are constants in the MDX statement below. I'd like to vary the elements [Year].[FY17] and [Scenario_Plan].[FC0517], because I need the process to create different subsets for [Period] in dependence of the selected elements in the dimension [Year] and [Scenario_Plan]. Is there any possibility to use TI Parameters as variables for the selection of the elements? If not, any idea how to solve this problem?
Code: Select all
SubsetCreateByMDX (SubNameSRC,
'{FILTER({TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Period] )}, 0)},
[Config_JPS].( [Year].[FY17], [Scenario_Plan].[FC0517], [Config_Measure].[Source]) > 0 )}'
);
You just assign the MDX statement to a String Variable and then you concatenate the Static Part of your MDX with the variable part.
sMDX = '{FILTER({TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Period] )}, 0)},
[Config_JPS].( [Year].[‘ + pYear + ‘], [Scenario_Plan].[FC0517], [Config_Measure].[Source]) > 0 )}' ;
I am typing from a Cell Phone and I cannot find the pipe character so I used + instead of pipe for concatenating strings
Then in your SubsetCreateByMDX use the sMDX variable
SubsetCreateByMDX (SubNameSRC, sMDX );
Re: MDX using TI Parameters
Posted: Mon Oct 30, 2017 8:07 pm
by PavoGa
RonLat wrote: ↑Mon Oct 30, 2017 11:37 am
using a MDX statement, it is possible to create a subset by filtering the values of a cube. However, it is necessary to explicitly name the dimensions and elements of the cube. In my case, the [Period] and [Config_Measure].[Source] are constants in the MDX statement below. I'd like to vary the elements [Year].[FY17] and [Scenario_Plan].[FC0517], because I need the process to create different subsets for [Period] in dependence of the selected elements in the dimension [Year] and [Scenario_Plan]. Is there any possibility to use TI Parameters as variables for the selection of the elements? If not, any idea how to solve this problem?
To make the code easier to read, I switched to this:
Code: Select all
strMDX = EXPAND('UNION( {[%dimName%].currentmember}
, FILTER( TM1SUBSETALL( [%dimName%] )
, [%cubName%].([%dimName1%].[%element%], ...) = %pParam1%)
, ALL)');
SubsetCreateByMDX(subName, strMDX);
SubsetElementDelete(dimName, subName, 1);
In this example, the dimensions, cube and elements are stored in variables. It is a lot easier to read, especially on more complex MDX, than the concatenation method.