|
Allow users to define their own reports by selecting various entities and their attributes and specifying any conditions or grouping for their display.
The selection of entities, attributes and specifying conditions and any grouping of data should ideally translate into the corresponding fetches from the database either through direct SQL queries or via the application layer.
The aim is to allow a business-user/administrator to be able to define their own reports
-
using selection or drag-and-drop or any other easily usable UI components
-
without having to understand the internals of the Mifos database tables and columns
-
the idea is to give a simple interface integrated with Mifos Community App - for defining “ad-hoc” reports and to allow the user (or other users) to run them like any other report.
-
2 types of user stories for this project:
o Business User – for the person trying to use these entities and creating customer reports
o Administrative User Stories – for the person defining the universe/entities
Ideally, the User interface should be intuitive. Hence we may have to define a “Mifos Data Universe” first. The “Mifos Data Universe” will define user understandable objects.
Example: User wants to create a report as follows:
List Customer ID, Name, Loan Amount, Outstnding Principal and Outstanding Interest for all customers with active loans where outstanding principal is greater than 60% of the Loan Amount
This report needs the following to be defined upfront:
a) Entities
Loan Customers - which internally is mapped to:
“select * from m_client where id in (select distinct client_id from m_loan)”.
Loans - which internally is mapped to m_loan table
b) Attributes (which are mapped as follows)
Customer ID --> m_client.id
Name --> m_client.display_name
Client Status --> m_client.status_enum
Loan Amount --> m_loan.principal_amount
Outstanding Principal --> m_loan.principal_outstanding_derived
Outstanding Interest --> m_loan.interest_outstanding_derived
Loan Status --> m_loan.loan_status_id
ACTIVE_LOAN_STATUS --> 300
ACTIVE_CLIENT_STATUS --> 300
Customer Branch --> m_client.office_id
Customer Branch Name --> m_office.name
c) Computed Fields
Outstanding Pricipal Percentage --> (Outstanding Principal / Loan Amount) * 100
d) Conditions
Outstanding Pricipal Percentage >= 60 and
Client Status = ACTIVE_CLIENT_STATUS and
Loan Status = ACTIVE_LOAN_STATUS
e) Group data by:
Customer Branch
f) Grouping Header Attributes
Customer Branch Name
g) Grouping Footer Attributes
sum(Outstanding Principal)
sum(Outstanding Interest)
This Mifos Data Universe definition (points a and b above) can be a one-time activity. Data Universe is the set of Entities and Attributes that will be recognized by the Mifos Ad-hoc Reporting Module. For the first iteration, we may not have a user interface for managing the Data Universe. It could be managed by defining the “Entities and Attributes Definitions” as entries in a set of database tables.
The data returned in the above example should be similar to data returned by the below query:
“select * from m_client where id in (select distinct client_id from m_loan where loan_status_id = 300) and status_enum = 300”.
Finally, the report when run should return the above attributes and also allow user to view the output in the mifos community application and allow the data to be exported as Excel, CSV or PDF.
Rough list of use cases:
-
As a Business User, I want to select the set or sub-set of Entities that will be displayed in this report
For example: set of customers with “Emergency Loans” (Emergency Loans is the name of a loan product here) – we will need to go through the Mifos database schema and identify “entities” that make sense to end users and define these as queries as mentioned above. If multiple entities are selected, then the join criteria should be more or less automatic (in a way – we could say this is hardcoded).
-
As a Business User I want to select List of attributes for report
For example: Customer ID, Customer Name, Disbursal Date, Disbursed Amount, Outstanding Amount, Outstanding Interest
-
As a Business User I want to specify any static filter criteria for the report – for example: show only loans that are currently active
-
As a Business User I want to specify any run-time filter criteria – for example, show only loans disbursed during 2014 (user specifies this at the time of running a report using a date range) and for specific branches
-
As a Business User I want to specify the list of (numeric or currency) fields that need totals
-
As a Business User I want to specify any grouping and group totals that need to be displayed in the report –
For example, for loan officers, do not show all loans, but only show sum of Disbursed Amount and Outstanding Amount and Interest for this loan officer
i.e. group the information by loan officer and show only totals.
-
As a Business User I want to view the report output in a graphical for, for example as line graph, barchart or pie-chart etc.
-
As a Business User I wish to save a report with a name for future re-use (Name and Description to be given)
-
As a Business User I wish to allow others also access to this report by specifying the report as a public report. A public report can be viewed and executed by other users, but may edited only by the “owner”. While a private report can only be viewed, edited and executed by the person who “owns” the report.
|
