Drill Down to SQL

Using a combination of Table and SQL Table components, a drill through into an SQL query can be created.

In order for this to have the desired effect, ensure the cube being referrred to in the Table component's data is a cube either fed by SQL directly or relating to available SQL data in the Model's datasources.

1. Set up a Table component as desired. See the Adding a Table guide for assistance.

2. Drag and drop a Container component into the Table component where the Data Text sits. The Container component is going to be used to set variables when the numbers in the table are clicked.

3. Drag the data Text component into the new Container component.

4. Click on the Container component icon to highlight it and see the container properties in the Properties Panel.

5. In the properties panel, navigate to the Set Variables property under the Interactivity section. In this example we're going to set a couple of variables (for the Product and time Dimensions), so we are going to open the formula editor by clicking on the pi icon.

   The variables set here are going to be the dynamic arguments used in your SQL. Ensure the elements you use as variables are what you want to filter your SQL by.

   

   Example: If the cell intersection that is being selected specifies time on columns and item on rows, you'll likely want to filter these two in your SQL so that the drill through only shows records that match the selected item and time period.

6. Set variables as desired. See the Variable Setting - Dynamic Multiple Variables for more information on the syntax.

   INFO

   Test variable setting by clicking the "Preview" button and clicking on table cells in the card preview panel. The variables should appear in the Variables panel on the top right.

7. Drag and drop an SQL Table component into the Card Editor panel.

8. Click on the SQL Table component icon to highlight it and see the container properties in the Properties Panel.

9. In the Datasource property Under the Table section, enter the name of the Datasource in which the tables of the query are found.

10. Click on the Pi icon of the Query property under the Table section to open the formula editor. Write in a query by parameterizing using "?"s. This is done to protect against SQL injection attacks.

    Example:

    SELECT * FROM sales  WHERE product_name = ? and time = ?"

11. Open the Query Parameters property under the Table section.

12. Click "Add Item" in the bottom left. There should be one item per query parameter (?).

13. Click on the Pi icon per item row to open the formula editor. Insert the variables here via the VARIABLE() syntax.

    INFO

    The parameters are set in order of appearance. The first row should be the first parameter, and so on.

14. After saving this, the SQL table will need to be filled with content. The content of the table will need to consist of Text components that reference the SQL in the Text property formula editor (accessed via the Pi icon). See below for available methods of retrieving SQL query data:

    • SQLCOLUMNNAME(column): Returns the name of the field for the specified column index in a cards SQL Table component.

    • SQLROW(): Returns the entire row as a comma-delimited string in a cards SQLTable component.

    • SQLCOLUMN(column): Returns the column value of the current row in a cards SQLTable component. The column argument can either be the column index or the name for the current row.

    • SQLVALUE(row, column): Returns the value of a specific column from a specific row in a cards SQL Table component.

Once this content is filled out, hit "Preview" to show the result in the card preview panel. Click the Table cells to watch the SQL table change the drill through results.