MDX using TI Parameters

[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 )}'

);

[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);

[tm123]

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 );

[PavoGa]

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.