nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Trying to write a formula using aggregates from main group and subgroup

rkraus10

i have a list report that groups on asset number with a Sum aggregate. Within each asset number, i have a subgroup separating Gas entries from E85 entries, both having a Sum aggregate. i want to calculate the Percent of E85 usage by using:Sum Aggregate of E85 subgroup / Sum Aggregate of Asset Number Group This formula gives me the error shown at the bottom of the page. A graphic of what I want to do is attached. Thanks in advance!!! BK + Column Binding "pct1" is incorrect:the parent query column bindings which include aggregations cannot be used in column bindings of subquery. data.engine.ColumnBindingReferToAggregationColumnBindingInParentQuery ( 1 time(s) )<div>detail : org.eclipse.birt.report.engine.api.EngineException: Column Binding "pct1" is incorrect:the parent query column bindings which include aggregations cannot be used in column bindings of subquery.
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1121)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1085)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:88)
at org.eclipse.birt.report.engine.executor.DataItemExecutor.execute(DataItemExecutor.java:75)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:42)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:45)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:33)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:434)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:442)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:442)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:442)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:442)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:442)
at org.eclipse.birt.report.engine.layout.html.HTMLListingBandLM.intializeHeaderContent(HTMLListingBandLM.java:96)
at org.eclipse.birt.report.engine.layout.html.HTMLListingBandLM.initialize(HTMLListingBandLM.java:48)
at org.eclipse.birt.report.engine.layout.html.HTMLTableBandLM.initialize(HTMLTableBandLM.java:42)
at org.eclipse.birt.report.engine.layout.html.HTMLLayoutManagerFactory.createLayoutManager(HTMLLayoutManagerFactory.java:50)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.createLayoutManager(HTMLReportLayoutEngine.java:142)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:67)
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27)
at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:76)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:68)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:101)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:151)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:877)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:938)
at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183)
at sun.reflect.GeneratedMethodAccessor57.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:616)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at org.mortbay.http.HttpConnection.service(HttpConnection.java:820)
at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986)
at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837)
at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245)
at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357)
at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)
Caused by: org.eclipse.birt.data.engine.core.DataException: Column Binding "pct1" is incorrect:the parent query column bindings which include aggregations cannot be used in column bindings of subquery.
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.findExpression(ExprManagerUtil.java:314)
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.validateInParentQuery(ExprManagerUtil.java:275)
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.checkColumnBindingExist(ExprManagerUtil.java:238)
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.checkColumnBindingExpression(ExprManagerUtil.java:193)
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.validateColumnBinding(ExprManagerUtil.java:73)
at org.eclipse.birt.data.engine.impl.ExprManager.validateColumnBinding(ExprManager.java:193)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.validateQuery(ServiceForQueryResults.java:932)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:158)
at org.eclipse.birt.data.engine.impl.ResultIterator.getSecondaryIterator(ResultIterator.java:837)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.doExecuteSubQuery(AbstractDataEngine.java:314)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:248)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1755)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:77)
... 71 more

</div>

Find more posts tagged with

Comments

kclark

You can do this by using global persistent variables. In the onRender() of sum(E85 gallons) and sum(totalGallons) you could do something like this<pre class="_prettyXprint">reportContext.setPersistentGlobalVariable("E85gallons", this.getValue());</pre>and<pre class="_prettyXprint">reportContext.setPersistentGlobalVariable("TotalGallons", this.getValue());</pre>Then later, where you are trying to average them you would use something like.<pre class="_prettyXprint">var e85 = parseFloat(reportContext.getPersistentGlobalVariable("E85gallons"));var totalGallons = parseFloat(reportContext.getPersistentGlobalVariable("TotalGallons"));var avg = e85 / totalGallons;this.text = avg;</pre>

rkraus10

Thanks. I figured out a work-around. Instead of grouping on the second variable, I used the filter option in the Sum aggregate. Since I only have one group in my report, I can do math with the aggregates without BIRT complaining or Maximo choking.

rkraus10

Huh...interesting that I can run the report without errors, but if I create and implement data range parameters, the sub-query error with the aggregate formula returns. Eliminating the date parameters stops the errors. So it looks like i will be investigating using your solution after all.thanks!

rkraus10

In an attempt to use the onRender method, I ran into some snags. I'll get to those after some history. Firstly, I had removed the second grouping from the report (group on fueldescription) since it wasn't necessary. Instead, I am using the Sum Aggregate with a Filter Condition to get the previously grouped fueldescription totals: E85gallonsFunction: SUMExpression: dataSetRow["Gallons"]Filter Condition: dataSetRow["fueldescription']=='E85'Aggregate On: Group -> AssetGroup GASgallonsFunction: SUMExpression: dataSetRow["Gallons"]Filter Condition: dataSetRow["fueldescription']=='GAS'Aggregate On: Group -> AssetGroup This way, I can do math on these aggregates with a Data Binding. E85PercentData Type: floatExpression: row["E85gallons"]/(row["E85gallons"]+row["GASgallons"]) This works fine if I am not using date range parameters to filter my report.Once I configure the date parameters, BIRT complains that it can't calculate formulas on aggregates within a subquery. Now to the onRender() part. When i implemented the onRender() solution (bearing in mind changes to names, etc.) I got strange calculations.NaN - when E85 = 0, (I would expect 0%)NaN - when E85 >0 and GAS>0 (should be a number)Erroneous Percentage - I get a percentage that is not equal to the perceived formula result. Which makes me suspect that the Global Variables are not refreshing with each rendering of the formula. This is all before I attempted the date paremeters.I am guessing that global variables may not be the way to go, since these variables have to change with every grouping. Thanks in advance for any other help BK

rkraus10

What I am finding out is that the persistent values are lagging one group. First grouping: E85 - null and displays "NaN"Second grouping: E85 - 186.77 and displays "NaN"Third grouping: E85 - null and displays 186.77

rkraus10

I found a way around this.Actually I found what was causing the problem.For some reason, when I dragged the date parameters into table header of the the report, it caused the error.Instead I used a data set expression and entered in the date parameter and all is well. I learned a lot. thanks!