Mifos User Documentation Portal
  • Overview and Background
    • History and Background
    • Mifos Vision, Mission & Values
    • Who Mifos is For
    • How Mifos is Used
    • The Mifos Stack
      • Overview
    • Mifos v/s Fineract
    • Community app v/s Webapps
  • General Contributing Guidelines
    • Upstream Development Approach
      • Contribution Process
      • The Apache Way
    • Contributors Guide
    • Contribution Guidelines: Android Project
    • Contribution Guidelines: Web Project
  • Getting Started
    • Getting started with Git & Github
    • Postman integration with Gitbook for testing
    • Testing framework-Web
    • Testing framework-Android
    • Mifos CI/CD tools and instructions
  • External 3rd Apps
    • Pentaho Reporting Plugin
    • Payment Hub
  • MifosX
    • Overview
    • User Manual
      • How to Use This Manual
      • For All Users
        • General Navigation
          • Sign In Page
          • Welcome Page
          • Dashboard Page
          • List Page
          • Menu Page
          • Form Page
          • Clients-Groups-Centers Navigation
          • Navigation Tool
          • Global Search
          • Loan Product and Accounting with Mifos
        • Reports
          • Standard Reports Shipped in Mifos X
          • Standard Reports Field Descriptions
          • Custom Reports
            • Creating Simple Pentaho Report and Embedding it to Mifos X
          • XBRL Reporting
      • For Administrators (Mifos X Platform)
        • Initial System Set Up
        • Administration
          • Users
          • Organization
            • Manage Offices
            • Currency Configuration
            • Manage Holidays
            • Manage Funds
            • Manage Employees
            • Bulk Loan Reassignment
            • Standing Instructions History
            • Teller / Cashier Management
            • Fund Mapping
            • Working Days
            • Password preferences
            • Payment type
            • Loan Provisioning Criteria
            • SMS Campaigns
            • Message Gateway Configuration
            • Daily Teller Cash Management
            • Entity Data Table Checks
          • System
            • Manage Data Tables
            • Audit Trails
            • Manage Codes
            • Manage Reports
            • Manage Roles and Permissions
            • Manage Scheduler Jobs
            • Configure Maker-Checker Tasks
            • Global Configuration
            • Manage Hooks
              • Steps for creating sms bridge
            • Account Number Preferences
            • Entity To Entity Mapping
              • External Services
          • Products
            • Loan Products
              • Loan Product Fields
                • Accounting Loan Product Fields
                • Configuring and Disbursing of Top-Up Loan
                • Declining Balance Interest Calculation
                • Enabling Floating Rate
                • Enabling Variable Installments
                • Flat interest calculation
                • Loan Product Fields—Additional Fields for Loans with Variable Terms based on Loan Cycle
                • Recalculate Interest
                • Tranche Loan Fields
              • Setting up Minimum Days between Disbursal and First Repayment
              • Product Mix
            • Charges
            • Floating Rate
            • Savings Products
              • Saving Product Fields
                • Accounting Saving Product Fields
            • Fixed Deposit Products
              • Fixed Deposit Product Fields
                • Accounting Fixed and Recurring Deposit Product Fields
              • Interest rate chart with amount range
            • Recurring Deposit
              • Recurring Deposit Product Fields
            • Interest Calculations for Deposit Products
            • Overdraft Product
            • Share products
              • Dividends
            • Manage Tax configuration
          • Templates
          • mifos dropdown
        • Clients, Groups and Centers
          • Manage Clients
            • Client Life Cycle Statuses
            • Client Relationship Management
          • Manage Groups
          • Manage Centers
        • Accounting
          • Chart of Accounts - General Ledger Setup
            • Default COA sql script
          • Accounts linked to Financial Activities
          • Migrate opening balances (Office-wise)
          • Accounting Rules
          • Provisioning Entries
          • Print General Ledger Listing
          • How To Enter GL Opening Balance
        • Configure Notifications
      • For Operational Users (Mifos X Web App)
        • Accounting operations
          • Add Journal Entries
          • Searching Accounting Transactions
          • Frequent Postings
          • Advanced Accounting Rules
          • Closing Entries
          • Accruals
        • Accounts and Transactions
          • Cash Transaction Processing
          • Deposit Accounts
            • Mifos X Saving Accounts
              • How to Activate a Saving Account
              • How to Add a Charge to a Saving Account
              • How to Approve a Saving Account Application
              • How to Calculate Interest for a Saving Account
              • How to Close a Saving Account
              • How to Create a Saving Account Application
                • Saving Account Fields
              • How to create overdraft account (Draft)
              • How to Delete a Saving Account Application
              • How to Make a Deposit to a Saving Account
              • How to Make a Withdrawal from a Saving Account
              • How to Modify a Saving Account Application
              • How to Post Interest to a Saving Account
              • How to Reject a Saving Account Application
              • How to Transfer funds from a Saving Account
              • How to Withdraw a Saving Account Application
            • Standing Instructions
              • How to Set up a Standing Instruction
                • Standing Instruction Form Fields
            • Term Deposit Accounts (Fixed Deposit / Recurring Deposit)
              • How to Activate a Term Deposit Account
              • How to Add a Charge to a Term Deposit Account
              • How to Approve a Term Deposit Account Application
              • How to Calculate Interest for a Term Deposit Account
              • How to Create a Term Deposit Account Application
                • Term Deposit Account Fields
              • How to Delete a Term Deposit Account Application
              • How to Make a Deposit to a Recurring Term Deposit Account
              • How to Modify a Term Deposit Account Application
              • How to Post Interest to a Term Deposit Account
              • How to Reject a Term Deposit Account Application
              • How to Withdraw a Term Deposit Account Application
            • Creating Recurring Deposit Account:
          • Loan Accounts
            • How to Create a Loan Account Application
              • Loan Account Fields
            • How to Make Repayments on a Client Loan Account
            • Track Loan Performance History
            • How to Approve a Loan Account Application
            • How to Add a Charge to a Loan Account
            • How to Modify a Loan Account Application
            • How to Assign a Loan Officer
            • How to Reject a Loan Account Application
            • How to Withdraw a Loan Account Application
            • How to Delete a Loan Account Application
            • How to Add Collateral to a Loan Account
            • How to Add/Remove/Modify a Guarantor to a Loan Account
            • How to Manage Loan Account Disbursement
            • How to Make Repayments on a Group Loan Account
            • Using collection sheet or other payment types for making payments
            • How to Waive Interest for a Loan Account
            • How to Write-off a Loan Account
            • How to Close a Loan Account as Rescheduled
            • How to Close a Loan Account
            • Making Prepay of the loan
            • How to Prepay a Loan Account
            • How To Create Group Loan Account
            • How to Process Bulk JLG Loan Application
            • How to generate - Loan transactions report
            • How to reschedule loan
            • How to provide floating rates
            • How to provide variable installments
            • How to create new loan - Interest Recalculation
            • Tranche loan process
            • Loan with term vary based on loan cycle
            • How to Place Guarantee Funds On-Hold?
          • Overdraft account
          • Saving Accounts
            • How To Create Group Saving Account
          • Share Accounts
        • Checker Inbox & Tasks
      • Side Bar - Features
        • Checker inbox and tasks
        • Collection sheet
        • Help.
        • Individual collection sheet
        • Keyboard short-cuts
        • Navigation
      • Others - mifos dropdown
        • Help
        • Profile
        • Settings
          • Date format
          • Language
        • Signout
      • Data Import Tool
        • Getting started with - Data Import Tool
        • Installing Data Import Tool
      • Glossary of Terms
      • Index
      • Reporting Documentation
      • Mobile Money Tool
    • User Space
      • New Feature Request Content
      • Recommended System Requirement
      • MifosX Overview
      • Installation guide
        • Upgrading Mifosx
        • Install MifosX on Windows
        • Install MifosX on Linux
        • Install MifosX on AWS
      • Release Notes
        • Release 1 and Features List
        • Release 2 and Features List
      • User Manual
        • How to Use This Manual
        • For All Users
          • General Navigation
            • Sign In Page
        • For Administrators
          • Initial System Set Up
        • For Operationals
      • Roadmap
      • Frequently Asked Questions (FAQs)
      • Demo Server Details
    • Developer Space
      • Setting Up Dev Environment
        • Setting up Frontend - community-app
      • Setting up MifosX from latest builds
      • Contributors Guide
      • Customising MifosX
      • Additional features
        • Data import tool
        • SMS campaigns
        • Pentaho reports
      • How Mifosx works
      • Adding a new tenant to MifosX instance
      • Using REST client for testing
      • API documentation
      • Scaling MifosX
      • Upgrading MifosX
      • Troubleshooting guide
  • Core Banking & Embedded Finance
    • Core Banking
      • Overview
    • Embedded Finance
      • Overview
  • Application Framework
    • Payment Hub EE
  • Main Platforms
    • Fineract 1.x
      • Apache Fineract
      • Apache Fineract Client
    • Fineract CN
      • Overview
      • User Manual
  • Staff UI Platforms
    • Fineract 1.x
      • Community-App
      • Web-App
      • Web Self-Service App
        • Online Banking App 2.0 User Manual
          • Developer Environment Setup for Online Banking App 2.0
      • Mobile Field Officer Applications
        • Android Client
          • User Manual
    • Fineract CN
      • Digital Bank User Interface
      • Mobile Field Officer Applications
        • Fineract CN Mobile
  • Customer UI Platforms
    • Fineract 1.x
      • Mobile Banking
        • Mifos Mobile
      • Mobile Wallets
      • Online Banking
    • Fineract CN
      • Mobile Banking
        • Fineract CN Mobile
        • Mifos Mobile CN
      • Mobile Wallets
      • Online Banking
  • Ancillary Tools
    • Fineract 1.x
      • Message Gateway
      • Mifos Passcode
      • PPI Vision
      • DevOps Tooling
      • Fineract Android SDK
      • MifosX Admin
      • Mifos ChatBot
      • Data Import Tool
    • Fineract CN
      • Fineract CN Containers
      • Fineract CN Terraform
  • Payment and Process Orchestration
    • Fineract 1.x
      • Payment Hub
      • Fineract Pentaho
    • Fineract CN
      • Staff Interfaces
        • Digital Bank UI
      • Customer Interfaces
        • Fineract CN Mobile
        • Mifos Mobile CN
        • Fineract CN Mobile
      • Ancillary Tools
        • Fineract CN Terraform
        • Fineract CN Containers
    • Payment Hub EE
