Enter an SQL statement to make a selection from a data connector instead of dragging native structures onto the canvas.
For example, the input data is:
SELECT ProductID, OrderQty, OrderQty * 2 as 'Order2' FROM Sales.SalesOrderDetail
2. Add the transform
Select the Manual Select transform from the toolbar.
Select the Data Connector.
Enter the SQL statement.
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.
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.
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 is basically a parameter which you can later connect to a filter control on a dashboard.
See Using Manual Select placeholders for more details.
The figure below illustrates the output from the Manual Select transform.
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