Passing placeholder value from dashboard to manual mdx select

Contents[Hide]

1. Overview

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

Tip
As much as possible, avoid using a manual mdx select when filtering data, as you will be limited in using a string textbox filter. If you need to use a member filter (a checkbox filter with multi-selection) or a calendar control, you need to use the regular MDX Select transform.

2. Prerequisites

Read the following articles:

3. Data

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]

4. Steps

Here are the detailed steps on how to accomplish this:

  1. Create a new Data Cube.
  2. Click the "Add/Edit Manual Select" icon on the upper left corner of the data Cube UI.
  3. Select the SSAS cube (e.g. AdventureWorks).

    Select the SSAS cube
    Select the SSAS cube

  4. Add your MDX query.

    MDX Query with a placeholder
    MDX Query with a placeholder

  5. Click Save.
  6. Click Add Placeholders.
  7. Enter values on the Create Placeholder dialog:
    • Identifier: $country$

      This should match your placeholder in the MDX query.

    • Parameter Name: @country_name

      This is the parameter name that will appear on the dashboard.

    • Data Type: String
    • String Value: [Customer].[Customer Geography].[Country].&[France]

      This is an optional default value.

    Define Placeholder dialog
    Define Placeholder dialog

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

    At this point, your manual mdx node should not be red. If it is, there is something wrong with your mdx query, or on how you have setup the placeholder. You can also preview data to confirm data is retrieved using your default placeholder value.

  8. Optional - You can preview the data if you have set the default placeholder value.
  9. Optional - You can further configure the Manual MDX transform. Double-click the transform for the option to change the command timeout, make changes to the MDX query, and 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.

  10. Create a new dashboard and expand the Data Cube folder (or a Metric Set, if you used your Data Cube in a Metric Set).
  11. Drag the Data Cube's (or Metric Set's) measure and dimension to the dashboard canvas.
  12. Add a STRING TextBox filter and connect it to the @country_name parameter.

    String TextBox filter
    String TextBox filter

    Tip:
    You can hide the filter's dropdown context menu. See screenshot below.

    Remove dropDown token menu
    Remove dropDown token menu

5. Testing

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