setting column width dynamically as per the dynamic date for excel report

Hii,

Currently I am generating an report. I t goes good, but I have one problem. What I want is the set the size of columns dynamically. since data is dynamic the report column should be sized dynamic, instead of small or big. If it has to be done with script then how to do that. I dont know that script. Tell me how to do that.

If there is any alternate way then also inform me.

Thanks,
Ronak

Find more posts tagged with

Comments

Hi Ronak,

You can set a column's width dynamically by selecting the table, going to the script tab, and putting the following in the onCreate script:

this.getColumn(0 based column number).width = "string width"

Hii,

I think this will work but can you give me the complete format i.e which API..etc.

Thanks,
Ronak

Hii,

I am using birt 3.7.
Currently I am generating excel report. In that what I want is to set the width size of the column dynamically i.e if the column 1 has a data data RONAK in the first row and COLUMN1 again has the data as MICHAEL in the second row then the size of column 1 is to be set as the width size of MICHAEL.

The data coming is dynamic. So how to do that. Even what the script will be, if the script to be written.

Thanks,
Ronak

Take a look at this report. It shows what I was telling you on facebook chat. Finding the longest value in for the column and dynamically setting the width in the onCreate of the table with this value.

Hii,

--I am using BIRT 3.7

In the last example you gave is working good.
Similarly I created a demo which wasn't working as what I wanted i.e to set the size of each column as per the max width data in the particular column.

I am sending you a design file and the result what I am getting in Excel format.
Also I think that in this case only the first row data width is calculated and then the width is set. It is not checking for all the column data for each row.
So please help me in this.

please check it out and tell me asap.

Thanks,
Ronak

You have a width set on your table. So, the first column is sized up to the width of the table and the others are just crammed in there. They have no room to dynamically size. You'll either have to remove the width on the table or do something like the following in your table's onCreate script to set the table's width to accept the column widths.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
c1 = col1*7;
c2 = col2*7;
c3 = col3*7;
ttl = c1+c2+c3;
this.width = ttl.toString() + "px";
this.getColumn(0).width = c1.toString() + "px";
this.getColumn(1).width = c2.toString() + "px";
this.getColumn(2).width = c3.toString() + "px";
</pre>

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80177" data-time="1311005684" data-date="18 July 2011 - 09:14 AM"><p>
You have a width set on your table. So, the first column is sized up to the width of the table and the others are just crammed in there. They have no room to dynamically size. You'll either have to remove the width on the table or do something like the following in your table's onCreate script to set the table's width to accept the column widths.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
c1 = col1*7;
c2 = col2*7;
c3 = col3*7;
ttl = c1+c2+c3;
this.width = ttl.toString() + "px";
this.getColumn(0).width = c1.toString() + "px";
this.getColumn(1).width = c2.toString() + "px";
this.getColumn(2).width = c3.toString() + "px";
</pre></p></blockquote>
<br />
<br />
Hii,<br />
<br />
Thanks for replying, but my question is somewhat different. What I want is that, when I ran the report the width is set only by taking the data of the first row only. i.e if the column1 and row1 has data as ABCD then the width for entire column is set as 28pixels as I wrote in the script as col1*7 where col1=4.<br />
Even that particular column has data as 123654789 which is of length 9 the width don't get changed to 63px. It remains 28pixel only and so lengthy data of that column gets mixed up.<br />
<br />
Hope I made you clear know. So please help in this. <br />
<br />
<br />
Regards,<br />
Ronak

I don't see the same issue without having a size set for the table's width. The issue I see is that when you have all numbers or all uppercase, it goes past the value of 7 pixels per character that worked with the lowercase text. You'll have to choose a larger value to work for all uppercase text. If you can reproduce the issue you're seeing with a report I can run, I can take a closer look, but with the exact same code as I gave you, I don't see the same.

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80232" data-time="1311086590" data-date="19 July 2011 - 07:43 AM"><p>
I don't see the same issue without having a size set for the table's width. The issue I see is that when you have all numbers or all uppercase, it goes past the value of 7 pixels per character that worked with the lowercase text. You'll have to choose a larger value to work for all uppercase text. If you can reproduce the issue you're seeing with a report I can run, I can take a closer look, but with the exact same code as I gave you, I don't see the same.<br /></p></blockquote>
<br />
Hi,<br />
<br />
Ok...I am sending you the file again and also the debug text file, so you can make easy understanding. What actually is happening that (*currently just focus on second column only) when I ran the report it checks the width of the first row<br />
and second row. If the width of the first row is greater then second row then the width of first row is considered for second column, else second row width, but even the third row of the second column contains larger width data then above two rows though there is no change in width, which is supposed to be as per the width of third row. Its clearly being seen in debug file as well. Please check it out.<br />
<br />
I am using BIRT 3.7.<br />
<br />
Here I attaches the file. If there is no error then create one example for me without scripted datasource.<br />
<br />
Thanks,<br />
Ronak

