This topic contains 2 replies, has 2 voices, and was last updated by shreyas 7 years, 8 months ago.
- Topic
The source option used to configure the Doctrine query builder to fetch the data to be displayed in the grid is below:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576source:type: ormacl_resource: salesdata.saleshistory_viewquery:select:- month(sh.invoicedate) as intinvoicemonth- monthname(sh.invoicedate) as invoicemonth- SUM(sh.netpricetotal) as salestotalfrom:- { table: %orocrm_account.account.entity.class%, alias: a }join:inner:- { join: a.owner, alias: u }- { join: u.owner, alias: bu }- { join: PGASalesDataBundle:SalesHistory, alias: sh, conditionType: WITH, condition: 'sh.clientnumber = a' }- { join: PGAPartReferenceBundle:PartFamily, alias: pm, conditionType: WITH, condition: 'sh.partfamily = pm' }- { join: PGAPartReferenceBundle:PartIndex, alias: pi, conditionType: WITH, condition: 'pm.partindexId = pi' }- { join: PGAPartReferenceBundle:PartSegment, alias: ps, conditionType: WITH, condition: 'pm.partsegmentId = ps' }- { join: PGAPartReferenceBundle:PartNiche, alias: pn, conditionType: WITH, condition: 'pm.partnicheId = pn' }where:and:- sh.invoicedate >= :currentstartdate- sh.invoicedate <= :currentenddategroupBy: intinvoicemonthbind_parameters:- currentstartdate- currentenddateproperties:id: ~intinvoicemonth: ~totals:total:extends: grand_totalper_page: truecolumns:salestotal:label: 'Total'grand_total:columns:salestotal:formatter: currencycolumns:invoicemonth:label: pga.reports.salessummary.monthfrontend_type: stringsalestotal:label: pga.reports.salessummary.salestotalfrontend_type: currencyfilters:columns:intinvoicemonth:label: pga.reports.salessummary.periodtype: choicedata_name: intinvoicemonthoptions:field_options:choices:1 : pga.reports.salessummarybyclient.january2 : pga.reports.salessummarybyclient.february3 : pga.reports.salessummarybyclient.march4 : pga.reports.salessummarybyclient.april5 : pga.reports.salessummarybyclient.may6 : pga.reports.salessummarybyclient.june7 : pga.reports.salessummarybyclient.july8 : pga.reports.salessummarybyclient.august9 : pga.reports.salessummarybyclient.september10: pga.reports.salessummarybyclient.october11: pga.reports.salessummarybyclient.november12: pga.reports.salessummarybyclient.decembersorters:default:intinvoicemonth: %oro_datagrid.extension.orm_sorter.class%::DIRECTION_ASCoptions:toolbarOptions:hide: trueThe SQL Equivalent Query of the above is :
1234567891011121314151617181920212223242526272829303132333435SELECTMONTH(p0_.invoicedate) AS intinvoicedat,MONTHNAME(p0_.invoicedate) AS intinvoicemonth,Sum(p0_.netpricetotal) AS salestotalFROMorocrm_account o5_INNER JOINoro_user o6_ ON o5_.user_owner_id = o6_.idINNER JOINoro_business_unit o7_ ON o6_.business_unit_owner_id = o7_.idAND (o7_.id IN (8)AND o7_.organization_id = 1)INNER JOINpga_saleshistory p0_ ON (p0_.clientnumber = o5_.idAND p0_.owner_id IN (26)AND p0_.organization_id = 1)INNER JOINpga_partfamily p3_ ON (p0_.partfamily = p3_.idAND p3_.owner_id = 1)INNER JOINpga_partindex p1_ ON (p3_.partindex_id = p1_.idAND p1_.owner_id = 1)INNER JOINpga_partsegment p2_ ON (p3_.partsegment_id = p2_.idAND p2_.owner_id = 1)INNER JOINpga_partniche p4_ ON (p3_.partniche_id = p4_.idAND p4_.owner_id = 1)WHEREp0_.invoicedate >= '2016-01-01'AND p0_.invoicedate <= '2016-05-16'ANDo5_.user_owner_id IN (26)AND o5_.organization_id = 1group by intinvoicedat;Now when I try to get Grand Total of the Salestotal Column , I am getting incorrect result .
Screenshot attached in given link below:Sql Query Result Screenshot
The Sum of all 5 columns mentioned above would be : 553685.05 But I am getting value as 1127193.40 which is incorrect as shown in link given below where in datagrid display of report.I am stuck in how to use the Totals & Grand Total for a particular column in Datagrid.
I gone through the link given below in OROCRM as follows to construct Datagrid:Please need help by suggestions / resources I can take a look at to get proper Total .
The forum ‘OroCRM’ is closed to new topics and replies.