MS Excel 97-2000 Data Only
The reason most people want to export to Excel files is so that the end user can work with the data, create custom formulas, and perform their own analysis within the spreadsheet. In fact, many people find that all the additional formatting provided by Crystal Reports gets in the way of them doing this analysis. In these circumstances, it is ideal to just export the raw data to Excel and leave out all the extraneous information altogether. Crystal Reports lets you do this by giving you a data only option for exporting to Excel.
When choosing the data only export option of exporting to Excel, you are shown the export option dialog box shown in Figure 15-6.
Figure 15-6. Excel data only export options (from Crystal Reports XI R2).
For pure data analysis you want to turn off all formatting possible. But depending upon the circumstance, some users might want some formatting included so that the data is easier to read and understand. It’s your job to find the right balance of how much formatting should stay and how much should go. The export options in this dialog box are primarily concerned with how much formatting you want to exclude from being exported.
Let’s start out by looking at the different formatting options at the bottom of the dialog box. This lists the details of what you can and can’t include. Each of the options are listed below with a description of what they do.
The Export Object Formatting option lets you keep the formatting for each report object. This applies to the object’s font, background color, etc.
The Export Images option includes images in your report.
The option Use Worksheet Functions For Summaries, tells Crystal Reports to attempt to convert the group summary formulas to their equivalent formulas in Excel. The benefit is that when the user changes the data in the Excel spreadsheet then the summaries get recalculated. If you disable this option, Crystal Reports stores the actual formula result in Excel. It is a static value and will not get updated when the user enters new numbers on the spreadsheet.
Crystal Reports only converts summary formulas to worksheet functions when the summary is based on a database field. If the summary is based on a formula field, then this is too complicated for Crystal Reports to convert to an Excel formula and instead it just exports the summary result (the actual number). You also have to have the detail rows exported to the spreadsheet as well. If you suppress the detail section then there wouldn’t be any data for the spreadsheet to calculate a summary formula on. Also note that grand total fields are never converted to Excel formulas.
The option Maintain Relative Object Position looks at how objects are positioned to each other. It will use blank columns to adjust the spacing to be as accurate as possible. This has the potential to insert empty values into certain columns so that the spreadsheet layout matches the report. Depending upon the report formulas you use, this could cause empty cells to generate incorrect results.
The option Maintain Column Alignment looks at columns and their related summary functions and keeps them in alignment on the spreadsheet.
The option Simplify Page Headers just exports a maximum of one line per header. If the header has more than one line, then only the bottom line is exported. If this option isn’t selected, all the lines of the header are exported (but they are kept on a single row in the Excel spreadsheet).
The last option on the Excel Format Options dialog box is Show Group Outlines. This is only available with the R2 edition. When the report is exported, it includes grouping information for use with Excel outline symbols to make navigating the spreadsheet data easier.
Now that you understand how the formatting options work, let’s see how we go about selecting which ones we want. At the top of the dialog box are three options. They let you set how much customization of the formatting you want to apply. The first option exports with the default options enabled. These default options are Use Worksheet Functions for Summaries, Maintain Column Alignment, Export Page Header and Footer, and Simplify Page Headers.
The second choice, minimal exporting with no formatting only uses the option Use Worksheet Functions For Summaries. It is the bare-bones exporting of raw data.
The third choice, custom exporting according to selected options, lets you select the options at the bottom of the dialog box according to your own preferences. No pre-selected options are chosen and only the ones you click on are used.
