Creating Column Dynamically

Ronak · December 16, 2011

Hi Friends,

I have a database in which the table contains different columns, some contain 10, other 20 upto 200.

So I want to create columns dynamically instead of taking 200 columns in the table and hiding the columns.
Can anyone tell me hoe to create the columns dynamically?
or
Is it possible to have columns dynamically in BIRT.

I am using BIRT 3.7

Thanks
Ronak

mwilliams · December 16, 2011

Take a look at this devShare post:

http://www.birt-exchange.org/org/devshare/designing-birt-reports/1099-add-column-to-table-dynamically/

Ronak · December 17, 2011

Hi Michael,

Thanks for the reply. I am sending you my report...please go through it.
In this report I created a table with 20columns, but I don't want such thing. In the parameter I am passing the number of columns to be displayed.

So it should be dynamic...instead of like this.
Please look the report and in case if you can..please make a change or suggest me what to be done in the report.

I am using BIRT 3.7

Thanks
Ronak

mwilliams · December 19, 2011

Ok. Here's an example that shows how to add a specific number of columns, dynamically, based on a selected value in a parameter. Hope this helps.

Ronak · December 20, 2011

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="92192" data-time="1324328213" data-date="19 December 2011 - 01:56 PM">
Ok. Here's an example that shows how to add a specific number of columns, dynamically, based on a selected value in a parameter. Hope this helps. </blockquote>
Hi Michael, 
 
Thanks for the report, but in this report you had made the column names fixed in the scripting, in my case I am passing two different parameters. 1)parameter name and 2)number of columns. 
 
Now when I pass different parameter, it will have different column names. for eg if I pass TITLE as parameter in parameter name then it will have columns like (Name, ID, Key) and if I pass USER in the parameter name(Report Parameter) then the column will be like (FirstName, LastName), so in this case what to be done for dynamic columns. 
 
As now in this if I pass parameter named TITLE and other parameter(number of columns) 2 then it should display the two columns report with Name and ID. 
 
Hope I made you understand, what actually I want. This is the case, so is it possible such kind of dynamism in BIRT 3.7. 
 
If possible then please generate an example for me. 
 
I am attaching one small report, so that you can get better idea. Its not using sample database

Thanks 
Ronak

mwilliams · December 20, 2011

You could just create the table from scratch, rather than just adding columns to an existing table. I had one optional set of column bindings. You'd have more sets, but it'd be similar. You would just use your second parameter to decide which list of column bindings you're going to use to add to the table. Let me know if you have questions.

Tubal · December 20, 2011

I don't mean to hijack this thread, but is there a way to create columns based on dataset data?

i.e. the column quantity, names, and widths would come from a data source.

I've tried doing this with crosstabs, and it's basically what I want, but I can't modify the column width in a crosstab.

My next try was creating them dynamically, but I'm not sure how to get the data from the data source in the beforeFactory event.

Basically what I'm trying to do is dynamically create a position log. For example, if you have a delivery driver, your data columns might be 'Date', 'Customer', 'Arrived', 'Left', 'Cases', etc... and you would want the 'Date' column to be smaller than the 'Customer' column.

Then you might have a warehouse picker, and your columns might be 'Date', 'Order #', 'Picked', 'Backordered', etc.... with a different set of column widths.

When the manager sets up the position, they enter the data they want collected in the log, and they can also enter a % of the table width they want that column to use. Then we pull that data out of the db and generate the log.

mwilliams · December 20, 2011

Have you tried connecting to your data through script in the beforeFactory? This might be the only way to do it. Bringing your table description data in through this connection. I don't know that it can be done any other way. If I figure something else out, I'll let you know.

Tubal · December 20, 2011

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="92229" data-time="1324400153" data-date="20 December 2011 - 09:55 AM">
Have you tried connecting to your data through script in the beforeFactory? This might be the only way to do it. Bringing your table description data in through this connection. I don't know that it can be done any other way. If I figure something else out, I'll let you know. </blockquote>
 
That was where I was going next. 
 
The crosstab would be a simple solution, if I could get the column widths to change. I've been able to change the style of the header cells in a crosstab based on the data in the cell with the onCreate function events in the crosstab, but I couldn't find anything that would let me resize a column. 
 
I can also pad the column names with spaces in my query before the data hits the crosstab, and shrink/widen the columns based on that with the "Shrink" property in the crosstab advanced properties. I think I could make that work, but when I print to PDF which is what is required, it seems to disregard the shrink property and all the column widths are the same again.

Ronak · December 20, 2011

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="92224" data-time="1324396039" data-date="20 December 2011 - 08:47 AM">
You could just create the table from scratch, rather than just adding columns to an existing table. I had one optional set of column bindings. You'd have more sets, but it'd be similar. You would just use your second parameter to decide which list of column bindings you're going to use to add to the table. Let me know if you have questions. </blockquote>
Hi michael...I am not getting you...will you please give me a demo example..please 
 
Thanks 
Ronak

mwilliams · December 20, 2011

Ronak,

Yes, I'll try to make an example showing what I mean.

mwilliams · December 20, 2011

Tubal,

