Enter a SQL or MDX statement to make a selection from a data connector instead of dragging native structures onto the canvas.
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
2. Add the transform
Select the Manual Select transform from the toolbar.
Select a data connector. (For OLAP databases, instead select the cube under the data connector you will be querying.)
Enter the query.
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.
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.
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.
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.
Open the Data Preview window to see the output.
- Manual MDX queries must return a cellset or multidimensional data set (i.e., DRILLTHROUGH is not supported).