Screen Based Reporting

Mifos X = Mifos NG = Mifos Individual Lending

Screen Based Reporting

INTRODUCTION

This introduction is for developers. Its purpose is to give a general overview of the functionality, implementation and indications of TODO work for screen based reporting (known as

stretchyreporting in the code).

It is a pointer to the actual implementation and not necessarily identical to the current code implementation. If you have the current Mifos X code & database you should be easily able to identify

the implementation from this introduction.

What Is It?

It's one way of making the majority of reporting needs available quickly and cheaply.

From a user point of view: a person picks a report from a drop down list, inputs any parameters required and clicks Run. The results are displayed in a "generic table" way with generic paging,

searching and export functionality. The best way is to see an example of it running (link to demo)

From a developer point of view: a person needs a new report, the developer does the sql and adds it to the reporting metadata making it and the generic functions quickly available.

What it isn't?

It's not adhoc reporting as the 'reports' are pre-defined. It's not meant to address every reporting need. However, it is possible to include other reporting mechanisms such as Pentaho Reports and simple charts in the 'drop down list' of reports. In

this case, the person still "picks a report from a drop down list, inputs any parameters required and clicks Run" but the output is, of couse, not shown in the 'generic table' way but in a frame. There's nothing particularly Microfinance/Mifos about this reporting functionality except the authorisation and data scope checks included in the "REST api" (see later)

Limitations

There is no real limitation as long as the report isn't 'crazy' big. The report data is a generic resultset which gets passed to the browser from the server. Very big resultsets can impact the user

experience. These 'size limits' will vary depending by browser and machine. The expectation is that most useful reports will be small (< 1000 lines) and that is comfortably catered for. It's

typically okay to return up to five megabytes of data which amounts to about thirty thousand lines of a pretty wide report... which is, of course, far too much for a person to look through. However,

it could be useful to run a big report and then make use of the search results functionality (which runs quickly in the browser)... in which case it's a subjective matter of 'finding out what is an

acceptable user experience for you' rather than having hard and fast rules generally applied.

The generic 'full export' functionality provided may also sensitive to the size of the report but much less so (i.e. you can probably export pretty massive reports to a csv file).

TECHNICAL IMPLEMENTATION

There are three parts to the implementation.

Metadata Tables There are 3 metadata tables that record report details, their parameters and ways of retrieving lists of reports and parameters. They also contain the sql (often parameterized) associated with

the reports and parameters. The tables are installed as part of the mifos X database installation. They are: stretchy_report stretchy_report_parameter stretchy_parameter

Some standard reports are set up with the installation data but that the metadata can be added and altered in order to add more parameters, reports, and report categorisations.

TODO IMPORTANCE List of standard reports not settled yet Minor

Maintenance pages for reports and parameters not yet developeed Medium (can be done using mysql GUI tool or ms-access etc)

REST api

The rest url passed by the client (Individual Lending) application is "${baseUrl}api/protected/reporting/flexireport". When a reporting request is made it eventually runs the imporant method called

retrieveGenericResultset which returns a generic resultset (GenericResultset). The generic resultset contains column heading information as well as the result data. This allows the JQuery UI

to be generic for any result set.

retrieveGenericResultset checks that the requesting user can run the requested report (authorisation) and use the parameter values passed (data scope) identifies the report sql that needs to be run does parameter replacement on the sql and wraps it a bit to avoid security and mysql issues runs the sql builds up and returns the generic resultset

TODO IMPORTANCE authorisation and data scope relates maintenance and Major (dependent on Oauth2 implementation) checks not implemented

JQuery datatable based UI The UI implementation of screen based reporting is encapsulated in the JQuery plugin called "stretchyreporting-V.js" (where V is a version number) stretchyreporting-V.js makes a lot of use of JQuery data table related functionality and other JQuery functionality/plugins ("i18n" implementation, for example)

common-head-stretchyreporting.jsp is the include file which has all the css and javascript imports required.

flexireport.jsp is used by the Individual Lending application to 'kick off' screen based reporting. The individual lending application uses just one page for all reporting, however, it's trivial to break

reporting pages into categories such as All, Loan, Client, Transaction, Pentaho, Charts, Screen - by providing different reportQuery parameters to the plugin initialisation (example below).

jQuery.stretchyReporting Plugin initialisation inside a java app (hope to be just html based soon).

var reportingParams = { rptDB: "mifosngprovider", //metadata database: could use different ones for testing, demos etc

RESTUrl: "${baseUrl}api/protected/reporting/flexireport",	//url for running ajax requests for screen based reports and parameters
pentahoUrl: "${baseUrl}pentahoreport",			//base url if the report is Pentaho
exportUrl: "${rootContext}/exportcsv",			//base url if user wants to export to csv file

bundleDir: "${resValue}/stretchyreporting/mifosngbundle/",	//directory to find translation files in

						//div names used in your html page for holding the various parts of the display
reportsListDiv: "myListOfReports",			
runReportDiv: "myRunReportButton",
clearReportDiv: "myClearReportButton",
inputParametersDiv: "myInputParameters",
reportOutputDiv: "myOutput",

indianFormat: false,				//Is current format Indian format?  
						//True means numbers are display in indian format from the start
highlightMissingXlations: "N",				//"Y" puts square brackets round any xlatable item not found in xlate files.
						//Lets a developer easily see what items are missing
loadingImg: "${resValue}/stretchyreporting/dots64.gif",	//override for "busy" animated gif
resValue: "${resValue}/stretchyreporting/",		//stretchyreporting directory - used to get swf file for copy/export buttons

//OAuth 1.0a parameters
apiKey: "${consumerKey}",
sharedSecret: "${consumerSecret}",
accessToken: "${accessToken}",
tokenSecret: "${tokenSecret}",

//other params
I18N_Needed: "Y",					//Y is default, if N then no translation lookup occurs
reportQuery: "FullReportList"				//FullReportList is default, but if you used SomeReportList then the initial list of 
						//reports in the drop down list will be got by running the sql of the matching row in
						//the stretchy_parameter metadata table

};

jQuery.stretchyReporting.initialise(reportingParams);