Passing placeholder value from dashboard to manual MDX select


1. Overview

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

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]} ON COLUMNS,
{ DESCENDANTS(StrToMember("[Customer].[Customer Geography].[Country].["+$country$+"]") ,   
[State-Province]) } ON ROWS
FROM [Adventure Works]

Here's another MDX query:

{ DESCENDANTS(StrToMember("[Customer].[Customer Geography].[Country].&["+$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 on the Manual MDX transform node and select Configure.
  6. Click Define Placeholders.
  7. Click Add Placeholders.
  8. Enter values on the Create Placeholder dialog:
    • Parameter Name: @country_name
      This is the parameter name that will appear on the dashboard.
    • Identifier: $country$

      This should match your placeholder in the MDX query.

    • Data Type: String

      Only String can be used at this time.

    • String Value: France

      This is an optional default value.

    Create Placeholder dialog
    Create Placeholder dialog

  9. Right-Click on the Manual MDX transform node and select Refresh. You can also use the Refresh icon from the toolbar. 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've setup the placeholder. You can also preview data to confirm data is retrieved using your default placeholder value.

    Refresh and Preview data
    Refresh and Preview data

  10. Optional - You can preview data if you've set the default placeholder value.
  11. Create a new dashboard and expand the Data Cube folder (or a Metric Set, if you used your Data Cube in a Metric Set).
  12. Drag and drop the Data Cube's (or Metric Set's) measure and dimension to the dashboard canvas.
  13. Add a STRING TextBox filter and connect it to the @country_name parameter.

    String TextBox filter
    String TextBox filter

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

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