Home / Business Intelligence / Excel in “Crystal Reports Export to Excel” – Part 1
InfoSol Blog Tech Tip

Excel in “Crystal Reports Export to Excel” – Part 1

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.

Issues faced:

  • 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:

export to excel image 1

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.

export to excel image 2

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

export to excel image 3

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.

export to excel image 5

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.

export to excel image 6

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.

About Rajesh Ponnurangam

Rajesh Ponnurangam is a Senior Business Intelligence Consultant with Infosol. He is a SAP certified associate with extensive experience in design and development of Business Intelligence applications for apparel, retail, consumer goods and manufacturing industries. He specializes in Business Objects solutions, SAP HANA and BW data modeling. He has a proven track record in designing strategies for ETL, BI application development, implementation, rollout and support.

Check Also

A Gathering of BusinessObjects Super Heroes

A Gathering of BusinessObjects Super Heroes

Isn’t it amazing how most super heroes never seem to grow old? In fact, they ...

5 comments

  1. Hi Rajesh
    I want to set Dynamic Name in Crystal Report. Like when my Report is exported it should be with Date or DateTime Naming convention.
    Like
    X_report_15072015
    Please reply

  2. Hi Abhishek,

    Crystal doesn’t have the functionality to set dynamic report names when exporting. The Dynamic name can be set using infoburst macros. Infoburst can create dynamic file names as well as dynamic folder names/paths on your shared drive or BI platform. A macro titled [$DOCNAME]-[TODAY] will satisfy your requirement. Check out the link below and please let us know if you would like to know more about infoBurst.

    http://www.infosol.com/solutions/infoburst/

  3. Rajesh, Your article ” Excel in “Crystal Reports Export to Excel” – Part 1″ is excellent. Where is the hyperlink to your promised follow-up of part 2? If not yet written, when do you plan to post it? Thanks.

  4. Hi Dennis,

    Apologize for being late. Here’s the link for Part 2.

    http://infosolblog.com/crystal-reports-export-to-excel-part-2/

    Your feedback is much appreciated. You can also subscribe for our LetsSpeakBO webinar series where we discuss tips & tricks for BusinessObjects tools.

    http://events.infosol.com/lets-speak-bo/

    Thanks,
    Rajesh