Syntax to create a report custom template

These templates allow to generate text reports having a custom format.

Prerequisites

Throughout the remainder of this document, the term template will refer to ScribanTextTemplate

   public string Id { get; set; }
   public string TemplateName { get; set; }
   public int AccountId { get; set; }
   public TemplateType TemplateType = TemplateType.String;
   public string CreatedBy { get; set; }
   public DateTime CreationDate { get; set; }
   public string UpdatedBy { get; set; }
   public DateTime? UpdateDate { get; set; }

   public string Body { get; set; } // The Scriban template.
   public string OutputExtension { get; set; } // The extension of the file generated from the template.
   public int TemplateFormat { get; set; } // The formatId of the generated file. This field will only be used in the UI.
   public int Version { get; set; } //The template engine version.

Template library

The report generator uses Scriban library version v2.1.3.
The syntax and the functionalities of this library can be found on this site
The list of the building functions can be found on this site

Variables

A template uses input variables and template variables.

Input variables may contains either metadata or data (aka data points) coming from their respective source definition (e.g. a source, a data point...).
Template variables are the variables defined on the template that will hold values used in the pseudo-code of the template.

1. Input variables defined in Data sources

{{while LoadDataBatch this["Consumption Index"] some_datapoint}}
...
{{end}}

LoadDataBatch is a loading function that will be explain below.
"Consumption Index" is the input variable containing the data corresponding to Data sources having this name.
The keyword this is used here to allow to specify a name containing white spaces. If the name does not contain white spaces, you may omit the this keyword:

{{while LoadDataBatch A_Name_Without_Space some_datapoint}}
...
{{end}}

some_datapoint is a template variable that will receive each data point corresponding to the data source definition.
In this case, some_datapoint will be a data point object containing the following properties:

  • SourceId
  • Date
  • VariableId
  • Value
  • UnitId

Example:

{{while LoadDataBatch this["Consumption Index"] some_datapoint}}
  {{some_datapoint.SourceId}}
  {{some_datapoint.Date}}
  {{some_datapoint.VariableId}}
  {{some_datapoint.Value}}
{{end}}

If multiple data sources have the same name (e.g more than one data source having the name "Consumption Index"), they will be iterated one after the other.

2. Input variables defined in Metadata

{{while LoadMetadataBatch MySourcesMetadata some_source}}
...
{{end}}

LoadMetadataBatch is a loading function that will be explain below.
MySourcesMetadata is the input variable and must correspond to one of the names specified in Metadata (e.g. MySourcesMetadata).
some_source is a template variable that will receive one instance at a time from the input variable.
In this case, some_source will be a DataHub source containing all properties of such a source.

Example:

{{for some_source in MySourcesMetadata}}
  {{some_source.Id}}
  {{some_source.Name}}
  {{some_source.Tags | array.join ","}}
  {{some_source.MyForm.MyGroup.MyField}}
  ...
{{end}}

3. Input variables on Datahub reports UI

image.png

Data Sources

Data Sources are the definitions of data points sources. When the scripts uses an input variable whose name is the one of a Data Source, the report engine will treat is as a collection of data points.

Metadata

Metadata refer to any DataHub object that is not a data point. In the above picture, the term MySourcesMetadata refer to the union of all sources related to all the defined data sources. See Available objects properties below.
The toggle button on meta data is there to reduce the amount of data that the reporting engine will load to generate the ouput: if a meta data is disabled, none of the corresponding data will be loaded.
As a general rule, only enable metadata that you will really use in the report template.

How to use input variables to generate output

Use some "functions" to load the data corresponding to the input variables.
In order to reduce the amount of data loaded at a time in the report generator, data are internally loaded by batch.

Load metadata batch

Data are loaded by batch to reduce the amount of resources present in the reporting engine environment.
This is expressed by a {{while LoadMetadataBatch ...}} loop:

{{ while LoadMetadataBatch MySourcesMetadata MyVariable}}
...
{{end}}

LoadMetadataBatch in an internal function that loads a batch of metadata
MySourcesMetadata is the name of an existing input variable defined by Metadata section (see Input variables defines in Metadata).
MySourcesMetadata is the name of the batch to iterate to. This name MUST be the same as the one specified in the outer {{while}} loop.
MyVariable is the name of the variable that will be contain one element.

Load metadata dependencies batch

Some objects can have a very large number of descendants.
To allow their loading, it is necessary to call an additional method.

This is expressed by a {{while LoadDependenciesBatch parentObject.***}} loop:

{{ while LoadDependenciesBatch MyAlertMetadata.Occurrences MyVariable}}
...
{{end}}

LoadDependenciesBatch in an internal function that loads a batch of metadata
MyAlertMetadata is the name of an existing input variable defined by Metadata section (see Input variables defines in Metadata).
Occurrences is the name of the property that need to be loaded separately (see Available objects properties).
MyVariable is the name of the variable that will be contain one element.

Load data points batch

Parameters

Type Name Description Value
Optional (default None) includeRelatedItems Load related items
!Huge performance impact.		 enum

None=0
IncludeBasedOnSource=1
IncludeBasedOnVariable=2
Optional (default True) useReportingTimezone Convert all dates to the variable’s parent site TimeZone true
false

This is expressed by a {{while LoadDataBatch ...}} loop:

{{ while LoadDataBatch this["Consumption Index"] MyVariable {includeRelatedItems:'IncludeBasedOnSource'} }}
...
{{end}}

LoadDataBatch in an internal function that loads a batch of data points.
Consumption Index is the name of an existing input variable defined by data source section (see Input variables defined in Data sources).
MyVariable is the name of the variable that will be contain one element.

By specifying includeRelatedItems, you instruct the report engine to load meta data related to the datapoints. It acts as a JOIN between data points and meta data. In this case, includeRelatedItems=IncludeBasedOnSource will append the source meta data to every single data point which dramatically increases the amount data processed by the reporting engine.

"Consumption Index" is the name of the batch to iterate to. This name MUST be the same as the one specified in the outer {{while}} loop.

Usage examples

Generate a custom-formated export of sources

Template

{{ name # this is a single line comment }}
This line will be written in the output file 
{{ while LoadMetadataBatch MySourcesMetadata source}}
    This line will be repeated on each sources
    {{source.Name}};{{source.Id}} 
{{end}}

Input sample

[
  {Name:"Test01,Id:01,SomeProperty:"SomeValue"},
  {Name:"Test02,Id:02,SomeProperty:"SomeValue"},
]

Result

The output will be

This line will be written in the output file


     This line will be repeated on each sources
     Test01;01

     This line will be repeated on each sources
     Test02;02

By default, any whitespace (including new lines) before or after a code/escape block are copied as-is to the output. See WhiteSpaces

Generate a CSV-formatted file

Template

SourceName;SourceId {{#This is the header line}}
{{~ while LoadMetadataBatch MySourcesMetadata source ~}}
    {{~ source.Name}};{{source.Id}} 
{{~end~}}

The characters '~' and '-' are used to control the spacing and newlines. See WhiteSpaces

Input sample

[
  {Name:"Test01,Id:01,SomeProperty:"SomeValue"},
  {Name:"Test02,Id:02,SomeProperty:"SomeValue"},
]

Result

SourceName;SourceId
Test01;01
Test02;02

Template

This line will be written in the output file 
{{~ while LoadMetadataBatch MySourcesMetadata source~}}
    This line will be repeated on each sources
    {{source.Name}};{{source.Id}};MySitesMetadata[source.SiteId].Name -}}
{{~end~}}

! Trying to access a property of an object that does not exist will cause an exception which will interrupt the generation of the report.

Input sample

{
  Sources:[
    {Name:"Test01,Id:01,SomeProperty:"SomeValue",SiteId:01},
    {Name:"Test02,Id:02,SomeProperty:"SomeValue",SiteId:02},
  ],
  Sites:[
    {Id:01,Name:"Site1",SomeProperty:"SomeValue"},
    {Id:02,Name:"Site2",SomeProperty:"SomeValue"},
  ]
}

Result

     This line will be repeated on each sources
     Test01;01;Site1
     This line will be repeated on each sources
     Test02;02;Site2

Example of if/else use

Template

{{~ while LoadMetadataBatch MySourcesMetadata source~}}
    {{source.Name}};{{source.Id}};
    {{-if MySitesMetadata[source.SiteId]~}}
        {{~MySitesMetadata[source.SiteId].Name}}
    {{~else~}}NOT FOUND
    {{~end~}}
{{~end~}}

This will avoid a report generation crash if MySitesMetadata[source.SiteId] does not exist.

Input sample

{
  Sources:[
    {Name:"Test01,Id:01,SomeProperty:"SomeValue",SiteId:01},
    {Name:"Test02,Id:02,SomeProperty:"SomeValue",SiteId:02},
  ],
  Sites:[
    {Id:01,Name:"Site1",SomeProperty:"SomeValue"},
    {Id:05,Name:"Site5",SomeProperty:"SomeValue"},
  ]
}

Result

     Test01;01;Site1
     Test02;02;NOT FOUND

Available objects properties

bold fields are the one that you can use to access a related meta data, e.g. using source.SiteId to access the site tied to this source.

Sources (array)

  • Id
  • Name
  • EnergyTypeId
  • EnergyUsageId
  • SourceTypeId
  • TimeZoneId
  • EanNumber
  • MeterNumber
  • Localisation
  • Description
  • Tags (array)
  • SerialNumber
  • GatewayId
  • GatewayTypeId
  • MeterAddress
  • ClientData (complex object)
  • SystemData (complex object)
  • SiteId
  • SiteName

Sites (array)

  • Id
  • Name
  • TypeId
  • Location
    • Longitude
    • Latitude
  • TypeId
  • TimeZoneId
  • Street
  • PostalCode
  • City
  • Country
  • ClientData (complex object)
  • SystemData (complex object)

Variables (array)

  • Id
  • Name
  • SourceId
  • VariableTypeId
  • UnitId
  • Granularity
  • GranularityTimeBase
  • QuantityType
  • MappingConfig
  • Aggregate
  • Tags (array)

Alerts

  • SourceId
  • AccountId
  • Id
  • SiteId
  • VariableId
  • FriendlyName
  • AlertThresholdType
  • CreatedBy
  • CreatedDate
  • Granularity
  • GranularityTimeBase
  • UpdatedBy
  • UpdatedDate
  • Value
  • State
  • Type
  • AggregationType
  • ScheduleNextExecutionDate
  • Notifications (array)
    • Type
    • Target
    • Value
  • Exceptions (array)
    • From
    • To
    • Reason
  • ScheduleConfig
    • ScheduleId
    • ScheduleLevelIds (array)
  • Cron
  • IsAdvancedCron
  • CronGap
  • Occurrences (array) ! Need to be loaded with LoadDependenciesBatch
    • Id
    • Status
    • CreatedDate
    • UpdatedDate
    • UpdatedBy
    • Notifications (array)
      • AlertNotification
        • Type
        • Target
        • Value
      • AlertNotificationMetadata
        • EmailTrackingId
    • Value
    • BeginDate
    • EndDate

Events

  • Id
  • ClientData (complex object)
  • SystemData (complex object)
  • Name
  • Info
  • SourceIds (array)
  • SiteId
  • FromDate
  • ToDate
  • TimeRangeType
  • EventType
  • Tags (array)

Invoices

  • Id
  • SourceId
  • Type
  • Status
  • InvoiceNumber
  • Date
  • Price
  • Amount
  • UnitId
  • DeltaActualAmount
  • PriceVat
  • PeriodFrom
  • PeriodTo
  • CloudItemId

Schedules

  • Id
  • Name
  • GatewayId
  • SiteId?
  • AccountId
  • ScheduleTicks (array) ! Need to be loaded with LoadDependenciesBatch
    • Id
    • ApplicableFrom
    • ApplicableTo
    • FromDayOfWeek
    • FromHour
    • FromMinute
    • ToHour
    • ToMinute
    • LevelId
  • ScheduleLevels (array)
    • Id
    • Name
    • Value
    • IsDefault
  • ScheduleExceptions (array) ! Need to be loaded with LoadDependenciesBatch
    • Id
    • from
    • to
    • reason
    • levelId

Datapoint

  • VariableId
  • SourceId
  • Date
  • UnitId
  • Value

Notes

Whitespace

By default, whitespaces (including new lines) before or after a code/escape block are copied as-is to the output.
Have a look at Controlling whitespaces

Nested loading methods (LoadMetadataBatch, LoadDataBatch or LoadDependenciesBatch)

You may not nest loading methods like this:

{{ while LoadMetadataBatch MySourcesMetadata}}
   {{ while LoadMetadataBatch MySiteMetadata}}
    Something
   {{end}}
{{end}}

It will cause an exception during execution.

Variables with compound names (names with white spaces)

To access to a compound name variable like "Consumption Index", use "this" keyword that refers to "this document object".

Sample:

this["Consumption Index"]

To specify compound form property names, use the same [""] pattern (no "this" keyword)

Sample:

source.forms["Compound Forms Name"]

Multiple files

To generate several files containing different data types or data formats from a single report, it is necessary to create several notifications in this report using different templates.

Sample 1:

To generate a file containing all the sources as well as another file containing all the sites, a template is necessary for each of these files and each of these templates must be used in a notification in the report.

Sample 2:

To generate a file containing the data in csv and another containing the data in json, a template is necessary for each of these files and each of these templates must be used in a notification in the report.

Runtime limitations

The execution of a scriban template has a number of limitations to protect the system from abuse and mishandling.
See this page
Below you will find the modified values in our implementation.

These limits may still change.

  • LoopLimit (maximum iterations on a loop): 10000
  • RecursiveLimit (maximum deep of a recursive call): 20

If this is exceeded, the creation of the report stops with an exception.

Tip

When your template is ready, you can use it in any reports by creating a new notifications. Learn more about reports notifications.