Passing placeholder value from dashboard to manual MDX select

Contents[Hide]

1. Overview

This article demonstrates how to pass a value from a dashboard to a placeholder in a manual MDX select.

2. Example query

Here is a sample MDX Select with $country$ as a placeholder using SSAS AdventureWorks cube:

SELECT {[Measures].[Internet Sales Amount]} DIMENSION PROPERTIES MEMBER_TYPE ON COLUMNS,
{ DESCENDANTS(StrToMember($country$) ,   
[Customer].[Customer Geography].[State-Province]) } DIMENSION PROPERTIES MEMBER_TYPE ON ROWS
FROM [Adventure Works]

3. Create a data cube

Create a new data cube using the Manual Query option, or choose the Manual Select option in the toolbar.

In the Open dialog, select the particular cube under your data connector you are querying (e.g., Adventure Works).

Select the cube
Select the cube

In the Script Editor window that appears, add your MDX query that includes a placeholder enclosed in dollar signs ($) and click Save.

MDX Query with a placeholder
MDX Query with a placeholder

The Define Placeholders dialog will appear automatically if your query includes a placeholder. Click Add placeholder.

The Identifier should match your placeholder in the MDX query (e.g., $country$). The Parameter Name will identify this parameter when you use it on the dashboard.

In our example, we set Data Type to String and the default String Value to [Customer].[Customer Geography].[Country].&[France].

Define Placeholder dialog
Define Placeholder dialog

Note
The Value Type Collection is not compatible with MDX queries.

After submitting the dialog, your Manual MDX transform should not be red. If it is, there is something wrong with your query, how you have set up the placeholder, or it still needs to be connected to another transform such as the Process Result. You can also open Data Preview to confirm data is retrieved using your default placeholder value.

You can configure the Manual MDX transform by double-clicking it, or from its toolbar or context menu. For example, you can change the Timeout, make changes to the MDX query, or define additional placeholders.

Configure the Manual MDX transform
Configure the Manual MDX transform
Note
The greater value between the command timeout on the transform and the command timeout on the data connector will be used.

Tip
In version 5.0.1 and above, the Default Query Timeout application configuration setting determines the initial timeout setting.

4. Edit the dashboard

Create or edit a dashboard, and find your data cube in the Explore window. Drag its data to the dashboard canvas to select it in a metric set.

From the Filter option in the toolbar, choose a Textbox filter and connect it to your parameter.

Connect the Textbox filter
Connect the Textbox filter

In this example, we'll open the Properties window for the filter and hide the token menu.

Hide token menu
Hide token menu

5. Testing

Click View in the toolbar.

Default data retrieved
Default data retrieved

After changing the filter value, new data should be retrieved:

Filter value changed
Filter value changed

6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri