JSI Report Experience

JSI Report Experience
Available reports

Baked in reports with eLMIS using AngularJS/Spring code for UI/HTML output and Jasper for PDF and EXCEL output
Custom report using an open source add on that accepts direct SQL
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.