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

Dynamic Column generation in scripted dataset ,BIRT

Sri Sudarsana · 2014-11-05T01:35:54+00:00

Hi All, I have a requirement to create a table…

Sri Sudarsana

<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Hi All,</span></p>
<div style="margin:0px auto;color:rgb(51,51,51);font-family:'Open Sans', Helvetica, Arial, sans-serif;"><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">I have a requirement to create a table dynamically in BIRT. In this scenario, the columns of the scripted data set is dynamic.</span><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Is it possible to dynamically populate the scripted data set with dynamic column generation and to create a table based on that dynamically generated data set? </span>
<div style="margin:0px auto;"> </div>
<div style="margin:0px auto;">Please me know is it feasible to do this way.If so , please provide me an example code snippet or rptdesign file.</div>
<div style="margin:0px auto;"> </div>
<div style="margin:0px auto;">
<div style="margin:0px auto;">
<div style="margin:0px auto;">
<div style="margin:0px auto;"><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Thanks in advance. </span></div>
</div>
</div>
</div>
</div>
<p> </p>

Find more posts tagged with

Comments

dsoon

<p>Yes. you can use designer API to create the design file from scratch.</p>

Matthew L.

<p>This can be accomplished in a few different ways depending on your requirements.</p>
<p> </p>
<p>However as an example, I've attached an example that dynamically changes the query of a data set (Classic Models Data Set) to simulate your dynamic scripted data set.</p>
<p> </p>
<p>The dynamic table is build in the beforeFactory code.</p>
<p>First, I query the data set (Classic Models Data Set) to get the result set.</p>
<p>Then in the DataSetLogic function we start building the table by iterating through the data columns and dynamically adding columns, rows, and labels to a base table.</p>
<p> </p>
<p>Hopefully this example is helpful for you.</p>

Sri Sudarsana

<p>Hi Matthew,</p>
<p>Thanks for the reply and the example.<br><br>
I found that the example was using the JDBC data source.I saw few list of columns added to the output columns field of "Data Set to populate parameter list" dataset. Was it defined statically or is it added dynamically from the "before Factory" code?</p>
<p> </p>
<p>I wanted to know how the dynamic column generation in the scripted data set which is using scripted Data source works?</p>
<p> </p>
<p>Can you please help me with the scenario?</p>
<p> </p>
<p> </p>
<p>Hi dsoon,</p>
<p>Can you please give me an example ?</p>

Matthew L.

<p>Attached is a new design that adds columns to an existing Scripted Data Set and creates a dynamic table to display the output.</p>
<p>As the columns are created via the API, they will not be visible to the GUI builder.</p>
<p> </p>
<p>However an example of the logic for adding columns to an existing Scripted Data Set is the following:</p>
<p> </p>
<div>
<div>dataSetHandle = reportContext.getDesignHandle().findDataSet(<span style="color:#0000ff;">"Data Set"</span>); <span style="color:#008000;">//Find Existing Data Set</span></div>
<div>computedSet = dataSetHandle.getPropertyHandle(ScriptDataSetHandle.RESULT_SET_PROP); <span style="color:#008000;">//Get PropertyHandle</span></div>
<div> </div>
<div><span style="color:#008000;">//Create a Column</span></div>
<div>resultColumn = StructureFactory.createResultSetColumn(); <span style="color:#008000;">//ResultSetColumn</span></div>
<div>resultColumn.setPosition(1); <span style="color:#008000;">//Set column position (Starting with 1)</span></div>
<div>resultColumn.setColumnName(<span style="color:#0000ff;">"ColumnName"</span>); <span style="color:#008000;">//Set the column name</span></div>
<div>resultColumn.setDataType(<span style="color:#0000ff;">"String"</span>); <span style="color:#008000;">//Set the data type of this column: Integer, String, Date Time, Decimal, Float, Boolean, Date, Time, Blob, Java Object </span></div>
<div>computedSet.addItem(resultColumn); <span style="color:#008000;">//Add column to Data set</span></div>
</div>
<p> </p>
<p> </p>

