Creating a Report Item with preconfigured Query Options

The quickest way to get started with Reporting is to use the pre-configured Query Options in the ReportItem. This allows you to quickly access Realtime and Historical data values in a report with the minimum of Lua scripting and configuration. We will also set a Namespace Query Option, which searches for all objects of a partcular class (and their selected properties) from the namespace to add to a report. In this example we will create a Report with both Realtime and History values retrieved from items in the I/O model, using the DemoData example items used in the Lua Scripting Jump Start (these example items can be configured by downloading and importing the Examples_DemoData Mass Config file from the resources page) to populate the I/O model (you can use any data items present in your own system if you wish).

Create a folder named "Report Demo" object underneath the Core to house the objects that we will create in the examples in this Jumpstart (right-click and select Admin → New → Data Processing → Folder). Create a Report Item in the folder (right-click and select Admin → New → Report> Report Item) and name it Test Report.

Table Demo items in I/O Model
Figure 1. Table Demo items in I/O Model

Adding a Realtime Query Option

Select the Test Report item and in the Object Properties panel, open the Report set of option and click the plus sign to add a RealtimeQuery option.

Adding a Realtime Query Option
Figure 2. Adding a Realtime Query Option

Click on Query Objects to open a Table Property Grid dialog. Here multiple query objects can be added by dragging and dropping items from the I/O model into the Query Objects column. Drag a data producing item from the I/O model into the Query Objects column, then click Apply.

Adding Query Objects
Figure 3. Adding Query Objects

Back in the Object Properties panel for the Report Item. Any Query Options added here will be accessible when designing the report so it is useful to give each Query Option a Table Name Prefix to make it distinguishable from other query options. It is also useful to select the Item Names Template-Able checkbox so that report templates can be exported and reused without being tied to certain item names. Open the Properties drop-down menu and scroll down to the object type you selected for the query option. Here you can select properties for your Query Object that will be available for use in the Report Designer. Click Apply to save the changes.

Selecting Object Properties for Query Options
Figure 4. Selecting Object Properties for Query Options

Adding a History Query Option

Adding a History Query Option is similar to adding a Realtime Query Option. Click the plus sign and add an object via the Query Objects table properties dialog (for this example, the same object was chosen for History and Realtime Query options so both can be displayed together in the report) and select available Properties from the drop-down menu. For the History Query, there are also options available for how the historical will be returned. It is necessary to select an aggregate for the retrieved history data and request the time period (clicking on Timesettings will open a standard DataStudio Time Period Settings dialog).

History Query Option Settings
Figure 5. History Query Option Settings

Select an aggregate from the drop-down menu and set a time period (for this example the aggregate is set to Interpolative and the time period set to retrieve the last hour of data with an interval count of 6). The other options are standard OPC History call options that correspond with the OPC UA standard, we will leave these as default for the moment. Click Apply to save the Query Options configurations to the Report Item.

Adding a Namespace Query Option

The Namespace Query Option is different to the Realtime and History Query Options in that rather than selecting query objects to retrieve data from, the namespace itself can be queried to return all objects of a certain class (Data Holder, Generic Item etc.), including any selected properties (for example, ObjectName). To add a Namespace query option, open the Report section in the Object Properties panel of the Report Item and click on the plus sign next to NamespaceQuery Options. The settings are similar to the Realtime and History query options, there is a table name prefix and the option to make the item names template-able. A Search Root must be defined (the level in the I/O model that the namespace search will begin to look), the default is I/O model root but we will change this to search the namespace from the DemoData root. The class of object is selected from the drop down menu (here we choose Generic Item) and properties for that item (Object Name and Engineering Unit are selected).

Namespace Query Options Settings
Figure 6. Namespace Query Options Settings

Click Apply to save the Namespace Query Option.

Adding a Lua Script to retrieve Query Option Data

Although the Query Option objects are defined, it is still necessary to add a Lua script to retrieve the data when the report is created at Run Time. It is also necessary to add a Lua script to return the JSON structure including all data fields that will be available to design a report during the Design Time phase. To do this click on the Lua Script Body to open a Script Editor display.

Adding a Lua Script to Retrieve Data from Preconfigured Query Objects
Figure 7. Adding a Lua Script to Retrieve Data from Preconfigured Query Objects

Enter the following Lua script into the editor and click Ok.

local report = require ("")
return report:getjsondata(syslib.getself():path(), false)

This Lua script calls the library and then invokes the getjsondata() method. This method has two parameters: pathtoReportItem - this parameter is set as a path to ReportItem for which data will be created (in this case we use the syslib.getself():path() method to set the path. limitNumberOfRecords - a boolean operator that if set to "true", will limit the number of records returned. We set this to "false" as we want all records for the defined time period to be returned getjsondata() reads the Query Options configured in the Report Item and returns the retrieved data for the selected query objects in JSON format. Each different Query Option is stored in a separate array in the JSON. This can be seen in the Faceplate for the Report Item.

When you click Ok in the Lua Script Editor, the script is executed once and a JSON is returned containing the query options, objects and properties that were configured. The values that populate the dynamic fields are the ones retrieved at the moment that the script is executed. These are the values that will be visible during Design Time and will remain static unless the script is re-excuted (for instance, if the Core service is stopped then the Report Item script will be re-executed when the Core is re-started.

Once the Query Objects have been configured and the Lua Script has been executed to return a JSON containing the all the Query Options, we can now use the Report Item to design a report layout in the Report Designer Display.