<blockquote class='ipsBlockquote' data-author="'Ronak'" data-cid="80283" data-time="1311142417" data-date="19 July 2011 - 11:13 PM"><p>
Hi,<br />
<br />
Ok...I am sending you the file again and also the debug text file, so you can make easy understanding. What actually is happening that (*currently just focus on second column only) when I ran the report it checks the width of the first row<br />
and second row. If the width of the first row is greater then second row then the width of first row is considered for second column, else second row width, but even the third row of the second column contains larger width data then above two rows though there is no change in width, which is supposed to be as per the width of third row. Its clearly being seen in debug file as well. Please check it out.<br />
<br />
I am using BIRT 3.7.<br />
<br />
Here I attaches the file. If there is no error then create one example for me without scripted datasource.<br />
<br />
Thanks,<br />
Ronak<br /></p></blockquote>

Hiii,

I got the solution of the above problems. Thanks a lot for your support.

Though there is one question.
I am not able to set the size of page dynamic as per the width of the columns.
How to do that??

Thanks,
Ronak

Unfortunately, the width of the masterpage has to be set in the beforeFactory, I believe, which is before you have access to your data, so you won't know the width of the data yet.

One thing you could do is to create several masterpages and depending on the width you have figured for the table, set the masterpage that you'll use for the report in the table's onCreate method with:

this.getStyle().masterPage = "myMasterPageName"

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80316" data-time="1311176673" data-date="20 July 2011 - 08:44 AM"><p>
Unfortunately, the width of the masterpage has to be set in the beforeFactory, I believe, which is before you have access to your data, so you won't know the width of the data yet.<br />
<br />
One thing you could do is to create several masterpages and depending on the width you have figured for the table, set the masterpage that you'll use for the report in the table's onCreate method with:<br />
<br />
this.getStyle().masterPage = "myMasterPageName"<br /></p></blockquote>
<br />
<br />
Can you create on small demo for me??<br />
I am using BIRT 3.7<br />
<br />
Thanks,<br />
Ronak

<blockquote class='ipsBlockquote' data-author="'Ronak'" data-cid="80361" data-time="1311227342" data-date="20 July 2011 - 10:49 PM"><p>
Can you create on small demo for me??<br />
I am using BIRT 3.7<br />
<br />
Thanks,<br />
Ronak<br /></p></blockquote>
<br />
Also, I told you that when I am running the report the width of the column is generated as per the width of the first two rows only in each column. Whenever there is third row with the width greater then above two rows for each column then also its not considering the width of that third row to be set as per the column. I am not getting whats the problem is there.<br />
<br />
This is the same issue which I posted you on 19th july 10:13pm. I told that I got solution, but actually not.<br />
<br />
It will be very nice if you create any sample example from a sample database.<br />
<br />
Thanks,<br />
Ronak

<blockquote class='ipsBlockquote' data-author="'Ronak'" data-cid="80366" data-time="1311230907" data-date="20 July 2011 - 11:48 PM"><p>
Also, I told you that when I am running the report the width of the column is generated as per the width of the first two rows only in each column. Whenever there is third row with the width greater then above two rows for each column then also its not considering the width of that third row to be set as per the column. I am not getting whats the problem is there.<br />
<br />
This is the same issue which I posted you on 19th july 10:13pm. I told that I got solution, but actually not.<br />
<br />
It will be very nice if you create any sample example from a sample database.<br />
<br />
Thanks,<br />
Ronak<br /></p></blockquote>
<br />
<br />
<br />
Hiii,<br />
<br />
I am using BIRt 3.7.<br />
Here there is a sample example, which I created for you to make the problem clear.

