Introducing Datalayers

A datalayer is a logical temporary container for data retrieved from a datasource and cached as XML data on the web server. This topic introduces developers to the use of datalayers within Logi reporting products.

• Selecting the Correct Datalayer
• Using the Data
• Data Scope and Local Data
• Manipulating the Datalayer
• Datalayer Error Handling
• Usage Examples
• Using Hard-coded Data in an Input Select List
• Using XML File Data in an Input Select List
• Using Datalayers to Retrieve SQL Data

Selecting the Correct Datalayer

In Logi reporting products, developers use datalayers to retrieve data for tables, charts, and input select lists from a datasource. A datasource can be one of the following:

• OLEDB-, ODBC-, or JDBC-compliant databases
• XML, CSV, Excel, or other files
• LDAP directories
• Web Feeds, Web Services, or web pages
• Hard-coded, static data
• Your Logi application's metadata

A number of different datalayer elements are available and your selection can depend on both the datasource and the retrieval technique. In addition, some datalayers are not available in Logi Report; check individual element documentation for availability.

The following table lists the typical uses for datalayers within Logi reporting products. The links direct you to individual documents that discuss the use of each datalayer.
 

The type of datalayer used often matches the type of Connection element used in your _Settings definition. Some datalayers, such as DataLayer.CSV and DataLayer.Excel, directly access the web server file system and may not require a connection.

In v10.2.224+, Data Engine performance, when processing large numbers of DateTime-type columns in a result set, can be significantly improved by skipping Time Zone processing. This is done using a special constant in the _Settings definition:

rdSQLIncludeGMTOffset = False

However, this feature does not offer improved performance with small numbers of DateTime columns.

In v11.0.416, the Connection.HTTP element was introduced, allowing the following datalayers to optionally use a connection to an HTTP or HTTPS datasource that requires authentication:

Using the Data

Logi reporting products are XML-based and the reporting engine converts data retrieved from any datasource into XML. Each row from the datasource is equivalent to one XML element and each column is equivalent to one XML attribute. For example, a data table containing four columns and nine rows is equivalent to nine XML elements, each with four attributes.
 

The example above shows the datalayer XML produced from a query to the Northwind database. The query was:

SELECT EmployeeID, LastName, FirstName, Title FROM Employees

Data retrieved into the datalayer is cached in XML format in memory and/or on the web server's file system. The latter is discussed in The Logi Server Engine and may be of interest to developers working with extremely large datasets or large numbers of concurrent users.

Data retrieved into the DataLayer.XML File and DataLayer.Web Service elements may need to be reformatted for use, and the XslTransform element is available for this purpose.

In v11.0.416, support for "bracketed" column names, such as "[Order Date]", was introduced. The brackets allow column names to include spaces, special characters, or SQL reserved words.

The data retrieved with a datalayer is available using @Data tokens, in the format @Data.ColumnName~. The spelling of the column name is case-sensitive.

For example, @Data.LastName~ retrieves all values, in all rows, for the LastName column. This might sound as if you get all the data in one place in your report but you don't. When the reporting engine generates the report, it iterates through each row in the datalayer; the @Data token at any point in time represents the value in a column for the current row in the iteration.

You can see the actual XML data after it's been retrieved for any report in the Debug Trace Report; see Debug Reports to learn more about troubleshooting datalayers.

Data Scope and Local Data

The availability of data is generally limited to the container (Data Table, Chart, etc.) which is the parent element of the datalayer element used to retrieve the data. The following example illustrates the use of multiple datalayers and the availability of their data.

1. The Data Table element within the div1 division uses data from DataLayer1 datalayer.
2. The Chart.XY element within the div2 division uses both the DataLayer2 datalayer and the DataLayer3 datalayer, but does not have access to data in DataLayer1.
3. The Data Table element within the div3 division uses the data from DataLayer1 by virtue of the two elements, Data Layer Link and DataLayer.Linked, which link it to the datalayer.

Linking datalayers is a very useful practice, allowing the data retrieved into one datalayer to be available for re-use in another. This eliminates extra datasource queries and improves performance. For more information, see Link Datalayers.

A special data container element, Local Data, can be used to make data available anywhere within the report:

As shown above, Local Data is a child of the report root element, not of the Body or other lower elements. The data retrieved by Local Data's child datalayer is accessed using a special @Local tokens, in the format @Local.ColumnName~. These tokens can be used anywhere in the report, and this is an excellent way to use data, for example, in headers and footers.

There is a caveat, however: @Local tokens can only access the first row of the data. Local Data is particularly useful, therefore, for "lookups" that return a limited amount of data.

Multiple Local Data elements can be used in the same report definition and they will run sequentially, not simultaneously, so that data from one can be used in the query of another. In the data retrieved, the effect is a "union" of all of the Local Data data.

You can use the linking elements shown earlier to link the Local Data datalayer to other datalayers in the report definition, which then provides access to all rows of data in the Local Data datalayer. However, the linked data will then be subject to the same rules as those for any other datalayer (scope limited to parent element and accessed using @Data tokens).

Local Data datalayers are run before other datalayers, so data from them can be used to condition the retrieval of data by other datalayers. In versions prior to 10.0.319, Local Data re-runs its datalayer every time any element in the report is refreshed; this may have performance implications and make it less desirable.

In v11.0.416, Local Data behavior was changed so that its datalayers are not re-run with all AJAX refresh requests. They are only re-run when the element being refreshed contains either a DataLayer.Linked element linked to the Local Data datalayer, or when it contains an @Local token.