Powered by GitBook
On this page

Was this helpful?

  1. MifosX
  2. User Manual
  3. For All Users
  4. Reports
  5. Custom Reports

Creating Simple Pentaho Report and Embedding it to Mifos X

PreviousCustom ReportsNextXBRL Reporting

Last updated 6 years ago

Was this helpful?

You could also follow the video guide link here:-

I. Downloading and Installing Pentaho Report Designer

1. To download Pentaho Report Designer Tool please click on the following link,

https://sourceforge.net/projects/jfreereport/files/latest/download

Note: The current version of Pentaho Report Designer is 5.0.1

2. It will be the zipped file with size around 268 MB.

3. Download it and unzip it in the required directory.

4. Copy mysql connector jdbc into lib folder. http://dev.mysql.com/downloads/file.php?id=457911

II. Starting Pentaho Report Designer

1. Go into the unzipped 'Pentaho Report Designer' folder and double click on 'report-designer.bat' file(for Windows).

2. Once Pentaho Report Designer tool starts, you will be welcomed with the following window.

III. Creating Your First Pentaho Report

1. Go to the 'File' Menu and click on 'New' button.

2. Go to the 'Data' tab, click on 'Add Data Source' and then click on JDBC.

3. Once you click on JDBC, the following window will open, where you need to click on '+' (Create New Connection) button.

