Crystal Reports
--
Questions
--
Followers
Top Experts
Crystal Reports - messy export to Excel
I am creating a crosstab that will be exported to Excel. Â
FILE >Â EXPORT >Â EXPORT REPORT >Â Microsoft Excel 97-2000 (XLS)
PLEASE NOTE: I cannot use Microsoft Excel 97-2000 - Data Only (XLS) because there is conditional formatting that must be preserved
ATTEMPT 1: I found that exporting it using all the default options as above creates a very messy spreadsheet with extra empty columns and rows between the data rows and columns. Â My report has no detail or headers apart from a single "Report Header" which is where the crosstab lives.
ATTEMPT 2: I changed the grid size to 0.5cm and aligned all the columns with the grid. Â After re-running the export, I discovered that some of the extra rows and columns were now gone. Â There were still, however, some places where Crystal placed a data column across two or more Excel columns
ATTEMPT 3: I changed the Export Format Options to "Constant Column Width (in points) ". Â THIS WAS PERFECT! except for the fact that the report that will be run using "Crystal Reports Server XI Embedded" on a SABA system (SABA is J2EE/Oracle/IIS). Â Because it is run using this web-based interface, the users have limited options for exporting the report. Â They can select the File Format and Page Range only.
ATTEMPT 4: I made the columns very narrow so that each column was 1 grid width. Â After an export, the user can always widen the Excel columns to suit. Â I tried the default export again, PRETTY GOOD (ie no extra blank columns) except now some text in the cells were truncated because the cells were too narrow.
ATTEMPT 5: I made the columns 2 grids wide. Â No more truncation of data, but the extra columns are back.
ATTEMPTS 6-99: Tried adjusting the grid size and column width with trial-and-error and finally got something that looked perfect. Â On closer inspection, I saw this:
        |   x    |   y    |   z    |
--------------------------
|   a     |   val1  |   val2  |   val3  |
--------------------------
|   b     |   val4  |   val5  |   val6  |
--------------------------
|   c     |   |   |   |   val7  |   val8  |
--------------------------
|   d     |   val9  |   val10 |   val11 |
--------------------------
Rows a, b and d are good: one excel cell per column/row. Â Row c has no value in column x, so Excel has inserted 3 cells instead of one.
Very frustrating!
Also, the crosstab export creates column headings that repeat on each "page" even though I am not using page breaks on the export.
FILE >Â EXPORT >Â EXPORT REPORT >Â Microsoft Excel 97-2000 (XLS)
PLEASE NOTE: I cannot use Microsoft Excel 97-2000 - Data Only (XLS) because there is conditional formatting that must be preserved
ATTEMPT 1: I found that exporting it using all the default options as above creates a very messy spreadsheet with extra empty columns and rows between the data rows and columns. Â My report has no detail or headers apart from a single "Report Header" which is where the crosstab lives.
ATTEMPT 2: I changed the grid size to 0.5cm and aligned all the columns with the grid. Â After re-running the export, I discovered that some of the extra rows and columns were now gone. Â There were still, however, some places where Crystal placed a data column across two or more Excel columns
ATTEMPT 3: I changed the Export Format Options to "Constant Column Width (in points) ". Â THIS WAS PERFECT! except for the fact that the report that will be run using "Crystal Reports Server XI Embedded" on a SABA system (SABA is J2EE/Oracle/IIS). Â Because it is run using this web-based interface, the users have limited options for exporting the report. Â They can select the File Format and Page Range only.
ATTEMPT 4: I made the columns very narrow so that each column was 1 grid width. Â After an export, the user can always widen the Excel columns to suit. Â I tried the default export again, PRETTY GOOD (ie no extra blank columns) except now some text in the cells were truncated because the cells were too narrow.
ATTEMPT 5: I made the columns 2 grids wide. Â No more truncation of data, but the extra columns are back.
ATTEMPTS 6-99: Tried adjusting the grid size and column width with trial-and-error and finally got something that looked perfect. Â On closer inspection, I saw this:
        |   x    |   y    |   z    |
