Reporting - Custom and Metabase reports
Target release | |
|---|---|
Epic | Reporting - Custom |
Document status | drafted |
Priority | HIGH |
eLMIS Status | Implemented |
OpenLMIS Status | Implemented with Tableau |
PATH |
|
OpenLMIS | @Mary Jo Kochendorfer (Deactivated) |
JSI | @Ashraf Islam (Unlicensed) |
Goals/Scope
Ability to make ad-hoc queries against the database, define custom reports and add add such reports to application menu system without requiring programmer involvement or without requiring a software release (see notes from 4/12/2018 below). This feature is meant to support ad hoc needs that arise time to time and can not wait for next development/deployment cycle.
The scope will also cover the metabase reports. Prior to starting on this work, we will want to confirm with a BA on frequency of using these reports to support in the prioritization.
Status in eLMIS: Implemented.
Status in OpenLMIS: Implemented with Tableau.
Priority: High priority for Tanzania, Zanzibar, Zambia
Background
Users have many ad hoc data needs for upcoming meetings/presentations and information request from MoH or funder. A flexible mechanism is needed for a trained user to run ad hoc reports.
Assumptions
Basic SQL query training is given to the admin/super user. eLMIS database schema is available with the user. Users knows how to assign roles and which specific role to assign to others who wants to be able to run these ad hoc reports
User Stories
# | Title | User Story | Label | Importance | Notes | JIRA Tickets |
|---|---|---|---|---|---|---|
1 | Custom reports | As an administrator I want to be able to define custom reports with parameters defined for report filtering | Custom reports | Must have |
|
|
2 | Custom reports | As an administrator I want to be able to add users to custom reports role | Custom reports | Must have |
|
|
3 | Custom reports | As a user I want to be able to select custom reports from menu option, to be able to set filtering parameter and view output in HTML and be able to download the underlying data in CSV/Excel format | Custom reports | Must have |
|
|
4 | Custom reports | As a user/supervisor/admin I need to be able to get Annual consumption figures | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 1: Annual Consumption by Facility |
|
5 | Custom reports | As a user/supervisor/admin I need to be able to get Consumption by product category | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 2: Consumption by Product Category |
|
6 | Custom reports | As a user/supervisor/admin I need to be able to get a List of users by role/type | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 3: List of users by role/type |
|
7 | Custom reports | As a user/supervisor/admin I need to be able to get Top Expired Products | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 4: Top Expired Products |
|
8 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Facilities where a specific product is expiring in large numbers | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 5: List of Facilities where a specific product is expiring in large numbers |
|
9 | Custom reports | As a user/supervisor/admin I need to be able to get a Count of facilities reported, by facility type | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 6: Count of facilities reported, by facility type |
|
10 | Custom reports | As a user/supervisor/admin I need to be able to get a list of Top 100 Ordered products | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 7: Consumption by Product Category |
|
11 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Products being carried by different types of facilities | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 8: Products being carried by different types of facilities |
|
12 | Custom reports | As a user/supervisor/admin I need to be able to get a List of R&R Reporting by types of facilities | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 9: R&R Reporting by types of facilities |
|
13 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Count of total R&Rs submitted by programs | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 10: Count of total R&Rs submitted by programs |
|
14 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Patients on ARV Regimens | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 11: Patients on ARV Regimens |
|
15 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Patients on ARV Regimens, by Facility | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 12: Patients on ARV Regimens, by Facility |
|
16 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Regular Vs Emergency R&Rs | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 13: Regular Vs Emergency R&Rs |
|
17 | Custom reports | As a user/supervisor/admin I need to be able to get a List of R&Rs being initiated by facility, user and timestamp | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 14: R&Rs being initiated by facility, user and timestamp |
|
18 | Custom reports | As a user/supervisor/admin I need to be able to get a List of users by role/type | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 15: R&R by workflow status |
|
19 | Custom reports | As a user/supervisor/admin I need to be able to get a List of R&Rs by reporting groups | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 16: R&Rs by reporting groups |
|
20 | Custom reports | As a user/supervisor/admin I need to be able to get a List of R&R by program and reporting group | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 17: R&R by program and reporting group |
|
21 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Top 100 stocked out products | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 18: Top 100 stocked out products |
|
22 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Losses and Adjustments by Facility | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 19: Losses and Adjustments by Facility |
|
23 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Current stock on hand by location | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 20: Current stock on hand by location |
|
24 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Top R&R Approvers | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 21: Top R&R Approvers |
|
25 | Custom reports | As a user/supervisor/admin I need to be able to get a List of Top R&R Initiators | Custom reports | Must have- TZ, ZnZ, ZM | See Figure 22: Top R&R Initiators |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Diagrams
Figure 1: Annual Consumption by Facility
Figure 2: Consumption by Product Category
Figure 3: List of users by role/type
Figure 4: Top Expired Products
Figure 5: List of Facilities where a specific product is expiring in large numbers
Figures 6: Count of facilities reported, by facility type
Figure 7: Top 100 Ordered products
Figure 8: Products being carried by different types of facilities
Figure 9: R&R Reporting by types of facilities
Figure 10: Count of total R&Rs submitted by programs
Figure 11: Patients on ARV Regimens
Figure 12: Patients on ARV Regimens, by Facility
Figure 13: Regular Vs Emergency R&Rs
Figure 14: R&Rs being initiated by facility, user and timestamp
Figure 15: R&R by workflow status
Figure 16: R&Rs by reporting groups
Figure 17: R&R by program and reporting group
Figure 18: Top 100 stocked out products
Figure 19: Losses and Adjustments by Facility
Figure 20: Current stock on hand by location
Figure 21: Top R&R Approvers
Figure 22: Top R&R Initiators
Dependencies
Description | Link |
|---|---|
Open Questions
Below is a list of questions to be addressed as a result of this requirements document:
Question | Outcome | Status | |
|---|---|---|---|
| 1 | How important is it for users to be able to create cutom reports within OpenLMIS instead through a third party BI tool? Need to understand if OpenLMIS wants to put the resources behind building out ad hoc reporting or use other approaches. |
|
|
Out of Scope
Gap Estimation Notes 4/12/2018
Attendees: Mary Jo, Josh, Ashraf, Muhammad, Peter, Craig, Brandon, Elias
See the "Custom Report" section of the PDF provided by Muhammad and Ashraf.
Question: How many people have ability to add custom reports?
It is a handful of people in Tanzania, for example (Alfred and a few of the folks there).
Discussion: Is it fair to say "without programmer involvement"?
It does require SQL skills, which is basically a programmer. You have to understand SQL and database and performance.
But the benefit of this approach is it does not have to wait for a release cycle.
How would we do this in the new reporting stack?
The new reporting stack would have an ad-hoc report ability so users could make new reports even without writing SQL.
Users could still use SQL if they wanted to.
Josh: In the new stack, if the data they need is not already in Druid, will they have to do things in Druid to somehow get the additional data they need? It takes someone like Hassan to do ingestion work.
Ashraf: In Guineau or Cote d'Ivoire experience, there is no on-going support mechanism there. People needed this flexible mechanism for custom reports. We taught the folks in those implementations how to do this.
What about roles/permissions in the new reporting stack?
Question: Do you verify that the SQL is valid before running?
Elias: We pass it through a filter. It only allows SELECT statements. It prevents SQL injection attacks.
Does this check against encoded (e.g. b64) statements?
Question: Does everything directly hit the database?
Yes.
Options for reports in eLMIS reports:
Export the eLMIS custom reports (grab their SQL) and
Embed Superset or some of it's UI into OpenLMIS v3 UI?
Could use iFrames for embed