Using manual select placeholders
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.
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 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$
The Define Placeholders panel will appear. Click Add placeholder and then set the following values:
- Set the Identifier to $BEID$
- Set the Parameter Name to Business Entity ID
- Set the Numeric Value to 274
- Select the Is Public checkbox
Click Submit and close the dialogs. A Manual Select and Process Result transform appear on the canvas. Select the Process Result transform and click Data Preview. You'll see that the records returned by the manual query have been filtered to the Numeric Value (BusinessEntityID = 274).
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).
Select the table visualization, go to the toolbar, click Filter, and then select Single Number. In the Filter Visualizations panel, select the Business Entity ID item. This is the placeholder that was created earlier.
Finally, switch to View mode to use the 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.