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.

Using SQL Compare Filters

The SQL Compare Filter, which is used with a SQL Condition Filter element, 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:

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.