I'm looking for a way to set the width of a crosstab column with script, but am unable to do so as of yet. The "width" property doesn't do anything, it appears. The easiest thing for you to do might be to create the connection and just build the table from scratch. It would probably be less work, that way, than trying to get the crosstab to look exactly how you want.

Tubal · December 20, 2011

<blockquote class='ipsBlockquote' data-author="'Ronak'" data-cid="92237" data-time="1324402656" data-date="20 December 2011 - 10:37 AM">
Hi michael...I am not getting you...will you please give me a demo example..please 
 
Thanks 
Ronak </blockquote>
 
Just modify Michael's beforeFactory script a little and it should work. Something along the lines of: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>

if(params["parameter name"]=="TITLE"){
myPossibleColumnList = ['Name','ID','Key'];
} else if (params["parameter name"]=="USER"){
myPossibleColumnList = ['FirstName','LastName'];
}

numColumns = params["NumColumns"].value;
i=1;
importPackage( Packages.org.eclipse.birt.report.model.api );
elementFactory = reportContext.getDesignHandle().getElementFactory()
var mytable = reportContext.getDesignHandle().findElement("myTable");
var colbinds = mytable.getColumnBindings( );
var cs1 = []; StructureFactory.createComputedColumn( );

while (i < numColumns){
cs1[i] = StructureFactory.createComputedColumn( );
cs1[i].setName(myPossibleColumnList[i]);
cs1[i].setExpression( "dataSetRow[\"" + myPossibleColumnList[i] + "\"]" );
colbinds.addItem( cs1[i] );

//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( myPossibleColumnList[i]);
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( myPossibleColumnList[i] );//$NON-NLS-1$
tcell.getContent( ).add( mylabel );
i++;
}
</pre>
 
You'd have to make sure your dataSetRow names matched your column names with this script, and also make sure that they never selected more columns than was available when you set up your parameters.

Ronak · December 20, 2011

Hi Michael,

Thanks for the reply, but in my case there can be thousand of parameter with bundle of columns and for those I can't mention everything in script like

if(params["parameter name"]=="TITLE"){
myPossibleColumnList = ['Name','ID','Key'];
} else if (params["parameter name"]=="USER"){
myPossibleColumnList = ['FirstName','LastName'];
}

also the parameter name coming from database...anytime the parameters can be added..so each time we can't make change in script..as its not good

Thanks
Ronak

mwilliams · December 20, 2011

Ronak,

It sounds like you're running into a similar issue as Tubal's. You'll need to connect to your database in script and figure out the columns that you'll have, then use the script from the example I posted to create the table based on the column names you grab from your connection you create in script.

Tubal · December 20, 2011

Do you have a sample script to get this data? I've gotten as far as getting a rowcount by using this sample here: <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/22742-dataset-value-in-beforefactory-method/page__s__5bf199e43dc0bdcf2d5cb45768d78b0a'>Link</a> 
 
With this code: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
...
queryDefinition = new QueryDefinition( );
queryDefinition.setDataSetName( odaDataSet.getName() );
queryDefinition.setAutoBinding(true);
var pq = de.prepare( queryDefinition );
var qr = pq.execute( null );
rowcount=0;
var ri = qr.getResultIterator( );
ris="";
while ( ri.next( ) )
{
rowcount++;
ris = ris + ri.next() + "\n";
}
...
</pre>
 
My ris variable returns 'true' for each record, but my query returns several columns with data in them.. 
 
How would I extract the data? 
 
Thanks, 
 
Phil

mwilliams · December 20, 2011

Phil,

Not sure if this will help, but this article shows how to use java to connect to data and step through the result set.

http://www.java-samples.com/showtutorial.php?tutorialid=202

Ronak · December 21, 2011

Hi Michael,

Yesterday.. I was talking about the load error...Here I am sending the snapshots. please go through it.

Thanks,
Ronak

mwilliams · December 29, 2011

What is the error in the log?

yoovrajkhullar · October 1, 2014

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="92192" data-time="1324328213">
<div>
Ok. Here's an example that shows how to add a specific number of columns, dynamically, based on a selected value in a parameter. Hope this helps.
</div>
</blockquote>
 
<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="92192" data-time="1324328213">
<div>
Ok. Here's an example that shows how to add a specific number of columns, dynamically, based on a selected value in a parameter. Hope this helps.
</div>
</blockquote>
Hi Williams
I liked this post of yours , i am new to birt and i modified as per my requirement to select muliple parameter values as per User selection.
My issue is that i dont want the CustomerNumber to be shown when the user does not select Customer number as parameter but selects other values, i.e CheckNumber, Payment Date, and Amount.
Also removed Customer Number from Report Layout as the user may never selct it.
He will selct one or two different columns.
i am attaching my example here. dont know how to modify your iterators in Before factory method.
Changed value of var 1 to 0 in it but then it does not work.
Please find attached my modified Report.
dont know how soon yourself look to these posts , so i am giving my e-mail too below.
 
Regards
Yoovraj
<a data-ipb='nomediaparse' href='mailto:[email protected]'>[email protected]</a>

mwilliams · October 1, 2014

