Reports

Overview

Reports provide a way to generate dynamic documents based on templates (which can be made available to other modules).

Capabilities

Capability	Support	Comment
Generate report to PDF format		See how to generate report
Generate report to CSV format		See how to generate report
Generate report to plain text format		See how to generate report
Using asset into template		See how to include assets
Usable from script		See reports script API
Direct downloading from frontend		See downloading from web
Generating content for module communication
Including image from module.resources		See including resources
Hardcoding images in reports		See how to include hardcoded image in base 64
Images from osp-storage in reports		See Including attachments from osp-storage. Currently only work for PDF reports.

Examples

Visualize reports

Define a report with a FTL template

A report allow to generate text output (HTML web pages, e-mails, configuration files, source code, etc.) by associating a FreeMarker Template Language (FTL) template and a data source.

Generating a report consist to give a FTL template a data source and then process the template to achieve the final output. The output represent the report content, which is, alongside other information like the report name or format, stored inside a MongoDB collection. Then, by requesting a report, OnSphere will generate a file based on the report content and format and send it to the client that made the request, most probably the OnSphere frontend interface.

This implies that a report doesn’t represent an actual file but only the information necessary to generate it, stored as a MongoDB entry.

Template

The template is the part that represents the content of the file generated by the report. In order to have a dynamic content, we use FreeMarker Template Language (FTL) templates which provide different ways to interact with a given set of data to dynamically change the end result (i.e. if/else conditions, loops, …).

External tools

Note

A template must be a valid FTL template. If any structural issues exist within the template, they will be detected when trying to push your configuration.

Data source

Along side a template, a report require a data source given as a key/value map. Each value associated to a key is available in the FTL template directly by using the key. For example, if the given data map contains a key alarms with value as a list of objects, you can access the list of objects in the template directly like ${alarms}.

Available formats

Currently, three formats are available for reports:

pdf: any report with this format will attempt to generate a pdf with the template output. If the pdf generation is successful, the pdf will be converted into a base64 string that will be use to generate the actual pdf file when requesting this report.
csv: when requesting this report, the generated file will be a .csv file.
plain: this will generate a file without any specific extension, allowing you to generate any kind of textual output.

Define a generator

A generator is an entity that allow you to define a list of data sources for a given report that are going to be automatically fetch by OnSphere when using the generator. Without using generators, we will need to manually provide the data source when generating a report.

Using generators allow you to define more easily the data source for the report, but it comes with the drawback of not having a lot of flexibility. For example, a generator that will get some elements form a collection will fetch those elements and give them directly to the report. If you were to define the data manually (without a generator), you could process the collection elements before sending them to the report. Therefore, generators are a good option when the data needed for the report doesn’t need any transformation.

Available data sources

Data sources are configured by associating a key and a source in the generator configuration. Each source from a generator can fetch data from three different context:

Alarm: get all the alarms that match a filter/view for a given range of time
Collection: get all the collection entries that match a filter/view for a specific collection (represented by a schema) and given range of time
Analytics: get values stored in InfluxDB by using a pre-configured analytics query. Range is already set on the query and therefore is not necessary to provide a range in the generator himself
Values: get all values that match the list of value ids linked to that source. The wildcard ‘*’ can be used to match multiple value (For example, ‘root.*.test’ will match ‘root.device.1.test’, ‘root.server.test’)

A generator can define multiple data sources. Once each source is correctly handle, the generator generate the report with all the sources data. In the template, they become accessible by using the key associated to the data source (i.e. ${alarms}).

Warning

The key might be missing in the final data object accessible by the template if no data was retrieve from the data source. Checking if the element is present in the template before using it is a good practice then. To achieve this, you can use something like <#if alarms??>.

Generating report

Reports are defined by two entities :

template.reports : this entity is the main part of a report. It provides all the metadata for the report and the link to the actual FTL template
template-name.ftl: FTL template file. FTL tags can be used to define loops, condition, data mapping and so on. These tags allow to make dynamic template that can generate reports with dynamic data.

Reports can be generated using two different triggers: an action or a script.

Action: Generation request

Use an action with a type as GENERATE_REPORT to generate a report. The payload must contain :

template: the id of the template used. This reference a template.reports file, not to a FTL template.
name: Name of generated report. Be aware that every space in the name is replace by “_”. The current date is automatically appended at the end of the name to differentiate the different generated reports.
data: Data source to generate the report with. The structure of the data must be in accordance with the template.
locale (optional): locale used by FreeMarker to format numbers and dates, e.g. fr_CH.

If the action request is successful, the returned result object will contain a content property with the generated report. A report is composed of these properties:

