Report Guidelines: Exporting Reports

This article is day four in a week of reporting articles.

Most users want to export reports to Excel so they can manipulate the data. If possible, set up the report for the cleanest exporting possible. This process starts when writing T-SQL. For example, one can extract only the date instead of a datetime value when time is not relevant.

As I mentioned in a previous post, if you are creating a VFP report for M2M only the fields called out in your prg file will export to Excel. Therefore, do not pull them in via the data environment or your export will be missing items.

SQL Server Reporting Services and Crystal Reports will export many formats including PDF, Excel, Word, HTML web pages, and several text based formats. Excel, in my experience is the most common export format followed by PDF. In Crystal, there are two basic options for exporting to Excel, standard and data only. Most often I use standard, but if default options are used the results will often be lacking. The problem is that Crystal will merge columns and rows in illogical ways and this makes data analysis impossible.

The answer is to select a different column width option. Depending on your report, you’ll most often use a Group Header, Footer, or the Details section to determine your column widths.

Set the Export Properties.

Set the Export Properties.

Keep in mind that your users will not remember to change this setting when exporting, so there is an option to save these settings with your report. Choose File –
Export – Report Export Options to change these options. These options are saved with the report and it will default to those settings for the users.

I always create these settings even if the user says they will never export it. Invariably, someone will come to me in the future and complain that the export doesn’t work.

As an aside, I have talked in the past about Excel Hell and often that hell is caused by spreadsheet exports from M2M or other ERP systems. For example, some companies want to keep track of their Sales Order Backlog or Inventory over time, but these figures are a snapshot. Therefore, an employee is assigned the task of running the corresponding report, exporting it to Excel, and keeping track of it on the network. This behavior becomes so ingrained in some users that they have difficulty abandoning it.

For example, I posted code to facilitate Cycle Counting. The code automatically exports cycle count information to a separate table to be kept forever. After I had demonstrated the capability for the user who requested the project, he actually said to me, “Great, so I can export this to excel and….”

Oh, how they love to export. Tomorrow I will discuss deployment.

2 comments to Report Guidelines: Exporting Reports

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>