Creating a Report using Report Design Time Data Property

In the previous examples we used the Pre-configured Query Options to retrieve data for the reports. These Query Options retrieve the data in a well-defined JSON table structure which is visible at Design Time in the Report Designer Display Dictionary. It is also possible to customize the JSON data structure, rather than use the Query Options, by defining the JSON table in the Report Design Time Data property. The following example will show you how to configure a Report item in this way and design a report without using the Query Options.

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.

We will first configure the data structure for the report by adding a JSON string to the Report Design Time Data property containing the data fields we wish to use at design time in the Report Designer display. Click on "…" by Report Design-Time Data in the Object Properties panel of Test Report item and enter the following JSON table:

{
  "phases":
  [
    {
      "data" : [{
            "name": "P1 Tag1",
            "val": 133.3
        }, {
          "name": "P1 Tag2",
          "val": 143.3
        }
      ],
      "phase": 1,
      "message": "Good"
    },
    {
      "data": [
        {
          "name": "P2 Tag1",
          "val": 13.3
        },
        {
          "name": "P2 Tag2",
          "val": 14.3
        }],
      "phase" : 2,
      "message": "Good"
    }
  ],
  "header": {
    "end_t": "2017-09-20T10:00:00.000Z",
    "product": "Acetyl",
    "start_t": "2017-09-20T08:00:00.000Z"
  }
}

The above JSON file structures "data" tables within a "phases" table that also contains a "phase" and "message" field. Each "data" table contains "name" and "value" fields. The JSON also contains a "header" table containing time and other information fields.

The values for the data fields in the Report Designer display are filled using the placeholder values from the Report Design-Time Data property JSON. They are only visible at Design-Time in the Report Designer display and do not represent real values. At run time, the values displayed in the report will be filled from source data items.

Right-click on the Test Report and select Add item to… > Report Designer from the context menu. Click on the Dictionary tab in the right hand panel to display the available data sources.

Figure 1. Dictionary Tab in Report Designer Display

The available data items (including pre-configured system variables and functions that can be added to reports) are grouped under nodes. The data fields specified in the Report Design Time Data JSON are under the Data Sources node (the data set name "ds" is specified by default in the DataSet name property of the Report Item). Expand the node fully to display all the data fields.

Figure 2. Dictionary Tab - Data Sources

The "phases", "data" and "header" tables from the Report Design Time Data JSON string are listed with the appropriate data fields (and associated realtional ID for the table). The relational "phases" table under data contains the "phase" and "message" fields associated with the particular instance of the "data" table.

If the data source table and data fields from the Report Design Time Data JSON are not visible in the Dictionary tab then there is probably a syntax problem with the JSON file. Check the Report Design Time Data JSON for mistakes until they are visible in the Dictionary tab.

Beginning to Design a Report

The report layout is made up of bands that are added to the Report Designer workspace by clicking on the "Bands" icon on the left hand side of the workspace. In this example we will use the "Report Title", "Group Header" and "Group Footer" bands and, most importantly the "Data" band.

Figure 3. Adding a Band to the Report Design

Click on the "Bands" icon and select "Report Title" from the list of options. The cursor changes to a pencil icon and you can click in the report workspace to add the band or drag and resize the band to the desired size (the band will automatically fill the width of the workspace page). By clicking on the icons in the bar to the left of the workspace window you can now add items to the Title band report such as text, shapes and images. We will first add the Report Name from the Dictionary tab in the right hand panel by expanding the "System Variables" tree. Click on "ReportName" from the list and drag it into the title band.

Figure 4. Adding System Variables to the Report Title band

The "ReportName" variable takes its content from the Report Design Data, Report Name property of the Report Item object. When a Report Item object is added to the Report Designer display, a Report Design Data entry is automatically created to store the design layout (as an XML file in Report Design). It is automatically given the name "Report" but you can change this in the Object Properties panel and it will be reflected in the "ReportName" system variable.

Be sure to save the report layout in the Report Designer first before making any changes in the Report Item Object Properties panel, otherwise the Report Design layout will be lost. A warning will be displayed when you apply any changes to the Report Item object.

Change the name to "Example Report" and click Apply.

Figure 5. Report Design Data

The ReportName properties (font, text size, alignment, colour etc.) can be changed by selecting it in the workspace and clicking on the Properties tab of the right hand panel. Change the title properties to your liking and make sure to resize the text box to accommodate any changes.

Figure 6. Properties panel for selected objects in the Report Designer workspace

Other System Variables can be added to bands in the workspace for example, the "Time" variable will display the time at which the report is generated.

Adding Group Header, Data and Group Footer Bands

Now we will add data bands to the report that will use values from the fields specified in the Report Design Time Data JSON. We will use the Group Header and Footers to group the data according to the "phase" table instance that the data occurs in. The grouping of data is useful to provide logical separation of data based on your data structure. Add a "Group Header" band to the report by clicking on the "Bands" icon and selecting "Group Header". When the pencil cursor is displayed, click in the Report Designer workspace and the Group dialog will be displayed. Expand the "data" and "data.phases" nodes.

Figure 7. Group Dialog

Select the "phase" item in the "data.phases" table and click OK. The Group condition is now set to group the data in terms of the "phase" number. The group condition is visible at the top of the "Group Header" band.

Figure 8. Group Condition

We can now add the "data.phases.phase" item into the Group Header to indicate to which phase the data is related. To do this, drag the "phase" item from the Dictionary tab into the Group Header band.

Figure 9. Adding Item to Group Header

Double-cick on the "data.phases" text box to open the "Text" edit dialog and select "Expression" from the options on the right. From here you can add in any other text to be displayed in the text box. The expression in curly parentheses is the "phase" data field selected fom the Dictionary and will be completed during the run-time of the report. Add some text into the expression box to provide context fro the data field value, for example:

Figure 10. Adding Text with the Expression Dialog

We will now add the "Data" band to the report by selecting "Data" after clicking on the "Bands" icon and clicking in the workspace. This brings up the Data dialog where the data band is assigned to the required table from the Report Design Time Data JSON. The fields we wish to be in the report are in the "data" table so once selected, click OK.

Figure 11. Data Band Assignment

Click on the Dictionary tab in the right hand panel, and drag and drop the "name" and "val" items from the "data" table into the Data band. These items will be displayed in the report at runtime.

Figure 12. Adding Items to Data Band

To complete the group assignment we will now add a "Group Footer" band from the "Bands" menu. Click on the "Bands" icon and choose "Group Footer" from the menu. Click in the workspace to add the band to the report and add the "message" data item from the Dictionary tab into the footer. We will also add a Count function to the footer that counts the number of entries in the "data" table of the assigned group. To do this, expand the "Functions" node in the Dictionary tab and under "Totals", drag and drop the Count(): long item into the "Group Footer". You can also add some extra text to these items to give some context to the values in the report by double clicking on the items and using the "Expressions" dialog. For example, add "Message" and "Count" like below:

Figure 13. Report Layout in Designer

To view a preview of the report appearance, click on the "Preview" tab at the top of the workspace. In the preview the data fields are filled by the values provided in the Report Design Time Data JSON. At run-time the data fields will be filled by values supplied by the dynamic property of the Report Item (in most cases this will be a JSON string returned by the embedded Lua script). To make the report clearer and easier to read, change some of the display properties of the bands in the Properties tab of the right hand panel in the Report Designer. For example, changing the background colour of the "Group Header" band and changing text colour.

Figure 14. Report Preview

As you can see from the above example, the data is grouped by phase (as per the grouping condition applied to the "Group Header" band). Removing the group condition would mean the data would be displayed all in one group. When you are happy with the deign of the report, save the layout in Report Designer and move onto the next section.

Report Run-time

After specifying the data structure in the Report Design Time Data JSON and designing a report layout using the items from the JSON, the next step is to provide real-time values to populate the specified data fields. This can be done by creating a Lua script in the Report item that retrieves values and then returns a JSON string containing the values (that matches the Report Design Time Data JSON). Alteratively, a JSON string can be set as the value of the ReportItem by another script running periodically in, for example, a Generic Item. To encode the return value as a JSON string we use David Kolf’s JSON library for Lua that can be downloaded here. Copy the library into a Script Library in the System or Core objects. We will retrieve data for the report from the DemoData items that can be created using the MassConfig file here (download the version that matches your system:inmation version).

Open the Lua Script Body property of the Report Item and insert the following Lua script:

local json = require ("dkjson")
local p1tag1 = syslib.getobject("/System/Core/Examples/DemoData/ProcessData/DC4711")
local p1tag2 = syslib.getobject("/System/Core/Examples/DemoData/ProcessData/FC4711")
local p2tag1 = syslib.getobject("/System/Core/Examples/DemoData/ProcessData/DC666")
local p2tag2 = syslib.getobject("/System/Core/Examples/DemoData/ProcessData/FC666")

local function qual(tagname)
	v, q, t = syslib.getvalue(tagname)
	if (inmation.isgoodstatus(q) == true) then
		return "good"
	end
	if (syslib.isbadstatus(q) == true) then
		return "bad"
	end
	if (syslib.isuncertainstatus(q) == true) then
		return "uncertain"
	end
end
local process =
{
  phases = {{
      data = {{
          name = p1tag1.ObjectName,
          val = p1tag1.ItemValue
        }, {
			name = p1tag2.ObjectName,
          	val =  p1tag2.ItemValue
			}},
      phase = 1, message = qual(p1tag1) .. ", " .. qual(p1tag2)}, {
	  data = {{
          name = p2tag1.ObjectName,
          val =  p2tag1.ItemValue
        }, {
			name = p2tag2.ObjectName,
          	val =  p2tag2.ItemValue
			}},
      phase = 2, message = qual(p2tag1) .. ", " .. qual(p2tag2)
    }},
  header = {
    end_t = "2017-09-20T10:00:00.000Z",
    product = "Acetyl",
    start_t = "2017-09-20T08:00:00.000Z"
  }
}

return json.encode(process)

Click Ok to add the script to the Report Item and execute the script once. The Faceplate of the report item will show the JSON string returned in the last line of the script: return json.encode(process).

Figure 15. Faceplate of Report Item

The Lua script can be broken into 4 sections:

At the beginning, the json library "dkjson" is retrieved from the script library using the require function and objects from the I/O model that will supply the run-time data values are retrieved using the syslib.getobject() function.
A function, "qual", is defined that checks the quality of the retrieved values and returns a message.
A Lua table, "process", is defined that mimics the structure of the Report Design Time Data JSON. Each of the data fields has a value defined from the retrieved I/O model values.
The Lua table, "process", is encoded and returned as a JSON string

To view the produced report add the Report Item to a Dashboard display. The Report is added to one tile on the dashboard, so to see it more clearly you can reduce the number of rows and columns in the Dashboard.

Figure 16. Report in Dashboard

If the Report does not appear in the Dashboard you may need to change to "Report View" in the Dashboard menu bar. If the report is still not visible try refreshing the dashbard tile by right clicking, selecting Report Template and clicking on the Report Design Data template name.

As mentioned earlier, Report Items are similar to Action Items and can be triggered to create the report through links to other objects. To do this, create a new Generic Item in the Report Demo folder (right-click on the folder and select Admin > New > Data Processing > Generic Item), name the item "Trigger", change the Generation Period propertyto 30,000 milliseconds and click Create. Once the item is created in the I/O model, create a triggering link by dragging it onto the Report Item and when the Set object reference dialog appears click OK.

Figure 17. Create Triggering Link to Report Item

A new report will be created every 30 seconds containing updated values for each of the data fields. To save or a copy of the report, right-click on the report tile in Dashboard and select Save or Print from the context menu. The reports can be saved in a number of different formats.

Figure 18. Save or Print Report

Fit Source Data

The system expects that every data field assigned in the report design will be matched by a corresponding data field in the source data (provided by the Lua script execution). If this is not the case, an exception will be thrown when the report tries to generate. In production settings it is not always the case that all the assigned data fields will be constantly present in the source data. In these cases, the Fit Source Data property allows the system to evaluate the source data against the design time data and fit as best possible. This avoids the exceptions and allows the report to be generated with an incomplete set of source data. To demonstrate this, open the Report Design Time Data JSON of the Report Item and add an extra data field "comment" below the "message" data field.

Figure 19. Add Extra Data Field to Report Design Time Data JSON

Click OK, to commit the change and if you already have a Report Designer display open containing the Report Item, a message will be displayed telling you that changes have been applied to the Report Item. Click OK again to reload the item. Open the Report Designer display for the Report Item and in the Dictionary tab, locate the "data.phases.comment" data field and drag it into the Group Footer Band.

Figure 20. Adding Comment Field to Group Footer

Save the report in Report Designer and then add the Test Report Item to a Dashboard Display. In the Session Log a System.Exception error should be being displayed, indicating that there is no definition for the "comment" field. Click the checkbox for Fit Source Data in the Test Report Item and click Apply.

Figure 21. Apply Fit Source Data Property

Add the Test Report item to the Dashboard again and this time the report will be generated successfully with no errors as the available source data is fitted to the design time data fields.