SQLDRILL_QUERY_TEMPLATE table
The SQLDRILL_QUERY_TEMPLATE table stores the custom query template that is called from the reporting and data entry template. There are four columns in the table.
| Column name | Description |
|---|---|
| NAME | Contains the query name. Query names must be unique. |
| TYPE | Contains the query type. If the TYPE column is empty, SQL is used. |
| QUERY | Contains the query template to run. Do not use any blanks between the semi-colon (;) and a dimension keyword |
| NEXTQUERYNAME | Contains the query name to be run for the next level drill through. If it is empty or the value is None, there is no next query. When the Type column is SQL_REDIRECT, the NEXTQUERYNAME column should be empty. In most cases, this column does not contain a value when TYPE column is VIEW as this is generally the first drill through. |
| DATETIME | Contains the query date and time. Use the CONVERT(VARCHAR(8), DATEFIELD, 1) format where DATEFIELD is the name of the column of the date field. |
The values for the TYPE column are:
| Type column | Description |
|---|---|
| SQL | The QUERY column is run as an SQL query. |
| SQL_REDIRECT | The QUERY column is an SQL Style query, generally a case statement, that returns a single QUERYNAME value of an existing template. The result of a SQL_REDIRECT query must be labeled QUERYNAME and its value must match the name of an existing query template. The returned query is then run. |
| URL | The QUERY column is a URL to be opened in a new window. URL must be invoked from a SQL or VIEW query type, by specifying the linking URL value in the NEXTQUERYNAME column. |
| VIEW | The QUERY column specifies a view to display. |
To have the Drill Through button open a new window with a view, specify VIEW in the type field, and use the VIEW=parameter in the query field. For example:
| Type | Query |
|---|---|
| VIEW | VIEW=CT MR KPI |
Use dimension placeholders to focus the view's shape by specifying the dimension name followed by the desired member. The dimension names must be separated by semi-colons. For example, to change the off grid members of the view to the selected product and unit, use a query such as:
View=CT MR KPI;Product={product};unit={unit}
A VIEW type query can only be specified in the URL. It cannot be used in the NEXTQUERYNAME column.