Crystal Subreport - Export to Excel issue 1

[RhythmAlways]

I am new to Crystal and am wondering if anyone could help....I have a report with two sub-reports, all on one row of my master report. This looks great in Crystal, but when I export the report to excel-data only (which is mostly what the report is used for) the sub-report comes out on a separate row.
I was going through several other threads posted and found two solutions related to my Problem

1) Attaching all the row objects to Guidelines, Align Bottoms and make everything os the same height -> I tried this option and spent lot of time on doing the exact formatting. However, this didnt work for me
2) Saving the values in shared variables and using the shared variable formula in the subreport -> This worked fine. However, when exporting to excel it inserts three blank rows between each record. I really need to have all the records together since users are going to do a lot of excel formatting and conversion on this.

Any help will be highly appreciated?

 

[lbass]

Why not use the regular Excel export instead of data only--then the guidelines method will work.

-LB

 

[kenhamady]

The techniques you tried are not for DATA ONLY exports, they are for Regular Excel exports.

As it happens, I was just testing this yesterday. I could not find ANY way to get a subreport value to export on the same line as the rest of the data as long as I used the "Data Only" option. I had to use the regular Excel option with the techniques mentioned above.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[RhythmAlways]

The end users need to do several operations on the excel once it is exported.A regular excel report does not work since it does a lot of column merging.

 

[kenhamady]

You can usually avoid the row and column merging if you are careful about alignment and positioning. This link might help with that.

http://kenhamady.com/cru/archives/231

http://www.kenhamady.com/news0506.shtml

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[MarcMolepske]

Did you try exporting it as Tab separated text file? Then open it in Excel? I have been able to get the reports to line up doing that.
-Marc

 

[SkipVought]

There are some pitfalls related to opening a TEXT file directly by Excel. Excel trys to be helpful and can CONVERT, that is CHANGE, some data. Some of that is probably what is intended, because if you have a date in your text file, remembering that it is JUST A STRING OF CHARACTERS, Excel 'recognizes' or 'assumes' that this STRING is intended to be a date and CONVERTS it to a NUMBER and FORMATS the display as a date.

For instance here's a .csv file data
[tt]
Date1,Date2,Date3
2012/08/31,8/31/2012,31/8/2012
[/tt]
Here's the data as OPENED in excel

Date1       Date2      Date3
8/31/2012   8/31/2012  31/8/2012

where Date1 & Date2 are REAL DATES and Date3 is JUST TEXT! Notice that m/dd/yyyy is Excel's default FORMAT position, regardless of the structure of the source data. This is reflected by the fact that then d/m/yyyy structure is an INCORRECT input structure.

I will be able to defeat that problem, if I use the IMPORT EXTERNAL DATA feature, by IMPORTING the .csv data into an EXISTING Excel sheet. I can discretely specify that the import parsing spec for column A is YMN, column B is MDY and column C is DMY.

Another problem comes with data that is really TEXT but contains ALL DIGITS. This sort of data is a category that we might call IDENTIFIER (Part Numbers, Invoice Numbers, ZIP Codes etc) where the LENGTH of the string might be significant or leading ZEROS might be significant, and that would NEVER be used in math. When you OPEN the .csv with this kind of data, the DIGITS are converted to NUMERIC data and store that way. When you IMPORT, you can specify that column of data to be TEXT, and it will be maintained as TEXT.

Further issues can affect data that APPEARS to be Scientific Notation, like 171E2. In one of my systems, this is a Machine Identifier. When I OPEN in Excel it CONVERTS this value to the NUMBER, 1.71E+04 or 17100. Again, using the IMPORT feature, I can specify the import type of the column to be TEXT.

BOTTOM LINE: BEWARE opening text file directly in Excel. You can be bitten!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]