Stretching subreport to fill band height for Excel

[tomsimmons]

Hi Folks

I have written a report that consists of top report with only a subreport which is in the detail band.  The subreport has some fields and a subreport in its detail band and column labels in it's Column Header band.  The final subreport has columns, and two fields only in the detail band - it has a group for the columns.

I have a couple if fields in the middle subreport that can be large, so they have stretch on overflow, consequently I have set all fields to strech to band height, and the subreport.  I have also set the fields in the subreport to strech to band height (clutching at straws?).  Long and short, everything in all reports is set to strech to band height.

When I run the report as PDF or JR Viewer, all is fine, but I need to run as Excel.  When I run as Excel, on rows where the two fields need to stretch, but there is nothing from the subreport, all is fine.  However if they stretch and there is info fromt he subreport, then the subreport isn't stretched, so I end up with a line of blank cells under the subreport data.  I've tried both Excel export API's.

How can I make the subreport stretch to band height?  I have attached a screen sippet, the blue being the fields (including the cause of the stretch, the yellow being the subreport, and the green being the bit I don't want.

 

Tom

Post Edited by tomsimmons at 05/07/2009 08:18

[jmartens]

I'm experiencing the same problem.  I've attached the sample XLS output file.  My main report's detail line has lots of columns, all set to stretch since the contents could be large.  Two of the columns are actually subreports, since an item from the main report could have from zero to many values for that column - i.e. my main report detail line is for a piece of equipment and one of the columns is "Accessories" and an item could have a handful of accessories.

An item that has only one accessory has output a row containing that accessory with the subreport's defined height of 20 pixels.  However, the detail line that it fits within is 58 pixels since one of the other columns had a long content and the detail line got "stretched".  Even though I have the subreport element's Stretch Type set to "Relative to Band Height", the subreport element is still only 20 pixels.  I end up with "merged" cells which mean the spreadsheet is not unsortable - yuck.

Another item that has two accessories show's two lines (each 20 pixels) and they caused the main report detail line to stretch to 40 pixels to contain these two lines - that's good, but I again have merged cells.

I'm thinking maybe I should abandon using a subreport and instead use a scriptlet to look up my accessories and concatenate them into one long variable value and use that variable in place of the subreport.

Recommendations?

Thanks in advance.

James Martens

[tomsimmons]

Hmmm, I'm quite surprised there has been no answer to this topic.

Surely there must be someway of making a subreport stretch to the tallest object or band height?

 

Tom

[kingsteff]

very difficult to understand ... the problem of yours.

 

two things:

first:dont set the cells to strech! set the cell(row) height to the same as in your subreports. then its gonna fit the lines.

second: try to avoid subreport! try to think of a better way to gather all informations in one single query.

 

hope that helps...

 

steff

[tomsimmons]

Hi

Thanks for your reply.

I know it's not easy to follow, wasn't easy to explain either :)

I've attached another screen shot showing the report in iReport.  The prroblem is that the long description field can be pretty big, to handle it overflow is enabled which causes where necessary to becomes 2+ lines deep.  On a PDF or similar output this is not a problem, but I need Excel and that results in the problem you see in the first posts attachment.  The subreport I can guarantee will always only be one row high, it is a column report actually. 

The band is currently the same height as the subreport, but grows when the description fields grows.

I can't see another way that the data could be gathered other than with a sub report, sadly.

 

Tom

 

[kingsteff]

hi tom,

as i did my xls reports, i come to some conclusions.

1. excel is not as easy to handle as pdf. so row and band height and width must be aligned and adjusted ver very carefully.

the best well be to work with the absolut pixel count.

2. why strech with overflow is a bad thing when it comes to excel? see 1. :) you can not say what the exact height is when the engine streches the cell. better is to disable this function and set the "net.sf.jasperreports.print.keep.full.text" to TRUE, for each cell. so the original dimension will not be changed and the content is complete.

 

the jrxml file in your designer mode in ireport must look excactly like the outcoming xls file!

 

that was the only way i fixed my excel export problems.

in your case i would half the first row height ,so that everything in the first row has the same height.

just give the subsystem column a little more width.

then disable for all fields the strech with overflow and insert the propertie i mentioned above.

 

that should do it

 

good luck

 

steff

Post Edited by kingsteff at 05/12/2009 14:06

Post Edited by kingsteff at 05/12/2009 14:08

[tomsimmons]

Thanks for your reply.

I'm looking now for information on the net.sf.jasperreports.print.keep.full.text option.

I don't suppose you have a link to a handy explantion/example of how to use it?

 

Tom

[kingsteff]

fyi

 

jasperforge.org/uploads/publish/jasperreportswebsite/JR%20Website/jasperreports_configuration_reference.html#net.sf.jasperreports.print.keep.full.text

[tomsimmons]

Thank you for your help, I have now been able to complete this report.

A note to others:

I found info here...

http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=35245

The point that I initially missed in the second post is that you only need to include the information in one place.

Also, for those using iReport, under the Edit Menu, you'll find Custom Properties, when you click New here, you'll find this setting in the Special Meaning Properties list, double click and add true as the property value, amke sure you've turned off all the over flow stuff and when you excel sheet loads you'll find if you expand the column all your text is present.

 

Tom

 

[ldcl289]

 if I understand you, it happend to me, but, you can't do it with subreports, at least, I couldn't

So, what I did was take the query of the subreport and put it in the query of the original report, something like that:

father report query

SELECT p.id AS 'id', ...*

FROM permiso AS p

...

Subreport query

SELECT *

FROM permiso AS p

....

WHERE p.id = $P{id}

GROUP BY p.id

 

result

SELECT p.id AS 'id', ...*

FROM permiso AS p

    INNER JOIN (SELECT p.id AS 'id', *

         FROM permiso AS p

....

         GROUP BY p.id) AS subreport ON subreport.id = p.id

 

And put the fields of the subreport in the original report, it was the only way that make this field "tallest to the largest object"