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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> 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 < >>> [email protected]> 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 < >>>> [email protected]> 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 (<[email protected]>) >>>>> 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 [email protected] Ciniter, Molyko, Buea, South West, Cameroon www.ciniter.com
