Low-code Architecture for Any Application

Introduction

Report Definition

Report Template is the object that contains:

· report description fields like name, label etc.

· data for building UI elements with for report parameters input

· result columns list

Report Data Source is the object that provides reports data.

  • Datasources can be based on SQL or No-SQL DB.
  • The Data Source contains DB query in SQL or Mongo DB format and parameters list for the query.
  • Query is template with parameters. The query template is processed with “FreeMarker” template engine to build final DB query.

Reporting Engine database model

Processing report data request

We now have the foundation for a generic reporting framework. Lets looks at the implementations aspects.

REST APIs

1. Creating Report Template

Request body:

uiParametersJson contains JSON that describes UI elements with names. The parameter names are used for creating the report data request.

uiParametersJson example:

uiFieldType is the type of UI input element.

Possible values: TEXT|TYPEAHEAD-SINGLE-SELECT|TYPEAHEAD-MULTI-SELECT|COMBO-SINGLE-SELECT|COMBO-MULTI-SELECT|RADIO|DATE|TEXTAREA

dataSource is optional element. It describes how to request data for the UI element.

To request data with the dataSource run the HTTP request:

GET http://localhost:8086/terabizcloud-reports/api/datasources/12345678-1234-5678-1234-567812345678/data?parameterName1=Val1&parameterName2=Val2

rules is the UI element validation rules.

dependents is list of dependent UI elements names. The dependents will be refreshed when the element state is changed.

columnsJson example:

2. Requesting Report data

Report parameters should be provided with the URL

Example with “ds_name” parameter:

GET /api/v1/reports/templates/6229815a-8b6e-4c87-877a-db0906d27d96/data?ds_name=DSNameDataTest

There are two reserved parameters names:

LIMIT_PARAM — limit report result data amount. The default value is 1000.

SORT_PARAM — sort report result data. Format — “property,property(,ASC|DESC)”.

Example:

GET /api/v1/reports/templates/6229815a-8b6e-4c87-877a-db0906d27d96/data?ds_name=DSNameDataTest&LIMIT_PARAM=2&SORT_PARAM=ds_name,DESC

For service to service intercommunication the URL shall be used:

GET /api/v1/reports/templates/{templateUUID}/data/internal

Report response structure

3. Creating Report Data Source

parametersJson — DS parameters description that will be passed to the databaseQuery template to build DB query.

columnsJson — list of the DS result columns

databaseQuery — query template string. The string may contain FreeMarker template engine instructions.

For MONGODB data sources the field contains JSON string with the two fields:

{

“colletionName”: “sample”,

“queryTemplate”: “{<#if name??>\”name\”: \”${name}\”</#if>}”

}

Where “queryTemplate” is the MongoDb query template.

sourceType — “MYSQL” or “MONGODB”.

4. Requesting Data Source data

GET /api/v1/reports/datasources/6d7d0a20-60c6-4bea-9b36-16f4d8289139/data?ds_name=DSNameDataTest

Data response structure

Data Processing for report execution

Example:

SELECT * FROM tct_report_schedule

WHERE is_active = true AND output_format_code = ‘${format_code}’

Optional parameters can be processed with the FreeMarker sintax: <#if param_name??> .. </#if>

Example:

SELECT * FROM tct_report_schedule

WHERE is_active = true <#if format_code??>AND output_format_code = ‘${format_code}’</#if>

If optional parameter format_code has ‘PDF’ value the result query is:

SELECT * FROM tct_report_schedule

WHERE is_active = true AND output_format_code = ‘PDF’

If there is no parameter with name “format_code” the result is:

SELECT * FROM tct_report_schedule

WHERE is_active = true

Mongo Db query Example:

{“name”: “Test Name”<#if label??>, “label”: “${label}”</#if>}

With the label parameter value “Text Label” the result is:

{“name”: “Test Name”, “label”: “Text Label”}

If all report parameters optional it can be that there are no parameters in request.

To handle this there is internal variable for query template processing — isParamsEmpty.

Example:

SELECT * FROM table1<#if !(isParamsEmpty??)> WHERE name = ‘${name}’ AND uuid=’${uuid}’</#if>

Batch report processing

Create report schedule

reportTemplateUuid — report template UUID to get data.

cronExpression — Cron expression to calculate next run date.

outputFormatCode — format code for export. Possible values — XML, CSV, TXT, JSON, EXCEL, PDF

email — an email to send notification and attached result

isActive — is the schedule active

reportRequestParametesJson — JSON with parameters values to pass in report data request

User Interface

Conclusion

Founder/CEO Tera Inc (https://www.tera.cloud)