You could create the table from scratch instead of having a starting table to add to. You could also simply hide the initial column if it's not selected or remove the binding and element from the table. Any of those should work.

yoovrajkhullar · October 2, 2014

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="131087" data-time="1412192477">
<div>
You could create the table from scratch instead of having a starting table to add to. You could also simply hide the initial column if it's not selected or remove the binding and element from the table. Any of those should work
</div>
</blockquote>
 
Thanks Willaims for prompt reply ,
I can not hide it as per requirement beacuse the user wants to get only the selected columns in the dataset to be displayed.
i leave the First column as blank in the designer layout and try to get it by declaring the array var i=0,
there is something in this iteration that if i declare array var i=0 , it gives error to get the first column if the user requires it too, right now it runs good with var i=1)
//second parameter is before(-1) or after(1)
mytable.insertColumn(i+1,-1);
 
Can you tell how can i get the first Column by twaeking in this iteration.
 
I would apprciate if you could fix this try.rptdesign as it may answer many questions in this post from my fellow Birt developers.
 
And like your posts so much.
Again thanks so much.
Regards
Yoovraj

yoovrajkhullar · October 2, 2014

Hi Mark
 
I am uploading this Report for other Report Developers as my contribution to the BIRT community as it is working now.
 
Thank you so much.
 
Regards
Yoovraj
<a data-ipb='nomediaparse' href='mailto:[email protected]'>[email protected]</a>

yoovrajkhullar · October 15, 2014

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="131087" data-time="1412192477">
<div>
You could create the table from scratch instead of having a starting table to add to. You could also simply hide the initial column if it's not selected or remove the binding and element from the table. Any of those should work.
</div>
</blockquote>
Hi Willaims
 
How to display the columns in BIRT in the order they are selected .
like display the column which is selcted first from the drop down.
Thanks and regards
Yoovraj

david.lee · October 29, 2014

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="92239" data-time="1324404397">
<div>
Tubal, I'm looking for a way to set the width of a crosstab column with script, but am unable to do so as of yet. The "width" property doesn't do anything, it appears. The easiest thing for you to do might be to create the connection and just build the table from scratch. It would probably be less work, that way, than trying to get the crosstab to look exactly how you want.

</div>
</blockquote>
 
Hi, Michael,
 
I am new to BIRT, happy to have seen this post, I tried the reports here, it works very well, thank you all.
 
I solved dynamically set column width, and alignment of each column, as well as the column sequence,since the columns are dynamically created, each numeric column needs a subtotal at the bottom of itself, so question is : How to dynamically add a subtotal at the end of a column?
 
Any information will be appreciated! Thank you so much!
 
 
Regards,
 
David

david.lee · November 2, 2014

<blockquote class="ipsBlockquote" data-author="yoovrajkhullar" data-cid="131103" data-time="1412278795">
<div>
Hi Mark
 
I am uploading this Report for other Report Developers as my contribution to the BIRT community as it is working now.
 
Thank you so much.
 
Regards
Yoovraj
<a data-ipb='nomediaparse' href='mailto:[email protected]'>[email protected]</a>
</div>
</blockquote>
Hi, Yoovraj,
 
Thank you so much, your try.rptdesign is very helpful to me!
 
 
Regards,
David

yoovrajkhullar · November 18, 2014

<blockquote class="ipsBlockquote" data-author="david.lee" data-cid="131765" data-time="1414902285">
<div>
Hi, Yoovraj,
 
Thank you so much, your try.rptdesign is very helpful to me!
 
 
Regards,
David
</div>
</blockquote>
You are welcome David,
Yoovraj

yoovrajkhullar · November 18, 2014

<blockquote class="ipsBlockquote" data-author="yoovrajkhullar" data-cid="132093" data-time="1416343487">
<div>
You are welcome David,
Yoovraj
</div>
</blockquote>
 
<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="131087" data-time="1412192477">
<div>
You could create the table from scratch instead of having a starting table to add to. You could also simply hide the initial column if it's not selected or remove the binding and element from the table. Any of those should work.
</div>
</blockquote>
Hi Mark
For these dyanamically Report i generated the interactive viewer does not work.
Is there a limitaion in BIRT that it works only for the Columns which are laid out in the Report dsigner Layout.
Or is there a way toenable interactivity for these dynamically generated columns, eithe in BeforeFactroy or some where else.
Please reply i will highy appreciate.
Thanks
Yoovraj

Matthew L. · December 31, 2014

Yoovraj,
 
It looks like Interactive Viewer (IV) could work for the try.rptdesign however there isn't a check in the beforeFactory code to check for existing columns. When IV is used, the beforeFactory code is run again which causes an error due to columns already existing.
 
Attached is my example of a dynamic data set with a dynamic table using pure API code.
Actuate Professional tools such as Interactive Viewer and export options work with this design.
 
This was built using Actuate BIRT 11SP3 (110E111028) however it was also verified to work in the iHub3 (230A131216) release.

Ka Lai · January 19, 2015

Is there a way to do this without Actuate Professional Tools?

Matthew L. · January 20, 2015

Attached is a modified version that works with OS BIRT.

Creating Column Dynamically

Comments