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

Steps to add the transform:

  1. Select the Manual Select transform from the toolbar.

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

  2. Select the Data Connector.

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

  3. Enter the SQL statement. For example:

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

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

    Code completion - SQL Keywords
    Code completion - SQL Keywords

    Code completion - SQL Tables
    Code completion - SQL Tables

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