JSI Report Experience

JSI Report Experience
Available reports

  1. Baked in reports with eLMIS using AngularJS/Spring code for UI/HTML output and Jasper for PDF and EXCEL output
  2. Custom report using an open source add on that accepts direct SQL
  3. Metabase, an open source tool for dashboard

As part of the Gap Analysis project there are 13 prioritized reports. The list can be found here under reporting sections.
These prioritized reports were also further discussed, refined and slotted for Jasper type of report or Superset/data warehouse type of reports during Aug 27 and 28, sessions in Seattle with VR, JSI and Ona team. Respective wiki page and notes were updated accordingly.
At this point around 10 reports were slotted under Superset.
Custom reports and Metabase dashboard were kept in backlog for now. Future funding and product milestone plan will determine what can be done for those reports.
eLMIS reports access control mechanism
Rights and Role based access
When a new report is developed (type 1 on above list), through SQL migration script, the report name is inserted in the rights table. Below is a sample of report rights from the rights table

name

description

displaynamekey

ACCESS_NEW_DASHBOARD

Permission to access new dashboard

right.dashboard

MANAGE_CUSTOM_REPORTS

Permission to manage custom reports

right.report.manage.custom.report

VIEW_ADJUSTMENT_SUMMARY_REPORT

Permission to view adjustment summary Report

right.report.adjustment

VIEW_AVERAGE_CONSUMPTION_REPORT

Permission to view avergae consumption Report

right.report.average.consumption

VIEW_CCE_STORAGE_CAPACITY_REPORT

Permission to view CCE Storage Capacity Report

right.report.cce.storage.capacity

VIEW_COLD_CHAIN_EQUIPMENT_LIST_REPORT

Permission to view cold chain equipment list Report

right.report.coldchain.equipment

VIEW_CONSUMPTION_REPORT

Permission to view Consumption Report

right.report.consumption

VIEW_DASHBOARD

Permission to view dashboard poc

right.report.dashboard

VIEW_DASHBOARD_POC

Permission to view Dashboard POC

right.report.dashboard

VIEW_DISTRIBUTION_SUMMARY_REPORT

Permission to view Distribution summary report

right.view.distribution.summary.report

VIEW_DISTRICT_CONSUMPTION_REPORT

Permission to view district consumption comparison report

right.report.district.consumption

VIEW_DISTRICT_FINANCIAL_SUMMARY_REPORT

Permission to view District Financial Summary Report.

right.report.district.financial

VIEW_ELMIS_DASHBOARD

Permission to view eLMIS dashboard poc

right.report.dashboard

VIEW_FACILITY_REPORT

Permission to view Facility List Report

right.report.facility

VIEW_LAB_EQUIPMENT_LIST_REPORT

Permission to view lab equipment list Report

right.report.lab.equipment

VIEW_LAB_EQUIPMENTS_BY_FUNDING_SOURCE

Permission to view lab equipment list by funding source Report

right.report.equipment.funding

VIEW_LAB_EQUIPMENTS_BY_LOCATION_REPORT

Permission to view lab equipments by location Report

right.report.lab.equipment.by.location

VIEW_MAILING_LABEL_REPORT

Permission to view Mailing labels for Facilities

right.report.mailing.label

VIEW_MSD_DASHLET_REPORT

Permission to view MSD Dashboard

right.view.msd.dashboard

VIEW_NON_REPORTING_FACILITIES

Permission to view Non reporting Facility List Report

right.report.non.reporting

VIEW_ORDER_FILL_RATE_REPORT

Permission to view Order Fill Rate Report

right.report.order.fillrate

VIEW_ORDER_FILL_RATE_SUMMARY_REPORT

Permission to view order fill rate summary Report.

right.report.fill.rate

VIEW_ORDER_REPORT

Permission to view Order Report

right.report.order

VIEW_PIPELINE_EXPORT

Permission to view Pipeline export Report

 

VIEW_REGIMEN_SUMMARY_REPORT

Permission to view Regimen Summary Report.

right.report.regimen

VIEW_REPAIR_MANAGEMENT_REPORT

Permission to view Repair Management Report

right.report.repair.management

VIEW_REPORTING_RATE_REPORT

Permission to view Reporting Rate Report

