Using SQL db with Excel spreadsheet

[Former Member]

I have had success in the past using my SQL db with an Excel spreadsheet for additional data in Crystal Reports.  However, I have a new report which uses an Excel spreadsheet that is quite lengthy, so it processes very slowly and then crashes.

Are there any general tips regarding the design of the report which could help with this issue?  Example:  The order of formulas in the Select Expert (does it matter which db is accessed first, etc.)  Or data link tips?

Any other thoughts?

Your help is appreciated.  Thanks.

[Former Member · Accepted solution]

Hello Warner,

Your using two datasources right? one is My SQL DE and another one is Excel spreadsheet.

Try to create subreport using one of the datasource and then insert subreport into  the main report.

Please share more information.

Thanks,

DJ

[Former Member · Accepted solution]

One datasource is off of SQL server 2008 (not My SQL) and the other is an Excel spreadsheet.

[Former Member · Accepted solution]

Hello warner,

You can convert excel file into .CSV  file format and then the report processess very fast....

Try this one give more updates...

Thanks,

DJ

[Former Member · Accepted solution]

This csv gives me a different issue.  I need to link one of the fields "Patient Number" to "Patient Number" in my SQL database.

When I try to link to the csv field, I get an error saying the data type is incompatible.  My SQL database field is a string whereas the csv field is read as a number.

I was able to do a workaround in my original Excel sheet by adding letters to the first few rows of data in order to "fool" Crystal that this was a text or string field, not a number.  However, this "trick" is not working with the csv file - Crystal stills sees this as a number.

[DellSC · Accepted solution]

If you put quotes around it in the CSV, Crystal should see it as a string.

-Dell

[Former Member · Accepted solution]

Since I have over 13,000 records in my csv, I added quotes to the first few records, still says data is incompatible (shows a number).

[Former Member · Accepted solution]

Hi Warner,

You can convert SQL database field as number then how?

Convert(number,required field) as xxxx     or  Cast(required field as number) as xxxx

Any problem occurred regarding this conversion.......Please update

Thanks,

DJ

[Former Member · Accepted solution]

Unfortunately, our company does not have authorization to change anything in the SQL database structure.  So, any changes has to be on the Excel or Crystal Reports side.

[Former Member · Accepted solution]

Hello Warner,

If you donot mind can you share the excel file . Is it possible ?

Thanks,

DJ

[Former Member · Accepted solution]

Unfortunately, no.  It has patient medical record information.

Maybe there is another way I can run this report.  Could I run one report from SQL and possibly run a new report or a sub report against the Excel sheet so they wouldn't have to process at the same time?

[Former Member · Accepted solution]

Warner,

What i exactly told my first post....You can proceed this way .

First main report process then after  the subreport checking all required links from main and executes based on section. you can put the subreport in report header then this executes. CR follows a top-to-bottom, left-to-right processing approach.

Pass a parameter from main to subreport like this follows:

Right-click the sub-report and select 'Change Sub-report links' -> ok

Subreports will (most of the times) slow the report

Thanks,

DJ

[Former Member · Accepted solution]

Before I tried to create a new report with subreports, etc., I wanted to try again the csv report route.  This time, I put quotes around the first "8" records (instead of first 5 records), and this worked!

Now the report is running much faster and is not crashing.

Thanks again for everyone's help!