Using manual select placeholders


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 marks where in the query to insert the value from a parameter, which you can later connect to a filter or script on a dashboard or set to an attribute value.

2. Using a placeholder with SQL

2.1. Defining a placeholder

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

In the Open dialog, select a SQL Server data connector (e.g., Adventure Works) and click the submit button at the bottom.

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

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

Enter a parameterized SQL statement
Enter a parameterized SQL statement

The Define Placeholders dialog will appear. Click Add placeholder and then change the settings as needed.

In our example, we set Identifier to BEID to match our query.

Select the Public checkbox to allow this parameter to be set outside the cube.

Enter a default value for the parameter (e.g., 274).

Define the placeholder
Define the placeholder

When Value Type is Collection, the placeholder is replaced by the query text <element> IN <values>, so set the additional setting Element Name to your column name (e.g., BusinessEntityID). The sample query above would instead be: select * from [Sales].[vSalesPerson] where $BEID$

Click the submit button at the bottom and close the dialogs. A Manual Select and Process Result transform appear on the canvas. Select the Process Result transform and open the Data Preview window. You'll see that the records returned by the manual query have been filtered (e.g., BusinessEntityID = 274).

Data Preview shows filtered results
Data Preview shows filtered results

Tokens such as built-in attributes and custom attributes can be selected as the placeholder parameter's default value only after it's been created, by re-opening the Manual Select transform and editing its placeholders.

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.

Drag SalesYTD and FirstName
Drag SalesYTD and FirstName

With the table selected, click Filter in the toolbar, and then select Single Number. In the Filter Visualizations 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

See the Manual Select article for details on this option. Alternatively, you can use a Stored Procedure Select instead of executing the stored procedure with your own query in a Manual Select.

4. Using MDX with a placeholder

See Passing placeholder value from dashboard to manual MDX select.

5. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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