How can I get a crosstab with multiple column headers to export to Excel correctly?

[johnhzero]

I have a report with a crosstab that I'm trying to export to Excel (using Data Only). The problem is that the column headers don't align with the data for those columns. It seems to create an extra column for the headers which means that there's 3 columns for each header even though there's only 2 columns for the data.

The crosstab is setup as follows:

Crosstab Columns:

Col1

Col2

Col3

Col4

Crosstab Rows: Row1

Summarised Fields:

Max of @Formula1

Max of @Formula2

In the Group options Col1, Col3, and Col4 are using Customize Group Name Field with a formula. Col2 is using Customize Group Name Field with an Existing Field selected, but If I switch that off for all of them then I still get the problem).

In Customize Style, each column has Suppress Subtotal selected where possible. Col1 and Col2 have Suppress Label checked as well. All the other suppress options are selected as well.

We have a similar crosstab directly below in a sub-report with all columns suppressed. The width of the column all match.

If I remove all but one column header then it exports fine but as soon as I have multiples then the headers go out of alignment.

We use CR2008 for .Net (v13) to produce reports and I'm trying the trial version of CR2013, but I get the same problem in both versions.

Is there anyway to get the column headers to export with the correct alignment?

Thanks,

John.

[johnhzero]

I've also come across another problem where a field placed in the top left corner of a cross tab (in the area that would otherwise be empty) is output below the table in the Excel file instead of putting it in the equivalent position.

I'm presuming that there's no solution to this other than not putting a field there?

[abhilash_kumar]

Hi John,

Could you paste a screenshot of the crosstab please?

-Abhilash

[johnhzero]

Here's a screenshot of how it looks in the viewer:

Here's what it looks like in Excel:

Here's what it looks like in the designer:

[abhilash_kumar]

To be honest, a Crosstab is probably one of the things that exports cleanly to Excel, especially with the Data Only option.

I always use the following settings when exporting a Crosstab report to Excel (Data only) and most of the times it looks clean:

- Uncheck 'Simplify Page Headers'

- Check 'Export Object Formatting'

Could you try setting these options and let me know if it helps?

-Abhilash

[former_member183750]

See  if this doc will help a bit:

http://scn.sap.com/docs/DOC-39608

- Ludek

  Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[johnhzero]

Unfortunately, switching off 'Simplify Headers' and switching on 'Export Object Formatting' doesn't seem to help:

Thanks,

John.

[johnhzero]

I'm not sure that I believe this bit of the guide:

1 If at all possible, use a Cross-Tab. They’re guaranteed to export well without any special formatting.

If we change the crosstab to only have one Summarized Field then the headers come out correctly but then we're obviously missing some data in the table. If we only have a single visible header row then the headers line up okay as well but we're missing the header info.

I might try combining all the header rows into a single row with a formula and then post-processing the Excel file to covert the lines into separate rows in Excel.

Thanks,

John.