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!

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.