Problem using "data-only" excel option

[ghbeers]

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.

 

[hilfy]

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.

-Dell

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

http://www.decisionfirst.com

 

[ghbeers]

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.

 

[hilfy]

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.

-Dell

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

http://www.decisionfirst.com

 

[Charliy]

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.