Using Excel as a Datasource

[Smithsc]

Is it possible to publish a CRXI report which has an excel spreadsheet as it's datasource onto BOE. I've tried to do this and keep it keeps failing with an error message The database logon information for this report is either incomplete or incorrect. .
The report works when I run it directly from crystal so I know the problem lies with BOE.
I've looked at the CMC and the datasource server and database both read W:\Bordereau\Stuart Test Rent Profile Data.xls. Is this correct?

The type of path I've used before is //elsprod01/users/finance/ssmith/Bordereau/Stuart Test Rent Profile Data.xls but I'm not sure which part to use for the server and which part is the database.

Any help would be greatly appreciated.

Stuart.

 

[hilfy]

Use the UNC path, the "W:\" path is not mapped on the report server. Use the entire path for the database, leave the server blank.

Also, you need to check how your BOE system is set up. The default install runs the services using the Local System account. This account does not have access to security network resources. So, BOE probably doesn't have access to the folder where the Excel file is located. Best practice is to create a "services" ID on the network that will be used for running BOE. This account should be set up so that the password never expires. Then, in the CCM, stop all of the servers (for XI 3.0 and above, this is just the Server Intelligence Agent, for r2 and below it's ALL of the servers listed) go to the properties for each. Under "Log In As", turn off "Local System" and enter the network ID.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[NickieP]

Under the Database menu option there is a Set Datasource choice. Click on it and then select said d/b.View properties.
where it says W:\Bordereau\Stuart Test Rent Profile Data.xls select 'convert to UNC' it will then say //elsprod01/users/finance/ssmith/Bordereau\Stuart Test Rent Profile Data.xls
save(all slashes will probably point the same way!)
resave to CE, it will run fine from CE now. Get into the habit of changing all xls files to UNC in case you ever do send the report to CE later.I have many s/sheets as my datasources linked to other s/sheets or ODBC's running through CE - and this problem is always fixed by just doing this.

 

[Smithsc]

Thanks for your help. I have changed converted the datasource choice to UNC and saved it to BOE. Unfortunately I'm still getting the same error.
I've looked at the database settings in the CMC and I'm using the 'Custom Database Logon'. The Database Type is set to ODBC and the server / database are both set to \\ELSPROD01\users\Finance\ssmith\Bordereau\Stuart Test Rent Profile Data.xls.

All the BO servers are located on a seperate box 'elsprod13' and I've set up an ODBC record on there for this spreadsheet. Could the problem be with this ODBC link?

 

[NickieP]

If the Excel file is in a folder that has any access restrictions on it, you need the CE server added to list of authorised users for that folder.
I access at least 30 .xls files in CR & CE, sometimes joined to 'proper d/b', sometimes not, but I never set my xls source files up as ODBCs. When I add a table to the report I choose the MS Access/Excel option
even if I want to join to a proper database like our WMS system or ops system. Worth trying to swap datasource (same table)from ODBC to Excel.