Manual Select

Contents[Hide]

Enter an SQL statement to make a selection from a data connector instead of dragging native structures onto the canvas.

1. Input

For example, the input data is:

Input Data: SQL Select
Input Data: SQL Select

SELECT ProductID, OrderQty, OrderQty * 2 as 'Order2'
FROM Sales.SalesOrderDetail

2. Add the transform

Select the Manual Select transform from the toolbar.

Adding the Manual Select transform - Step 1
Adding the Manual Select transform - Step 1

Select the Data Connector.

Adding the Manual Select transform - Step 2
Adding the Manual Select transform - Step 2

Enter the SQL statement.

Adding the Manual Select transform - Step 3
Adding the Manual Select transform - Step 3

Tip
Use the keyboard shortcut Cntrl + Space for a list of available SQL keywords and table names.

You can drag the native structure (cube, table, column, measure, or hierarchy level) from the Explore tab to the Script Editor to add the respective unique name.

You can also enable code completion by hitting CTRL+SPACE on the keyboard. SQL keywords and Tables will appear on the list.

Code completion - SQL Tables
Code completion - SQL Tables

Click Save.

3. Configure

3.1. Enable Subquery Optimization

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.

Manual Select - Using Stored Procedure
Manual Select - Using Stored Procedure

You should also uncheck this option if you need to run queries that create temporary tables. For example, you may need to uncheck this option if your data connector uses an ODBC data provider that incorrectly reports its capabilities.

Note
If you have enabled subquery optimization, it is recommended to avoid inserting comments into your query, especially for non-SQL Server data providers.

3.2. Placeholders

The Manual Select transform also lets you define a placeholder 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.

See Using Manual Select placeholders for more details.

4. Output

The figure below illustrates the output from the Manual Select transform.

Manual Select - Sample Output
Manual Select - Sample Output

You can also use Manual Query to use hard-coded data. See sample query below:

SELECT CONVERT (date , '01/15/2013', 101) as [Date], 1000 as [Series1], 400 as [Series2], 100 [Series3], 500 [Series4] UNION ALL
SELECT CONVERT (date , '01/16/2013', 101), 300, 100, 300, 900 UNION ALL
SELECT CONVERT (date , '01/17/2013', 101), 700, 200, 200, -500 UNION ALL
SELECT CONVERT (date , '01/18/2013', 101), 200, 300, 300, 300

Manual Select - Hard-coded data
Manual Select - Hard-coded data

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