DataLayer.ActiveSQL - Retrieving Data with DataLayer.ActiveSQL
Like other datalayers, DataLayer.ActiveSQL has some special child elements (discussed below) that developers can use to shape the retrieved data. However, with the exception of Formatted Column, other child elements for linking datalayers and formatting or transforming the data are not available.
The data retrieved is available using @Data tokens, in the format @Data.ColumnName~. The spelling of the column name is case-sensitive. The data is only available within the scope of the parent element of the datalayer, not throughout the entire report definition.
The data retrieved into the datalayer can be viewed by turning on the Debugging Link in your _Settings definition (General element) and using the resulting link at the bottom of your report page to view the Application Trace page. You'll be able to see the generated SQL query, and a link on the page will display the data it retrieved.
Using the Datalayer to Retrieve SQL Data
DataLayer.ActiveSQL runs a SQL query to retrieve data from a SQL datasource. The element's Source attribute value is the actual SQL statement, which must be composed using valid syntax to be run.
Use of the DataLayer.ActiveSQL element with an Analysis Grid is shown above.
For easier editing, double-click the Source attribute name and the attribute value can be entered in the Attribute Zoom Window, as shown above.
Special DataLayer.ActiveSQL Child Elements
As mentioned earlier, DataLayer.ActiveSQL has some unique child elements that can be used to shape the SQL query that's generated and the retrieved data.
| Element | Description |
|---|---|
SQL Aggregate Column | Adds a new column in the datalayer that represents an aggregate value of all rows of the datalayer. The value is populated in every row so that it can be used in further calculations, such as Summary Rows, Link Params, etc. It is also populated in the top-level row, allowing reference to the value anywhere in the report outside of a Data Table. Available aggregate functions include Sum, Count, Average, Min, or Max. The column's data type must be numeric for the Sum, Count, and Average functions. |
SQL Calculated Column | Adds a new column in the datalayer created from other values in the same row, or from token values. |
SQL Condition Filter | Applies a WHERE-clause like functionality to the datalayer. Its Condition attribute (an expression) is evaluated for each row, and the row is removed when the result is False. The expression must written in the SQL query language of the data provider. Expressions usually contain tokens such as @Data to access specific values of each data row. Expressions can include almost any combination of tokens available in Info. May be used in conjunction with SQL Compare Filters, discussed below. |
SQL Group | Groups rows in the datalayer and allows grouped aggregate values to be created. Rows are grouped by values in one or more datalayer columns. Grouped data can consist of just the first row of each group, or all grouped rows. Grouping elements can be nested. |
SQL Parameters | Specifies the parameters for the SQL command. Parameters are listed in the SQL command using question marks as placeholders. For example, this command requires two parameters: SELECT * FROM Customers WHERE State=? AND City=? Specified parameters are supplied to the command based on their order, and their ID is ignored. |
SQL Sort | Sorts the rows of the datalayer. You may specify more than one column, using a comma-separated list of column names. Multiple Sort Sequences may also be entered in the same way. |
SQL Time Period Column | Adds a new column in the datalayer that represents a time period, such as year, quarter, month, etc., derived by parsing the value in another date-time type column. |
Generated Sql Plugin Call | Used to access and modify the SQL code generated by DataLayer.Active SQL, or the Active Query Builder element of an Analysis Grid. The plug-in is called right before the SQL is sent to the database and can append additional commands or modify existing commands. |
Using SQL Compare Filters
The SQL Compare Filter, which is used with a SQL Condition Filterelement, is quite similar to The Compare Filter element. When used, it frequently provides better performance than the SQL Condition Filter element alone, because it's implemented as a native part of the Logi Data Engine and so does not need to execute script, as the SQL Condition Filter does.
Consider a typical arrangement like the one shown above, which just uses a SQL Condition Filter. The SQL Expression attribute value used is:
@Data.Freight~ > 50 AND ('@Data.CustomerID~' = 'HANAR' OR '@Data.CustomerID~' = 'MAGAA')
There are three comparisons that must evaluate to True in order for a row in the datalayer to be retained and each is run using scripting. Now let's see how we can obtain maximum performance by spreading those evaluations out, using the native power of SQL Compare Filter:
In the example above, three SQL Compare Filter elements have been added beneath the SQL Condition Filter element. Each has a unique ID and its attributes are set to handle one of the three comparisons from our previous example and each of these "sub evaluations" will return a True or False result.
Finally, we change the SQL Condition Filter element's SQL Expression attribute to evaluate the results of the three SQL Compare Filter "sub evaluations", like this:
@Compare.compareFreight~ AND (@Compare.compareCustomer1~ OR @Compare.compareCustomer2~)
Note the use of a special token, @Compare, to represent the results of each "sub evaluation".
This approach not only provides better performance for large data sets, it may also be easier to use when there are many more comparisons and/or more complexity involved.
This table provides information about the SQL Compare Filter element's Compare Type options:
| Operator | Description |
|---|---|
=, <, >, <=, >=, < > | Basic standard comparison operations. String types evaluated are culture-sensitive. |
InDay | For the row to be retained, the date value in the specified Data Column must be within the same day as the Compare Value. |
InList | For the row to be retained, the value in the specified Data Column must be one of the values specified in a comma-separated list in the Compare Value. |
InWeek | For the row to be retained, the date value in the specified Data Column must be within the same week as the Compare Value. |
InMonth | For the row to be retained, the date value in the specified Data Column must be within the same month as the Compare Value. |
InQuarter | For the row to be retained, the date value in the specified Data Column must be within the same quarter as the Compare Value. |
InYear | For the row to be retained, the date value in the specified Data Column must be within the same year as the Compare Value. |
NotInList | For the row to be retained, the value in the specified Data Column must not be one of the values specified in a comma-separated list in the Compare Value. |
The Compare Column attribute allows you to compare two columns, rather than a column and a value. It and the Compare Value attribute are mutually exclusive and it supports all of the operators shown above except InList and NotInList.