nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Designing reports for excel output with Tribix emitter

bmario

Greetings!

Recently I've been asked to generate reports with an xls output. After trying some different emitters, I ended up going with Tribix since it could manage images and seemed to generate everything almost exactly the same as defined in the .rptdesign, but then I realized that it uses a lot of empty rows and columns for the indentation to simulate padding (at least thats what I've found out so far) and that there are some elements that it doesn't display in its full width (like labels with long text).

I was wondering if there is any documentation about what to do to avoid that kind of "issues" when generating xls files with Tribix? like tips or anything alike. Also, I would like to know if there is a way to display a label (or cell content in this case) completely without the need to resize later the column's width, is there any property that could help? (I have tried the excelOptions.setOption("fixed_column_width", true); and xlsConfig.put("fixed_column_width", new Integer(100)); but apparently that doesn't help much)

Thanks!

Find more posts tagged with

Comments

Yaytay

I found that removing all padding from the report design, and ensuring that everything lined up precisely, enabled me to get usable spreadsheets from Tribix. 
Note that when I say removing all padding I mean absolutely all of it, which isn't easy as it defaults to 1px and you can't apply top-level styles for everything. 
 
But then I tried it with Crosstab reports and it screwed them up completely, so I wrote my own emitter. 
 
There are quite a few options for Excel emitters, and Tribix is one of the worst (it's better than the built in emitter and it looks great, but if you want to use the output as a spreadsheet I would recommend avoiding it). 
See here for a brief comparison of the others and a link to mine: <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/'>http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/</a> 
 
Just as a PS, my complaints about the Tribix emitter only apply to the Excel one, I use the PPT one in my live system and (with the latest POI, not the one it ships with) I'm quite happy with it.

bmario

Hi Yaytay!

I did try using your emitter, it just seemed to not work properly since it didn't print all the data in the report for some reason, so I went back to Tribix. Not sure what I did wrong though.

Also, does anyone know if there is a way to name the excel sheets with Tribix?

Yaytay

<blockquote class='ipsBlockquote' data-author="'bmario'" data-cid="98094" data-time="1332343789" data-date="21 March 2012 - 08:29 AM">
Hi Yaytay! 
 
I did try using your emitter, it just seemed to not work properly since it didn't print all the data in the report for some reason, so I went back to Tribix. Not sure what I did wrong though. 
 
Also, does anyone know if there is a way to name the excel sheets with Tribix? </blockquote>
 
Hi, 
 
I'd love it if you could help me work out why you lost information - I really don't want that to go unfixed. If you could spare the time to post a bug at <a class='bbc_url' href='https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/issues'>https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/issues</a> with an example that doesn't work it'd be a huge help. 
 
For the sheet name, have you looked at: <a class='bbc_url' href='http://sourceforge.net/apps/mediawiki/tribix/index.php?title=Examples_for_custom_sheet_name_expression'>http://sourceforge.net/apps/mediawiki/tribix/index.php?title=Examples_for_custom_sheet_name_expression</a> 
As I understand it you need to supply a RenderOption called sheet_name that is an expression that is evaluated to generate the name.

bmario

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="98095" data-time="1332344485" data-date="21 March 2012 - 08:41 AM">
Hi, 
 
I'd love it if you could help me work out why you lost information - I really don't want that to go unfixed. If you could spare the time to post a bug at <a class='bbc_url' href='https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/issues'>https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/issues</a> with an example that doesn't work it'd be a huge help. 
 
For the sheet name, have you looked at: <a class='bbc_url' href='http://sourceforge.net/apps/mediawiki/tribix/index.php?title=Examples_for_custom_sheet_name_expression'>http://sourceforge.net/apps/mediawiki/tribix/index.php?title=Examples_for_custom_sheet_name_expression</a> 
As I understand it you need to supply a RenderOption called sheet_name that is an expression that is evaluated to generate the name. </blockquote>
 
Thank you again Yaytay, as soon as I make some changes to reproduce the error I'd let you know there. 
 
I read the wiki about Tribix's KEY_SHEET_NAME_EXPR option to specify the sheet's name but I can't figure out where should I put that variable nor how I relate that to an expression. I'm guessing I should define it first in one of the report's events and then access it on the bookmark expressions? I really have no idea so far.

Yaytay

<blockquote class='ipsBlockquote' data-author="'bmario'" data-cid="98097" data-time="1332346287" data-date="21 March 2012 - 09:11 AM">
Thank you again Yaytay, as soon as I make some changes to reproduce the error I'd let you know there. 
 
I read the wiki about Tribix's KEY_SHEET_NAME_EXPR option to specify the sheet's name but I can't figure out where should I put that variable nor how I relate that to an expression. I'm guessing I should define it first in one of the report's events and then access it on the bookmark expressions? I really have no idea so far. </blockquote>
 
Thanks. 
 
How are you running the report? 
 
If you are using the ReportEngine then I think you need to set it as a RenderOption. If you are using just the designer I'm not sure what you can do - maybe you can access the RenderOptions from the BeforeFactory event, try: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
reportContext.getRenderOption().setOption( "sheet_name", "EXPR" );
</pre>

bmario

Ok, oficially It seems like I just can't change the name of the excel's sheet. Have tried pretty much in every single possible report's event and nothing. Have anyone actually done this and have any example that can share? Thanks

Yaytay, I changed back to your emitter and it generated the report without any missing data so probably I did something wrong the first time I tried it. However, I got an error after I deleted a table because of a formula (it said that it was expecting a string, an integer or another data type I can't remember right now) and didn't let me generate the report. I'm not sure why should it be expecting something from a deleted table... If I can't solve my Tribix problem I think I'll go back to your emitter even though I'll probably change the reports design again because I saw some "issues" like it didn't separate the data in rows but it seemed like it grouped values in cells instead.

Yaytay

<blockquote class='ipsBlockquote' data-author="'bmario'" data-cid="98114" data-time="1332370885" data-date="21 March 2012 - 04:01 PM">
Ok, oficially It seems like I just can't change the name of the excel's sheet. Have tried pretty much in every single possible report's event and nothing. Have anyone actually done this and have any example that can share? Thanks </blockquote>
I just set up an instance of BIRT (3.7.1) with the tribix emitters (2.5.2) and used one of my test reports with the beforeFactory script set to: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
reportContext.getRenderOption().setOption("sheet_name", "\"My Sheet \" + sheetIndex");
</pre>
And it worked. 
 
<blockquote class='ipsBlockquote' data-author="'bmario'" data-cid="98114" data-time="1332370885" data-date="21 March 2012 - 04:01 PM">
Yaytay, I changed back to your emitter and it generated the report without any missing data so probably I did something wrong the first time I tried it. However, I got an error after I deleted a table because of a formula (it said that it was expecting a string, an integer or another data type I can't remember right now) and didn't let me generate the report. I'm not sure why should it be expecting something from a deleted table... </blockquote>
That's BIRT for you

I've had a few instances where things have required more deletion than one would think in order to get back to a working state - the general problem is that BIRT auto generates things that it (quite reasonably) doesn't then auto delete. 
 
<blockquote class='ipsBlockquote' data-author="'bmario'" data-cid="98114" data-time="1332370885" data-date="21 March 2012 - 04:01 PM">
If I can't solve my Tribix problem I think I'll go back to your emitter even though I'll probably change the reports design again because I saw some "issues" like it didn't separate the data in rows but it seemed like it grouped values in cells instead. </blockquote>
My emitter has very limited support for nested tables, so if you have a table within a table (or a table within a grid) it puts the entirety of the subtable into a single cell. Two situations in which it doesn't do this are: 
<ul class='bbc'><li> When the subtable fills all the columns of the super table.</li><li> When the subtable is a single row grid and has the same number of columns as spanned by the cell containing it.</li></ul>
Nested tables are awkward for the approach my emitter uses (which maps one BIRT cell to one Excel cell), but I'm happy to try to find workarounds for any specific instances where this is a problem. 
 
Alternatively don't neglect to consider the Arctorus emitter (not used the latest version, it sounds very good but it's not free) or the "<a class='bbc_url' href='http://code.google.com/a/eclipselabs.org/p/native-excel-emitter-birt-plugin/'>Native Excel Emitter</a>" (uses the same layout code as the built in version, but writes to Excel files using POI - I don't like the name 'cos it implies it's the only one to output native Excel files, but give it a go). 
 
Oh, sheet names for my emitter are taken from the names of the tables/grids on the sheet - no need for complicated RenderOptions or expression evaluation.

bmario

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="98121" data-time="1332406446" data-date="22 March 2012 - 01:54 AM">
I just set up an instance of BIRT (3.7.1) with the tribix emitters (2.5.2) and used one of my test reports with the beforeFactory script set to: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
reportContext.getRenderOption().setOption("sheet_name", "\"My Sheet \" + sheetIndex");
</pre>
And it worked. </blockquote>
 
The report's beforeFactory event? I did the same but nothing happened, hehe, this is kinda frustrating, sorry. 
 
Edit: IT WORKED! Sorry, apparently this code doesn't work, I guess I needed to pass an expression instead of a string for it to work. Thanks! 
<pre class='_prettyXprint _lang-auto _linenums:0'>
reportContext.getRenderOption().setOption("sheet_name", "My Sheet" + sheetIndex);
</pre>
 
<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="98121" data-time="1332406446" data-date="22 March 2012 - 01:54 AM">
That's BIRT for you

I've had a few instances where things have required more deletion than one would think in order to get back to a working state - the general problem is that BIRT auto generates things that it (quite reasonably) doesn't then auto delete. 
 
 
My emitter has very limited support for nested tables, so if you have a table within a table (or a table within a grid) it puts the entirety of the subtable into a single cell. Two situations in which it doesn't do this are: 
<ul class='bbc'><li> When the subtable fills all the columns of the super table.</li><li> When the subtable is a single row grid and has the same number of columns as spanned by the cell containing it.</li></ul>
Nested tables are awkward for the approach my emitter uses (which maps one BIRT cell to one Excel cell), but I'm happy to try to find workarounds for any specific instances where this is a problem. 
 
Alternatively don't neglect to consider the Arctorus emitter (not used the latest version, it sounds very good but it's not free) or the "<a class='bbc_url' href='http://code.google.com/a/eclipselabs.org/p/native-excel-emitter-birt-plugin/'>Native Excel Emitter</a>" (uses the same layout code as the built in version, but writes to Excel files using POI - I don't like the name 'cos it implies it's the only one to output native Excel files, but give it a go). 
 
Oh, sheet names for my emitter are taken from the names of the tables/grids on the sheet - no need for complicated RenderOptions or expression evaluation. </blockquote>
Oh, I do have a lot of nested tables so is a shame (for me that is). I'll have to give it a try to other emitters then if I can't make it with Tribix. Thank you again!

Yaytay

<blockquote class='ipsBlockquote' data-author="'bmario'" data-cid="98141" data-time="1332422889" data-date="22 March 2012 - 06:28 AM">
Oh, I do have a lot of nested tables so is a shame (for me that is). I'll have to give it a try to other emitters then if I can't make it with Tribix. Thank you again! </blockquote>
 
If you'd be willing to post your report design I could take a look at what it would take to make it work (with changes to either the report or my emitter). 
 
Jim

dzo67

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="98075" data-time="1332315380" data-date="21 March 2012 - 12:36 AM">
 
See here for a brief comparison of the others and a link to mine: <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/'>http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/</a> 
 </blockquote>
 
Hello Yaytay, 
 
I tried your emitter, it is quite what I need, great stuff! 
 
(I tried to register at your site, but the CAPTCHA check kept saying I entered the wrong code) 
 
Thanks! 
dzo

Yaytay

<blockquote class='ipsBlockquote' data-author="'dzo67'" data-cid="117202" data-time="1369726420" data-date="28 May 2013 - 12:33 AM">
Hello Yaytay, 
 
I tried your emitter, it is quite what I need, great stuff! 
 
(I tried to register at your site, but the CAPTCHA check kept saying I entered the wrong code) 
 
Thanks! 
dzo </blockquote>
 
Good news, thanks. 
 
Regarding your mail: Look at the value of the "Page Break Interval" property on the Table. 
This defaults to 40 (which would explain ~40000 rows splitting over 95 sheets) - if you set it to zero it won't ever page break for table size, but if you are outputting to XLS a value of 65000 may be a good idea. 
 
Jim

dzo67

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="117224" data-time="1369773771" data-date="28 May 2013 - 01:42 PM">
Good news, thanks. 
 
Regarding your mail: Look at the value of the "Page Break Interval" property on the Table. 
This defaults to 40 (which would explain ~40000 rows splitting over 95 sheets) - if you set it to zero it won't ever page break for table size, but if you are outputting to XLS a value of 65000 may be a good idea. 
 
Jim </blockquote>
Thanks, I already found that, that's why I removed that question from this site. 
 
In the mean time my collegue managed to modify the BirtOutput plugin (which can be used in Pentaho Data Integration, to be found at <a class='bbc_url' href='https://github.com/knowbi/BIRTOutput'>https://github.com/knowbi/BIRTOutput</a>) to use your Excel emitter. 
 
dzo