I found something weird. If I output the value of col1 to the report, I got the correct value, but c1 was not correct, so I moved the calculation for c1 to the onFetch and I also increased the pixels per character so that each column for sure fit the longest text since spacing and formatting is different for different rows in this example. It works now though. Take a look.

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80382" data-time="1311254044" data-date="21 July 2011 - 06:14 AM"><p>
I found something weird. If I output the value of col1 to the report, I got the correct value, but c1 was not correct, so I moved the calculation for c1 to the onFetch and I also increased the pixels per character so that each column for sure fit the longest text since spacing and formatting is different for different rows in this example. It works now though. Take a look.<br /></p></blockquote>
<br />
Hii,<br />
<br />
Here is the debug file.

Here is what I meant in our chat by saying to try putting a hidden text box before the table to see if it makes the onFetch be completed completely before the onCreate for the table. Let me know.

Hii,

The last example (sample) you gave me is working fine. But when I am generating the same report, its not working for me. So can you please tell me how you did that. So I can work over it.

Also I am sending an example which is not working for me. In this when I debug, its onCreate script fires before onFetch. So please check it out and inform me what I did wrong in this.

Thanks,
Ronak

The reason yours isn't working is because the "Hidden Text Box" is not bound to the dataSet. If you bind it to the dataSet, it causes the dataSet to run prior to the table so that all of your computations are completed before the table.

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80555" data-time="1311624525" data-date="25 July 2011 - 01:08 PM"><p>
The reason yours isn't working is because the "Hidden Text Box" is not bound to the dataSet. If you bind it to the dataSet, it causes the dataSet to run prior to the table so that all of your computations are completed before the table.<br /></p></blockquote>
Hi<br />
<br />
Currently I am getting one new problem. In the lase report you send to me(sample.rptdesign) in that when I ran the report it works only for the data which has datatype varchar2(size). The width of the data with datatype integer/number doesn't change. It tooks 0pixel only.<br />
<br />
Can you make the modification in that example you gave me, so that the data with integer datatype also sets its width dynamically.<br />
<br />
<br />
Thanks,<br />
Ronak

Hii,

Also in the current report when any data in the column is empty or null then the script returns error.
So how to solve that problem.

I am using BIRT 3.7

Wrap your .length statement in a check for null or empty to avoid the error. I'll get the integer example done soon and attach.

Sorry for the delay. My cable went out this afternoon. Here is the report that shows how to do this with a column of type INTEGER.

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80623" data-time="1311724645" data-date="26 July 2011 - 04:57 PM"><p>
Wrap your .length statement in a check for null or empty to avoid the error. I'll get the integer example done soon and attach.<br /></p></blockquote>
<br />
Hi,<br />
<br />
I tried but didn't get anything right to avoid that error occuring due to null value in data.<br />
Can you tell me what shall I write in that??<br />
<br />
<br />
Also in mine case if any column is empty its entire row is ignored and it didn't gets execute under the script. what shall I do. I have no idea in this case.<br />
<br />
Thanks,<br />
Ronak

You wrap the .length check with something like this as we discussed in chat:

if (row["myRow"] != null){
if (row["myRow"].length > col1){
col1 = row["myRow"].length;
}
}

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80657" data-time="1311779893" data-date="27 July 2011 - 08:18 AM"><p>
You wrap the .length check with something like this as we discussed in chat:<br />
<br />
if (row["myRow"] != null){<br />
if (row["myRow"].length > col1){<br />
col1 = row["myRow"].length;<br />
}<br />
}<br /></p></blockquote>
<br />
Hii,<br />
<br />
Can you tell me. how to use persistent global variable, as I have to use the value final value which I am getting from dataset1 in dataset2.<br />
I am using BIRT 3.7<br />
<br />
<br />
Thanks<br />
Ronak

If you're using a value from dataSet 1 in dataSet 2, you'll need to make sure that you have a report element bound to dataSet 1 before you have one bound to dataSet 2 so you can be certain the dataSets are ran in the order you need. To use a PGV, you'd set it with:

reportContext.setPersistentGlobalVariable("PGVName","string or object");

To grab it, you'd use:

reportContext.getPersistentGlobalVariable("PGVName");

Hope this helps.

Hii....

I am getting this error now....I cant understand why it is coming...
Error is :- Can not convert the value of MS to BigDecimal type.

How to resolve this error?

Thanks,
Ronak

Do you have a computed column or parameter or something where you're trying to assign a string to a decimal? Can you post the entire error?