Export to Excel data only - group header alignment

[BCarol]

In Crystal reports, exporting to Excel works fine. However, exporting to Excel data only has two issues:


Report looks like this:

jan feb mar
Tom 100 110 220

The months (jan feb mar) are in a Group header.

Problem #1
When exporting to Excel data only, the "jan" moves to the first column i.e. it appears above Tom. The feb moves above the 100 instead of above the 110.

I can solve it by having something in the first column of the group header, but I wonder if there is another solution.

Problem #2
If the Group heading has a second row like this:

jan--- jan--- feb--- feb---
Budget Amount Budget Amount

Tom 100 105 220 225


then:

When exporting to Excel, it shows the two rows in the group heading OK. However, when exporting to Excel data only, the two rows are shown in one row, with the second row shown in the columns to the right of the first row.

Anyone got some information on either or both of these problems?

I am using Crystal reports 9, but I don't think that matters.

Thanks.

 

[kray4660]

Problem #1 & #2. Exporting to Excel is tricky. I have not worked with enough to always get column headings to line up correctly. Since it does not 'know' where the columns belongs, it just lines up fields as they appear. So somehow you have to trick CR with a dummy label. I am thinking on the Group header level to have a label that the text color is the same as the page color.

Lots of luck.

 

[BCarol]

For Problem #1:

The dummy label works when exporting to PDF, etc, but unfortunately when exporting to Excel data only it does export that field.

I wonder if there is another way for Problem #1.

 

[Madawc]

I've found that a blank text-box in the header line will often fix it.

Putting another blank box on the right might solve your other problem, though I've never had that particular problem.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

I never use export to data only, and I'm wondering what you see as the value of doing this over the regular export--especially since you are running into problems with it.

-LB

 

[kray4660]

I do not know what BCarol's requirements are, but my clients are always asking to get their reports exported to Excel. Sometimes they are adding data to the report that is not in any database.

 

[lbass]

Yes, but you can use the regular Excel export for that--I was wondering about why people might prefer data only to the regular Excel export.

-LB

 

[kray4660]

For reasons that I do not know of, my clients prefer to to have unformatted output (of course column headings in the right place).

 

[IanWaterman]

As LB says export to excel doing a regular export.

Keep the report as simple as possible without any formatting or extra white space. ie no blank lines anywhere. Also tie all your cells together by anchoring to guides.

Then you can use blank text boxes as Madawc suggested to stop cell movement

Ian

 

[SkipVought]

Hi,

Here's an Excel-centric opinion: EXTREMELY biased!

Excel wants and needs to have a heading in every column in order to function as it should: a PROPER table.

NOT doing so, renders some of Excel's analysis and reporting features useless!

I literally dispise data in Excel that is a REPORT import -- missing headings, interspersed heading rows, empty rows, wrapped data (multiple rows of headings/data). It is nearly as worthless as a paper report in a file cabinet. Might as well export to Word!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hilfy]

lbass and IanWaterman,
There are many problems with the regular Excel export when you want to use Excel to slice and dice data - extra rows, merged cells, etc. They basically make is so that you have to do a lot of work to reformat things before you can even begin working with the data.

BCarol,
Is there any way you can do a cross-tab with your data? If you can, cross-tabs export VERY cleanly to Excel data-only. If not, I have a couple of suggestions.

For your group headers that are two lines, you can either make sure they're in a single text box instead of in two boxes, one on top of the other, or create a new group header section for the second row of them. If you do the first, you'll need a text box over the name that is the same height as the ones with labels in them. In that text box type a space, press the Enter Key, type another space. If you use separate sections, you'll need to put two text boxes, one in each section, each with a space in them. To export cleanly, ALL of your header objects need to be left-aligned with the data underneath them and the headers and data need to be the same width.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SkipVought]

AMEN! Dell!

If you want people to JUST LOOK at your pretty report, then do not export it directly to Excel.

If the purpose of the export is for users to actually USE THE DATA, then make an export of the DATA, not the REPORT!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

It is possible to design a report for export to regular Excel that does export correctly--without extra rows, etc., but thanks for explaining your rationales.

-LB

 

[BCarol]

We use the Export data only for when they want to take the report data and do further calcs.

I used Dell's (Hilfy) suggestion of an extra group header for the second row. That works well.

Thanks for all the ideas.