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; } } }

ranking the sum of 4 columns per row

onyog

I am trying to rank the sum of 4 fields say (field a + field b + field c+ field d)
I must only sum the fields that equal one and I am comparing 4 fields in one row from
one table to 4 fields in all rows of another table. I have been trying to
figure out how to do in Eclipse BIRT

Find more posts tagged with

Comments

mwilliams

I'm not sure I'm understanding, exactly. Can you explain with data?

onyog

Ok, I have a table say table 1 and the fields I am interested in
are system name system description and four
fields that are checkboxes. I need to sum all the checkboxes
that are yes, i.e. 1, and rank all the rows.
Additionally, I have to only select the yes checkbox
fields that match the yes checkbox fields in another
table say table 2. And I am only comparing all of
table 1 with one row of table 2 and I need to return
the first return that single row as the first row of the
Table if ranked sums.

Kind of confusing! I will send a sample visual.
Does that help for now until I send the attachment?

mwilliams

So, the values you're ranking are totals of 0-4? Or are there larger values associated with the check boxes. I think that's part of my confusion, here. The visual would be great!

onyog

I have to sum 4 attributes of one row from Table 2 that are checked
which are represented by a 1. That will define the
sum that has a ranking of 1. Then fromTable 1, I match those same attributes
that are checked with the same attributes in Table 2. I sum the attributes in
Table 1 and Rank them in descending order in relation to the top ranking
in Table 2. I can't join on the PK's for either tables because they are characters
not numbers ( I am not quite sure of this).

Table 2:
PK. System name. System Desc. Attribute 1. Attribute 2. Attribute 3. Attribute 4 Total. Rank
Initiative. Name D. Desc D. 0. 1. 1. 1. 3. 1

Table 1:
PK. System name System Desc. Attribute 1. Attribute 2. Attribute 3. Attribute 4. Total. Rank
System. Name1. Desc1. 0. 0. 1. 1. 2. 2
System Name2. Desc2 1. 0. 1 0. 1. 3
System. Name3. Desc3. 0. 1. 1. 1. 3. 1

onyog

I continuation from my previous reply, I have to order by
System name and return a table that has the control
row from Table 2 as the first row and the rest of
the rows will be the ranked rows of Table 1.

mwilliams

Alright. I understand, now. What is your BIRT version?

onyog

BIRT 3.7.1

mwilliams

Take a look at this report. Be sure to change the location of the flat files, in the flat file dataSource, to the location that you save the csv files. Look at the table2DS computed columns, then look at the table1DS computed columns to see how I used the PGV I created in table1DS. For the layout, I sized the columns the same, so I could use both tables and just deleted the header row on the second table. Hope this helps.

onyog

By the way, I don't think I properly thanked you for all your help!
I took your suggestions and I am trying to use the real source of
data which is a sql server. I selected the fields for Table2
and tried to generate computed columns similar to what you did and
I got the following error:

The following items have errors:

