Using Manual Select placeholders

Contents[Hide]

1. Overview

This article shows you how to define a placeholder for a Manual Select transform and use it in your manual queries. A placeholder is basically a parameter which you can later connect to a filter control on a dashboard.

2. Using a placeholder with SQL

2.1. Defining a placeholder

For this example, create a new data cube using the Manual Select option.

In the Open File dialog, select a SQL Server data connector (e.g., Adventure Works) and click Submit.

In the Script Editor window, enter a parameterized SQL statement where each parameter is enclosed between dollar sign ($) characters. Then click Save.

select * from [Sales].[vSalesPerson] where BusinessEntityId = $BEID$

Enter a parameterized SQL statement
Enter a parameterized SQL statement

A Manual Select and Process Result transform appear on the canvas. Double-click the Manual Select transform in order to configure it.

In the Manual Select configuration dialog, click Define Placeholders. In the Define Placeholders dialog, click Add placeholder and then set the following values:

  • Set the Identifier to $BEID$
  • Set the Parameter Name to Business Entity ID
  • Set the Data Type to Number
  • Set the Numeric Value to 274
  • Select the Is Public checkbox

Define the placeholder
Define the placeholder

Click Submit and close the dialogs. Select the Process Result transform on the canvas. Then click Data Preview. You'll see that the records returned by the manual query have been filtered to the Numeric Value (BusinessEntityID = 274).

Data Preview shows filtered results
Data Preview shows filtered results

2.2. Using the placeholder on a dashboard

Next, create a new dashboard to see how to connect this placeholder parameter to a filter control.

Go to the Explore window and expand the data cube that was just created. Drag the SalesYTD measure to the canvas. It will appear as a table visualization. Then drag the FirstName dimension onto the table (Row Header drop region).

Drag SalesYTD and FirstName
Drag SalesYTD and FirstName

Select the table visualization, go to the toolbar, click Filter, and then select Single Number. In the Filter Binding Panel, select the Business Entity ID item. This is the placeholder that was created earlier.

Connect the Single Number filter to the placeholder
Connect the Single Number filter to the placeholder

Finally, switch to View mode to use the filter.

Switch to View mode and use the Single Number filter
Switch to View mode and use the Single Number filter

3. Using a stored procedure with a placeholder

In order to execute a Stored Procedure in the Script Editor, you need to uncheck the Enable Subquery Optimization checkbox in the Manual Select transform configuration.

Using a stored procedure with a placeholder
Using a stored procedure with a placeholder

4. Using MDX with a placeholder

See Passing placeholder value from dashboard to manual MDX select.

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