4. Once you click on 'Create New Connection', the following window will appear. Here, you have to provide all the setting information as given in the following table and then click on 'Test' button to generate the Message Box where it shows the confirmation of connection. Once Connection is established click on 'OK' button.

SI.NO

Field Name

Description

1

Connection Name

Provide the appropriate connection name

2

Host Name

This is the name of server to which designer tool connects to. In this case, we are connecting to 'localhost'.

3

Database Name

Provide the database name to which designer tool needs to connect. In MifosX, we generally connect to the database 'mifostenant-default'

4

Connection Type

In the left column, please select the appropriate connection type(in this case, mysql) . Once you select 'mysql' the Port Number automatically selected as 3306.

5

User Name

Provide the mysql username( in this case it is 'root')

6

Password

Provide the mysql password.

5. As shown in the following window, click on '+' button to add the sql query.

6. Here, Provide the Sql Query Name and write the appropriate Sql Query and then click on the 'Preview' button to confirm that, the Sql query executed successfully. Then click on the 'OK' button.

Query example

select mc.firstname,mc.lastname,mc.account_no from m_client mc where mc.activation_date between ${startdate} and ${enddate}

7. Once you create executable Sql Query, you may have to add parameters if required. In order to embed Pentaho Report to MifosX, At least you have to create 3 parameters which are passed to MifosX, namely:

  • tenantUrl

  • username

  • password.

