Creating Column Dynamically

Take a look at this devShare post:

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

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

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

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.

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.

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

<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

Ronak,

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

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.

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

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

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.

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

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

Hi Michael,

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

Thanks,
Ronak

What is the error in the log?

<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:yoovraj@gmail.com'>yoovraj@gmail.com</a>

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.

<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

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:yoovraj@gmail.com'>yoovraj@gmail.com</a>

david.lee

<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

<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:yoovraj@gmail.com'>yoovraj@gmail.com</a>
</div>
</blockquote>
Hi, Yoovraj,
 
Thank you so much, your try.rptdesign is very helpful to me!
 
 
Regards,
David

<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