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

Comments

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969

    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]

  • edited 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
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    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]

  • edited December 31, 1969


    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.


    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
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    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]

  • edited December 31, 1969
    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.
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    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]

  • edited December 31, 1969


    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.




    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.
  • edited December 31, 1969


    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.


    Hi michael...I am not getting you...will you please give me a demo example..please



    Thanks

    Ronak
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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]

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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. :)

    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]

  • edited December 31, 1969


    Hi michael...I am not getting you...will you please give me a demo example..please



    Thanks

    Ronak




    Just modify Michael's beforeFactory script a little and it should work. Something along the lines of:





    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++;
    }



    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.
  • edited December 31, 1969
    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
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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]

  • edited December 31, 1969
    Do you have a sample script to get this data? I've gotten as far as getting a rowcount by using this sample here: Link



    With this code:


    ...
    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";
    }
    ...



    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
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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]

  • edited December 31, 1969
    Hi Michael,

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

    Thanks,
    Ronak
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    What is the error in 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]

  • edited October 1, 2014


    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.




     




    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.




    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


    [email protected]


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


    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]



  • 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




     


    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


    [email protected]




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




    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

  • edited October 29, 2014


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




     


    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




  • 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


    [email protected]




    Hi, Yoovraj,


     


    Thank you so much, your try.rptdesign is very helpful to me!


     


     


    Regards,


    David



  • Hi, Yoovraj,


     


    Thank you so much, your try.rptdesign is very helpful to me!


     


     


    Regards,


    David




    You are welcome David,


    Yoovraj



  • You are welcome David,


    Yoovraj




     




    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.




    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

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

    Matthew L.
    Developer
    OpenText
  • Is there a way to do this without Actuate Professional Tools?


    Sincerely

    Ka Lai

     

     

    Working with:

    Open Source BIRT V4.4.0

    Eclipse Luna

    Oracle database

  • Attached is a modified version that works with OS BIRT.


    Matthew L.
    Developer
    OpenText
Sign In or Register to comment.