Home
Analytics
Calculate Cumulative
princeabd
Hi Jason,
I want to generate a report with cumulative.
I am creating the report using Cross tab.
The report is like,
jan || Feb || Mar
1 || 2 || 3
1 is the value of Jan and 2 is the value of Feb and 3 is the value of Mar.
for Jan, the value is same.
For Feb, The report should calculate jan+ Fab
i.e
Jan || Feb || Mar
1||3||6
Plz help me on this.
Find more posts tagged with
Comments
mwilliams
Since RunningSum is not an option in a crosstab, you'll probably need to create this functionality yourself using some script. You could store the current value of the measure in a persistentGlobalVariable named after the row dimension so you can keep different row totals separate. Then, you'd delete the measure binding and create a new binding or use a dynamic text to compute your own display value. Let me know if you need more explanation.
princeabd
Hi William,<br />
<br />
Thanks for your reply.<br />
<br />
I need some clarification. can you send a snap shot and also the script. Because we don't the script.<br />
If you attach any rptfile, sometimes some files not supported for me. i can't open.<br />
So that i am asking snopshots.<br />
<br />
Plz help me on this,<br />
<br />
Regards,<br />
Jeyinul.<br />
<br />
<br />
<br />
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80948" data-time="1312304367" data-date="02 August 2011 - 09:59 AM"><p>
Since RunningSum is not an option in a crosstab, you'll probably need to create this functionality yourself using some script. You could store the current value of the measure in a persistentGlobalVariable named after the row dimension so you can keep different row totals separate. Then, you'd delete the measure binding and create a new binding or use a dynamic text to compute your own display value. Let me know if you need more explanation.<br /></p></blockquote>
Hans_vd
Hi Jeyinul,
You can add a computed column with function RUNNINGSUM (or RUNNINGCOUNT, depends on what you need) to your dataset and then add that computed column as a summary field in the cube.
That should do it.
Regards
Hans
princeabd
Hi Hans,
Thanks for your reply,
I am trying to generate the cumulative report using RUNNING SUM.
But i want the output Project wise.
Now the out is coming like,
PROJECTNAME || Month || value || RUNNING SUM
SIT || Jan || 4 || 4
UAT || Feb || 5 || 9
SIT || Feb || 5 || 14
UAT || Mar || 10 || 24
But i want the output based on the projectwise.
In crosstab i used project as one group and month as anotherone group
But Cumulative is not right.
I want the output like,
SIT || Jan || 4 || 4
SIT || Feb || 5 || 9
UAT || Feb || 5 || 5
UAT || Mar || 10 || 15
The output should come based on project also.
Plz help me on this.
Regards,
Jeyinul.
Hans_vd
Hi Jeyinul,
In that case it cannot be done as I suggested.
What database do you have? If it supports analytical functions you could do it in the query.
princeabd
HI Hans,
I am using the below code,
<VALUE-OF>if (reportContext.getPersistentGlobalVariable("runningbalance") == null){
if (row["amount"] == null){
temp = 0;
}
else{
temp = row["amount"];
reportContext.setPersistentGlobalVariable("runningbalance", temp.toString());
}
}
else{
temp = parseInt(reportContext.getPersistentGlobalVariable("runningbalance")) + row["amount"]
reportContext.setPersistentGlobalVariable("runningbalance", temp.toString());
}
temp;</VALUE-OF>
This code is working for running sum.
I have one doubt,
My cross tab is like,
Project Name || Revenue || Jan || Feb || Mar || Apr
A ||||||||||| J1 ||||||| 0 || 1 || 3 || 0
A ||||||||||| J2 ||||||| 10 || 11 || 3 || 0
B ||||||||||| J1 ||||||| 0 || 1 || 3 || 0
B ||||||||||| J2 ||||||| 10 || 11 || 3 || 0
From the above table, The revenue will be changing always,
From the script, the output is correct for 2 revenues
I want the output like, running sum
A ||||||||||| J1 ||||||| 0 || 1 || 4 || 4
A ||||||||||| J2 ||||||| 10 || 21 || 24 || 24
B ||||||||||| J1 ||||||| 0 || 1 || 3 || 0
B ||||||||||| J2 ||||||| 10 || 21 || 24 || 24
Can you please help me on this.
Thanks,
Jeyinul.
mwilliams
To get the runningSum to work for each row of the crosstab, you'll need to have a different PGV for each row. To do this, you'll assign the name of the PGV with the dimension name so that it's dynamic. If you'd like an example, let me know your BIRT version.
princeabd
Plz send me an example.
My version is,
BIRT Designer Version 2.5.0.v20090603 Build <2.5.0.v20090617-0630.
Regards,
Jeyinul.
mwilliams
Take a look at this report:
princeabd
Hi williams,
Thanks for the reply.
I am getting the same output.
The output is not right.
Please find the attachment.
Please help me on this.
Regards,
Jeyinul.
princeabd
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="81197" data-time="1312576150" data-date="05 August 2011 - 01:29 PM"><p>
Take a look at this report:<br /></p></blockquote>
<br />
Hi williams,<br />
<br />
<br />
From your report i am getting the following error.<br />
<br />
There are errors evaluating script "temp = reportContext.getPersistentGlobalVariable(data["CUSTOMERNUMBER"]);<br />
amount = data["AMOUNT_Group/CUSTOMERNUMBER_Group1/year"];<br />
if (temp == null){<br />
reportContext.setPersistentGlobalVariable(data["CUSTOMERNUMBER"],amount.toString());<br />
amount;<br />
}<br />
else{<br />
amount = parseFloat(temp) + amount;<br />
reportContext.setPersistentGlobalVariable(data["CUSTOMERNUMBER"],amount.toString());<br />
amount;<br />
}":<br />
TypeError: Cannot call method "toString" of null (<inline>#4) <br />
birt.core.JavascriptCommonError ( 13 time(s) )<br />
detail : org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "temp = reportContext.getPersistentGlobalVariable(data["CUSTOMERNUMBER"]);<br />
amount = data["AMOUNT_Group/CUSTOMERNUMBER_Group1/year"];<br />
if (temp == null){<br />
reportContext.setPersistentGlobalVariable(data["CUSTOMERNUMBER"],amount.toString());<br />
amount;<br />
}<br />
else{<br />
amount = parseFloat(temp) + amount;<br />
reportContext.setPersistentGlobalVariable(data["CUSTOMERNUMBER"],amount.toString());<br />
amount;<br />
}":<br />
TypeError: Cannot call method "toString" of null (<inline>#4)<br />
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1166)<br />
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1118)<br />
at org.eclipse.birt.report.engine.executor.ReportItemExecutor.evaluate(ReportItemExecutor.java:286)<br />
at org.eclipse.birt.report.engine.executor.TextItemExecutor.executeHtmlText(TextItemExecutor.java:114)<br />
at org.eclipse.birt.report.engine.executor.TextItemExecutor.execute(TextItemExecutor.java:70)<br />
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)<br />
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)<br />
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:64)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:135)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:106)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:155)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:135)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:69)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLTableBandLM.layoutChildren(HTMLTableBandLM.java:67)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:135)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:69)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLGroupLM.layoutChildren(HTMLGroupLM.java:117)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:135)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:69)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:112)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:135)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:69)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90)<br />
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)<br />
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:167)<br />
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:75)<br />
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:882)<br />
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)<br />
at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131)<br />
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)<br />
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)<br />
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)<br />
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183)<br />
at sun.reflect.GeneratedMethodAccessor28.invoke(Unknown Source)<br />
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)<br />
at java.lang.reflect.Method.invoke(Unknown Source)<br />
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)<br />
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)<br />
at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source)<br />
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)<br />
at java.lang.reflect.Method.invoke(Unknown Source)<br />
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)<br />
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)<br />
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)<br />
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)<br />
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)<br />
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)<br />
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)<br />
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)<br />
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)<br />
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)<br />
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)<br />
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)<br />
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)<br />
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)<br />
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)<br />
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90)<br />
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111)<br />
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59)<br />
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)<br />
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:318)<br />
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)<br />
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:380)<br />
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)<br />
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)<br />
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)<br />
at org.mortbay.jetty.Server.handle(Server.java:324)<br />
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:535)<br />
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:880)<br />
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:748)<br />
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:213)<br />
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)<br />
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)<br />
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:520)<br />
Caused by: org.eclipse.birt.core.exception.CoreException: There are errors evaluating script "temp = reportContext.getPersistentGlobalVariable(data["CUSTOMERNUMBER"]);<br />
amount = data["AMOUNT_Group/CUSTOMERNUMBER_Group1/year"];<br />
if (temp == null){<br />
reportContext.setPersistentGlobalVariable(data["CUSTOMERNUMBER"],amount.toString());<br />
amount;<br />
}<br />
else{<br />
amount = parseFloat(temp) + amount;<br />
reportContext.setPersistentGlobalVariable(data["CUSTOMERNUMBER"],amount.toString());<br />
amount;<br />
}":<br />
TypeError: Cannot call method "toString" of null (<inline>#4)<br />
at org.eclipse.birt.core.script.ScriptContext.eval(ScriptContext.java:341)<br />
at org.eclipse.birt.core.script.ScriptContext.eval(ScriptContext.java:369)<br />
at org.eclipse.birt.report.engine.executor.ExecutionContext.evaluate(ExecutionContext.java:718)<br />
at org.eclipse.birt.report.engine.executor.ExecutionContext.evaluate(ExecutionContext.java:699)<br />
at org.eclipse.birt.report.engine.executor.ReportItemExecutor.evaluate(ReportItemExecutor.java:282)<br />
... 77 more<br />
Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot call method "toString" of null (<inline>#4)<br />
at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3557)<br />
at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3535)<br />
at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3563)<br />
at org.mozilla.javascript.ScriptRuntime.typeError2(ScriptRuntime.java:3582)<br />
at org.mozilla.javascript.ScriptRuntime.undefCallError(ScriptRuntime.java:3601)<br />
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThis(ScriptRuntime.java:2160)<br />
at org.mozilla.javascript.optimizer.OptRuntime.callProp0(OptRuntime.java:117)<br />
at org.mozilla.javascript.gen.c1831._c0(<inline>:4)<br />
at org.mozilla.javascript.gen.c1831.call(<inline>)<br />
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:401)<br />
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3003)<br />
at org.mozilla.javascript.gen.c1831.call(<inline>)<br />
at org.mozilla.javascript.gen.c1831.exec(<inline>)<br />
at org.eclipse.birt.core.script.ScriptContext.eval(ScriptContext.java:336)<br />
... 81 more
mwilliams
Hmm. I didn't see that error when I ran it, but all you should have to do for this is check the current measure for null before calling the .toString(). If it's null, make the "amount" variable 0.
princeabd
Thanks Williams,
Everything is fine.
Now i have added one more group in the row.
Please find the attachment for your reference.
Now please send me script for calculating Running sum.
The below code i am using for single group in row,
<VALUE-OF>if (reportContext.getPersistentGlobalVariable(data["PRODUCTLINE"]) == null){
if (data["QUANTITYINSTOCK_Group/PRODUCTLINE_Group1/PRODUCTSCALE"] == null){
temp = 0;
}
else{
temp = data["QUANTITYINSTOCK_Group/PRODUCTLINE_Group1/PRODUCTSCALE"];
reportContext.setPersistentGlobalVariable(data["PRODUCTLINE"], temp.toString());
}
}
else{
temp = parseInt(reportContext.getPersistentGlobalVariable(data["PRODUCTLINE"])) + data["QUANTITYINSTOCK_Group/PRODUCTLINE_Group1/PRODUCTSCALE"]
reportContext.setPersistentGlobalVariable(data["PRODUCTLINE"], temp.toString());
}
temp;</VALUE-OF>
Please help me on this.
Regards,
Jeyinul.
mwilliams
Not sure I'm understanding what you're trying to do exactly. Can you recreate what you're trying to do with the sample database. You can just edit the above report that I attached to add a second group like you're trying to do and reattach it. Thanks!
mwilliams
I ran the report I gave you in the web viewer and got the null error. Here is a revised version of the report that does not get the error.
princeabd
Thanks Williams,
Its working fine.
In my report, There are two inputs which are not mandatory. I will give any one input at a time.
BIRT doesn't prompt the input parameters while running. If input is mandatory then it displays.
But I don't want like that.
Plz help me on this
Regards,
Jeyinul.
mwilliams
Sorry for the delay. I was out of town and this got pushed back and lost. To make the parameter page show, just set the __parameterpage url parameter to true when you're calling the report.