Table (id = 12):
+ Fail to compute value for computed column "Total".
A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "temp=new Array();temp[0]=row["Attribute1"];temp[1]=row["Attribute2"];
temp[2]=row["Attribute 3"];temp[3]=row["Attribute 4"];
reportContext.setPersistentGlobalVariable("Table2Key",temp);row["Attribute1"]+
row["Attribute2"]+row["Attribute3"]+row["Attribute4"]":
Invalid field name: Attribute1. (Element ID:12)
data.engine.CompCol.FailRetrieveValueComputedColumn ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Fail to compute value for computed column "Total".
A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "temp=new Array();temp[0]=row["Attribute1"];temp[1]=row["Attribute2"];
temp[2]=row["Attribute3"];temp[3]=row["Attribute4"];
reportContext.setPersistentGlobalVariable("Table2Key",temp);row["Attribute1"]+
row["Attribute2"]+row["Attribute3"]+row["Attribute4"]":
Invalid field name: Attribute1. (Element ID:12)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1214)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1193)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:180)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:929)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)
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.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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:727)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
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.service(ServletRegistration.java:61)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:317)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:939)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: org.eclipse.birt.data.engine.core.DataException: Fail to compute value for computed column "Total".
A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "temp=new Array();temp[0]=row["Attribute1"];temp[1]=row["Attribute2"];
temp[2]=row["Attribute3"];temp[3]=row["Attribute4"];
reportContext.setPersistentGlobalVariable("Table2Key",temp);row["Attribute1"]+
row["Attribute2"]+row["Attribute3"]+row["Attribute4"]":
Invalid field name: Attribute1.
at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:523)
at org.eclipse.birt.data.engine.impl.ComputedColumnHelper.process(ComputedColumnHelper.java:130)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.processFetchEvent(RowResultSet.java:160)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.doNext(RowResultSet.java:121)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:91)
at org.eclipse.birt.data.engine.executor.cache.ExpandableRowResultSet.next(ExpandableRowResultSet.java:63)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.populateData(SmartCacheHelper.java:316)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initInstance(SmartCacheHelper.java:285)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initOdaResult(SmartCacheHelper.java:154)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.getResultSetCache(SmartCacheHelper.java:79)
at org.eclipse.birt.data.engine.executor.cache.SmartCache.<init>(SmartCache.java:57)
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.populateOdiResultSet(PassUtil.java:99)
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.pass(PassUtil.java:62)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSetCacheInResultSetPopulator(PassManager.java:317)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateDataSet(PassManager.java:279)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.prepareDataSetResultSet(PassManager.java:98)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(PassManager.java:125)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(PassManager.java:74)
at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(ResultSetPopulator.java:198)
at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(CachedResultSet.java:97)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:1025)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:441)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1124)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:173)
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:98)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:267)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1905)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
... 59 more
Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "temp=new Array();temp[0]=row["Attribute1"];temp[1]=row["Attribute2"];
temp[2]=row["Attribute3"];temp[3]=row["Attribute4"];
reportContext.setPersistentGlobalVariable("Table2Key",temp);row["Attribute1"]+
row["Attribute2"]+row["Attribute3"]+row["Attribute4"]":
Invalid field name: Attribute1.
at org.eclipse.birt.data.engine.core.DataException.wrap(DataException.java:123)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:946)
at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:486)
... 88 more
Caused by: org.eclipse.birt.core.exception.CoreException: There are errors evaluating script "temp=new Array();temp[0]=row["Attribute1"];temp[1]=row["Attribute2"];
temp[2]=row["Attribute3"];temp[3]=row["Attribute4"];
reportContext.setPersistentGlobalVariable("Table2Key",temp);row["Attribute1"]+
row["Attribute2"]+row["Attribute3"]+row["Attribute4"]":
Invalid field name: Attribute1.
at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:295)
at org.eclipse.birt.core.script.ScriptContext.evaluate(ScriptContext.java:154)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:918)
... 89 more
Caused by: org.mozilla.javascript.EvaluatorException: Invalid field name: Medicare_Part_A.
at org.mozilla.javascript.DefaultErrorReporter.runtimeError(DefaultErrorReporter.java:109)
at org.mozilla.javascript.Context.reportRuntimeError(Context.java:938)
at org.mozilla.javascript.Context.reportRuntimeError(Context.java:994)
at org.eclipse.birt.data.engine.script.JSRowObject.get(JSRowObject.java:267)
at org.mozilla.javascript.ScriptableObject.getProperty(ScriptableObject.java:1617)
at org.mozilla.javascript.ScriptRuntime.getObjectElem(ScriptRuntime.java:1390)
at org.mozilla.javascript.ScriptRuntime.getObjectElem(ScriptRuntime.java:1372)
at org.mozilla.javascript.gen.c123438._c0(unnamed script:0)
at org.mozilla.javascript.gen.c123438.call(unnamed script)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.gen.c123438.call(unnamed script)
at org.mozilla.javascript.gen.c123438.exec(unnamed script)
at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:290)
... 91 more

mwilliams

The error is that you're using an invalid field name "Attribute1". If your fields are named differently, you need to use what your fields are named.

onyog

I changed the actual field name in the report to the
sample one I gave you an example of so you could
follow the error message. I pulled the fields from the SQL db
and I tried it again and got the same error...am I missing
something? I figured using BIRT was easier than
trying to do it in MYSQL because there are no
function called rank & I would have to do it in
a bunch of nested queries which was more confusing!

mwilliams

Can you attach your actual report that you're having issues with? Or send it to me in email, if you can't post it in here?

onyog

Ok i will send it to you within an hour.

onyog

let me know if you have any problems opening it...i attached the report

onyog

Just wondering if you had a chance to look at the file I sent Friday.
Thanks!

mwilliams

Taking a look at it, right now.

mwilliams

So, what is the exact error that you get with this report that you attached. The computed column in your pattern dataSet seems to be correct. The computed column in your other dataSet isn't complete though, if you're doing the same that I did in the example report. Though, I notice that you've not put an element in the report bound to this dataSet yet, so you might just not be there, yet. If you can post the error, exactly as it shows when your run this report, that'd be great.

mwilliams

Based on the error you sent to me, in email, the reason you're having an error is because you've got a '}' where you should have a ']', in the last line of your script.

row["Medicare_Part_C"}