[Mifos-developer] Pentaho Reporting

[Lajeo Gloria]

Hi All,

Can anyone please help me solve this small issue. i want to subtract two numerica fields value in Pentaho Report. But i don't how to do this. I have this fields for sumDeposits and sumWithdrawals so i want to get the balance as at present by subtracting sumWithdrawals from sumDeposits but i don't know how to do that and where exactly to do it( this function and its syntax). I was thinking of using a message field to write the function in and also to display result.

And what will be the alias for a database table "other clients details" with spaces and how to query from a table with spaces in the name and also column name with spaces?

i was using e.g {select [Phone Number] from [other clients details]} but it throws errors.

Thanks

Regards

Gloria

[Tengiz Merabishvili]

Hello,

I have had some experience with Pentaho Reporting, so let me give you a brief tutorial.

FIrst when you open up the designer, you have to add Data Source. Click Data, Add Data Source, JDBC.

Choose the type of your DataSource and click on the pencil icon above to Edit it. After entering the details, you can click Test to see if it really connects to the Database.

Then on the right click the Add Query button ( a green + ), choose the query and write your SQL syntax on the bottom. ( You can click Preview to see what the query will return ).

Then Click OK, and on the right side of the designer on the Data Tab you will see that your query was added to Data Sets. In that query are the fields that the query returned, and you can work with them.

Now on to your question about formulas.

In the Data tab on the right, right click on Functions, Add Functions. Choose Common, Open Formula. Press OK. A new Formula will be added. Function name field is the name of the formula. The Formula field is the formula itself.

In this field,write =[yourField1]-[yourField2] and press enter. Then just drag the formula on the designer and it will display the formula as the usual fields.

See this for more information.

http://wiki.pentaho.com/display/Reporting/Formula+Expressions

Best Regards,

Tengiz Merabishvili

FINA Ltd |   ORACLE   Gold Partner

------------------------------------------------------------------------------

Mifos-developer mailing list
mifos-d...@lists.sourceforge.net
Unsubscribe or change settings at:
https://lists.sourceforge.net/lists/listinfo/mifos-developer

[Vishwas Babu]

>>select [Phone Number] from [other clients details]

select `Phone Number` from `other clients details`;

[Lajeo Gloria]

Hi All,

Thanks @Vishwas, that worked fine. Is this report possible for the current version of Mifos X? If yes, how can i go about the sql query?

See attached excel file below.

Thanks

Regards

Gloria

[Vishwas Babu]

Hi Gloria,

What does ledger card number mean? Could you maybe populate some dummy data (along with comments describing their mapping to Mifos fields) so that we can better understand the requirements

Regards,

Vishwas

[Lajeo Gloria]

Hi Vishwas,
The details column captures Transaction type and Mode of payment
(Payment id) and the ledger card actually captures the transaction
amount as in the attached excel file.

Thanks
Regards
Gloria

[Vishwas Babu]

Hi Gloria,

The query from the perspective of savings would be similar to 

select sa.account_no as "Account Number", concat (ev.enum_value, " ", IFNULL(paymentType.code_value,"")) as "Activity Details", st.amount as "Amount", user.username as "Teller", pd.receipt_number as "Receipt Number"

from m_savings_account sa join m_savings_account_transaction st on sa.id = st.savings_account_id and st.transaction_type_enum in (1,2)

left join m_payment_detail pd on st.payment_detail_id = pd.id

left join r_enum_value ev on st.transaction_type_enum = ev.enum_id and ev.enum_name = "savings_transaction_type_enum"

left join m_appuser user on st.appuser_id = user.id

left join m_code_value paymentType on pd.payment_type_cv_id=paymentType.id;

Loan tables are structured similarly

Note the while the current develop branch adds user_id in transaction table, for older version of mifosx, you would need to join with m_portfolio_command_source to fetch the same

Regards,

Vishwas

[Lajeo Gloria]

Hi Vishwas,

Thanks for the above sql query, i have made the one for loan. You can look at it to double check that its correct.

For Savings

 

select sa.account_no as "Account Number", concat (ev.enum_value, " ", IFNULL(paymentType.code_value,"")) as "Activity Details", st.amount as "Amount", user.username as "Teller", pd.receipt_number as "Receipt Number"