Beginning in v10.0.319, the Local Data element was given a Condition attribute. If the value of this attribute is left blank or contains a formula that evaluates to True, then the element's child elements (its datalayers) will run. If the value evaluates to False, the element is ignored and the datalayers are not run. This allows developers to dynamically determine when the Local Data should be run, or re-run if a page is refreshed (in this case, a @Request token can be used in the formula in the Condition attribute).

Manipulating the Datalayer

Once data has been retrieved into a datalayer, a number of elements can be used to manipulate its data. This is generally done by either re-arranging rows, removing rows, or by adding columns to the datalayer. These elements are summarized below; the links direct you to individual documents that discuss the use of each element:

Columns that are added to a datalayer using these elements are then available for use and subsequent manipulation themselves, like any other datalayer column, and their values are available using @Data tokens.

The Include Condition Attribute

Beginning in v10.0.319, all of the datalayer manipulation elements listed in the table above have an Include Condition attribute. If the value of this attribute is left blank or contains a formula that evaluates to True, the element is applied to the datalayer. If the value evaluates to False, the element is ignored and does not affect the datalayer. This powerful feature allows developers to dynamically determine if the datalayer will be manipulated or not.
 

Datalayer Element IDs

Element IDs were required for datalayers through v10.1.46, after which they became optional. However, you may want to always provide a unique datalayer element ID, for ease in understanding the definition and, significantly, so that the datalayer is easily identified in the Debug Trace page.

Datalayer Error Handling

The If Data Error element, introduced in v10.0.319, allows developers to handle errors, such as a failure to find or connect to data sources, or missing or corrupt data files, that may occur while retrieving data into a datalayer. In this way, errors are trapped and processing can be switched to alternate data sources, instead of displaying the system error page.

Consider the example shown above. A Data Table normally uses a Web Service to retrieve the latest currency conversion information. However, if there is some problem with the web service and an error occurs, the If Data Element allows the datasource to switch to an XML data file. In this way, data is still presented and an error page is not displayed.

What about any data manipulation elements that may exist? As shown above, when an error occurs, the alternate datalayer replaces the original datalayer as the data retrieval mechanism and applies its own data manipulation elements. Then the application will process any data manipulation elements for the original datalayer.

You may use multiple, nested If Data Error elements. The Debug Trace page will include an entry containing the actual error message and any details generated each time an If Data Error element is processed. The @Function.ErrorDataLayerID~ token can be used to determine the element ID of the datalayer that encountered the error.

Usage Examples

Datalayer elements are available for various table, chart and user-input elements. In Studio, the Element Toolbox Panel displays all the datalayer elements when an appropriate parent element is selected. The following three examples illustrate common uses of datalayers.

Using Hard-Coded Data in an Input Select List

Hard-coded values can be used with the DataLayer.Static element:

In the example above, the user should be able to select garment sizes from a drop-down list. Hard-coded data makes sense here because the standard sizes (Small, Medium, Large) are few in number and static. To make this functional:

1. Add the Input Select List element to the definition, as shown above.
2. Beneath it, add a DataLayer.Static element, which has no attributes, and three Static Data Row child elements, one for each choice (garment size) in the drop-down list.
3. Set the Input Select List element's attributes as shown above. The values for the Caption Column and Value Column attributes are an arbitrary names ("SizeName" and "SizeCode") that will be referenced in the next step.
    

4. Set the attributes for each Static Data Row elements as shown above, one element for each size.

When the report is run, the datalayer will get its displayed options (the "Caption") and related values from the Static Data Row elements and the Input Select List will display them.
 

Using XML File Data in an Input Select List

The DataLayer.XML File element is used to retrieved from an XML file. This approach is useful when there is a substantial amount of data and, though generally static, it may change someday and/or being able to edit the data outside of the report definition is desirable. In this example, the user will be able to select a currency from a drop-down list.
 

The currencies are contained in CurrencyCodes.xml, part of which is shown above, and which should be added to the application's Support Files.
 

1. Add the Input Select List element to the definition, as shown above.
2. Beneath it, add a DataLayer.XML File element.
3. Set the Input Select List element's attributes as shown above. Note that the values for the Caption Column attribute and Value Column attribute correspond to the attribute names in the XML file.

4. Set the DataLayer.XML File element's attributes as shown above.

When the report is run, the datalayer will read all of the values from the XML file and the Input Select List will display them. If this report calls another report or process, based on the attributes set above, in that report the @Request.inpCurrency~ token will contain the value from the XML file's Code attribute. So, selecting the Australian Dollar in the select list will result in the value "AUD" being passed to the next report.
 

Using Datalayers to Retrieve SQL Data

DataLayer.SQL runs a SQL query to retrieve data from a SQL datasource. The element's Source attribute value is the actual SQL statement, and any valid SQL statement (or statements) will be run.

The example above shows a simple SQL query that returns data to a simple data table.

However, as shown in the example above, the Source attribute value (opened in the Attribute Zoom Window) can consist of multiple SQL statements. They will all be executed, as long as the syntax is correct, and any results will be returned to the datalayer. The Attribute Zoom window is opened for any attribute by double-clicking the attribute name.

While the multi-statement capability makes it easy to use them, your best performance for complex SQL queries and best security is still to be found by using stored procedures, and the DataLayer.SP element is used to work with them:
 

In the example shown above, DataLayer.SP is used to call a stored procedure. An SP Parameters element, and one SP Parameter element for each parameter, is used to pass arguments to the stored procedure. The result set from the stored procedure is retrieved into the datalayer.