(v23) Passing placeholder value from dashboard to manual MDX select

Passing placeholder value from dashboard to manual MDX select

This applies to: Managed Dashboards, Managed Reports

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

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]

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

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

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].

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.

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

Note: The Default Query Timeout application configuration setting determines the initial timeout setting.

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.

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

Testing

Click View in the toolbar.

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