right.report.reporting.rate

VIEW_REQUISITION_REPORT

Permission to View Requisitions Report

right.report.requisition

VIEW_RNR_FEEDBACK_REPORT

Permission to view Report and Requisition Feedback Report.

right.report.rnr.feedback

VIEW_SEASONALITY_RATIONING_REPORT

Permission to view seasonality rationing Report

right.report.seasonality.rationing

VIEW_STOCKED_OUT_REPORT

Permission to view stocked out commodity Report

right.report.stocked.out

VIEW_STOCK_IMBALANCE_REPORT

Permission to view Stock Imbalance Report.

right.report.stock.imbalance

VIEW_STOCK_LEDGER_REPORT

Permission to View Stock Ledger Report

label.rights.view.stock.ledger.report

VIEW_STOCK_ON_HAND_REPORT

Permission to View Stock On Hand Report

right.report.stockonhand

VIEW_SUMMARY_REPORT

Permission to view Summary Report

right.report.summary

VIEW_SUPPLY_STATUS_REPORT

Permission to view supply status by facility report

right.report.supply.status

VIEW_TIMELINESS_REPORT

Permission to view Timeliness Report

right.report.timeliness

VIEW_USER_SUMMARY_REPORT

Permission to view user summary Report

right.report.user.summary

VIEW_VACCINE_REPLACEMENT_PLAN_SUMMARY

Permission to View Replacement Plan Summary Report

right.report.vaccine.replacement.plan.summary

VIEW_VACCINE_REPORT

Permission to view vaccine report

right.view.vaccine.report

VIEW_VACCINE_STOCK_STATUS_REPORT

Permission to View Vaccine Stock Status Report

label.rights.vew.vaccine.stock.status.report

VIEW_VIMS_MAIN_DASHBOARD

Permission to view Vims Main Dashboard

right.view.vims.main.dashboard

VIEW_VIMS_NOTIFICATION

Permission to view vims dashboard notification

right.view.vims.notification


Creating a role with a set of rights
eLMIS Role UI allows creating a right with set of roles. See screenshot below


Example of report related rights assigned through the Role menu option


















Users are granted roles through which they gain access to a report




Granting Report Viewing roles for regular reports





Granting Custom Report viewing roles

Custom reports role displays the Custom Report menu option to the user and give access to all custom report. An additional admin type role enable create/edit grants for custom reports.
In the example below, a logged in user is displayed a list of regular reports granted to him as well as custom reports









Data level access
Data level accesses are granted through granting Supervisory node and Program specific grants.
For example, in the example below, a user is granted access to TB program data for Arusha MC District Office.






Report UI and Filtering options
Each report has its own UI for report run/filter options. Filter options are typically by program, period, geographic locations, products etc. The drop down for each of the filter is rendered based on which program or supervisory node the user have access to.











!worddavf12518e39388ea257bd1066affcfec8f.png|height=338,width=148!Below is an example where the user has some limited access to facilities for TB program, but more access for ILS program

















Drilling down to source code
Full eLMIS source code can be found at GitHub. https://github.com/USAID-DELIVER-PROJECT/elmis/tree/develop


Reports code can be drilled down from Angular UI code. Below is link to Angular controller codes























HTML code fragments that makes up the UI can be found at the following location and folders











SQL code for all of the regular reports can be found here








Sample of SQL for a specific report

PDF and CSV outputs
eLMIS provides PDF and CSV output options for most of the reports. PDF and CSV outputs are provided through Jasper report.






Jasper report program (jrxml) files can be found at following location






Jasper reports can be opened in open source iReport IDE. Some of the jasper report shares common data set with Angular HTML report, through the common data provider to ensure that both HTML and PDF/CSV outputs are producing same results.
PostgreSQL Database level objects
Database views
Most of the report SQL uses database level views. These views and their SQL source code can be found on PostgreSQL database, under view node.
Database stored procedures
For complex business logic, some of the reports use functions/procedures stored at the database. The source code for these can be found on respective node at the PostgreSQL database.
Materialized views
To provide better data retrieval performance, some of the views results set are materialized into physical tables. Materialized views SQL source code and associated tables can be found at the PostgreSQL database, under respective nodes.

OpenLMIS: the global initiative for powerful LMIS software