result.content.template: template id used for the report
result.content.name: name of the report
result.content.format: type of media generated (PDF/CSV/PLAIN)
result.content.report: contains the PDF generated data in base64. Only contains something if format is PDF
result.content.parsed: contains the parsed version of the template. This represents the template after all data were merged in

Script: Generation request

Script possess an API that allow you to generate reports in two different ways:

Raw generation: a raw report generation is what we called when the report data is passed directly from the script context into the generating request. Meaning that you have to define, in your script, an object that will contain all the data the report need and use that object to generate the report. For example, if you want to get elements from a collection and pass them to the report, you will need to make a query using the script collections API to fetch the elements and then process the response before adding it to the data object you will send with the report generation request. This can add a lot of complexity to a script but it’s useful when you need to process the data before using it for the report.
Generate with a generator: you can use a generator.reports to define the data sources of the report and generate the report by simply passing the according generator. In opposition to the raw generation request, you have less control over the data you sent to the report as you can’t process them before generating the request.

Get more info about scripts reports API here.

Download from frontend

A menu output called download can generate a file based on any content you provide to it. Menu outputs can be used after an action was send. Therefore, you can follow the generation request action by an download output and feed this output with the result from the action.

For a download output, you need to provide the following inputs:

content: source content for the file to be generated. From an generation request action, you can retrieve result.content.report if the format of the report is PDF and result.content.parsed for any other type of report.
format: media type of the file. From an generation request action, you can retrieve result.content.format.
filename: name of the file when downloading. From an generation request action, you can retrieve result.content.name to use the name of the report, or use any other name.

Example

See : Downloading report from dashboard

Action: download pre-generated report

Use an action with a type as DOWNLOAD_REPORT to fetch an existing report. Downloading a report only returns the report data. In order to actually download the report into a file, you will need to use the download output, like explained for the generation request action.

The payload of this action must contain :

documentId: Id of the database document representing the report.

Warning

You have to provide the id of the database element representing the report, which implies that you have to retrieve this information first. Therefore, this action is meant to be used inside a menu.web referenced by a ReportsList widget.

If the action request is successful, the returned result object will contain a content property with the generated report. A report is composed of these properties:

result.content.template: template id used for the report
result.content.name: name of the report
result.content.format: type of media generated (PDF/CSV/PLAIN)
result.content.report: contains the PDF generated data in base64. Only contains something if format is PDF
result.content.parsed: contains the parsed version of the template. This represents the template after all data were merged in

Example :

{
    "label": "Download",
    "icon": "file_download",
    "context": [
        {
            "type": "ReportsList",
            "action": "root.reports.actions.download",
            "condition": "${reports.selected}.length === 0",
            "input": {
                "documentId": {
                    "extract": "reports.clicked.documentId",
                    "as": "string"
                }
            },
            "output": [
                {
                    "operation": "download",
                    "input": {
                        "content": {
                            "extract": "result.content.parsed",
                            "as": "string"
                        },
                        "format": {
                            "extract": "result.content.format",
                            "as": "string"
                        },
                        "filename": {
                            "extract": "result.content.name",
                            "as": "string"
                        }
                    }
                }
            ]
        }
    ]
}

Accessing resources

In order for the module to generate a report based on a template, it must have access to them. Then, all your FTL templates files (and any other assets used inside a template) must be part of the module resources. To do so, you must define the resources inside the module.resources file.

Assets usage

When adding an asset into a template (images,fonts,…), the asset must be part of the module resources. Then, when using them in a template, you need to use the relative path to the asset from the configuration directory as its source. For example, if you have an image called logo.png in the root/template/images directory, then the source must be root/template/images/logo.png, no matter where the template is in the configuration. The source can’t be an absolute path.

Custom OnSphere template API

Customs methods were implemented to simplify operations that you will most likely use in every template. All of these methods are accessible via an OSP object that is part of the template data.

Getting severity

This returns an object containing the severity.ospp and severity.reports files content. To have access to this method, you need to create a severity.reports file alongside the targeted severity.ospp.

To call this method inside a FTL template :

${osp.getSeverityById("root.alarms.severity.clear")}

This returns an object with (values with * cannot be empty):

name*: The name of the severity
description*: A description of the severity
severity*: The numerical value associated with this severity
fallback*: If set to true this severity will be used as fallback for any invalid/unknown severity
fgColor: The color of the text for alarms of this severity as an hex string
bgColor: The color of the background for alarms of this severity as an hex string
fgHiColor: The color of the text for alarms of this severity when selected as an hex string
bgHiColor: The color of the background for alarms of this severity when selected as an hex string

Get severity by severity

This method is similar to the precedent one but instead of using the severity id to find it, you can use its numerical value. To have access to this method, you need to create a severity.reports file alongside the targeted severity.ospp.

