Adding rows/columns to table at runtime based on the results returned by dynamic dataset

Hi,
I am a newbie to BIRT reporting, and i have come across a hurdle while creating dynamic report (based on a dataset on dynamic query) based on the parameter list.
Problem definition:
I have a report which takes a report parameter (this is a dynamic list where user can select more that one value) called paramIssues.
Now i have successfully created the query based on this input, which has dynamically joined multiple tables based on the paramIssues.length.
The problem comes when i have to bind and display the selected values in query (variable in number but following a pattern) in a table, as i have to create the columns/rows at runtime for these select parameters and i am not able to find any javascript based code snippet for adding columns at runtime.
I was thinking if I could build the entire table from scratch from the dynamic dataset.

Please help.
Let me know if sample code is needed to solve the issue.
Thanks.
Ankit

Find more posts tagged with

Comments

Hi,
I have found a similar problem in http://www.birt-exchange.org/org/forum/index.php/topic/19855-how-to-create-table-with-variable-no-of-columns-using-deapi/
So trying to follow it.
Thanks,
Ankit

Hi Ankit,

If your columns are based on selections from a parameter list, you could probably create a layout that includes all the columns and drop the ones that you don't need based on the parameter.

hi Michael,<br />
that's a great solution but my problem is that i have a variable list of parameters (populated via a select query) and then for each parameter i have two new selection items.<br />
I had restricted the user to always select at least one value from the parameter list, but that is it, the user can select from one-to-n parameters.<br />
Quoting from the other post <br />
<blockquote class='ipsBlockquote' ><p>
I have a similar issue, only difference is that i have a dataset which generates columns via a dynamic query based on user input.<br />
Hence i will not be able to determine the columns in the beforeFactory() Method of the report, as till probably the DataSet.onFetched() [or afterOpen()].<br />
So how should i go about this, adding columns after DataSet.onFetched() [or afterOpen()] has been executed.<br /></p></blockquote>
Will this solve the problem?<br />
Regards,<br />
Ankit

Ankit,

If it's all parameter selection by the user, you should know the values of the parameters before the beforeFactory method, I think. So, you should be able to use the parameter values to know how many columns you need in your table and dynamcially create the table there.

Hi Michael,

Let me try that and i'll report back how did it go..

Regards

Hi,
So finally my problem boils down to this.
By the time i call beforeFactory() method to add columns based on the number of parameters passed by the user i don't have a datasetrow element to bind it with, hence it throws an error, there itself.

If i do not call setExpression(<<with appropriate dataSetRow[]>>) on computedColumn while adding it to the table. I should add this call setExpression() as soon as my dataset has been opened (maybe in afterOpen() ). But i was not able to try this as i was not able to figure how will i get the computedColumn from the table while in DataSet.afterOpen()

So is the second solution on the right track? and if it is can i please know how to go about calling computedColumn in afterOpen()

I am also attaching my report code to this post. Please also have a look .
Reference (Steps while using this report)
1) I am asking user to input a list of issue_names which come via a dataset
2) After the input I try to create dynamic columns in beforeFactory() and the query before second dataset is opened

Edit: Deleted the attachment, will post fresh one with sample database.

Ankit,

If you add the data element in the beforeFactory and set the expression to row["computedColumnName"], do you get an error? If you know the computed column will exist, it should be there by the time the expression for the data element is processed, I would think. Is the user choosing the column names they want to see? If so, the expressions should just be able to be set with these.

Does the example you attached work on your data or on the sample database that I can run?

Hi Michael,

I will try to incorporate the your suggestion and will post example with sample database example.
Trying to formulate an example right now.

regards.

Hi Michael,
After replacing to row["computedColumnName"] i got the below error, while i was accessing the first column.
I am still working on the sample database example which you can use to detect the problem. Will post it here as soon as i am able to create that.

- Binding "p1_issueName" has dependency cycle.

data.engine.ColumnBindingCycle ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Binding "p1_issueName" has dependency cycle.
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1215)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1179)
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:90)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:170)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:75)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:920)
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.GeneratedMethodAccessor19.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
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.GeneratedMethodAccessor13.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
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.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:820)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:318)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:380)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
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:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:535)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:880)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:748)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:219)
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:520)
Caused by: org.eclipse.birt.data.engine.core.DataException: Binding "p1_issueName" has dependency cycle.
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.checkDependencyCycle(ExprManagerUtil.java:153)
at org.eclipse.birt.data.engine.impl.ExprManagerUtil.validateColumnBinding(ExprManagerUtil.java:76)
at org.eclipse.birt.data.engine.impl.ExprManager.validateColumnBinding(ExprManager.java:195)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.validateQuery(ServiceForQueryResults.java:929)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:156)
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:265)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1875)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
... 57 more

Hi Michael,<br />
<br />
I am not following the approach of adding columns in the table at runtime . Rather, as you advised in the <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/20720-adding-rowscolumns-to-table-at-runtime-based-on-the-results-returned-by-dynamic-dataset/page__view__findpost__p__69424'>your post above</a> . I am going to remove / hide the columns which were not passed via the selection parameters which is a report Parameter (dropdown where multiple values can be selected).<br />
<br />
I have tried to create the following page.<br />

Ankit,<br />
<br />
Try just this code in the table's onRender event. No script on any individual cell. You could probably clean the code up a little. I just threw it together. It should work though. Worked with my limited testing in a second table in the same report.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
cCount = this.getColumnCount();
i=0;
j=0;
isChosen = "";
paramVal = params["paramSelectList"].value;
paramLength = params["paramSelectList"].length;
while(i < cCount){
while(j<paramLength){
if (this.getRowData().getColumnName(i) == paramVal[j].toUpperCase()){
isChosen = "yes";
}
else{
if (isChosen == "yes"){
isChosen = isChosen;
}
else{
isChosen = "no";
}
}
j++;
}
if (isChosen == "yes"){
this.getColumn(i).getStyle().display = "block";
}
else{
this.getColumn(i).getStyle().display = "none";
}
j=0;
i++;
isChosen = "";
}
</pre>