Sri Sudarsana

<p>Hi Matthew,</p>
<p>Thanks a lot for the reply.I would like to use my own array which i fetched from other datasets via "onfetch" event in the scripted dataset and give as input to the column name <strong>method </strong><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">resultColumn.setColumnName(</span><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">); </span><span style="font-family:'Source Sans Pro', sans-serif;color:rgb(0,128,0);">//Set the column name</span></p>
<p> </p>
<p><span>i see all the code is being in the before factory event and the Onfetch gets called after the before factory which will not work. </span></p>
<p><span>I tried to implement that via "<strong>describe</strong>" event in the scripted dataset.</span></p>
<p><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/20417-scripted-data-source-describe-event/'>http://developer.actuate.com/community/forum/index.php?/topic/20417-scripted-data-source-describe-event/</a></p>
<p> </p>
<p><span>i could not find an api to add the column name to the dataset. </span></p>
<p> </p>
<p><span>Can you please provide me an example for this?.</span></p>

Matthew L.

<p>Hello Sri,</p>
<p> </p>
<p>Because of the order of operations, I'm not sure if using the "describe" event is possible for what you are wanting to accomplish, as it seems that you have determined this due to the onFetch is called after the beforeFactory method.</p>
<p> </p>
<p>Dynamically creating elements based on the result set of a data set can get complex.</p>
<p>In the attached example, I tried to simplify the design to demonstrate how to accomplish this.</p>
<p>In the new example there is a function you can call to query a data set when needed.</p>
<p>This example pulls column names from a Scripted Data Set, then provides examples to assign the data both hard coded and dynamically from another data set to the column either based on the Column Name or Column Position.</p>
<p> </p>
<p>The code in the initialize method is used to query a data set. The logic in the ResultLogic function pulls data from the first column, assigns it to an array, then returns it back to the original call.</p>
<p> </p>
<p>The code in the beforeFactory creates a New Scripted Data Source and New Scripted Data Set, then calls the code in the initialize method to query one of the data sets for the column names and creates the columns.</p>
<p>Then I set the Open and Fetch methods of the New Scripted Data Set in code with a few examples on how to assign the data, including how to pull data from another data set.</p>
<p> </p>
<p>The table is built dynamically based on the number of columns in the New Scripted Data Set.</p>
<p> </p>
<p>Let me know if you have any questions about this new example.</p>
<p> </p>
<p> </p>

Sri Sudarsana

<p>Hi Matthew,</p>
<p>i tried to modify your example by replacing the jdbc datasource with my required datasource and tried running it. i am facing few issues in running it.</p>
<p> </p>
<p>i modified in intialize and before factory methos.Please let me know the way i understood is right?</p>

Matthew L.

<p>The example was only setup for a simple scenario.</p>
<p>What issues are you running into?</p>
<p>Or do you have any specific questions on how the example works?</p>
<p>Also what modifications did you make to the initialize and beforeFactory methods?</p>

Sri Sudarsana

<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Hi Matthew,</span></p>
<p>when i run the code as such i am getting an error as "Parameter label must be a string"</p>
<p> </p>
<p>Change i made for dataset is in before factory method</p>
<div>//Or query another data set</div>
<div>+ "if( columnNameArray.indexOf(\"temp\") > -1 ){"</div>
<div>+ "row[\"temp\"] = QueryDataSet(\"Data Set\")[4];}"</div>
<div>+ "if(columnNameArray[5]){row[columnNameArray[5]] = QueryDataSet(\"Data Set\")[5];}"</div>
<div>+ "if(columnNameArray[6]){row[columnNameArray[6]] = QueryDataSet(\"Data Set\")[6];}"</div>
<div>+ "i++;"</div>
<div>+ "return true;"</div>
<div>+ "}"</div>
<div>+ "else return false;");</div>
<p> </p>
<p>Even after changing the dataset iam getting the same error. I tried to use the capabilities of cross tab for my requirement which had positive results.</p>
<p> </p>
<p>So i am proceeding with the same.</p>
<p> </p>
<p>Thanks for the help.Please let me know where i did wrong in the modification</p>

