Tip of the Month
Fixing Crystal Reports to Excel Export Formatting
A student recently wrote about a question that puzzles many Crystal Report designers. After placing a subreport into a detail section of a Crystal Report, the subreport data appears on a new line after exporting the Crystal Report into Excel. The problem is alignment of the subreport object on the main report.
Starting with Version 9 of Crystal Reports, the same "engine" or software used for formatting the Crystal Report presentation is used to format the exported Excel (or Word or Adobe PDF) document. In Version 8.5 and below, Crystal Reports would look at all fields and objects such as subreports to determine if they were properly aligned. If they were they would all be exported to the same row in an Excel spreadsheet. If not, they may appear on different rows. The default row height and column width of the Excel spreadsheet would be used. This lead to very long field data appearing over top of multiple columns within a spreadsheet and improperly design reports splitting a single row of information onto two or more rows within the Excel spreadsheet.
Version 9 and above make a very serious attempt to deliver a WYSIWYG (what you see is what you get) presentation into an exported document. Nonetheless, data that appears to be on one row in the Crystal Report will now appear in multiple rows within the exported Excel document. The difference is that the row heights in Excel will be adjusted so that the spreadsheet appears much more like the original report.
To accommodate the ability of the export "engine" to deliver one row of information in an exported document for each row of information in a Crystal Report, try these steps:
Select File | Options and from the Layout tab enable guidelines in Design mode. Also, turn off (disable) Snap To Grid. This will allow you to smoothly drag fields and objects into new positions.
Switch to design mode and set the zoom to a level where you can clearly see the fields and objects.
Temporarily expand the section in question by dragging the bottom section divider down a substantial distance (about one inch or more).
Insert a horizontal guideline by moving your mouse over the vertical ruler (left side) and clicking. Insert the guideline well below the fields and objects but within the same section.
Select all the fields and objects in the section by holding down the control key and clicking once on each of the objects.
Still holding down the Control Key, find the object which has a height that is adequate for the section you are in and click on it two times. This will give that object focus. In other words you will see little handle-bars appear on each side of the object.
Stop pressing down on the Control key. Use the Format | Size menu command to set the Height of the objects to the same height.
To align the base of the text of all the fields and objects, click and drag any one of the selected and highlighted fields. Drag it toward the guideline which you inserted. If your fields become unselected, you can drag each one down individually.
When the field stops and "snaps" onto the horizontal guideline, you are finished aligning the fields. There will be a gap between the fields and the top section divider. Click on the guideline on the vertical ruler and slide it up as far as it will go in that section.
In the same section, right-click on top of the grey section divider and select Fit Section. Instead, you may grab the section divider at the bottom of the section and slide it up until it stops.
You should now be able to export successfully while preserving the individual rows within your Crystal Report.
|
