Dynamic Table based off dynamic data set
<p>Hey,</p>
<p> </p>
<p>We are using OS BIRT 4.2.2.</p>
<p> </p>
<p>I'm trying to make a report where we display the full data set result in a table. The data set SQL will be different depending on a report parameter.</p>
<p> </p>
<p>Currently I have the following.</p>
<p> </p>
<p><strong>Data Set - Before Open Script</strong></p>
<p>this.queryText = 'SELECT * FROM ' + params["rpTable"].value</p>
<p> </p>
<p><strong>Data Set - After Open Script</strong></p>
<p>importClass(org.eclipse.birt.report.engine.script.internal.instance.DataSetInstance);<br>
//Do not execute when dataset is previewed<br>
if( this instanceof DataSetInstance ){<br><br>
cmd = this.getColumnMetaData( );<br>
colCount = cmd.getColumnCount( );</p>
<p> </p>
<p>if (params["rpCounter"].value != colCount)<br>
{<br>
params["rpCounter"].value = colCount<br><br>
for ( i = 1; i <= colCount; i++ )<br>
{<br><br>
if (i < colCount) {<br>
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i) + ',';<br>
} else if (i == colCount){<br>
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i);<br>
}<br>
}<br>
}</p>
<p>}</p>
<p> </p>
<p><strong>Table - On Prepare Script</strong><br><br>
var columnList = params["rpColumn"].value. split(",");<br><br>
numColumns = params["rpCounter"].value;<br>
i=1;<br>
importPackage( Packages.org.eclipse.birt.report.model.api );<br>
elementFactory = reportContext.getDesignHandle().getElementFactory()<br>
var mytable = reportContext.getDesignHandle().findElement("test");<br>
var colbinds = mytable.getColumnBindings();<br>
var cs1 = []; StructureFactory.createComputedColumn();<br>
<br>
<br>
while (i <= numColumns){<br>
cs1 = StructureFactory.createComputedColumn( );<br>
cs1.setName(columnList[i-1]);<br>
cs1.setExpression( "dataSetRow[\"" + columnList[i-1] + "\"]" );<br>
colbinds.addItem( cs1 );<br>
<br>
//second parameter is before(-1) or after(1)<br>
mytable.insertColumn(i+1,-1);<br>
//get first detail row<br>
mydetail = mytable.getDetail( ).get( 0 );<br>
//get first column and add detail data item<br>
var tcell = mydetail.getCells( ).get( i );<br>
var mydata = elementFactory.newDataItem( null );<br>
mydata.setResultSetColumn( columnList[i-1]);<br>
tcell.getContent( ).add( mydata );<br>
//get header and add label<br>
var myheader = mytable.getHeader( ).get( 0 );<br>
tcell = myheader.getCells( ).get( i );<br>
var mylabel = elementFactory.newLabel( null );<br>
mylabel.setText( columnList[i-1] );//$NON-NLS-1$<br>
tcell.getContent( ).add( mylabel );<br>
i++;<br>
}<br>
</p>
<p> </p>
<p>This will work if I do a drill down to the same report keeping the parameters. (Basically a refresh of the page)</p>
<p> </p>
<p>The reason that I think this works with the refresh is because the table script does not have the data set data (param rpColumn) so it doesn't populate the table.</p>
<p> </p>
<p>So is there a better way to do this, or maybe a way to get the table script to get the data set data without a refresh, or a way to make the report refresh with parameters after it loads the first time?</p>
<p> </p>
<p>Thanks for any Help!</p>
<p> </p>
<p>We are using OS BIRT 4.2.2.</p>
<p> </p>
<p>I'm trying to make a report where we display the full data set result in a table. The data set SQL will be different depending on a report parameter.</p>
<p> </p>
<p>Currently I have the following.</p>
<p> </p>
<p><strong>Data Set - Before Open Script</strong></p>
<p>this.queryText = 'SELECT * FROM ' + params["rpTable"].value</p>
<p> </p>
<p><strong>Data Set - After Open Script</strong></p>
<p>importClass(org.eclipse.birt.report.engine.script.internal.instance.DataSetInstance);<br>
//Do not execute when dataset is previewed<br>
if( this instanceof DataSetInstance ){<br><br>
cmd = this.getColumnMetaData( );<br>
colCount = cmd.getColumnCount( );</p>
<p> </p>
<p>if (params["rpCounter"].value != colCount)<br>
{<br>
params["rpCounter"].value = colCount<br><br>
for ( i = 1; i <= colCount; i++ )<br>
{<br><br>
if (i < colCount) {<br>
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i) + ',';<br>
} else if (i == colCount){<br>
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i);<br>
}<br>
}<br>
}</p>
<p>}</p>
<p> </p>
<p><strong>Table - On Prepare Script</strong><br><br>
var columnList = params["rpColumn"].value. split(",");<br><br>
numColumns = params["rpCounter"].value;<br>
i=1;<br>
importPackage( Packages.org.eclipse.birt.report.model.api );<br>
elementFactory = reportContext.getDesignHandle().getElementFactory()<br>
var mytable = reportContext.getDesignHandle().findElement("test");<br>
var colbinds = mytable.getColumnBindings();<br>
var cs1 = []; StructureFactory.createComputedColumn();<br>
<br>
<br>
while (i <= numColumns){<br>
cs1 = StructureFactory.createComputedColumn( );<br>
cs1.setName(columnList[i-1]);<br>
cs1.setExpression( "dataSetRow[\"" + columnList[i-1] + "\"]" );<br>
colbinds.addItem( cs1 );<br>
<br>
//second parameter is before(-1) or after(1)<br>
mytable.insertColumn(i+1,-1);<br>
//get first detail row<br>
mydetail = mytable.getDetail( ).get( 0 );<br>
//get first column and add detail data item<br>
var tcell = mydetail.getCells( ).get( i );<br>
var mydata = elementFactory.newDataItem( null );<br>
mydata.setResultSetColumn( columnList[i-1]);<br>
tcell.getContent( ).add( mydata );<br>
//get header and add label<br>
var myheader = mytable.getHeader( ).get( 0 );<br>
tcell = myheader.getCells( ).get( i );<br>
var mylabel = elementFactory.newLabel( null );<br>
mylabel.setText( columnList[i-1] );//$NON-NLS-1$<br>
tcell.getContent( ).add( mylabel );<br>
i++;<br>
}<br>
</p>
<p> </p>
<p>This will work if I do a drill down to the same report keeping the parameters. (Basically a refresh of the page)</p>
<p> </p>
<p>The reason that I think this works with the refresh is because the table script does not have the data set data (param rpColumn) so it doesn't populate the table.</p>
<p> </p>
<p>So is there a better way to do this, or maybe a way to get the table script to get the data set data without a refresh, or a way to make the report refresh with parameters after it loads the first time?</p>
<p> </p>
<p>Thanks for any Help!</p>
0
Comments
-
<p>Perhaps the example here might be helpful: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987'>http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987</a></p>
<p> </p>
<p>The example dynamically creates a table based on the query.</p>
<p>The number of columns are also dynamic based on which table you select from the parameter screen.</p>
<p>Hopefully this is similar to what you are trying to accomplish.</p>
<p> </p>
<p>If not, could you provide a sample of what you are trying to accomplish?</p>
Warning No formatter is installed for the format ipb0 -
<p>Matthew,</p>
<p> </p>
<p>That looks great!</p>
<p> </p>
<p>Although I am having some issues changing that to work in my environment when using our data source from the library. I have it working when I did a hard code of the data source.</p>
<p> </p>
<p> </p>
<p>This is the part that I don't want to hard code</p>
<p><span style="font-size:10px;"> var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" ); //OdaDataSourceHandle<br>
odaDataSource.setProperty( "odaDriverClass", "org.eclipse.birt.report.data.oda.sampledb.Driver" ); //Add odaDriverClass public connection property<br>
odaDataSource.setProperty( "odaURL", "jdbc:classicmodels:sampledb" ); //Add odaURL public connection property<br>
odaDataSource.setProperty( "odaUser", "" ); //Add odaUser public connection property<br>
odaDataSource.setProperty( "odaPassword", "" ); //Add odaPassword public connection property<br>
reportDesignHandle.getDataSources( ).add( odaDataSource ); //SlotHandle - Add report item to the slot with the given element handle</span><br>
</p>
<p>I've tried to use the idea from <a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/27967471/birt-access-content-of-dataset-from-beforefactory'>http://stackoverflow.com/questions/27967471/birt-access-content-of-dataset-from-beforefactory</a></p>
<p>To make it use the data source properties from the library file.</p>
<p> </p>
<p>Any idea on how to make it use the data source from the library?</p>
<p> </p>
<p>Thanks for the help!</p>
0 -
<p>Here is a code snippet that finds an rptlibrary, locates a referenced data source from within the rptlibrary, collects the driver class, url, user name, and password information to be used in a dynamically generated ODA Data Source.</p>
<p> </p>
<p>I've attached an example zip that includes both an rptdesign and rptlibrary that uses this method for reference.</p>
<pre class="_prettyXprint _lang-js">
importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);
var libraryHandle = reportContext.getDesignHandle().findLibrary("(Topic_37058)OS_BIRT_2.6.2_RunDataSetFromWithinLibrary.rptlibrary"); //Find rptlibrary file
if(libraryHandle){ //If libraryHandle exists
var dataSourceHandle = libraryHandle.findDataSource("Classic Models Data Source"); //Find Data Source
if(dataSourceHandle){ //If dataSourceHandle exists
var dataSourceDriverClass = dataSourceHandle.getProperty("odaDriverClass").toString(); //Get odaDriverClass public connection property
if(dataSourceDriverClass){dataSourceDriverClass = dataSourceDriverClass.toString();} //Get value if not null
var dataSourceURL = dataSourceHandle.getProperty("odaURL").toString(); //Get odaURL public connection property
if(dataSourceURL){dataSourceURL = dataSourceURL.toString();} //Get value if not null
var dataSourceUser = dataSourceHandle.getProperty("odaUser").toString(); //Get odaUser public connection property
if(dataSourceUser){dataSourceUser = dataSourceUser.toString();} //Get value if not null
var dataSourcePassword = dataSourceHandle.getProperty("odaPassword"); //Get odaPassword public connection property
if(dataSourcePassword){dataSourcePassword = dataSourcePassword.toString();} //Get value if not null
//New OdaDataSource based on properties from OdaDataSource in a rptlibrary
var odaDataSource = new OdaDataSourceDesign( "MyNewDataSource" ); //New OdaDataSourceDesign
odaDataSource.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc" ); //Set the data source extension id as defined by ODA driver
odaDataSource.addPublicProperty( "odaDriverClass", dataSourceDriverClass); //Add odaDriverClass public connection property
odaDataSource.addPublicProperty( "odaURL", dataSourceURL); //Add odaURL public connection property
odaDataSource.addPublicProperty( "odaUser", dataSourceUser); //Add odaUser public connection property
odaDataSource.addPublicProperty( "odaPassword", dataSourcePassword ); //Add odaPassword public connection property
}
}
</pre>
Warning No formatter is installed for the format ipb0 -
<p>Glad I could help.</p>
<p>If other questions come up, just let me know.</p>
Warning No formatter is installed for the format ipb0 -
<p>So now I need to get this to work with a JNDI dataSource. Does the below look like it should work. Setting the odaJndiName instead of the odaDriverClass/odaURL/odaUser/odaPassword.</p>
<p> </p>
<p>if(libraryHandle){ //If libraryHandle exists<br>
var dsrc = libraryHandle.findDataSource("JNDI_DataSource"); //Find Data Source<br>
if(dsrc){ //If dataSourceHandle exists<br>
<br>
var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" ); //OdaDataSourceHandle<br>
odaDataSource.setProperty( "odaJndiName", dsrc.getProperty("jndiName") ); //Add odaJndiName public connection property<br>
reportDesignHandle.getDataSources( ).add( odaDataSource ); //SlotHandle - Add report item to the slot with the given element handle<br>
}<br><br>
}</p>
0 -
@Matthew L. said:
Perhaps the example here might be helpful: http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987The example dynamically creates a table based on the query.
The number of columns are also dynamic based on which table you select from the parameter screen.
Hopefully this is similar to what you are trying to accomplish.
If not, could you provide a sample of what you are trying to accomplish?
Do you happen to have the rptdesign file. I am not able to see the attachment in the forum.
0
Categories
- All Categories
- 107 Developer Announcements
- 53 Articles
- 104 General Questions
- 141 IM Services
- 43 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 898 Cloud Fax and Notifications
- 76 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 117 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management