Question Re: Using SQL db with Excel spreadsheet in Technology Q&A

Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 14:58:55 GMT

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.

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 15:31:57 GMT

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,

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 15:38:51 GMT

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

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 15:43:04 GMT

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,

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 16:15:30 GMT

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.

Re: Using SQL db with Excel spreadsheet

DellSC — Fri, 12 Sep 2014 16:26:27 GMT

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

-Dell

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 16:59:27 GMT

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).

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 17:04:18 GMT

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,

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 17:08:42 GMT

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.

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 17:21:01 GMT

Hello Warner,

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

Thanks,

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 17:33:54 GMT

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?

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 17:56:36 GMT

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,

Re: Using SQL db with Excel spreadsheet

Former Member — Fri, 12 Sep 2014 19:26:08 GMT

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!