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:
- 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.
Using XML File Data in an Input Select List
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. 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.
Using Datalayers to Retrieve SQL Data
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.
Comments
0 comments
Please sign in to leave a comment.