In one of my recent client engagements, there was a requirement to replace Microstrategy reports to Crystal Reports. The business users were not ready to compromise on the format of Excel. I created a crystal report with Microstrategy SQL and used InfoBurst – Infosol’s flagship bursting product to deliver these excel files. I created this simple, straight forward crystal report with SQL query, exported the crystal report to Excel and sent it to the user for feedback. There were at least 20 emails going back and forth on the format of the Excel. Controlling the Excel format via crystal reports was not an easy task – designing a Crystal Report so that it exports well to MS Excel requires a little bit of planning and a lot of precision. There are several blogs on this topic which helped me through this process and I thought I should consolidate them through my experience with this report.
- The major problem that I faced was the merge cells issue. It’s hard to notice that some of the excel cells merge. Due to this merge, when the user tries to sort a column you get the error shown below:
The user could do a select All -> Unmerge cells and this can be resolved. But the current Microstrategy output doesn’t require this extra step.
- Alternate row coloring was to be followed for the Excel cells. I did implement this requirement using formulas in section expert, but this gave alternate colors for the entire excel which the user did not like. They wanted alternate colors just for the rows where data exists and rest of the excel in white. In other words, row color formatting should be limited to actual print area.
- When the user prints in Excel, the default printer orientation should be in Landscape mode.
- They wanted a report name and a description in the report header section
The following tips & tricks will help you in providing a perfect/clean export to Excel from Crystal Reports
Tip 1: All of the objects in a row MUST have data in them. If there’s a single objects that has a blank value, it creates a blank cell in excel which gets merged. This issue can be resolved at the query level or you can create a formula as shown below. In this example, if a customer doesn’t have a phone number, a blank space will be inserted in the report and hence the cells will not be merged when exported in excel.
If it’s a numeric field set the default values so they always show up as zeros.
Tip 2: Remove unwanted sections in the report. If a section is not being used either suppress or delete it.
Tip 3: Avoid spaces between objects. If there’s any space between objects, when you export to excel the space will be considered as a blank cell and the data will be shifted randomly.
Tip 4: Turn on the Grid & Guidelines in the design mode
Use the guide in the ruler at the top of the report to mark the left AND Right of the column. Using the guide, start the next column at the exact same spot where the previous column ended.
Tip 5: Since we are manually placing the objects, they might not be aligned at the top. Make sure that the objects are placed at the very top of the section – right click on an object -> select “Size and Position” -> set the Y Co-ordinates to 0.0. Then align all the other objects in the row with the top of the one just set. Also move the bottom edge of the section up to the bottom of the objects in the row.
Tip 6: Make sure the data and column header in each column line up exactly on the left and that each of the objects is the same width. Set up guides on the ruler above the report to help with this.
The above tips are just the basic ones. Stay tuned for my next blog post in which I will explain more complex ones about alternate row colors and printing options.