Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Problem using "data-only" excel option

Problem using "data-only" excel option

Problem using "data-only" excel option

Business Objects users are accessing a crystal report I created, and are having a problem when downloading the results to Excel using the data-only option. One of the columns of data is shifting to the left, although the column headers are not shifting. But when I download it using the plain Excel option, this is not happening. Most of the rows in the column that is being over-written by its adjacent column are null. When the data-only option is used when executing the report in Crystal Reports, the shifting is not occurring.

Has anyone else experienced this problem and determined what could be causing the problem?

Thank you.

RE: Problem using "data-only" excel option

The problem is because of the null data. The easiest thing to do would be to use a formula instead of a field in the column so that you have some value - even just a space - to tell Crystal to export that "cell". If the value is a string, the formula would look like this:

If IsNull({MyTable.StringField}) then ' ' else {MyTable.StringField}

If the value is a number, you'll need to convert it to text like this:

If IsNull({MyTable.NumberField}) then ' ' else ToText({MyTable.NumberField}, <formatting options>)

NOTE: ToText will use your default number format. If you want to change that, see the Crystal Help for the formatting options. If you want to keep that format, take out the comma and the formatting options.

If the value is a date or a time, you'll use ToText as for the number but with the appropriate formatting options.

Use this formula in the column instead of the field.


DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

RE: Problem using "data-only" excel option

I was finally able to try your suggestion Dell, but the results were the same when I used the data-only Excel download option. The Excel option is still not causing the column to shift. Is there possibly something else I can try? The column that is shifting is a text field. The column that is being overwritten is a text field. I even added the ISNULL cmd to the text value in the column to the left of the one that is being overwritten. Frustrated by this! Thank you for your help.

RE: Problem using "data-only" excel option

Make sure that your formula has an actual space between the quotes for the null value. Another option might be to turn on "Convert Database NULL Values to Default" in the Report Options on the File menu and use the field on the report. NOTE: This will have an affect on number and date fields that might have null values in your report, so it may or may not work appropriately for you.


DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

RE: Problem using "data-only" excel option

If you have columns of data that do not have headings, then you need to place objects (a blank text object will work) in the empty space. I always line them up and make then the same size as the data field.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close