Hi Michael,<br />
I tried the solution posted above, in the following report :

Ah, I see it does not work in PDF. It works in other outputs, but not in PDF.

Hi Michael,

I was trying to preview this in the Birt Web Viewer to see this code, and it did not work in that. So I believe along with PDF this will not work in html too.
Please let me know if I am wrong.
Thanks for all the help.

Regards.
Ankit

<blockquote class='ipsBlockquote' data-author="'ankitK'" data-cid="69824" data-time="1288256585" data-date="28 October 2010 - 02:03 AM"><p>
Hi Michael,<br />
<br />
I was trying to preview this in the Birt Web Viewer to see this code, and it did not work in that. So I believe along with PDF this will not work in html too.<br />
Please let me know if I am wrong.<br />
Thanks for all the help.<br />
<br />
Regards.<br />
Ankit<br /></p></blockquote>
<br />
I'm currenty working on something rather similar, I'll have a look when I get home this evening and will post back if I find a suitable solution.<br />
<br />
Rgds,<br />
Cedric

Ankit,

It worked in the designer in the web viewer and preview for me. Just not in PDF. I did not check in the deployed viewer.

Hi Michael,<br />
I tried using the preview mode of eclipse and birt web viewer . But this columns were not hidden.<br />
<br />
Although, as i had put in log statements to look at column visibility settings after the "hiding code" in <strong class='bbc'>onRender</strong> method of table. These log statements output correct display property (i.e the columns that should be displayed have property as 'block' and the one's which should not be displayed have value as 'none'). <br />
<br />
I was reading about the property "visibleFormat" will this property affect if the column is displayed or not.<br />
<br />
Below are the specifications for my machine.<br />
Viewer Version : 2.6.1<br />
Engine Version: 2.6.1<br />
JRE version: 1.6.0_21<br />
<br />
Please let me know if i should use some other configuration. <br />
<br />
One request, can you please your working rptdesign file. I might have missed some minute detail.<br />
<br />
Thanks,<br />
Ankit

<blockquote class='ipsBlockquote' data-author="'cypherdj'" data-cid="69828" data-time="1288263968" data-date="28 October 2010 - 04:06 AM"><p>
I'm currenty working on something rather similar, I'll have a look when I get home this evening and will post back if I find a suitable solution.<br />
<br />
Rgds,<br />
Cedric<br /></p></blockquote>
<br />
Thanks for your help cedric<br />
Regards,<br />
Ankit

Here is mine. It works for me in 2.6.1 on all but PDF, which defeats the purpose for you since you're going to use PDF. Using the visibility in the property editor may be the way to go if it works for PDF. Not sure why this approach doesn't work in PDF, but does in the others. I have not tried this in the deployed viewer.

Hi Michael,<br />
<br />
I tried the same report on the fresh installation of eclipse helios-sr1 (Preview) on my windows machine and <strong class='bbc'>it worked fine</strong>. But, somehow the same example was not working on the eclipse europa (Preview) on my linux machine.<br />
<br />
So anyhow, I am sorry for wasting your time unnecessarily and thanks a ton for all the help that you have provided.<br />
<br />
Regards,<br />
Ankit

Ankit,

I did not try this in Europa. Sorry, I thought you were using a newer version.

What is the version that you have deployed that you're using to run reports?

Hi Michael,

I am using Birt runtime 2.6.1 for deployed Web Viewer.
Till now I have not tried the same procedure on my Fedora machine, where i had the problem.
Please can you let me know if you are using an Windows machine to test or an Linux.

Thanks,
Ankit

I'm using Windows. I have most versions from 2.0 + to test on.

Hi Michael ,

Somebody has to confirm this, but for me the logic of hiding columns is not working on fedora machine but is working on the Windows machine. I tested this on the preview mode in eclipse (both galileo and helios-SR1) and hiding logic did not work.

Then I deployed the birt web viewer (extracted from the birt runtime) and integrated into an existing web-application. Now the reports are displayed fine but i was not able to get the logic to hide columns working.

So test cases
1) IDE : Eclipse galileo
OS : Fedora 13 (32 bit)
Birt version : 2.6.1

2) IDE: Eclipse helios SR1
OS : Fedora 13 (32 bit)
Birt version : 2.6.1

3) IDE: Eclipse helios SR1
OS : Windows 7
Birt version : 2.6.1

So for cases 1 and 2 the code did not work, but for the 3rd case, it worked.

So can you or someone verify this if you have setup available. I am sorry to bug all of you, but this is confusing.
Please let me know if more sample reports are needed, also if more details on the environment are needed.

Regards,
Ankit

Ankit,

Unfortunately, I don't have Fedora to be able to test on. You might log a bug for this saying that the logic works in Windows, but not on Fedora.

http://www.birt-exchange.org/org/resources/bug-reporting/

Hi Michael,<br />
Sorry for responding late, was caught up.<br />
bug logged <a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=330307'>bug link</a>

<p>hi williams,</p>
<p> </p>
<p>your example works perfectly. it is disbaled based on the selection of items. can we also do the same thing for exporting to CSV.</p>
<p> </p>
<p>i need an option to set the allowexport or visibility option , so that it should not appear in export data -->csv also. is there a way we can do this in BIRT4.4</p>