BOE Central Management Console: Setup crystal report using Excel as data source

[sfultz]

Good Morning;

We have created a report that is using an excel spreadsheet and oracle database as data sources. It runs great on our local boxes, however, we'd like to set it up for user's to run in InfoView.

So far we have not been successful because we don't know how to correctly enter the server/database information or what to use as the user/password for the excel piece. We have entered the server/database pieces in various ways and with and without user/passwords.

Some pertinent information:
1. Excel file is saved as 97-2003 Worksheet format on a shared windows server directory, to which the BOE server does have access.
2. Report was created in Crystal 11.5

We haven't found any documentation or any forum postings that addresses this part of using excel as a data source. So any information or assistance will be greatly appreciated!

 

[SkipVought]

hi,

I can't give you any specific information related to CR.

But I can tell you that Excel Workbook SHEETS that are structured as TABLES (one row of unique headings in row 1) with ONE TABLE PER SHEET, can be accessed via a connection to the workbook and using SQL, with this caveat regarding the SHEET NAME using the sheet name followed by the DOLLAR CHARACTER and bracketed by BRACKETS []

FROM ['Sheet Name$']

The APOSTROPHY (single quotes) is only necessary when the sheet name contains one or more spaces.

Your ODBC Data Source Administrator in Control Panel > Administrative Tools > Data Sources (ODBC) should have a driver for Excel Files, that can be used for connecting to a specific Excel Workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sfultz]

Skip,

Thanks for the reply!

Not sure how to use that information in a crystal report, which is the tool we need to use. I believe we would still have the issue of being able to connect to the excel data source, but am not sure. Will need some feedback from the Crystal Reports community on that.

Thanks.

 

[BettyJ]

What is the Excel file used for? Can you give more insight into what you are trying to achieve?

 

[sfultz]

Every day, we have business users who receive excel files from vendors that they need to have bounced against our databases to pull various account information for reconciliation or verification. Currently, they send the files to my team, we load them into a temp tables, run queries on the data, export back to excel, and email the files back.

Our hope was that we would create a crystal report and a shared directory for each group where they would save their files, log into InfoView, run the crystal report against them, and then export the results into excel, all without daily handling on our side. Does this all make sense?

Again, any help in figuring this out will be appreciated!

 

[BettyJ]

I am sorry I have no solution to offer for your situation.

But in the past, we were trying to do something similar, with an Excel file which needed to be updated with an information (not available in the database) every time a new location is added. We were eventually able to do it by adding a section to the existing report and using formulas.

 

[SkipVought]

Every day, we have business users who receive excel files from vendors that they need to have bounced against our databases to pull various account information for reconciliation or verification. Currently, they send the files to my team, we load them into a temp tables, run queries on the data, export back to excel, and email the files back.

You could have a BO report (run on a daily schedule and exported as an Excel file) that exports the data required by the "excel files from vendors".

The "business users" would each have a workbook designed to AUTOMATICALLY
[li]query the exported BO Excel report when the workbook openes[/li]
[li]query the "excel files from vendors" [/li]
[li]combine to report back to the vendor.[/li]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

How are you connecting to the Excel file when run from the local machine?

If you are using ODBC, you just need to replicate the ODBC DSN from the local machine on to the server.

I don't have any way to test it but I seem to recall that if you use DAO rather than ODBC the full connection details become part of the report itself and therefore do not need to be set up on the server. You just need to be sure use the full UNC path in the connection to the spreadsheet, rather than drive mappings.

Hope this helps.

Cheers
Pete