from m_savings_account sa join m_savings_account_transaction st on sa.id = st.savings_account_id and st.transaction_type_enum in (1,2)

left join m_payment_detail pd on st.payment_detail_id = pd.id

left join r_enum_value ev on st.transaction_type_enum = ev.enum_id and ev.enum_name = "savings_transaction_type_enum"

left join m_appuser user on st.appuser_id = user.id

left join m_code_value paymentType on pd.payment_type_cv_id=paymentType.id

where (st.transaction_date between '2014-10-30' and '2014-10-30')

order by 2 DESC,ev.enum_value;

 

For Loan

 

select ml.account_no as "Account Number", concat (ev.enum_value, " ", IFNULL(paymentType.code_value,"")) as "Activity Details", lt.amount as "Amount", user.username as "Teller", pd.receipt_number as "Receipt Number"

from m_loan ml join m_loan_transaction lt on ml.id = lt.loan_id and lt.transaction_type_enum in (1,2)

left join m_payment_detail pd on lt.payment_detail_id = pd.id

left join r_enum_value ev on lt.transaction_type_enum = ev.enum_id and ev.enum_name = "transaction_type_enum"

left join m_appuser user on lt.appuser_id = user.id

left join m_portfolio_command_source processing_result_enum on lt.appuser_id = user.id

left join m_code_value paymentType on pd.payment_type_cv_id=paymentType.id

where (lt.transaction_date ='2014-10-30')

order by 2 DESC,ev.enum_value;

How do i made this date be picked automatically i.e current date since this report is run on daily basis.

lt.transaction_date =${.............}

Secondly, isn't there a way to make this two sql queries as one since this is to be on on paper, please kindly integrate to make work easy.

Thanks

Regards

Gloria

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk

[Vishwas Babu]

Gloria,

If you are looking for a format identical to the one you had shared earlier, you would simply have to do a union between the two queries, ex

select st.transaction_date as  "Transaction Date",sa.account_no as "Account Number", concat (ev.enum_value, " ", IFNULL(paymentType.code_value,"")) as "Activity Details", if(st.transaction_type_enum=2, st.amount,"" ) as "Withdrawal Amount", if(st.transaction_type_enum=1, st.amount,"" ) as "Deposit Amount", "" as "Disbursal Amount", "" as "Repayment Amount", user.username as "Teller", pd.receipt_number as "Receipt Number"

from m_savings_account sa join m_savings_account_transaction st on sa.id = st.savings_account_id and st.transaction_type_enum in (1,2)

left join m_payment_detail pd on st.payment_detail_id = pd.id

left join r_enum_value ev on st.transaction_type_enum = ev.enum_id and ev.enum_name = "savings_transaction_type_enum"

left join m_appuser user on st.appuser_id = user.id

left join m_code_value paymentType on pd.payment_type_cv_id=paymentType.id

where st.transaction_date = '2014-12-30' and st.is_reversed= false

 

union all

select lt.transaction_date as  "Transaction Date", ml.account_no as "Account Number", concat (ev.enum_value, " ", IFNULL(paymentType.code_value,"")) as "Activity Details", "" as "Withdrawal Amount", "" as "Deposit Amount", if(lt.transaction_type_enum=1, lt.amount,"" ) as "Disbursal Amount", if(lt.transaction_type_enum=2, lt.amount,"" ) as "Repayment Amount",  user.username as "Teller", pd.receipt_number as "Receipt Number"

from m_loan ml join m_loan_transaction lt on ml.id = lt.loan_id and lt.transaction_type_enum in (1,2)

left join m_payment_detail pd on lt.payment_detail_id = pd.id

left join r_enum_value ev on lt.transaction_type_enum = ev.enum_id and ev.enum_name = "transaction_type_enum"

left join m_appuser user on lt.appuser_id = user.id

left join m_code_value paymentType on pd.payment_type_cv_id=paymentType.id

where lt.transaction_date = '2014-12-30' and lt.is_reversed = false

If you want to pass in today's date automatically, you can either use MySQL's CURDATE() function (Ex: select CURDATE() from dual ) or pass in the date as a pentaho parameter, details at http://infocenter.pentaho.com/help/index.jsp?topic=%2Freport_designer_user_guide%2Ftask_date_parameters.html

You could also consider making data a configurable parameter (in case you want to take a report for the previous day etc)....

Regards,

Vishwas