--------------------------
|   a     |   val1  |   val2  |   val3  |
--------------------------
|   b     |   val4  |   val5  |   val6  |
--------------------------
|   c     |   |   |   |   val7  |   val8  |
--------------------------
|   d     |   val9  |   val10 |   val11 |
--------------------------
Rows a, b and d are good: one excel cell per column/row. Â Row c has no value in column x, so Excel has inserted 3 cells instead of one.
Very frustrating!
Also, the crosstab export creates column headings that repeat on each "page" even though I am not using page breaks on the export.
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Very persistent! I'd have given up log ago!
One additional thing you can try is to ensure that there is no space at all between the objects on each line.
So use guideline markers to attach each label/text object at both ends to ensure there is no gap
One additional thing you can try is to ensure that there is no space at all between the objects on each line.
So use guideline markers to attach each label/text object at both ends to ensure there is no gap
Thanks peter57r. Â There is no space between the objects that I have any control over. Â There is only ONE object in the entire report and it is a crosstab.
mlmcc, I found that document in the knowledge base and it was somewhat helpful. Â I followed the instruction, used guides to try aligning columns to guides. Â What I found is that using the guides was exactly the same as aligning the columns to a grid.
Here's a new question for the experts. Â In my original post, I described the attempts 6-99 which yielded an almost perfect result with all columns doing exactly what I wanted EXCEPT FOR the cells that contained no value. Â ie. the crosstab has cells where the summary field is undefined, and I assumed that these cells are NULL. Â I tried to create a formula for the summary cells that would set the value to "-" if the cell was NULL, but this didn't work.
If there is a way for me to assign "-" to the blank cells (and therefore there are no blank cells left in the crosstab) then I can wrap this up. Â I have read through the whole crosstab section of the CRXI book (the one subtitled "The ONLY Crystal Reports XI book you need", if only that were true) but couldn't find out how to do it.
I have tried going back to the original datasets and doing a full outer join so that there are no undefined cells in the crosstab, but this has made the report really slow.
mlmcc, I found that document in the knowledge base and it was somewhat helpful. Â I followed the instruction, used guides to try aligning columns to guides. Â What I found is that using the guides was exactly the same as aligning the columns to a grid.
Here's a new question for the experts. Â In my original post, I described the attempts 6-99 which yielded an almost perfect result with all columns doing exactly what I wanted EXCEPT FOR the cells that contained no value. Â ie. the crosstab has cells where the summary field is undefined, and I assumed that these cells are NULL. Â I tried to create a formula for the summary cells that would set the value to "-" if the cell was NULL, but this didn't work.
If there is a way for me to assign "-" to the blank cells (and therefore there are no blank cells left in the crosstab) then I can wrap this up. Â I have read through the whole crosstab section of the CRXI book (the one subtitled "The ONLY Crystal Reports XI book you need", if only that were true) but couldn't find out how to do it.
I have tried going back to the original datasets and doing a full outer join so that there are no undefined cells in the crosstab, but this has made the report really slow.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I eventually made the report work through more trial and error. Â I was using EE to see if there was a feature of CRXI that I didn't know about. Â Thanks for your suggestions anyway!
What I did to fix it was change the grid setting to 1cm, placed a guide every 2cms on the horizontal ruler, lined up the crosstab columns with the guides. Â So far, this is no different to what I had tried before. Â
What I did next was to create a textbox exactly 2cm wide and 1cm high. Â I copied and pasted this textbox at each of the guides so that each textbox was lined up exactly with the crosstab column. Â For some reason, this did the trick. Â The export seems to ignore the cell alignment/sizing within the crosstab itself, but cannot ignore the textboxes placed outside the crosstab. Â
What I did next was to create a textbox exactly 2cm wide and 1cm high. Â I copied and pasted this textbox at each of the guides so that each textbox was lined up exactly with the crosstab column. Â For some reason, this did the trick. Â The export seems to ignore the cell alignment/sizing within the crosstab itself, but cannot ignore the textboxes placed outside the crosstab. Â
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Crystal Reports
--
Questions
--
Followers
Top Experts
Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.