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
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:
1. Export the eLMIS custom reports (grab their SQL) and
2. Embed Superset or some of it's UI into OpenLMIS v3 UI?
  1. Could use iFrames for embed
  2. Could change the header of Superset to make it look like LMIS
3. Entire data analysis platform.
  - Josh: We have a constraint that no service can directly hit the database. Why? For upgradeability. We do not version the database schema. The semantic versioning is on the API, not the database. We are writing API calls for our reports, not SQL queries. There is a pattern we can follow: data pumps. If we define a contract within the service–the service owns the data pump, that is fine. The semantic versioning of the data pump goes along with the service itself. But this architectural paradigm makes the "custom reports" feature moot.
Discussion of data pump architecture:
- A data pump is the ability to get data out of a micro-service where the format is defined by the service that owns the data.
  - It publishes the data, EG providing the data as an Atom feed, or a Web-Sub.
  - It is semantically versioned.
  - It's not that much different from an API.
  - The details are pub-sub, how do you notify others when there is new data; those details impact performance.
- After the data is pumped out, it is out of the service's control. The other components can use that data however they need.
  - Another service can listen or subscribe to that data and use it how they need.
Is there a down-side or concern with this data pump architecture?
- Operational Support - server purchases. You will need 2 servers.
- Craig: People on the ground will want access to their own data.
Josh: We already decided this for version 3 because of lessons learned from prior versions.
Peter: So the "custom report" feature with the ability to paste in SQL queries can still be possible in the new reporting stack using the data pump approach.
- Ashraf: We are basically building the data warehouse. That could be relational or not relational. The SQL queries for the "custom reports" feature can be done against that data warehouse. In a cost-benefit analysis, does the data warehousing approach pay off? Is it worth the problem it solves?
- Peter: Using a data warehouse is the only approach. It is more complex, but with micro-services it is the only approach we have.
- Ashraf: What prevents us from directly connecting to a SQL database to pull out the data? Josh: As the architect, we would not recommend that. But nothing prevents an individual implementation from doing that.
- Peter: Actually it would be prevented. When the next version of the micro-service system comes out, it could break the report when the schema changes.
- Brandon: We should discourage the implementations from writing direct SQL queries, because it will be more brittle and it will be harder to query across micro-services than it was in OpenLMIS version 3.
Question (Craig): I have a mental block on how a change in a micro-service requires us to change the reporting stack?
- The data pumps are versioned.
Craig: What about Pick Lists and PDF print-outs?
- Josh: We will need to see whether it is part of the user workflow or whether it should be part of the data warehouse.
- Craig: Should we assume there is a Jasper reporting as part of each micro-service?
- Josh: Currently that is the case. They are not for heavy reporting lifting. The Jasper reports break down with heavy reporting/dashboard requirements. There is also a "reporting service" but it needs more work to run off of a database that is powered by data pumps.
Mary Jo: We will need to do more to make sure we understand and articulate and explain this data warehouse architecture and strategy to implementations and partners.