To add parameters, under the 'Data' tab, scroll down to see 'parameters' and then right click on 'parameters' and then click on 'Add parameter' to see the following window.

SI.NO

Parameter Name

Parameter Fields

1

tenantUrl

Name:tenantUrl

Label:Tenant Db

Value type: String

Default Value: jdbc:mysql://localhost:3306/mifostenant-default

Check mandatory

2

username

Name:username Label:User Name Value Type: String

Default Value: root

3

password

Name:password Label:Password Value Type: String

Default Value: mysql

Check Mandatory Field

4

startdate (For this example)

Name:startdate Label:Start date Value Type: Date (SQL)

Display Type: Date Picker

5

enddate (For this example)

Name:enddate Label:End date Value Type: Date (SQL)

Display Type: Date Picker

Basically, these three parameters are required to embed Pentaho Report in MifosX. Similarly, you can define more parameters as per requirement of the report.

8. Once you add the parameters, you need to check whether the report is working fine in the pentaho environment. First, You need to add the Report fields into 'Details' section of the pentaho environment. In our example, the fields 'firstname', 'lastname' and 'account_no' of the clients are added. In order to add them you need to drag each field in 'Details' section of the pentaho environment as shown in following two screenshots.

9. Next you need to click on the 'Run' button and choose appropriate format for the report.

10. Once you 'Run' the report by choosing appropriate format, the following window will open where you enter the parameters and generate the report. (In this case, PDF is selected).

11. If you click on the 'OK' button and a PDF is generated, it means Pentaho Report was created successfully.

12. Add Global scripting once you are done with all the design and testing. Before you save the report you need to add global scripting.

Copy this content into it

// This is a template to demonstrate the existing functions
// you can use in a global data-source script. All functions and
// global variables you define here will be available for the
// all query scripts.
//
// Feel free to delete any function you don't use.
//
// you can process scripts via
// scriptHelper.eval("println ('Hello World');", "ECMAscript");
//
// or your can load your own ECMA/JavaScript files and execute them via
// scriptHelper.evalFile ("/your/file/here.js", "ECMAscript");
//
// both inline scripts and files will be executed in the global context -
// any function or variable defined there will be available globally.
//
// The following global variables exist by default:
// resourceManager : The ResourceManager can be used to load files
// contextKey : ResourceKey the context key points to the prpt-bundle
// dataFactory : The current datafactory instance
// configuration : The current report configuration
// resourceBundleFactory : Access to translations and locale information

function init(dataRow)
{
// place all initialization logic here. This is the right space to
// prepare complex lookup tables or to fill global variables.

// this method is called once when the data-source is first used.
var tenantUrl = dataRow.get("tenantUrl");
var user = dataRow.get("username");
var password = dataRow.get("password");
dataFactory.getConnectionProvider().setProperty("user", user);
dataFactory.getConnectionProvider().setProperty("password", password);
dataFactory.getConnectionProvider().setUrl(tenantUrl);
}

function shutdown()
{
// place all shutdown logic here. If you use any persistent resources
// like files or connections make sure you close them here.

// this method is called once during the data-source shut-down. It
// will be called after all query scripts have been shut down.

}

13. You need to save the report with '.prpt' extension. In order to do it, Just go to the 'File' menu and click on 'Save' button.

IV. Embedding Pentaho Report in MifosX

1. Create a '.mifosx' folder (use DOS to create the folder) in your home directory and then create a sub folder 'pentahoReports' in '.mifosx' folder.

2. Please copy the pentaho report file( with *.prpt file extension) which you have created earlier using 'Penntaho Report Designer Tool' in "home/.mifosx/pentahoReports" folder.

3. Go to Community-app and then go to Admin >> System >> Manage Reports and click '+Create Report' to see the following window.

4. Ensure 'Report Name' should be the same as the pentaho report file name (that is in this example, "Activated Clients.prpt").

5. Select Report Type as 'Pentaho' from drop-down list.

6. Ensure to check the box for "Use report(UI)".

7. Select the required parameters which are passed to pentaho. (in this case, startDate, endDate).

8. Click on the 'Submit' button to create the report.

V. Generating created Pentaho Report

Go to Community-app and then go to 'Reports' tab and then by selecting the report section>>then the required report you could generate the report.

In this example: Reports>>clients>>Activated clients.