Manual Select

Contents[Hide]

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

1. Input

For example, the figure below shows the data retrieved by a simple query:

Sample data
Sample data

You might also use hard-coded data, as in the 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

2. Add the transform

Select the Manual Select transform from the toolbar.

Toolbar option
Toolbar option

Select a data connector. (For OLAP databases, instead select the cube under the data connector you will be querying.)

Select a data connector
Select a data connector

Enter the query.

Enter the query
Enter the query

Tip
Use the keyboard shortcut Ctrl + Space for a list of available SQL keywords and table names. You can also drag the native structure (cube, table, column, measure, or hierarchy level) from the Explore window to the Script Editor to add the respective unique name.

Click Save.

3. Configure

To configure the transform, select it and choose Configure in the toolbar.

Manual select configuration
Manual select configuration

The Timeout (in seconds) can be customized to determine how long to wait before terminating a long-running command.

Tip
In version 5.0.1 and above, the Default Query Timeout application configuration setting determines the initial timeout setting.

3.1. Enable subquery optimization

In order to execute a stored procedure from the script editor, you need to uncheck the Enable Subquery Optimization checkbox in the Manual Select transform configuration.

Manual select using a stored procedure
Manual select using a stored procedure

Tip
You can also drag a stored procedure from the Explore window onto the canvas instead of typing it into a query.

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 inserts a parameter into the query, which you can set from outside the transform or optionally the data cube, for example by connecting it to a filter on a dashboard.

See Using manual select placeholders for more details.

4. Output

Open the Data Preview window to see the output.

Manual select sample output
Manual select sample output

5. Notes

  • Manual MDX queries must return a cellset or multidimensional data set (i.e., DRILLTHROUGH is not supported).

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