Matthew L.

<p>My apologies for the late reply.</p>
<p>I'm not sure why your receiving the "Parameter label must be a string".</p>
<p>The code snippet you posted looks correct.</p>
<p>If possible, can you post your report example to look over?</p>
<p>Please remove any sensitive data source information before posting.</p>

Atul Arora

<p>Hi,</p>
<p> </p>
<p>I am using the same piece of code in data design scripted code. But getting the below error. </p>
<div> </div>
<div><strong>Any help would be really appreciated...</strong></div>
<p> </p>
<div>A BIRT exception occurred. See next exception for more information.</div>
<div>Wrapped java.lang.ClassCastException: <strong>com.actuate.birt.report.model.api.DataMartHandle cannot be cast to org.eclipse.birt.report.model.api.ReportDesignHandle</strong> (/datamart/data-sets/script-data-set[@id="3"]/method[@name="afterOpen"]#6)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(JSMethodRunner.java:147)</div>
<div> </div>
<div>at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(DtEScriptExecutor.java:90)</div>
<div> </div>
<div>at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleJS(DataSetScriptExecutor.java:256)</div>
<div> </div>
<div>at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleAfterOpen(DataSetScriptExecutor.java:160)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.DataSetRuntime.afterOpen(DataSetRuntime.java:603)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.QueryExecutor.dataSetAfterOpen(QueryExecutor.java:229)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor.executeOdiQuery(PreparedScriptDSQuery.java:224)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1237)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)</div>
<div> </div>
<div>at org.eclipse.birt.report.engine.api.impl.ExtractionResults.nextResultIterator(ExtractionResults.java:157)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:69)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:372)</div>
<div> </div>
<div>at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)</div>
<div> </div>
<div>Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information.</div>
<div>Wrapped java.lang.ClassCastException: com.actuate.birt.report.model.api.DataMartHandle cannot be cast to org.eclipse.birt.report.model.api.ReportDesignHandle (/datamart/data-sets/script-data-set[@id="3"]/method[@name="afterOpen"]#6)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.core.DataException.wrap(DataException.java:123)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evaluateJSAsExpr(ScriptEvalUtil.java:1003)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(JSMethodRunner.java:138)</div>
<div> </div>
<div>... 13 more</div>

Matthew L.

<p>It looks like there is a DataMartHandle vs ReportDesignHandle issue.</p>
<p>Can you post a data design example so that I can look it over?</p>

Atul Arora

<p>I am trying to create 3 data set output columns in afterOpen or beforeOpen methods of data set with the below code but getting the <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">ClassCastException: </span><em><strong>com.actuate.birt.report.model.api.DataMartHandle cannot be cast to org.eclipse.birt.report.model.api.ReportDesignHandle</strong><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </span></em></p>
<p> </p>
<p>code snippet:</p>
<p> </p>
<div>importPackage(Packages.org.eclipse.birt.report.model.api);</div>
<div>importPackage(Packages.org.eclipse.birt.report.model.api.elements);</div>
<div>importPackage(Packages.org.eclipse.birt.report.model.elements.interfaces);</div>
<div> </div>
<div>//dataDesignHandle = reportContext.getDesignHandle().getDesignHandle();</div>
<div>dataSetHandle = reportContext.getDesignHandle().findDataSet("Data Set");</div>
<div>computedSet = dataSetHandle.getPropertyHandle(ScriptDataSetHandle.RESULT_SET_PROP); </div>
<div> </div>
<div>ch = StructureFactory.createColumnHint();</div>
<div>ch.setProperty("columnName", "Month");</div>
<div> </div>
<div>columnHint = dataSetHandle.getPropertyHandle(ScriptDataSetHandle.COLUMN_HINTS_PROP);</div>
<div>columnHint.addItem(ch);</div>
<div> </div>
<div> </div>
<div>resultColumn = StructureFactory.createResultSetColumn(); </div>
<div>resultColumn.setPosition(1); </div>
<div>resultColumn.setColumnName("Month"); </div>
<div>resultColumn.setDataType("String"); </div>
<div>computedSet.addItem(resultColumn); </div>
<div> </div>
<div>//Create a Column</div>
<div>resultColumn = StructureFactory.createResultSetColumn(); //ResultSetColumn</div>
<div>resultColumn.setPosition(2); //Set column position (Starting with 1)</div>
<div>resultColumn.setColumnName("Product"); //Set the column name</div>
<div>resultColumn.setDataType("String"); //Set the data type of this column: Integer, String, Date Time, Decimal, Float, Boolean, Date, Time, Blob, Java Object </div>
<div>computedSet.addItem(resultColumn); //Add column to Data set</div>
<div> </div>
<div>//Create a Column</div>
<div>resultColumn = StructureFactory.createResultSetColumn(); //ResultSetColumn</div>
<div>resultColumn.setPosition(3); //Set column position (Starting with 1)</div>
<div>resultColumn.setColumnName("Amount"); //Set the column name</div>
<div>resultColumn.setDataType("Integer"); //Set the data type of this column: Integer, String, Date Time, Decimal, Float, Boolean, Date, Time, Blob, Java Object </div>
<div>computedSet.addItem(resultColumn); //Add column to Data set</div>

Matthew L.

<p>Looking over this in more depth, I am unsure if this method will work in a data design due to how data designs fall in the order of operations.</p>
<p> </p>
<p>However since you are trying to create the columns in the data set and not in the reports beforeFactory method, you could dynamically create columns in the data set using the describe method.</p>
<p>So for example, in your data set's describe method you would enter the following lines of code to create the 3 columns as in the example code posted.</p>
<pre class="_prettyXprint _lang-js">
this.addDataSetColumn("Month","STRING"); //JSDataSetImpl (String name, String type("INTEGER","DOUBLE","DECIMAL","DATE","STRING","ANY"))
this.addDataSetColumn("Product","STRING"); //JSDataSetImpl (String name, String type("INTEGER","DOUBLE","DECIMAL","DATE","STRING","ANY"))
this.addDataSetColumn("Amount","INTEGER"); //JSDataSetImpl (String name, String type("INTEGER","DOUBLE","DECIMAL","DATE","STRING","ANY"))
return true; //Return true
</pre>
<p>Attached is an example that demonstrates this alternative method.</p>

Atul Arora

<p>Thanks for your reply..</p>
<p>Could you pls help me with above code on how to set the locale specific column names. If my resource file is already linked with data design file.</p>

Matthew L.

<p>Localization is typically set in the rptdesign file. For column names in a table, you can set th localization of the label in the header row under the label's Property Editor -> Localization.</p>
<p> </p>
<p>Or are you referring to something else?</p>

Atul Arora

<p>In data sets we can set the display name of columns, i am referring to that requirement.</p>
<p>Basically, I want to create columns dynamically at runtime & want to set the display name of those columns as well (By mapping display key with some resource file property)</p>

tanvi_openText

@Matthew L. said:
This can be accomplished in a few different ways depending on your requirements.

However as an example, I've attached an example that dynamically changes the query of a data set (Classic Models Data Set) to simulate your dynamic scripted data set.

The dynamic table is build in the beforeFactory code.

First, I query the data set (Classic Models Data Set) to get the result set.

Then in the DataSetLogic function we start building the table by iterating through the data columns and dynamically adding columns, rows, and labels to a base table.

Hopefully this example is helpful for you.

Hi Matthew,

I also want to do the same thing. Will it be possible for you to attach an example again? I am not able to see any attached example here.

Thank you in advance,
Tanvi.

Sweet33bdx

Hi,
I have the same pb than tanvi : I can't get any attached samples
Too bad...it seems so fine !!!

Thanks in advance,
Laurent

hunters1094

Did anyone solve this problem? Please send me the example.
Thank you