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 <
chee...@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 <bgo...@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.kibo...@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.rom...@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 (<bgo...@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>
>>>>>
>>>>