To call this method inside a FTL template :

${osp.getSeverityBySeverity(200)}

This returns an object with (values with * cannot be empty):

name*: The name of the severity
description*: A description of the severity
severity*: The numerical value associated with this severity
fallback*: If set to true this severity will be used as fallback for any invalid/unknown severity
fgColor: The color of the text for alarms of this severity as an hex string
bgColor: The color of the background for alarms of this severity as an hex string
fgHiColor: The color of the text for alarms of this severity when selected as an hex string
bgHiColor: The color of the background for alarms of this severity when selected as an hex string

Get Filter by Id

This returns an object containing the filter.ospp and filter.reports files content. To have access to this method, you need to create a filter.reports file alongside the targeted filter.ospp.

To call this method inside a FTL template :

${osp.getFilterById("root.alarms.filter.all")}

This returns an object with (values with * cannot be empty):

name*: The name of the filter
description*: A description of the filter

Get view by Id

This returns an object containing the view.ospp and view.reports files content. To have access to this method, you need to create a view.reports file alongside the targeted view.ospp.

To call this method inside a FTL template :

${osp.getViewById("root.alarms.view.default")}

This return an object with (values with * cannot be empty):

name*: The name of the view
description*: A description of the view
liveColumns*: The column to show on the live table
historyColumns*: The column to show on the history table

Convert timestamp in nanoseconds to a full date and time representation

This returns a date from an timestamp in nanoseconds. Every date or time in OnSphere is expressed in nanoseconds. You can use this method and the built-ins for dates values from FTL to handle dates as you want.

To call this method inside a template:

${osp.convertNanoToDatetime(alarm.firstTimestamp)}

This returns a date as a string that you might want to transform into a date.

${osp.convertNanoToDatetime(alarm.firstTimestamp)?datetime.xs}

You can use the built-ins functions:

Date and time: ${osp.convertNanoToDatetime(alarm.firstTimestamp)?datetime.xs}
Date: ${osp.convertNanoToDatetime(alarm.firstTimestamp)?datetime.xs?date}
Time: ${osp.convertNanoToDatetime(alarm.firstTimestamp)?datetime.xs?time}

Retrieves data from analytics module

This returns the result of the given query with the given query filters. These parameters can be easily retrieved from a Chart widget and by using a menu to generate the report while passing the widget context, which contains chart.query (string) and chart.queryFilters (array of filters).

To give a report those parameters using a Chart widget menu:

"type": "Chart",
"action": "root.reports.osp.actions.generate",
"condition": "true",
"input": {
    "template": {
        "expression": "'root.reports.osp.templates.csv'"
    },
    "data.query": {
        "extract": "chart.query",
        "as": "string"
    },
    "data.queryFilters": {
        "extract": "chart.queryFilters[*]"
    }
}

To call this method inside a template:

${osp.analyticsQuery(queryId, queryFilters)}

This returns the result of the query grouped by fields name, in the form of a map like {[key = fieldname]: [value = array of points for this field]}.

Retrieves entries from a collection

You can retrieve entries from a collection based on a schema and a list of ids.

To gather those information from a Collection table, you can extract them like:

"data.schema": {
  "extract": "schema",
  "as": "string"
},
"data.documents": {
  "extract": "rows.selected[*]._id"
}

To call this method inside a template:

${osp.collectionsListById(schema, documents)}

This returns directly the list of entries.

You can retrieve the historic with every entry as well by using a different method that requires a schema and a list of ids as well.

${osp.collectionsListByIdWithHistory(schema, documents)}

This returns the list of entries with a property history which contains the full historic (list).

Get an user preferences attribute value for a given user and attribute name

This returns either the value associated with the given attribute for the given user or the user given when the attribute is not found or any other error occurs. A defaultValue can be given (not mandatory) as a return value if the given attribute doesn’t exist for the user or if any other issues occurs.

To call this method inside a template:

${osp.getUserAttribute(user, attribute, defaultValue)}

Fetch for a BACnet device

Retrieves the object/properties of a BACnet device given an id. Returned a list of objects that contains an id and a properties list.

To call this method inside a template:

${osp.fetchBacnetDevice(deviceId)}

Including hardcoded images

Hardcoded images can be embedded in the report using two distinct methods: via HTML or CSS. This approach avoids the use of modules.resources for images that do not require dynamic processing, by directly embedding them as Base64-encoded data.

HTML example

Here is an example of a hardcoded Base64-encoded image for use in HTML.

<img alt="" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAMAAABg3Am1AAABqlBMVEUAAAAAAAAAAAAAAAAAAAAAAAAAAABHcEwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADyxX9OAAAAjXRSTlP+AhfuP8/9AGenu6Qjt1IT0vkKIeubZbU8vn77mUFjwUxKygVXsw3x9x641jndkIZxLOjpB0TzxDJWcN+LBJT6g9qrkS/yFN7vUzP23DBi0xhbyfzIDqMnCDZA5CVoBrD4Gh/YGZhuphDOlSjwVeWXOjThA18WnL9dXnsiTR0SLs2FgUhsw9eok7ys1boDfMHsAAABr0lEQVRIx+1VVVMDQQxO7QItVSq4u1txd3d3d3d3h/xn7toyZQba3TI88NC8fJu9+2a/ZJMsoI8GfgInQR7AaXIXQQmcpvwkkCKQwxTkJqh41KvcBOAjwO9P+EYwha2kW4L5JR0YpJysb/NK2juizJiMNNjUc0qygjkVMT6OVnkIwbr9XTiWVjuUsiZjxTBSJKnPdP6nBmgL7Yz3FsNoChWqtw5PEC/OEDfa8wSiXr0XSV2gSXKuXq4d0D0xBomeJaWCYtj19e3xMyajIPcoKZfSJVBm3aHNhlfWWckz0JBHSQNGIUiEHKCiV8070KnozIT36T1naQmiC6ST7p8L8zXZ52KyZFPuGH7I0ryZtMsiFjw95N1KG7IISh73dnEZyTQngoXsN2RFTFLTdKz34luENMQg0OClOR9x0gg6RvEtUI4opMeE2Boluv1kZ9SSllq+FlwgJTD6webSUOF0ByGBIamcIiQIqbE0Om8tkiGpWAgvrY+pFYAasiI7sqnZxGrRMMfIqqoLdaAQxW7REnVZdLVYISGVcU3a2H8zl5gEX4exr+Pe5wfF/07/OeEDZOCFKCpfC84AAAAASUVORK5CYII=" />

CSS example

Here is an example of a hardcoded Base64-encoded image for use in CSS.

<style>
    .image-container {
        width: 48px;
        height: 48px;
        background-image: url("data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAMAAABg3Am1AAABqlBMVEUAAAAAAAAAAAAAAAAAAAAAAAAAAABHcEwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADyxX9OAAAAjXRSTlP+AhfuP8/9AGenu6Qjt1IT0vkKIeubZbU8vn77mUFjwUxKygVXsw3x9x641jndkIZxLOjpB0TzxDJWcN+LBJT6g9qrkS/yFN7vUzP23DBi0xhbyfzIDqMnCDZA5CVoBrD4Gh/YGZhuphDOlSjwVeWXOjThA18WnL9dXnsiTR0SLs2FgUhsw9eok7ys1boDfMHsAAABr0lEQVRIx+1VVVMDQQxO7QItVSq4u1txd3d3d3d3h/xn7toyZQba3TI88NC8fJu9+2a/ZJMsoI8GfgInQR7AaXIXQQmcpvwkkCKQwxTkJqh41KvcBOAjwO9P+EYwha2kW4L5JR0YpJysb/NK2juizJiMNNjUc0qygjkVMT6OVnkIwbr9XTiWVjuUsiZjxTBSJKnPdP6nBmgL7Yz3FsNoChWqtw5PEC/OEDfa8wSiXr0XSV2gSXKuXq4d0D0xBomeJaWCYtj19e3xMyajIPcoKZfSJVBm3aHNhlfWWckz0JBHSQNGIUiEHKCiV8070KnozIT36T1naQmiC6ST7p8L8zXZ52KyZFPuGH7I0ryZtMsiFjw95N1KG7IISh73dnEZyTQngoXsN2RFTFLTdKz34luENMQg0OClOR9x0gg6RvEtUI4opMeE2Boluv1kZ9SSllq+FlwgJTD6webSUOF0ByGBIamcIiQIqbE0Om8tkiGpWAgvrY+pFYAasiI7sqnZxGrRMMfIqqoLdaAQxW7REnVZdLVYISGVcU3a2H8zl5gEX4exr+Pe5wfF/07/OeEDZOCFKCpfC84AAAAASUVORK5CYII=");
    }
</style>
<div class="image-container"></div>

Including attachments from osp-storage

Warning

This feature only works for reports intended to be used to generate a PDF file. This won’t work if the goal is to create a plain HTML file.

Storage attachments can be incorporated in a report by using a specific source in the HTML image src attribute.

Source must follow the format: osp-image://image?uploadId=id&imageId=id, where:

uploadId: Item id of the upload configuration used to upload the file.
imageId: Identifier of the attachment

Example

Here is an example of how you can load an image.

<img style="object-fit: contain; height: 150px" src="osp-image://image?uploadId=root.upload&imageId=69c2938bd3219c532609bb66"/>