(v23) Manual Select

Manual Select

This applies to: Managed Dashboards, Managed Reports

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

Input

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

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

Add the transform

Select the Manual Select transform from the toolbar.

Note: You may need to be assigned an application privilege by your administrator before you can access this transform.

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

Enter the query.

Note: For some data providers, inserting comments into your query may cause an issue when subquery optimization is enabled.

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.

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

Configure

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

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

Note: The Default Query Timeoutapplication configuration setting determines the initial timeout setting.

Enable subquery optimization

Subquery optimization allows your query to be incorporated automatically into a larger query that also performs data cube transformations, metric set aggregations, and filtering, so that this work is done in the database to optimize performance when possible.

Your query must be able to be enclosed in a SELECT statement when this optimization is enabled. For statements such as executing a stored procedure, or advanced or database-specific SQL features such as creating temporary tables, uncheck the Enable Subquery Optimization option in the transform configuration dialog. You may also need to uncheck this option if your data connector uses an ODBC driver that incorrectly reports its capabilities.

Note: Do not end your query statement with a semi-colon ( ; ) if subquery optimization is enabled.

When this option is unchecked, transforms connected to the manual select transform's output will display an icon indicating that the data is being brought into memory in Dundas BI to perform the transformation rather than using the database.

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.

Output

Open the Data Preview window to see the output. If the query did not succeed, this may include errors or warnings explaining why.

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