Dynamically generated substitution lists

To add parameter from dynamically generated substitution list let us first create ordinary parameter with measures.

 

 

 

Now change substitution mode to:

 

·          Replace this instance from dynamically generated list from the MDX command

 

 

Substitution MDX tab on this dialog becomes visible:

 

 

Change the tab:

 

 

Now:

 

·          Select server from the list or enter server name

·          Select Connect button

 

 

After connection is established:

 

·          Select button Select cube

·          Select database and cube

 

 

MDX command to be executed field expects you to type a valid MDX command that will be executed on the target cube.

This command should contain only one defined axis, COLUMNS or AXIS(0) because we are building a list of individual

items from the returned results. Let us enter this simple command here:

 

SELECT

[Measures].[MeasuresLevel].MEMBERS

ON AXIS(0)

FROM

[Sales]

 

In this MDX command, we are returning summary values (on the entire cube) for all existing measures.

However, it is not yet clear what is supposed to be substituted and what should the substitution values be.

This is the purpose of the last two combo boxes.

The two (2) bottom combo-boxes allow you to specifically define the values for:

 

1.       Displayed value (Display this type of information dropdown list), and

2.       Substitution value (Replace with this type of information dropdown list)

 

Displayed value will be presented to the end user and you should type some user-friendly, easy to understand string.

Substitution value will be used in the replacement process. When the end-user selects some item from this

list, the default parameter value will be replaced with the substitution value of the selected item.

 

 

Both combos contain the following thirteen (13) items:

 

1.       Dimension unique name

2.       Dimension caption

3.       Hierarchy unique name

4.       Hierarchy caption

5.       Level unique name

6.       Level caption

7.       Member unique name

8.       Member caption

9.       Measure unique name

10.    Measure caption

11.    Member property unique name

12.    Member property value

13.    Cell value

 

Except for the cell value, you would typically set the display value to some caption, and the substitution value

to the unique name of some cube structure element. In our example, we shall select the

Measure caption item for the displayed value, and the substitution value will be automatically set to

Measure unique name!

When you have defined all these parameters, you must test your definition before it can be accepted.

A Test button serves exactly this purpose. When you click it, it will try to open a new connection and actually

execute the provided MDX statement on the selected database and cube.

We can now accept the predefined list by clicking on the OK button.

 

Save Query and run it: