Report Service

Overview

Reporting Service is a service running independently on separate server. The main objective of this service is to provide a common framework for generating reports. This service loads the report configuration from a yaml file at the run time and provides the report details by using couple of API’s.

Pre-requisites

Before you proceed with the documentation, make sure the following pre-requisites are met -

  • Prior Knowledge of Java/J2EE.

  • Prior Knowledge of SpringBoot.

  • Advanced Knowledge of PostgreSQL.

  • Encryption and MDMS services must be running.

  • Prior Knowledge of REST APIs and related concepts like path parameters, headers, JSON etc.

  • JSONPath for filtering required data from json objects.

Key Functionalities

  • Provides an easy way to add reports on the fly just by adding configurations without any coding effort.

  • Provides flexibility to customise result column names in the config.

  • Provides flexibility to fetch data from DB and also from some other service returning required json objects when its not possible to get all required data from DB.

  • Provides functionality to add filters as per requirements before actually fetching data for report.

  • Provides user data in masked form. People with authorization will get plain data one by one.

Configuration Details

Definitions:

  1. Config file

    • A YAML (xyz.yml) file which contains configuration for report requirements. 

  2. API

    • A REST endpoint to fetch data based on the configuration.

  3. Inline-table

    • If we also want to show data from some external service with data coming from DB  in reports we use inline-tables. The data from external service is stored in inline-table and then used as any normal DB table to get data. This table is short lived and stays only for the time when query is being executed. It is never stored in DB. We provide JSON paths in ordered manner corresponding to each column in the table. These JSON paths will be used to extract required data from external service’s response. For configs please see ‘How to Use’ section. 

How to Use:

  • Configuration: As mentioned above, report service uses a config file per module to store all the configurations of reports pertaining to that module. Report service reads multiple such files at start-up to support reports of all the configured modules. The file contains the following keys:

    •     reportName: name of report, to be used with module name to identify any report config

  •     summary: summary of report

  •     version: version of report

  •     moduleName: name of module which the report belongs to

  •     externalService: To be used when some of report data needs to be fetched from external service through inline-tables. It contains following fields

    1. entity: JSON Path to filter json arrays(result to be turned into tables) from returned json 

    2. apiURL: api URL of the external service

    3. keyOrder: order of JSON object keys to form table columns from JSON  object arrays

    4. tableName: name to be given to represent this transformed data which will be used as table in the SQL query

  •     sourceColumns : These represent the final data sent by service on GET_DATA api call. Order of sourceColumns in the Config is same as that of columns in the result.  Each sourceColumns  represent one column in the result. For each column data is picked after executing final SQL query formed after appending groupby, orderby, search params into base query

    • name: name of column to fetch data from query results, must be there in query results

    • label: custom column label

    • type: data type of column

    • source: module name

    • total: whether column total required on front end

  • searchParams:

    • name: name of search param. Must match variable used in search clause

    • label: custom label for viewing on front end

    • type: type of search params. If type is ‘singlevaluelist’ then  use pattern to populate searchparams possible values to select from by the user
      Ex:-number,string,singlevaluelist etc

    • source: module name

    • isMandatory: If user must fill this searchparam before requesting report data

    • searchClause: SQL search clause for corresponding search params to filter results, to be appended in base query Ex:- AND fnoc.tenantId IN ($ulb)
      Here $ulb will be replaced by user inputs

    • Pattern: This field will be used only when ‘type’ is set to ‘singlevaluelist’. It is external service URL combined with JSON Paths separated by ‘|’. The first JSON path is for codes and second for values. Values will be shown to the user in drop down. And codes corresponding to user selected value will be sent to the report service and will be used in searchClauses mentioned in last point.
      Ex:-http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=tenant&masterName=tenants|$.MdmsRes.tenant.tenants.*.code|$.MdmsRes.tenant.tenants.*.name

  • Query: Main/base query clause for fetching report data from DB and custom tables formed after fetching data from external service 

  • Orderby: order by clause to be appended into base query

  • Groupby: group by clause to be appended into base query

  • additionalConfig: to provide additional custom configs which are not present above

Call the MDMS or any other API with the post method

  1. Configuring the post object in the yaml itself like below.
    externalService:

    • entity: $.MdmsRes.egf-master.FinancialYear

      • apiURL: http://egov-mdms-service:8080/egov-mdms-service/v1/_search

      • keyOrder: finYearRange,startingDate,endingDate,tenantId

      • tableName: tbl_financialyear

      • stateData: true

      • postObject:

        • tenantId: $tenantid

        • moduleDetails:

          • moduleName: egf-master

          • masterDetails:

            • name: FinancialYear filter: "[?(@.id IN [2,3] && @.active == true)]"

  2. Keep the post object in a seperate json file externally and call at runtime.

Sample yaml configuration :

ReportDefinitions: - reportName: EmployeeReport decryptionPathId: EmployeeReport summary: Fetches employee data based on provided search parameters version: 1.0.0 moduleName: hrms externalService: - entity: $.MdmsRes.common-masters.Department apiURL: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?moduleName=common-masters&masterName=Department&tenantId=$tenantId keyOrder: name,code tableName: tbl_def_dept - entity: $.MdmsRes.common-masters.Designation apiURL: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?moduleName=common-masters&masterName=Designation&tenantId=$tenantId keyOrder: name,code tableName: tbl_def_desig - entity: $.MdmsRes.ACCESSCONTROL-ROLES.roles apiURL: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?moduleName=ACCESSCONTROL-ROLES&masterName=roles&tenantId=$tenantId keyOrder: name,code tableName: tbl_def_roles sourceColumns: - name: id label: reports.hrms.id type: string source: eg_hrms_employee - name: name label: reports.hrms.name type: string source: eg_hrms_employee - name: rolecodes label: reports.hrms.role type: stringarray source: eg_hrms_employee isLocalisationRequired: true localisationPrefix: ACCESSCONTROL_ROLES_ROLES_ - name: designation label: reports.hrms.designation type: string source: eg_hrms_employee isLocalisationRequired: true localisationPrefix: COMMON_MASTERS_DESIGNATION_ - name: department label: reports.hrms.department type: string source: eg_hrms_employee isLocalisationRequired: true localisationPrefix: COMMON_MASTERS_DEPARTMENT_ - name: status label: reports.hrms.status type: string source: eg_hrms_employee isLocalisationRequired: true localisationPrefix: EGOV_HRMS_EMPLOYEESTATUS_ searchParams: - name: ulb label: ULB type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=tenant&masterName=tenants|$.MdmsRes.tenant.tenants.*.code|$.MdmsRes.tenant.tenants.*.name source: pt wrapper: true isMandatory: true searchClause: AND emp_data.tenantid = $ulb - name: status label: reports.hrms.status type: multivaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=pb&moduleName=egov-hrms&masterName=EmployeeStatus|$.MdmsRes.egov-hrms.EmployeeStatus[?(@.active)].code|$.MdmsRes.egov-hrms.EmployeeStatus[?(@.active)].code source: seva isMandatory: false isLocalisationRequired: true localisationPrefix: EGOV_HRMS_EMPLOYEESTATUS_ searchClause: AND emp_data.employeestatus IN ( $status ) - name: role label: reports.hrms.role type: multivaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=pb&moduleName=ACCESSCONTROL-ROLES&masterName=roles|$.MdmsRes.ACCESSCONTROL-ROLES.roles[?(@.code nin ['CITIZEN'])].code|$.MdmsRes.ACCESSCONTROL-ROLES.roles[?(@.code nin ['Citizen'])].code isMandatory: false isLocalisationRequired: true localisationPrefix: ACCESSCONTROL_ROLES_ROLES_ searchClause: AND ARRAY[ $role ]::varchar[] && emp_data.rolecodes::varchar[] - name: deptname label: reports.pgr.dept.name type: multivaluelist source: eg_pgr_service pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=common-masters&masterName=Department|$.MdmsRes.common-masters.Department[*].code|$.MdmsRes.common-masters.Department[*].code isMandatory: false isLocalisationRequired: true localisationPrefix: COMMON_MASTERS_DEPARTMENT_ searchClause: AND deptmap_def.code IN ($deptname) query: | WITH emp_data AS (SELECT DISTINCT ehe.id, ehe.uuid, ehe.tenantid, eu.name, (SELECT ARRAY(SELECT DISTINCT er.name from eg_user eu LEFT JOIN eg_userrole_v1 eur ON eu.id=eur.user_id AND eu.tenantid=eur.user_tenantid LEFT JOIN (VALUES tbl_def_roles) AS er(name,code) ON eur.role_code=er.code WHERE eu.uuid=ehe.uuid AND er.name IS NOT NULL)) as role, (SELECT ARRAY(SELECT DISTINCT er.code from eg_user eu LEFT JOIN eg_userrole_v1 eur ON eu.id=eur.user_id AND eu.tenantid=eur.user_tenantid LEFT JOIN (VALUES tbl_def_roles) AS er(name,code) ON eur.role_code=er.code WHERE eu.uuid=ehe.uuid AND er.code IS NOT NULL)) as rolecodes, eha.designation, eha.department, ehe.employeestatus FROM eg_hrms_employee ehe LEFT OUTER JOIN eg_user eu ON ehe.uuid = eu.uuid LEFT OUTER JOIN eg_hrms_assignment eha ON eu.uuid = eha.employeeid WHERE eu.active = true) SELECT emp_data.id as id, emp_data.name as name, ARRAY_TO_STRING(emp_data.role, ',') as role,