You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@fineract.apache.org by Bharath Gowda <bg...@mifos.org> on 2021/06/02 13:01:35 UTC
Pentaho Plug-in help
Hi Michael and all Dev,
I hope you all are keeping safe and doing well.
I had tested the Pentaho plugin which Michael had created, and it works
pretty well.
It is a very useful plugin for reporting, thanks to Michael for this
valuable contribution :)
https://github.com/vorburger/fineract-pentaho
With inbuilt Pentaho support missing from Fineract, Many organizations are
trying to adopt this plugin into their servers and a lot of requests are
pouring in on how we can get this working through the Tomcat server along
with the "fineract-provider.war" file.
Tomcat servers are the ideal way of how most of the organizations run their
prod applications, so it would be perfect for production If we can run the
pentaho plug-in in the same server.
Any person who has any idea on how we can get it working on the tomcat
server along with "fineract-provider.war" please do share it here.
I tried to add the 'fineract-pentaho.jar' and other related .jar files from
"fineract-pentaho\build\distributions\lib" to Tomcat's Jar folder, but it
doesn't work.
Regards,
Bharath
Lead Implementation Analyst | Mifos Initiative
Skype: live:cbharath4| Mobile: +91.7019635592
http://mifos.org <http://facebook.com/mifos>
<http://www.twitter.com/mifos>
Re: Pentaho Plug-in help
Posted by Kelvin Ikome <ke...@ciniter.com>.
Hi Bharath,
It seems to be an issue with the report query. You can try debugging by
pasting in MySQL workbench of any DB client you are using then update in
the report when you have it working.
If you have difficulties with this, we can maybe schedule a call so I walk
you through it
On Fri, Jun 25, 2021 at 4:07 PM Bharath Gowda <bg...@mifos.org> wrote:
> Hi Aleks,
> Thank you for your input, adding the JAR file in the WEB-INF/lib folder
> did work.
>
> However, there seems to be a new issue where we are getting the
> error"org.pentaho.reporting.engine.classic.core.ReportDataFactoryException:
> Failed at query:"
>
> Full error message is below.
>
> There is no issue with the Query and the error is consistent for all the
> Pentaho reports, It would be great if anyone could help in analyzing the
> issue at the Fineract code level.
> I will also raise the JIRA ticket for the same
>
> "{"developerMessage":"The request caused a data integrity issue to be
> fired by the
> database.","httpStatusCode":"403","defaultUserMessage":"Pentaho failed:
> Failed at query: \n\n\n\n\n\n select a.name Branch,f.clients,a.
> Disbursed,a.principalpaid,a.intrestpaid,a. principalout,a.intrestout,\n
> a.Amountarres,b.groupcount,c.centercount,d.activeloan,e.areesloan 'no of
> loan arres'\n from\n (select ounder.name,\n \n
> sum(ml.principal_disbursed_derived) Disbursed,\n ounder.id oid,\n
> sum(ml.principal_repaid_derived) principalpaid,\n
> sum(ml.interest_repaid_derived) intrestpaid,\n
> sum(ml.principal_outstanding_derived)
> principalout,\nsum(ml.interest_outstanding_derived)
> intrestout,\nifnull(sum( mlarr.total_overdue_derived),0)
> Amountarres\n\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\n and ounder.hierarchy like
> concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
> ounder.id\nleft join m_loan ml\non ml.client_id=mc.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\nleft
> join m_loan_arrears_aging mlarr\non mlarr.loan_id=ml.id\nwhere
> mc.status_enum=300 and o.id=1\ngroup by ounder.name) a\n\ninner join
> (select '#group',count(mg2.id) as groupcount,ounder.id oid\nfrom m_office
> o\nleft join m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\n\nleft join m_group mg2 on mg2.office_id=
> ounder.id and mg2.level_id=2\n#where mg2.level_id=2\nwhere o.id=1\ngroup
> by ounder.id) b on b.oid=a.oid\ninner join (select count(mg.id) as
> centercount,ounder.id oid\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\n\nleft join m_group mg on mg.office_id=
> ounder.id and mg.level_id=1\n#where mg.level_id=1\nwhere o.id=1\ngroup by
> ounder.id) c on b.oid=c.oid\ninner join (select count(ml.id) activeloan,
> ounder.id oid\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
> ounder.id\ninner join m_loan ml on ml.client_id=mc.id\nwhere
> ml.loan_status_id=300 and o.id=1\ngroup by ounder.name\n)d on
> c.oid=d.oid\nleft join (select count(mlarr.loan_id) areesloan, ounder.id
> oid\nfrom m_office o\nleft join m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
> ounder.id\nleft join m_loan ml on ml.client_id=mc.id\nleft join
> m_loan_arrears_aging mlarr on ml.id=mlarr.loan_id\nwhere
> ml.loan_status_id=300\n and o.id=1\ngroup by ounder.name)e on c.oid=e.oid
> \n\ninner join (select count(mc.id) clients, ounder.id
> oid\nfrom m_office o\njoin m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
> ounder.id \nwhere mc.status_enum=300 and o.id=1\ngroup by ounder.name )f
> on b.oid=f.oid \n
>
> ","userMessageGlobalisationCode":"error.msg.reporting.error","errors":[{"developerMessage":"Pentaho
> failed: Failed at query: \n\n\n\n\n\n select a.name Branch,f.clients,a.
> Disbursed,a.principalpaid,a.intrestpaid,a. principalout,a.intrestout,\n
> a.Amountarres,b.groupcount,c.centercount,d.activeloan,e.areesloan 'no of
> loan arres'\n from\n (select ounder.name,\n \n
> sum(ml.principal_disbursed_derived) Disbursed,\n ounder.id oid,\n
> sum(ml.principal_repaid_derived) principalpaid,\n
> sum(ml.interest_repaid_derived) intrestpaid,\n
> sum(ml.principal_outstanding_derived)
> principalout,\nsum(ml.interest_outstanding_derived)
> intrestout,\nifnull(sum( mlarr.total_overdue_derived),0)
> Amountarres\n\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\n and ounder.hierarchy like
> concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
> ounder.id\nleft join m_loan ml\non ml.client_id=mc.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\nleft
> join m_loan_arrears_aging mlarr\non mlarr.loan_id=ml.id\nwhere
> mc.status_enum=300 and o.id=1\ngroup by ounder.name) a\n\ninner join
> (select '#group',count(mg2.id) as groupcount,ounder.id oid\nfrom m_office
> o\nleft join m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\n\nleft join m_group mg2 on mg2.office_id=
> ounder.id and mg2.level_id=2\n#where mg2.level_id=2\nwhere o.id=1\ngroup
> by ounder.id) b on b.oid=a.oid\ninner join (select count(mg.id) as
> centercount,ounder.id oid\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\n\nleft join m_group mg on mg.office_id=
> ounder.id and mg.level_id=1\n#where mg.level_id=1\nwhere o.id=1\ngroup by
> ounder.id) c on b.oid=c.oid\ninner join (select count(ml.id) activeloan,
> ounder.id oid\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
> ounder.id\ninner join m_loan ml on ml.client_id=mc.id\nwhere
> ml.loan_status_id=300 and o.id=1\ngroup by ounder.name\n)d on
> c.oid=d.oid\nleft join (select count(mlarr.loan_id) areesloan, ounder.id
> oid\nfrom m_office o\nleft join m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
> ounder.id\nleft join m_loan ml on ml.client_id=mc.id\nleft join
> m_loan_arrears_aging mlarr on ml.id=mlarr.loan_id\nwhere
> ml.loan_status_id=300\n and o.id=1\ngroup by ounder.name)e on c.oid=e.oid
> \n\ninner join (select count(mc.id) clients, ounder.id
> oid\nfrom m_office o\njoin m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
> ounder.id \nwhere mc.status_enum=300 and o.id=1\ngroup by ounder.name )f
> on b.oid=f.oid \n
>
> ","defaultUserMessage":"Pentaho failed: Failed at query: \n\n\n\n\n\n
> select a.name Branch,f.clients,a.
> Disbursed,a.principalpaid,a.intrestpaid,a. principalout,a.intrestout,\n
> a.Amountarres,b.groupcount,c.centercount,d.activeloan,e.areesloan 'no of
> loan arres'\n from\n (select ounder.name,\n \n
> sum(ml.principal_disbursed_derived) Disbursed,\n ounder.id oid,\n
> sum(ml.principal_repaid_derived) principalpaid,\n
> sum(ml.interest_repaid_derived) intrestpaid,\n
> sum(ml.principal_outstanding_derived)
> principalout,\nsum(ml.interest_outstanding_derived)
> intrestout,\nifnull(sum( mlarr.total_overdue_derived),0)
> Amountarres\n\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\n and ounder.hierarchy like
> concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
> ounder.id\nleft join m_loan ml\non ml.client_id=mc.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\nleft
> join m_loan_arrears_aging mlarr\non mlarr.loan_id=ml.id\nwhere
> mc.status_enum=300 and o.id=1\ngroup by ounder.name) a\n\ninner join
> (select '#group',count(mg2.id) as groupcount,ounder.id oid\nfrom m_office
> o\nleft join m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\n\nleft join m_group mg2 on mg2.office_id=
> ounder.id and mg2.level_id=2\n#where mg2.level_id=2\nwhere o.id=1\ngroup
> by ounder.id) b on b.oid=a.oid\ninner join (select count(mg.id) as
> centercount,ounder.id oid\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\n\nleft join m_group mg on mg.office_id=
> ounder.id and mg.level_id=1\n#where mg.level_id=1\nwhere o.id=1\ngroup by
> ounder.id) c on b.oid=c.oid\ninner join (select count(ml.id) activeloan,
> ounder.id oid\nfrom m_office o\nleft join m_office ounder on
> ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
> ounder.id\ninner join m_loan ml on ml.client_id=mc.id\nwhere
> ml.loan_status_id=300 and o.id=1\ngroup by ounder.name\n)d on
> c.oid=d.oid\nleft join (select count(mlarr.loan_id) areesloan, ounder.id
> oid\nfrom m_office o\nleft join m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
> ounder.id\nleft join m_loan ml on ml.client_id=mc.id\nleft join
> m_loan_arrears_aging mlarr on ml.id=mlarr.loan_id\nwhere
> ml.loan_status_id=300\n and o.id=1\ngroup by ounder.name)e on c.oid=e.oid
> \n\ninner join (select count(mc.id) clients, ounder.id
> oid\nfrom m_office o\njoin m_office ounder on ounder.hierarchy like
> concat(o.hierarchy,'%')\nand ounder.hierarchy like
> concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
> ounder.id \nwhere mc.status_enum=300 and o.id=1\ngroup by ounder.name )f
> on b.oid=f.oid \n
>
> ","userMessageGlobalisationCode":"error.msg.reporting.error","parameterName":null,"value":null,"args":[]}]}"
>
> Regards,
> Bharath
> Lead Implementation Analyst | Mifos Initiative
> Skype: live:cbharath4| Mobile: +91.7019635592
> http://mifos.org <http://facebook.com/mifos>
> <http://www.twitter.com/mifos>
>
>
> On Wed, Jun 23, 2021 at 8:39 PM Aleksandar Vidakovic <
> cheetah@monkeysintown.com> wrote:
>
>> Hi Bharath,
>>
>> ... a bit late to the party, but I'll try to help. As far as I remember
>> the reporting module was tested by Michael with a standalone Fineract JAR
>> (all dependencies packaged in one big JAR... like most Spring Boot
>> applications). If you are able to use/deploy Fineract that way I think that
>> would be easiest; because then you just have to drop the Pentaho module
>> into a folder called "libs" (located in the same parent folder as the
>> Fineract JAR itself; btw: there is a command line parameter that determines
>> the name of that folder... so doesn't need to be called necessarily "libs";
>> please see the Dockerfile entrypoint).
>>
>> If you have no choice and you need to deploy Fineract as a WAR with a
>> pre-installed Tomcat instance then I think the right way to add Pentaho is
>> to add the JAR file in the WEB-INF/lib folder of the exploded Fineract WAR.
>>
>> Let me know if that helped.
>>
>> Cheers,
>>
>> Aleks
>>
>> On Fri, Jun 4, 2021 at 12:16 PM Bharath Gowda <bg...@mifos.org> wrote:
>>
>>> Hi Victor,
>>>
>>> Thank you for your email, following are the details
>>>
>>> 1. Tomcat Version is 9.0.38
>>> 2. Fineract version is 1.5
>>> 3. Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>>>
>>> Regards,
>>> Bharath
>>> Lead Implementation Analyst | Mifos Initiative
>>> Skype: live:cbharath4| Mobile: +91.7019635592
>>> http://mifos.org <http://facebook.com/mifos>
>>> <http://www.twitter.com/mifos>
>>>
>>>
>>> On Thu, Jun 3, 2021 at 1:30 PM Laban Kibowen <
>>> laban.kibowen@intrasofttechnologies.com> wrote:
>>>
>>>> Hello Victor
>>>>
>>>> Tomcat Version is 9
>>>> Fineract version is 1.x presumably 1.4 or 1.5
>>>> Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>>>>
>>>> *LABAN KIBOWEN | **DevOps Engineer*
>>>> *Intrasoft Technologies Limited*
>>>> *3rd Floor, Ruman Plaza, St. Bernard Street.*
>>>> *P. O. Box 82298 - 80100, Mombasa.*
>>>> *Tel: +254729743332*
>>>> *Skype: live:bowenjnr37*
>>>> *Web: www.intrasofttechnologies.com
>>>> <http://www.intrasofttechnologies.com>*
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Jun 3, 2021 at 12:51 AM VICTOR MANUEL ROMERO RODRIGUEZ <
>>>> victor.romero@fintecheando.mx> wrote:
>>>>
>>>>> Hello Bharath,
>>>>>
>>>>> Quick questions?
>>>>>
>>>>> 1. Which is the Tomcat version?
>>>>> 2. Which is the Fineract version?
>>>>> 3. Which is the Tomcat's jar folder? (please share the directory or
>>>>> path)
>>>>>
>>>>> Regards
>>>>>
>>>>> Victor
>>>>>
>>>>> El mié, 2 jun 2021 a las 8:01, Bharath Gowda (<bg...@mifos.org>)
>>>>> escribió:
>>>>>
>>>>>> Hi Michael and all Dev,
>>>>>>
>>>>>> I hope you all are keeping safe and doing well.
>>>>>>
>>>>>> I had tested the Pentaho plugin which Michael had created, and it
>>>>>> works pretty well.
>>>>>> It is a very useful plugin for reporting, thanks to Michael for this
>>>>>> valuable contribution :)
>>>>>>
>>>>>> https://github.com/vorburger/fineract-pentaho
>>>>>>
>>>>>> With inbuilt Pentaho support missing from Fineract, Many
>>>>>> organizations are trying to adopt this plugin into their servers and a lot
>>>>>> of requests are pouring in on how we can get this working through the
>>>>>> Tomcat server along with the "fineract-provider.war" file.
>>>>>>
>>>>>> Tomcat servers are the ideal way of how most of the organizations run
>>>>>> their prod applications, so it would be perfect for production If we can
>>>>>> run the pentaho plug-in in the same server.
>>>>>>
>>>>>> Any person who has any idea on how we can get it working on the
>>>>>> tomcat server along with "fineract-provider.war" please do share it here.
>>>>>>
>>>>>>
>>>>>> I tried to add the 'fineract-pentaho.jar' and other related .jar
>>>>>> files from "fineract-pentaho\build\distributions\lib" to Tomcat's Jar
>>>>>> folder, but it doesn't work.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Bharath
>>>>>> Lead Implementation Analyst | Mifos Initiative
>>>>>> Skype: live:cbharath4| Mobile: +91.7019635592
>>>>>> http://mifos.org <http://facebook.com/mifos>
>>>>>> <http://www.twitter.com/mifos>
>>>>>>
>>>>>
--
Kelvin Ikome
Chief Executive Officer [image: facebook icon]
<https://www.facebook.com/ciniter.ltd> [image: twitter icon]
<https://twitter.com/ciniter_ltd> [image: youtube icon]
<https://www.youtube.com/channel/UCfOgsIgX3l1TT4G79NBUt-A> [image: linkedin
icon] <https://www.linkedin.com/company/ciniter>
t:
m:
e:
a: +(237) 677-666-659
kelvin@ciniter.com
Ciniter, Molyko, Buea, South West, Cameroon
www.ciniter.com
Re: Pentaho Plug-in help
Posted by Bharath Gowda <bg...@mifos.org>.
Hi Aleks,
Thank you for your input, adding the JAR file in the WEB-INF/lib folder did
work.
However, there seems to be a new issue where we are getting the
error"org.pentaho.reporting.engine.classic.core.ReportDataFactoryException:
Failed at query:"
Full error message is below.
There is no issue with the Query and the error is consistent for all the
Pentaho reports, It would be great if anyone could help in analyzing the
issue at the Fineract code level.
I will also raise the JIRA ticket for the same
"{"developerMessage":"The request caused a data integrity issue to be fired
by the database.","httpStatusCode":"403","defaultUserMessage":"Pentaho
failed: Failed at query: \n\n\n\n\n\n select a.name Branch,f.clients,a.
Disbursed,a.principalpaid,a.intrestpaid,a. principalout,a.intrestout,\n
a.Amountarres,b.groupcount,c.centercount,d.activeloan,e.areesloan 'no of
loan arres'\n from\n (select ounder.name,\n \n
sum(ml.principal_disbursed_derived) Disbursed,\n ounder.id oid,\n
sum(ml.principal_repaid_derived) principalpaid,\n
sum(ml.interest_repaid_derived) intrestpaid,\n
sum(ml.principal_outstanding_derived)
principalout,\nsum(ml.interest_outstanding_derived)
intrestout,\nifnull(sum( mlarr.total_overdue_derived),0)
Amountarres\n\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\n and ounder.hierarchy like
concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
ounder.id\nleft join m_loan ml\non
ml.client_id=mc.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\nleft
join m_loan_arrears_aging mlarr\non mlarr.loan_id=ml.id\nwhere
mc.status_enum=300 and o.id=1\ngroup by ounder.name) a\n\ninner join
(select '#group',count(mg2.id) as groupcount,ounder.id oid\nfrom m_office
o\nleft join m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\n\nleft join m_group mg2 on mg2.office_id=
ounder.id and mg2.level_id=2\n#where mg2.level_id=2\nwhere o.id=1\ngroup by
ounder.id) b on b.oid=a.oid\ninner join (select count(mg.id) as
centercount,ounder.id oid\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\n\nleft join m_group mg on mg.office_id=
ounder.id and mg.level_id=1\n#where mg.level_id=1\nwhere o.id=1\ngroup by
ounder.id) c on b.oid=c.oid\ninner join (select count(ml.id) activeloan,
ounder.id oid\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
ounder.id\ninner join m_loan ml on ml.client_id=mc.id\nwhere
ml.loan_status_id=300 and o.id=1\ngroup by ounder.name\n)d on
c.oid=d.oid\nleft join (select count(mlarr.loan_id) areesloan, ounder.id
oid\nfrom m_office o\nleft join m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
ounder.id\nleft join m_loan ml on ml.client_id=mc.id\nleft join
m_loan_arrears_aging mlarr on ml.id=mlarr.loan_id\nwhere
ml.loan_status_id=300\n and o.id=1\ngroup by ounder.name)e on c.oid=e.oid
\n\ninner join (select count(mc.id) clients, ounder.id oid\nfrom
m_office o\njoin m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
ounder.id \nwhere mc.status_enum=300 and o.id=1\ngroup by ounder.name )f on
b.oid=f.oid \n
","userMessageGlobalisationCode":"error.msg.reporting.error","errors":[{"developerMessage":"Pentaho
failed: Failed at query: \n\n\n\n\n\n select a.name Branch,f.clients,a.
Disbursed,a.principalpaid,a.intrestpaid,a. principalout,a.intrestout,\n
a.Amountarres,b.groupcount,c.centercount,d.activeloan,e.areesloan 'no of
loan arres'\n from\n (select ounder.name,\n \n
sum(ml.principal_disbursed_derived) Disbursed,\n ounder.id oid,\n
sum(ml.principal_repaid_derived) principalpaid,\n
sum(ml.interest_repaid_derived) intrestpaid,\n
sum(ml.principal_outstanding_derived)
principalout,\nsum(ml.interest_outstanding_derived)
intrestout,\nifnull(sum( mlarr.total_overdue_derived),0)
Amountarres\n\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\n and ounder.hierarchy like
concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
ounder.id\nleft join m_loan ml\non
ml.client_id=mc.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\nleft
join m_loan_arrears_aging mlarr\non mlarr.loan_id=ml.id\nwhere
mc.status_enum=300 and o.id=1\ngroup by ounder.name) a\n\ninner join
(select '#group',count(mg2.id) as groupcount,ounder.id oid\nfrom m_office
o\nleft join m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\n\nleft join m_group mg2 on mg2.office_id=
ounder.id and mg2.level_id=2\n#where mg2.level_id=2\nwhere o.id=1\ngroup by
ounder.id) b on b.oid=a.oid\ninner join (select count(mg.id) as
centercount,ounder.id oid\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\n\nleft join m_group mg on mg.office_id=
ounder.id and mg.level_id=1\n#where mg.level_id=1\nwhere o.id=1\ngroup by
ounder.id) c on b.oid=c.oid\ninner join (select count(ml.id) activeloan,
ounder.id oid\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
ounder.id\ninner join m_loan ml on ml.client_id=mc.id\nwhere
ml.loan_status_id=300 and o.id=1\ngroup by ounder.name\n)d on
c.oid=d.oid\nleft join (select count(mlarr.loan_id) areesloan, ounder.id
oid\nfrom m_office o\nleft join m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
ounder.id\nleft join m_loan ml on ml.client_id=mc.id\nleft join
m_loan_arrears_aging mlarr on ml.id=mlarr.loan_id\nwhere
ml.loan_status_id=300\n and o.id=1\ngroup by ounder.name)e on c.oid=e.oid
\n\ninner join (select count(mc.id) clients, ounder.id oid\nfrom
m_office o\njoin m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
ounder.id \nwhere mc.status_enum=300 and o.id=1\ngroup by ounder.name )f on
b.oid=f.oid \n
","defaultUserMessage":"Pentaho
failed: Failed at query: \n\n\n\n\n\n select a.name Branch,f.clients,a.
Disbursed,a.principalpaid,a.intrestpaid,a. principalout,a.intrestout,\n
a.Amountarres,b.groupcount,c.centercount,d.activeloan,e.areesloan 'no of
loan arres'\n from\n (select ounder.name,\n \n
sum(ml.principal_disbursed_derived) Disbursed,\n ounder.id oid,\n
sum(ml.principal_repaid_derived) principalpaid,\n
sum(ml.interest_repaid_derived) intrestpaid,\n
sum(ml.principal_outstanding_derived)
principalout,\nsum(ml.interest_outstanding_derived)
intrestout,\nifnull(sum( mlarr.total_overdue_derived),0)
Amountarres\n\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\n and ounder.hierarchy like
concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
ounder.id\nleft join m_loan ml\non
ml.client_id=mc.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\nleft
join m_loan_arrears_aging mlarr\non mlarr.loan_id=ml.id\nwhere
mc.status_enum=300 and o.id=1\ngroup by ounder.name) a\n\ninner join
(select '#group',count(mg2.id) as groupcount,ounder.id oid\nfrom m_office
o\nleft join m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\n\nleft join m_group mg2 on mg2.office_id=
ounder.id and mg2.level_id=2\n#where mg2.level_id=2\nwhere o.id=1\ngroup by
ounder.id) b on b.oid=a.oid\ninner join (select count(mg.id) as
centercount,ounder.id oid\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\n\nleft join m_group mg on mg.office_id=
ounder.id and mg.level_id=1\n#where mg.level_id=1\nwhere o.id=1\ngroup by
ounder.id) c on b.oid=c.oid\ninner join (select count(ml.id) activeloan,
ounder.id oid\nfrom m_office o\nleft join m_office ounder on
ounder.hierarchy like concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
ounder.id\ninner join m_loan ml on ml.client_id=mc.id\nwhere
ml.loan_status_id=300 and o.id=1\ngroup by ounder.name\n)d on
c.oid=d.oid\nleft join (select count(mlarr.loan_id) areesloan, ounder.id
oid\nfrom m_office o\nleft join m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\nleft join m_client mc on mc.office_id=
ounder.id\nleft join m_loan ml on ml.client_id=mc.id\nleft join
m_loan_arrears_aging mlarr on ml.id=mlarr.loan_id\nwhere
ml.loan_status_id=300\n and o.id=1\ngroup by ounder.name)e on c.oid=e.oid
\n\ninner join (select count(mc.id) clients, ounder.id oid\nfrom
m_office o\njoin m_office ounder on ounder.hierarchy like
concat(o.hierarchy,'%')\nand ounder.hierarchy like
concat(${userhierarchy},'%')\ninner join m_client mc on mc.office_id=
ounder.id \nwhere mc.status_enum=300 and o.id=1\ngroup by ounder.name )f on
b.oid=f.oid \n
","userMessageGlobalisationCode":"error.msg.reporting.error","parameterName":null,"value":null,"args":[]}]}"
Regards,
Bharath
Lead Implementation Analyst | Mifos Initiative
Skype: live:cbharath4| Mobile: +91.7019635592
http://mifos.org <http://facebook.com/mifos>
<http://www.twitter.com/mifos>
On Wed, Jun 23, 2021 at 8:39 PM Aleksandar Vidakovic <
cheetah@monkeysintown.com> wrote:
> Hi Bharath,
>
> ... a bit late to the party, but I'll try to help. As far as I remember
> the reporting module was tested by Michael with a standalone Fineract JAR
> (all dependencies packaged in one big JAR... like most Spring Boot
> applications). If you are able to use/deploy Fineract that way I think that
> would be easiest; because then you just have to drop the Pentaho module
> into a folder called "libs" (located in the same parent folder as the
> Fineract JAR itself; btw: there is a command line parameter that determines
> the name of that folder... so doesn't need to be called necessarily "libs";
> please see the Dockerfile entrypoint).
>
> If you have no choice and you need to deploy Fineract as a WAR with a
> pre-installed Tomcat instance then I think the right way to add Pentaho is
> to add the JAR file in the WEB-INF/lib folder of the exploded Fineract WAR.
>
> Let me know if that helped.
>
> Cheers,
>
> Aleks
>
> On Fri, Jun 4, 2021 at 12:16 PM Bharath Gowda <bg...@mifos.org> wrote:
>
>> Hi Victor,
>>
>> Thank you for your email, following are the details
>>
>> 1. Tomcat Version is 9.0.38
>> 2. Fineract version is 1.5
>> 3. Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>>
>> Regards,
>> Bharath
>> Lead Implementation Analyst | Mifos Initiative
>> Skype: live:cbharath4| Mobile: +91.7019635592
>> http://mifos.org <http://facebook.com/mifos>
>> <http://www.twitter.com/mifos>
>>
>>
>> On Thu, Jun 3, 2021 at 1:30 PM Laban Kibowen <
>> laban.kibowen@intrasofttechnologies.com> wrote:
>>
>>> Hello Victor
>>>
>>> Tomcat Version is 9
>>> Fineract version is 1.x presumably 1.4 or 1.5
>>> Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>>>
>>> *LABAN KIBOWEN | **DevOps Engineer*
>>> *Intrasoft Technologies Limited*
>>> *3rd Floor, Ruman Plaza, St. Bernard Street.*
>>> *P. O. Box 82298 - 80100, Mombasa.*
>>> *Tel: +254729743332*
>>> *Skype: live:bowenjnr37*
>>> *Web: www.intrasofttechnologies.com
>>> <http://www.intrasofttechnologies.com>*
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Jun 3, 2021 at 12:51 AM VICTOR MANUEL ROMERO RODRIGUEZ <
>>> victor.romero@fintecheando.mx> wrote:
>>>
>>>> Hello Bharath,
>>>>
>>>> Quick questions?
>>>>
>>>> 1. Which is the Tomcat version?
>>>> 2. Which is the Fineract version?
>>>> 3. Which is the Tomcat's jar folder? (please share the directory or
>>>> path)
>>>>
>>>> Regards
>>>>
>>>> Victor
>>>>
>>>> El mié, 2 jun 2021 a las 8:01, Bharath Gowda (<bg...@mifos.org>)
>>>> escribió:
>>>>
>>>>> Hi Michael and all Dev,
>>>>>
>>>>> I hope you all are keeping safe and doing well.
>>>>>
>>>>> I had tested the Pentaho plugin which Michael had created, and it
>>>>> works pretty well.
>>>>> It is a very useful plugin for reporting, thanks to Michael for this
>>>>> valuable contribution :)
>>>>>
>>>>> https://github.com/vorburger/fineract-pentaho
>>>>>
>>>>> With inbuilt Pentaho support missing from Fineract, Many organizations
>>>>> are trying to adopt this plugin into their servers and a lot of requests
>>>>> are pouring in on how we can get this working through the Tomcat server
>>>>> along with the "fineract-provider.war" file.
>>>>>
>>>>> Tomcat servers are the ideal way of how most of the organizations run
>>>>> their prod applications, so it would be perfect for production If we can
>>>>> run the pentaho plug-in in the same server.
>>>>>
>>>>> Any person who has any idea on how we can get it working on the tomcat
>>>>> server along with "fineract-provider.war" please do share it here.
>>>>>
>>>>>
>>>>> I tried to add the 'fineract-pentaho.jar' and other related .jar files
>>>>> from "fineract-pentaho\build\distributions\lib" to Tomcat's Jar folder,
>>>>> but it doesn't work.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>> Bharath
>>>>> Lead Implementation Analyst | Mifos Initiative
>>>>> Skype: live:cbharath4| Mobile: +91.7019635592
>>>>> http://mifos.org <http://facebook.com/mifos>
>>>>> <http://www.twitter.com/mifos>
>>>>>
>>>>
Re: Pentaho Plug-in help
Posted by Aleksandar Vidakovic <ch...@monkeysintown.com>.
Hi Bharath,
... a bit late to the party, but I'll try to help. As far as I remember the
reporting module was tested by Michael with a standalone Fineract JAR (all
dependencies packaged in one big JAR... like most Spring Boot
applications). If you are able to use/deploy Fineract that way I think that
would be easiest; because then you just have to drop the Pentaho module
into a folder called "libs" (located in the same parent folder as the
Fineract JAR itself; btw: there is a command line parameter that determines
the name of that folder... so doesn't need to be called necessarily "libs";
please see the Dockerfile entrypoint).
If you have no choice and you need to deploy Fineract as a WAR with a
pre-installed Tomcat instance then I think the right way to add Pentaho is
to add the JAR file in the WEB-INF/lib folder of the exploded Fineract WAR.
Let me know if that helped.
Cheers,
Aleks
On Fri, Jun 4, 2021 at 12:16 PM Bharath Gowda <bg...@mifos.org> wrote:
> Hi Victor,
>
> Thank you for your email, following are the details
>
> 1. Tomcat Version is 9.0.38
> 2. Fineract version is 1.5
> 3. Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>
> Regards,
> Bharath
> Lead Implementation Analyst | Mifos Initiative
> Skype: live:cbharath4| Mobile: +91.7019635592
> http://mifos.org <http://facebook.com/mifos>
> <http://www.twitter.com/mifos>
>
>
> On Thu, Jun 3, 2021 at 1:30 PM Laban Kibowen <
> laban.kibowen@intrasofttechnologies.com> wrote:
>
>> Hello Victor
>>
>> Tomcat Version is 9
>> Fineract version is 1.x presumably 1.4 or 1.5
>> Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>>
>> *LABAN KIBOWEN | **DevOps Engineer*
>> *Intrasoft Technologies Limited*
>> *3rd Floor, Ruman Plaza, St. Bernard Street.*
>> *P. O. Box 82298 - 80100, Mombasa.*
>> *Tel: +254729743332*
>> *Skype: live:bowenjnr37*
>> *Web: www.intrasofttechnologies.com
>> <http://www.intrasofttechnologies.com>*
>>
>>
>>
>>
>>
>> On Thu, Jun 3, 2021 at 12:51 AM VICTOR MANUEL ROMERO RODRIGUEZ <
>> victor.romero@fintecheando.mx> wrote:
>>
>>> Hello Bharath,
>>>
>>> Quick questions?
>>>
>>> 1. Which is the Tomcat version?
>>> 2. Which is the Fineract version?
>>> 3. Which is the Tomcat's jar folder? (please share the directory or path)
>>>
>>> Regards
>>>
>>> Victor
>>>
>>> El mié, 2 jun 2021 a las 8:01, Bharath Gowda (<bg...@mifos.org>)
>>> escribió:
>>>
>>>> Hi Michael and all Dev,
>>>>
>>>> I hope you all are keeping safe and doing well.
>>>>
>>>> I had tested the Pentaho plugin which Michael had created, and it works
>>>> pretty well.
>>>> It is a very useful plugin for reporting, thanks to Michael for this
>>>> valuable contribution :)
>>>>
>>>> https://github.com/vorburger/fineract-pentaho
>>>>
>>>> With inbuilt Pentaho support missing from Fineract, Many organizations
>>>> are trying to adopt this plugin into their servers and a lot of requests
>>>> are pouring in on how we can get this working through the Tomcat server
>>>> along with the "fineract-provider.war" file.
>>>>
>>>> Tomcat servers are the ideal way of how most of the organizations run
>>>> their prod applications, so it would be perfect for production If we can
>>>> run the pentaho plug-in in the same server.
>>>>
>>>> Any person who has any idea on how we can get it working on the tomcat
>>>> server along with "fineract-provider.war" please do share it here.
>>>>
>>>>
>>>> I tried to add the 'fineract-pentaho.jar' and other related .jar files
>>>> from "fineract-pentaho\build\distributions\lib" to Tomcat's Jar folder,
>>>> but it doesn't work.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Regards,
>>>> Bharath
>>>> Lead Implementation Analyst | Mifos Initiative
>>>> Skype: live:cbharath4| Mobile: +91.7019635592
>>>> http://mifos.org <http://facebook.com/mifos>
>>>> <http://www.twitter.com/mifos>
>>>>
>>>
Re: Pentaho Plug-in help
Posted by Bharath Gowda <bg...@mifos.org>.
Hi Victor,
Thank you for your email, following are the details
1. Tomcat Version is 9.0.38
2. Fineract version is 1.5
3. Tomcat's jar folder is in '/usr/share/tomcat9/lib'
Regards,
Bharath
Lead Implementation Analyst | Mifos Initiative
Skype: live:cbharath4| Mobile: +91.7019635592
http://mifos.org <http://facebook.com/mifos>
<http://www.twitter.com/mifos>
On Thu, Jun 3, 2021 at 1:30 PM Laban Kibowen <
laban.kibowen@intrasofttechnologies.com> wrote:
> Hello Victor
>
> Tomcat Version is 9
> Fineract version is 1.x presumably 1.4 or 1.5
> Tomcat's jar folder is in '/usr/share/tomcat9/lib'
>
> *LABAN KIBOWEN | **DevOps Engineer*
> *Intrasoft Technologies Limited*
> *3rd Floor, Ruman Plaza, St. Bernard Street.*
> *P. O. Box 82298 - 80100, Mombasa.*
> *Tel: +254729743332*
> *Skype: live:bowenjnr37*
> *Web: www.intrasofttechnologies.com <http://www.intrasofttechnologies.com>*
>
>
>
>
>
> On Thu, Jun 3, 2021 at 12:51 AM VICTOR MANUEL ROMERO RODRIGUEZ <
> victor.romero@fintecheando.mx> wrote:
>
>> Hello Bharath,
>>
>> Quick questions?
>>
>> 1. Which is the Tomcat version?
>> 2. Which is the Fineract version?
>> 3. Which is the Tomcat's jar folder? (please share the directory or path)
>>
>> Regards
>>
>> Victor
>>
>> El mié, 2 jun 2021 a las 8:01, Bharath Gowda (<bg...@mifos.org>)
>> escribió:
>>
>>> Hi Michael and all Dev,
>>>
>>> I hope you all are keeping safe and doing well.
>>>
>>> I had tested the Pentaho plugin which Michael had created, and it works
>>> pretty well.
>>> It is a very useful plugin for reporting, thanks to Michael for this
>>> valuable contribution :)
>>>
>>> https://github.com/vorburger/fineract-pentaho
>>>
>>> With inbuilt Pentaho support missing from Fineract, Many organizations
>>> are trying to adopt this plugin into their servers and a lot of requests
>>> are pouring in on how we can get this working through the Tomcat server
>>> along with the "fineract-provider.war" file.
>>>
>>> Tomcat servers are the ideal way of how most of the organizations run
>>> their prod applications, so it would be perfect for production If we can
>>> run the pentaho plug-in in the same server.
>>>
>>> Any person who has any idea on how we can get it working on the tomcat
>>> server along with "fineract-provider.war" please do share it here.
>>>
>>>
>>> I tried to add the 'fineract-pentaho.jar' and other related .jar files
>>> from "fineract-pentaho\build\distributions\lib" to Tomcat's Jar folder,
>>> but it doesn't work.
>>>
>>>
>>>
>>>
>>>
>>> Regards,
>>> Bharath
>>> Lead Implementation Analyst | Mifos Initiative
>>> Skype: live:cbharath4| Mobile: +91.7019635592
>>> http://mifos.org <http://facebook.com/mifos>
>>> <http://www.twitter.com/mifos>
>>>
>>
Re: Pentaho Plug-in help
Posted by Laban Kibowen <la...@intrasofttechnologies.com>.
Hello Victor
Tomcat Version is 9
Fineract version is 1.x presumably 1.4 or 1.5
Tomcat's jar folder is in '/usr/share/tomcat9/lib'
*LABAN KIBOWEN | **DevOps Engineer*
*Intrasoft Technologies Limited*
*3rd Floor, Ruman Plaza, St. Bernard Street.*
*P. O. Box 82298 - 80100, Mombasa.*
*Tel: +254729743332*
*Skype: live:bowenjnr37*
*Web: www.intrasofttechnologies.com <http://www.intrasofttechnologies.com>*
On Thu, Jun 3, 2021 at 12:51 AM VICTOR MANUEL ROMERO RODRIGUEZ <
victor.romero@fintecheando.mx> wrote:
> Hello Bharath,
>
> Quick questions?
>
> 1. Which is the Tomcat version?
> 2. Which is the Fineract version?
> 3. Which is the Tomcat's jar folder? (please share the directory or path)
>
> Regards
>
> Victor
>
> El mié, 2 jun 2021 a las 8:01, Bharath Gowda (<bg...@mifos.org>)
> escribió:
>
>> Hi Michael and all Dev,
>>
>> I hope you all are keeping safe and doing well.
>>
>> I had tested the Pentaho plugin which Michael had created, and it works
>> pretty well.
>> It is a very useful plugin for reporting, thanks to Michael for this
>> valuable contribution :)
>>
>> https://github.com/vorburger/fineract-pentaho
>>
>> With inbuilt Pentaho support missing from Fineract, Many organizations
>> are trying to adopt this plugin into their servers and a lot of requests
>> are pouring in on how we can get this working through the Tomcat server
>> along with the "fineract-provider.war" file.
>>
>> Tomcat servers are the ideal way of how most of the organizations run
>> their prod applications, so it would be perfect for production If we can
>> run the pentaho plug-in in the same server.
>>
>> Any person who has any idea on how we can get it working on the tomcat
>> server along with "fineract-provider.war" please do share it here.
>>
>>
>> I tried to add the 'fineract-pentaho.jar' and other related .jar files
>> from "fineract-pentaho\build\distributions\lib" to Tomcat's Jar folder,
>> but it doesn't work.
>>
>>
>>
>>
>>
>> Regards,
>> Bharath
>> Lead Implementation Analyst | Mifos Initiative
>> Skype: live:cbharath4| Mobile: +91.7019635592
>> http://mifos.org <http://facebook.com/mifos>
>> <http://www.twitter.com/mifos>
>>
>
Re: Pentaho Plug-in help
Posted by VICTOR MANUEL ROMERO RODRIGUEZ <vi...@fintecheando.mx>.
Hello Bharath,
Quick questions?
1. Which is the Tomcat version?
2. Which is the Fineract version?
3. Which is the Tomcat's jar folder? (please share the directory or path)
Regards
Victor
El mié, 2 jun 2021 a las 8:01, Bharath Gowda (<bg...@mifos.org>) escribió:
> Hi Michael and all Dev,
>
> I hope you all are keeping safe and doing well.
>
> I had tested the Pentaho plugin which Michael had created, and it works
> pretty well.
> It is a very useful plugin for reporting, thanks to Michael for this
> valuable contribution :)
>
> https://github.com/vorburger/fineract-pentaho
>
> With inbuilt Pentaho support missing from Fineract, Many organizations are
> trying to adopt this plugin into their servers and a lot of requests are
> pouring in on how we can get this working through the Tomcat server along
> with the "fineract-provider.war" file.
>
> Tomcat servers are the ideal way of how most of the organizations run
> their prod applications, so it would be perfect for production If we can
> run the pentaho plug-in in the same server.
>
> Any person who has any idea on how we can get it working on the tomcat
> server along with "fineract-provider.war" please do share it here.
>
>
> I tried to add the 'fineract-pentaho.jar' and other related .jar files
> from "fineract-pentaho\build\distributions\lib" to Tomcat's Jar folder,
> but it doesn't work.
>
>
>
>
>
> Regards,
> Bharath
> Lead Implementation Analyst | Mifos Initiative
> Skype: live:cbharath4| Mobile: +91.7019635592
> http://mifos.org <http://facebook.com/mifos>
> <http://www.twitter.com/mifos>
>