A datalayer is a temporary container for data retrieved from a datasource and, after retrieval, its data is cached in memory and/or in XML data files on the web server. This topic introduces developers to the use of datalayers in Logi Info.
- Selecting the Correct Datalayer
- Debugging Datalayers
- Using the Datalayer Data
- Using Local Data
- Manipulating the Data
- Datalayer Error Handling
- Usage Examples
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-, and JDBC-compliant databases
- XML, CSV, Excel, JSON, and other data files
- LDAP directories
- Web feeds, web services, or web pages
- REST and SOAP APIs
- 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.
The following table lists the typical uses for datalayers within Logi reporting products. The links direct you to individual topics that discuss the use of each datalayer.
|DataLayer.ActiveSQL||A special type of datalayer designed for use with the Analysis Grid super-element, it only retrieves a limited number of rows based on an initial SQL query and, in response to runtime manipulations of the Analysis Grid interface by users, it dynamically modifies and resends its query.|
|DataLayer.Bookmarks||Retrieves data directly from bookmark collection files.|
|DataLayer.Cached||Modifies the normal data retrieval activities of datalayers; when it's used, data is retrieved, cached, and made available for use in Logi reports for a specific time period, after which the data is refreshed (deleted and recreated).|
|DataLayer.CSV||Retrieves data directly from a .CSV text file.|
||Uses Logi Services for data retrieval and is only available in Logi Info if the Discovery Module v3.x has been installed.|
|DataLayer.Dataview||Retrieves the data for the Thinkspace element.|
|DataLayer.Definition List||Retrieves a list of definition files used in your application, and their properties, such as author name, timestamp, and engine version.|
|DataLayer.Directory||Retrieves a list of files and/or folders in a specified directory, including their size, timestamps, and other properties.|
|DataLayer.Excel||Retrieves data directly from a Microsoft Excel spreadsheet file.|
|DataLayer.Fixed File Format||Retrieves data directly from text files that use column widths to specify data format.|
|DataLayer.Google App||Enables connection to the datastore of a published application hosted by the Google App Engine.|
|DataLayer.Google Spreadsheet||Retrieves data in a Google online spreadsheet.|
|DataLayer.GPX File||Retrieves data directly from a GIS data file in the GPX format.|
Retrieves data directly from a JSON data file.
Prior to v12.1, this datalayer was named DataLayer.JSON File.
|DataLayer.KML File||Retrieves data directly from a GIS data file in the KML format.|
|DataLayer.LDAP||Retrieves data from a Lightweight Directory Access Protocol (LDAP) directory.|
|DataLayer.LDAP Authentication||Authenticates user against a Lightweight Directory Access Protocol (LDAP) directory.|
|DataLayer.Linked||Reuses data retrieved in another datalayer. For more information, see Link Datalayers.|
|DataLayer.MDX||Retrieves cube data and populates Logi OLAP Grid or OLAP Table elements.|
|DataLayer.Mongo Find||Retrieves documents from a MongoDB collection using the MongoDB Find API.|
|DataLayer.Mongo Map Reduce||Runs a MongoDB map-reduce operation to return one or more documents. For more information, see|
|DataLayer.Mongo Run Command||Runs a MongoDB command, suitable for use with the Aggregation Pipeline, a simpler alternative to using map-reduce operations, to return one or more documents.|
|DataLayer.Plugin||Retrieves data from a custom-written code module, the "plug-in".|
|DataLayer.REST||Retrieves data from a REST-style web service.|
|DataLayer.Scheduler||Retrieves data associated with the Logi Scheduler service. For more information, see Logi Scheduler.|
|Retrieves data from Amazon's SimpleDB web service.|
|DataLayer.SP||Retrieves data from a SQL database, using a Stored Procedure.|
|DataLayer.SQL||Retrieves data from a SQL database, using a SQL query.|
|DataLayer.Static||Uses hard-coded data values.|
|DataLayer.Twitter||Retrieves tweets and messages from the Twitter web site.|
|DataLayer.WebFeed||Retrieves data from an RSS or Atom web feed.|
|DataLayer.Web Scraper||Retrieves data from within web pages or HTML files.|
|DataLayer.Web Service||Retrieves data from a SOAP-style web service.|
Retrieves data directly from an XML data file.
Prior to v12.1, this datalayer was named DataLayer.XML File.
|DataLayer.XOLAP Query||Pre-defines a data view based on a Logi XOLAP cube.|
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. The Connection.HTTP element allows the following datalayers to optionally use a connection to an HTTP or HTTPS datasource that requires authentication:
The following datalayers include the optional Http Method attribute, which allows you to select the one of these verbs to be sent with a REST request: DELETE, GET, POST, or PUT, or to enter a custom verb. The default is POST.
Many datalayers are run by the Logi Engine in separate threads, which means that data retrieval time is limited to the longest running query, rather than the number of queries, resulting in excellent performance. This includes all datalayers except the following: Local Data datalayers, DataLayer.Linked, DataLayer.XML, DataLayer.Cached, DataLayer.Bookmark, DataLayer.Static, and any datalayer whose data is displayed using the Auto Columns element.
Data Engine performance, when processing large numbers of DateTime-type columns in a result set, can be significantly improved by skipping Time Zone processing, if it's not needed in the application. 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.
Datalayer results and performance can be viewed during development using the Debugger Trace Report:
The debug information for a typical DataLayer.SQL run is shown above.
This is very useful in determining the exact query (especially if tokens are being used) and the execution time required. The actual data retrieved can also be viewed using the Memory Stream Data link. If multiple datalayers run concurrently, they'll be shown here with individual links to their details.
Element IDs for datalayers are optional. However, we suggest you always provide a unique datalayer element ID, for ease in understanding the definition and, significantly, so that the datalayers are easily identified in the Debugger Trace page.
More information about use of the debugger can be found in Debug Reports.
The following example shows how the data in a data table corresponds with the XML data retrieved using DataLayer.SQL to query the Northwind database:
The SQL query used was:
SELECT EmployeeID, LastName, FirstName, Title FROM Employees
The Logi 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, the data table shown above contains four columns and nine rows, which is equivalent to nine XML elements, each with four attributes.
Data retrieved by a datalayer is cached 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 by the DataLayer.XML and DataLayer.Web Service elements may need to be reformatted for use, and the XslTransform element is available for this purpose.
Use of "bracketed" column names, such as "[Order Date]", is supported. 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.
The scope, or availability, of data is generally limited to the element (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.
- The Data Table element within the div1 division can only use data from DataLayer_1 datalayer.
- The chart's Series.Area element within the div2 division can only use data from the DataLayer_2 datalayer.
- The Data Table element within the div3 division can also use the data from DataLayer_1 by virtue of the two elements, Data Layer Link and DataLayer.Linked, which link it to the datalayer's cached data.
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 values retrieved by Local Data's child datalayer is accessed using 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.
If there are multiple Local Data elements in use, it may be difficult to determine which data came from which Local Data element. To make usage easier to understand, if a Local Data element is given an element ID, then that ID can be incorporated into its tokens. For example, if the Local Data element ID is "localOrders" and its datalayer queries the Northwind Orders table, this token can be used to access Freight column data: @Local.localOrders.Freight~. This is an optional notation variant you may wish to use to improve definition readability.
By themselves, @Local tokens can only be used to access the values of the first row of the data retrieved under Local Data. However, 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. The linked data will then be subject to the same rules as those for any other datalayer (scope limited to its parent element and accessed using @Data tokens).
Developers often use this technique (linked Local Data datalayers) to reduce data retrieval to one query, which is then re-used via linking in multiple places in the report, for best performance.
Using Multiple Local Data Elements
It's also common to use multiple Local Data elements in the same report definition. 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.
Local Data datalayers are run before other datalayers, so data from them can be used to condition the retrieval of data by other datalayers. Their 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.
The Local Data element has 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).
This can be very useful, for example, if you just want to run the Local Data datalayer once when the report is first loaded.
Handling Columns with the Same Name
If you're using multiple Local Data elements, it's possible that their datalayers could retrieve data from data sources with similar schema and identical column names.
In the example shown above, suppose that LocalData1DataLayer.SQL retrieves a column named "EmployeeID" and LocalData2 DataLayer.XML does too. What will happen? When the data from both datalayers is combined, the values for EmployeeID from the first datalayer will be overwritten
by the values
from the second datalayer. Using
the @Local.EmployeeID~ token later in the application will only produce values from the second datalayer.
To make the values from the first datalayer available, use a Calculated Column element, as shown above, beneath it. This will add a new column with the same values but a different name. Then in the application, use the @Local.calcEmployeeID~ token to access the values. Note that, if the EmployeeID column contains string values, in the Formula attribute you'll need to enclose the @Data.EmployeeID~ token in double-quotes.
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 topics that discuss the use of each element:
|Aggregate Column||Adds a new column to the datalayer that aggregates data from another column. Functions include Average, Count, DistinctCount, Max, Median, Min, Mode, Sum, and StdDev.. For more information, see The Aggregate Column|
|Color Spectrum Column||Adds a new column to the datalayer, containing color values that fall between the LowValueColor and HighValueColor attribute values. The color for each row is generated based on where the current Data Column value falls between the Min Range and Max Range|
|Compare Filter||Analogous to the WHERE clause in a SQL query, this element removes rows from the datalayer that do not meet specified criteria. Uses native data engine code for fastest performance. For more information, see The Compare Filter.|
|Condition Filter||Similar to the Compare Filter, this element uses scripting to remove rows from the datalayer that do not meet specified criteria. However, this filter uses scripting and therefore performs much more slowly than the Compare Filter. We recommend that you use the Compare Filter instead whenever possible. For more information, see The Condition Filter.|
|Contain Filter||Analogous to using a WHEREx CONTAINS y clause in a SQL query, this element removes rows from the datalayer that do not return results in a text search of specified columns. For more information, see The Contain Filter.|
|Crosstab Filter||"Pivots" the data to convert the datalayer into a "cross-tab" format.|
|DeDuplicate Filter||Analogous to using a DISTINCT clause in a SQL query, this element removes rows from the datalayer that have duplicated values in specified columns. For more information, see The DeDuplicate Filter.|
|Difference Column||Adds a new column to the datalayer that contains the difference, as a number or as a percentage, between a numeric value in a column in the current and the previous rows.|
|File Column||Used with BLOBs and CLOBS, this element copies the column data and saves it to a file, then optionally adds columns to the datalayer containing the saved file's name and path. For more information, see The File Column (BLOBs).|
|Flattener||Processes hierarchical data into a tabular set of rows and columns, so that it can be used with a range of Logi elements. For more information, see The Flattener.|
|Forecasting Elements||Forecasting elements use a variety of techniques to produce projected values by analyzing existing values. The future values they "predict" are, in most cases, added as rows or columns to a datalayer so the data can be displayed along with the existing data. For more information, see The Forecasting Elements.|
|Formatted Column||Adds a new column to the datalayer that represents the source value after formatting. For more information, see The Formatted Column.|
|Group Filter||Groups rows in the datalayer based on one or more column values and allows group aggregate values to be created. For more information, see The Group Filter.|
|Join||Analogous to a JOIN clause in a SQL query, this element adds columns to the datalayer so that multiple tables can be joined to create a single dataset. For more information, see Join Datalayers.|
|Lookup Element||Performs a "look up" of related data. It runs its own datalayer once for each row in its parent datalayer and automatically joins the results. For more information, see The Lookup Element.|
|Moving Average Column||Adds a new column to the datalayer based on the average value of another column spread over some number of previous rows. Used to smooth data series and make it easier to spot trends.|
|Percent of Total Column||Adds a new column to the datalayer containing the percentage of some value in the row compared to the total of that value in all rows.|
|Rank Column||Adds a new column to the datalayer containing either a numeric or percentile rank based on all the values of some other column.|
|RegEx Filter||Removes rows from the datalayer by applying pattern matching using regular expressions. For more information, see The RegEx Filter.|
|Relevance Filter||Removes rows (irrelevant data) from the datalayer that do not meet a threshold; optionally, irrelevant rows can be retained, grouped together, and handled as an individual entity. For more information, see The Relevance Filter.|
|Remove Columns||Removes one of more columns from the datalayer to reduce the size of the data during processing. If a column is no longer required, you can use this element to delete it.|
Rename Columns||Renames one or more columns in the datalayer. To use it, specify a comma-separated list of existing column names in the Column Names attribute, and a matching number of comma-separated new names in the New Column Name attributes. Columns named in the first list but which do not exist will be ignored.|
|Running Total Column||Adds a new column to the datalayer containing values based on the sum of all previous values of another column.|
|Security Filter||Removes rows from the datalayer, like a Condition Filter, but is applied based on a user's security rights. For more information, see Work with Logi Security.|
|Sequence Column||Numbers the rows in a datalayer by adding a new column containing a sequence integer for each row, starting at 1. Also very useful for counting the number of datalayer rows.|
|Sort Filter||Sorts the datalayer rows. For more information, see The Sort Filter.|
|Switch Column||Makes data replacements. It's analogous to using both a CASE structure and a REPLACE function in a SQL query. For more information, see The Switch Column.|
|Time Period Column||Adds a new column to the datalayer containing a time period value (Year, Month, Day, Hour, Minute, etc.) parsed from another column's date-time value. Eliminates the need to parse date-time values using Calculated Columns and script functions. For more information, see Time Period Columns.|
|UnCrosstab Filter||Used to "un-pivot" or "reverse pivot" rows of data into columns of data, it converts one row of multi-column data into multiple rows, each with a single data value. For more information, see The UnCrosstab Filter.|
|Unit Conversion Column||Converts column values from one standard measurement to another. For more information, see The Unit Conversion Column.|
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
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.
The If Data Error element 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 Debugger 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.
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.
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:
- Add the Input Select List element to the definition, as shown above.
- 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.
- 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.
- 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.
The DataLayer.XML 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.
- Add the Input Select List element to the definition, as shown above.
- Beneath it, add a DataLayer.XML element.
- 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.
- Set the DataLayer.XML element's attributes as shown above (assumes the file is in the _SupportFiles folder.)
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.
One of the most common usages of the datalayer is to retireve data from a database. The next two examples show how to use a datalayer to retrieve data from a SQL database and they assume an appropriate connection element for the database server has already been configured. See Datasource Connections for more information.
This example uses DataLayer.SQL to run a query. The datalayers's Source attribute value contains the actual SQL statement to be executed and any valid SQL code can be used. This example uses a connection to a Microsoft SQL Server database.
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. More information is available in DataLayer.SQL.
While the multi-statement capability shown above makes it easy to use complex SQL queries, the best performance and the best security for them is achieved by using the DataLayer.SP element to run a stored procedure.
In the example shown above, DataLayer.SP is used to call a stored procedure. Using stored procedures provides the best protection against a SQL Injection attack.
An SP Parameters element, and one SP Parameter element for each parameter, are used to pass arguments to the stored procedure and to receive output values. The result set from the stored procedure is retrieved into the datalayer.
The order of the SP Parameter elements, not their IDs, is used to determine their correlation to the declared variables in the stored procedure code. More information is available in DataLayer.SP.