Why is my Crosstab being cut off in Excel?

Question

I'm attempting to create an excel spreadsheet using BIRT. The spreadsheet is a crosstab mapping two objects together. The number of rows and columns are dynamic based on values in a MySQL database. Currently I have a working implementation of the report for PDF output. Now, I am trying to create a second version of the report for Excel.

I have copied the report design and begun adjusting it to work with Excel. Everything looks good, but only the first 3 columns are displayed after the header. All rows appear correctly.

I have tried the following:

• I tried setting Overflow to Visible on every element on the page. This had no effect.
• I tried setting the master page's height and width to ridiculously large values. All of the information displayed correctly, but I am hoping for a solution without hard coded values. In the future the data width might exceed my arbitrary value again and be cut off.

I am constrained in the following ways:

• I am not able to switch reporting engines (I have to use BIRT).
• I am not able to switch Excel emitters.

This blog entry mentions my problem: http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/ but it does not offer a solution other than an emitter switch. The specific quote is "The files also have problems with page layout that I could not work around (specifically wide reports would be cut off)."

Beyond the one blog entry my googlefu has failed me. Any help is appreciated! Thank you!

Bricole

I would suggest uploading a sample file somewhere of the BIRT's output, how you see it in Excel, and how you would like to see it, so that we can work with something. Avoid personal/sensitive data and post a view/download link – Indophenol

Unfortunately, I'm no longer at the company I was working for when I asked this question. I would definitely do that if I was. Since I'm not, I no longer have access to the code that was causing the problem, or any of the resulting output. I believe that we ended up using the arbitrarily large width "solution." I wasn't really satisfied, but it worked well enough at the time. – Bricole

This question should be closed, in general BIRT will export all the columns to excel, even with the out of the box emmitter. This is a one off question about a specific problem on one report, where the report is not available for problem solving. Hence there is no possible answer to this question. – Circassian

I think you will find that, for any significantly wide report, some columns will not be visible, unless this has been fixed in a newer version of BIRT. A note: It is entirely possible that the data is actually present in the file structure but it is not rendered in Excel correctly. I'm not sure what the SO policy is for this situation. I will be around occasionally to check on the question's progress, but I don't expect to have time to update it with an example. If it should be closed, that is fine. However, I won't be the one to close it unless someone shows me a good reason to do so. – Bricole

So one solution would be to a way to dynamiclaly alter the masterpage's width. When the 'Type' is set to 'custom', currently the only point and click options require specific values (numerical digits only allowed in pop-up) for any of 8 unit types (i.e. 'in'). (using BIRT 4.2.1) – Circassian

@JamesJenkins That is exactly what I was looking for, but I was unable to find a way to do it. – Bricole

These two Eclipse Community Forums posts with answers by Jason Weathersby provided the path to modifing the master page width based on number of columns. Creating the answer from them to post here is more then I want to attempt at this time. eclipse.org/forums/index.php/t/392881 eclipse.org/forums/index.php/mv/tree/118884/#page_top – Circassian

@JamesJenkins Looks like a good start. If someone else encounters this problem, hopefully this will help them create a solution! Thanks for your help on this. – Bricole

@ radicaledward101, I am working to document a solution for adjusting the width of the master page based on the number of columns using BIRT 4.2.1. I have cross tab report (from sample data base) that varies between 1 and 30 columns based on values (dates in parameter) I am unable to recreate the issue of not displaying all the values. I used a Cross Tab report item, it automatically adjusts to fit the items in the available space, I don't see any options for changing the column width (you could do it with a grouped table). For this solution I going to continue with the Cross Tab Item. – Circassian

@JamesJenkins Thanks for all your work on this! – Bricole

Answer 1

There are two questions here. The first one is relatively easy, the second is complex.

1.Why is my Cross tab being cut off in Excel?

2.How do I dynamically adjust the master page width based on the number of columns in the report at runtime?

A1: The Cross tab is being cut off because column widths have been manually set, where the number of columns will expand past the set width of the Master page. Anytime you grab report design element and adjust, BIRT assumes you know what your doing and does not override your setting.

The solution is to recreate the report element (Table or Cross Tab) and not manually adjust any sizes. When run in HTML or Excel all the columns will be automatically set to display in the available master page width.

Screen shot of a BIRT 4.2 Cross Tab, Report Item with a 2 inch master page width and 30 columns

A2: This is not easy, and I will not be providing the answer at this time. I will point toward the solution and identify a couple of the road blocks. A valid solution to this question must include a functioning solution using the Sample Database.

(as of BIRT 4.2.1)

Challenge1 - The Master Page Width is set BIRT Report Scripting in events prior to report Table or Cross Tab item being completed. You can not simply count how many columns are in the report;

If you wanted to count, columns --

Report Design intialize

 columnCount = 0;

Cross Tab, onCreate

 columnCount ++;

In my research there are two paths suggested for counting columns prior to the Cross Tab item being created. Either

1. Run the data set in the beforeFactory (this means two queries to the data base, one to count and one for the report), then get a count and use it.

2. Calculate the value in your intial query and harvest it in the Data Set, onFetch.

I followed the Data Set, onFetch, option using a computed column but did not get it working.

Challenge2 - The Width Property of the Master Page must be set on or before the Report Design, beforeRender. With the beforeFactory being the most often recommended. Additionally the Width Property of the Master Page is only available when the Master Page "Type" is set to "Custom", in my attempts I set this manually in the Property Editor General.

Passing Values from the onFetch to beforeFactory must be done using a PersistentGlobalVariable which can only pass strings, not integers. I found all kinds of way for this to not work. Even passing "12in" in PersistentGlobalVariable failed to adjust the master page Width

Either of these codes in beforeFactory will adjust the Master Page Width (when Type = Custom)

Pass the Value

reportContext.getReportRunnable().designHandle.getDesignHandle().findMasterPage("Simple MasterPage").setProperty("width","12in");

Calculate a value and pass it

increaseWidth = 20;
reportContext.getReportRunnable().designHandle.getDesignHandle().findMasterPage( "Simple MasterPage").setProperty("width",((2+increaseWidth)+"in"));

In the end I have been unable to find or create a functional report that adjusts the Master Page Width passed on the number columns generated at report run time. I think it is possible, but doing so is beyond my current skills.

Circassian