Dynamic Table based off dynamic data set

Hey,
 
We are using OS BIRT 4.2.2.
 
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.
 
Currently I have the following.
 
Data Set - Before Open Script
this.queryText = 'SELECT * FROM ' + params["rpTable"].value
 
Data Set - After Open Script
importClass(org.eclipse.birt.report.engine.script.internal.instance.DataSetInstance); 
//Do not execute when dataset is previewed 
if( this instanceof DataSetInstance ){ 
cmd = this.getColumnMetaData( ); 
colCount = cmd.getColumnCount( );
 
if (params["rpCounter"].value != colCount) 
{ 
params["rpCounter"].value = colCount 
for ( i = 1; i <= colCount; i++ ) 
{ 
if (i < colCount) { 
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i) + ','; 
} else if (i == colCount){ 
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i); 
} 
} 
}
}
 
Table - On Prepare Script 
var columnList = params["rpColumn"].value. split(","); 
numColumns = params["rpCounter"].value; 
i=1; 
importPackage( Packages.org.eclipse.birt.report.model.api ); 
elementFactory = reportContext.getDesignHandle().getElementFactory() 
var mytable = reportContext.getDesignHandle().findElement("test"); 
var colbinds = mytable.getColumnBindings(); 
var cs1 = []; StructureFactory.createComputedColumn(); 
 
 
while (i <= numColumns){ 
cs1 = StructureFactory.createComputedColumn( ); 
cs1.setName(columnList[i-1]); 
cs1.setExpression( "dataSetRow[\"" + columnList[i-1] + "\"]" ); 
colbinds.addItem( cs1 ); 
 
//second parameter is before(-1) or after(1) 
mytable.insertColumn(i+1,-1); 
//get first detail row 
mydetail = mytable.getDetail( ).get( 0 ); 
//get first column and add detail data item 
var tcell = mydetail.getCells( ).get( i ); 
var mydata = elementFactory.newDataItem( null ); 
mydata.setResultSetColumn( columnList[i-1]); 
tcell.getContent( ).add( mydata ); 
//get header and add label 
var myheader = mytable.getHeader( ).get( 0 ); 
tcell = myheader.getCells( ).get( i ); 
var mylabel = elementFactory.newLabel( null ); 
mylabel.setText( columnList[i-1] );//$NON-NLS-1$ 
tcell.getContent( ).add( mylabel ); 
i++; 
} 

 
This will work if I do a drill down to the same report keeping the parameters. (Basically a refresh of the page)
 
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.
 
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?
 
Thanks for any Help!

Find more posts tagged with

Comments

Matthew L.

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>
 
The 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?

hallj05

Matthew,
 
That looks great!
 
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.
 
 
This is the part that I don't want to hard code
 var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" ); //OdaDataSourceHandle 
 odaDataSource.setProperty( "odaDriverClass", "org.eclipse.birt.report.data.oda.sampledb.Driver" ); //Add odaDriverClass public connection property 
 odaDataSource.setProperty( "odaURL", "jdbc:classicmodels:sampledb" ); //Add odaURL public connection property 
 odaDataSource.setProperty( "odaUser", "" ); //Add odaUser public connection property 
 odaDataSource.setProperty( "odaPassword", "" ); //Add odaPassword public connection property 
 reportDesignHandle.getDataSources( ).add( odaDataSource ); //SlotHandle - Add report item to the slot with the given element handle 

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>
To make it use the data source properties from the library file.
 
Any idea on how to make it use the data source from the library?
 
Thanks for the help!

Matthew L.

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.
 
I've attached an example zip that includes both an rptdesign and rptlibrary that uses this method for reference.
<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>

hallj05

This is great, thanks for the help.

Matthew L.

Glad I could help.
If other questions come up, just let me know.

hallj05

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.
 
if(libraryHandle){ //If libraryHandle exists 
 var dsrc = libraryHandle.findDataSource("JNDI_DataSource"); //Find Data Source 
 if(dsrc){ //If dataSourceHandle exists 
 
 var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" ); //OdaDataSourceHandle 
 odaDataSource.setProperty( "odaJndiName", dsrc.getProperty("jndiName") ); //Add odaJndiName public connection property 
 reportDesignHandle.getDataSources( ).add( odaDataSource ); //SlotHandle - Add report item to the slot with the given element handle 
 } 
}

itsavikchatterjee

@Matthew L. said:
Perhaps the example here might be helpful: http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987

The 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.