The community of Analytics developers are invited to follow and participate in the developer network.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.
Creating Column Dynamically
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
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
0
Comments
http://www.birt-exchange.org/org/devshare/designing-birt-reports/1099-add-column-to-table-dynamically/
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
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
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
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.<br /></p></blockquote>
Hi Michael,<br />
<br />
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.<br />
<br />
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.<br />
<br />
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.<br />
<br />
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.<br />
<br />
If possible then please generate an example for me. <br />
<br />
I am attaching one small report, so that you can get better idea. Its not using sample database
<br />
Thanks <br />
Ronak
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
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.
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
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.<br /></p></blockquote>
<br />
That was where I was going next.<br />
<br />
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. <br />
<br />
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.
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.<br /></p></blockquote>
Hi michael...I am not getting you...will you please give me a demo example..please<br />
<br />
Thanks <br />
Ronak
Yes, I'll try to make an example showing what I mean.
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
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.
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
Hi michael...I am not getting you...will you please give me a demo example..please<br />
<br />
Thanks <br />
Ronak<br /></p></blockquote>
<br />
Just modify Michael's beforeFactory script a little and it should work. Something along the lines of:<br />
<br />
<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>
<br />
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.
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
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.
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
<br />
With this code:<br />
<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>
<br />
My ris variable returns 'true' for each record, but my query returns several columns with data in them..<br />
<br />
How would I extract the data?<br />
<br />
Thanks,<br />
<br />
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
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
Yesterday.. I was talking about the load error...Here I am sending the snapshots. please go through it.
Thanks,
Ronak
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
<div>
<p>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.</p>
</div>
</blockquote>
<p> </p>
<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="92192" data-time="1324328213">
<div>
<p>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.</p>
</div>
</blockquote>
<p>Hi Williams</p>
<p>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.</p>
<p>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.</p>
<p>Also removed Customer Number from Report Layout as the user may never selct it.</p>
<p>He will selct one or two different columns.</p>
<p>i am attaching my example here. dont know how to modify your iterators in Before factory method.</p>
<p>Changed value of var 1 to 0 in it but then it does not work.</p>
<p>Please find attached my modified Report.</p>
<p>dont know how soon yourself look to these posts , so i am giving my e-mail too below.</p>
<p> </p>
<p>Regards</p>
<p>Yoovraj</p>
<p><a data-ipb='nomediaparse' href='mailto:[email protected]'>[email protected]</a></p>
Regards,
Michael Williams
eSignLive Evangelism & Community Manager | eSignLive by VASCO
Find me on:
Twitter
Facebook
Blog
LinkedIn
eSignLive Developer Community
Email me:
Google: [email protected]
<div>
<p>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</p>
</div>
</blockquote>
<p> </p>
<p>Thanks Willaims for prompt reply ,</p>
<p>I can not hide it as per requirement beacuse the user wants to get only the selected columns in the dataset to be displayed.</p>
<p>i leave the First column as blank in the designer layout and try to get it by declaring the array var i=0,</p>
<p>there is something in this<strong> iteration </strong>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)</p>
<p>//second parameter is before(-1) or after(1)</p>
<p>mytable.insertColumn(i+1,-1);</p>
<p> </p>
<p>Can you tell how can i get the first Column by twaeking in this iteration.</p>
<p> </p>
<p>I would apprciate if you could fix this try.rptdesign as it may answer many questions in this post from my fellow Birt developers.</p>
<p> </p>
<p>And like your posts so much.</p>
<p>Again thanks so much.</p>
<p>Regards</p>
<p>Yoovraj </p>
<p> </p>
<p>I am uploading this Report for other Report Developers as my contribution to the BIRT community as it is working now.</p>
<p> </p>
<p>Thank you so much.</p>
<p> </p>
<p>Regards</p>
<p>Yoovraj</p>
<p><a data-ipb='nomediaparse' href='mailto:[email protected]'>[email protected]</a></p>
<div>
<p>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.</p>
</div>
</blockquote>
<p>Hi Willaims</p>
<p> </p>
<p>How to display the columns in BIRT in the order they are selected .</p>
<p>like display the column which is selcted first from the drop down.</p>
<p>Thanks and regards</p>
<p>Yoovraj</p>
<div>
<p>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>
<p> </p>
<p>Hi, Michael<span>,</span></p>
<p> </p>
<p>I am new to BIRT, happy to have seen this post, I tried the reports here, it works very well, thank you all.</p>
<p> </p>
<p>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?</p>
<p> </p>
<p>Any information will be appreciated! Thank you so much!</p>
<p> </p>
<p> </p>
<p>Regards,</p>
<p> </p>
<p>David</p>
<div>
<p>Hi Mark</p>
<p> </p>
<p>I am uploading this Report for other Report Developers as my contribution to the BIRT community as it is working now.</p>
<p> </p>
<p>Thank you so much.</p>
<p> </p>
<p>Regards</p>
<p>Yoovraj</p>
<p><a data-ipb='nomediaparse' href='mailto:[email protected]'>[email protected]</a></p>
</div>
</blockquote>
<p>Hi, <span style="font-size:12px;background-color:rgb(247,247,247);">Yoovraj,</span></p>
<p> </p>
<p><span style="font-size:12px;background-color:rgb(247,247,247);">Thank you so much, your try.rptdesign is very helpful to me!</span></p>
<p> </p>
<p> </p>
<p><span style="font-size:12px;background-color:rgb(247,247,247);">Regards,</span></p>
<p><span style="font-size:12px;background-color:rgb(247,247,247);">David</span></p>
<div>
<p>Hi, Yoovraj,</p>
<p> </p>
<p>Thank you so much, your try.rptdesign is very helpful to me!</p>
<p> </p>
<p> </p>
<p>Regards,</p>
<p>David</p>
</div>
</blockquote>
<p>You are welcome David,</p>
<p>Yoovraj</p>
<div>
<p>You are welcome David,</p>
<p>Yoovraj</p>
</div>
</blockquote>
<p> </p>
<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="131087" data-time="1412192477">
<div>
<p>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.</p>
</div>
</blockquote>
<p>Hi Mark</p>
<p>For these dyanamically Report i generated the interactive viewer does not work.</p>
<p>Is there a limitaion in BIRT that it works only for the Columns which are laid out in the Report dsigner Layout.</p>
<p>Or is there a way toenable interactivity for these dynamically generated columns, eithe in BeforeFactroy or some where else.</p>
<p>Please reply i will highy appreciate.</p>
<p>Thanks</p>
<p>Yoovraj</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>Attached is my example of a dynamic data set with a dynamic table using pure API code.</p>
<p>Actuate Professional tools such as Interactive Viewer and export options work with this design.</p>
<p> </p>
<p>This was built using Actuate BIRT 11SP3 (110E111028) however it was also verified to work in the iHub3 (230A131216) release.</p>
Developer
OpenText
Sincerely
Ka Lai
Working with:
Open Source BIRT V4.4.0
Eclipse Luna
Oracle database
Developer
OpenText