Configuring Excel DataSource for Universe-driven Read-Only Report

[Former Member]

Hello everyone, hoping someone out there can help me figure out this issue.

BOBJ 4.1 SP6

Design Studio 1.5 SP1 Patch 1

Excel 2007

I have a developer who wants to use an Excel spreadsheet to build a universe in order to create reports.  We have MS Excel 2007.  Yes, I know its not supported, and yes, I know its not recommended for various reasons.  However, I have a directive, and I'm unsure next steps.  So appreciate any guidance here on the forum.

What I've done:

1) Installed the 2010 Microsoft redistributable 64 bit on split web app server.

2) Added the spreadsheet and DSN for ODBC 64 on web app server.  Chose Excel 12.0 version for DSN on server. (Named spreadsheet and DSN same)

3) Added the same spreadsheet with same DSN for ODBC 32 on Local Developers PC using ODBC Administrator.(Named spreadsheet and DSN same as server)

4) Developer built and published connection and universe.

5) Developer designed report.

   a. Can successfully run locally on his PC

   b. Cannot see it "Execute on Platform" (Report shell appears - excel data not shown - Chart placeholder shows)

   c. Even with Administrator rights, cannot view in BI Launchpad (Report shell appears - excel data not shown - Chart placeholder shows)

Other tests (connection):

1) Created new spreadsheet, new DSN, with same name on both personal PC and web app server.

2) Using IDT, was able to log on to session, and saw my DSN in dropdown when creating a connection.

3) Built and published universe.

4) Opened Design Studio, selected the universe for DataSource.  Edited the Query, selected the objects/fields, refreshed and received error: [Microsoft][ODBC Excel Driver] External table is not in the expected format.

5.b.) Opened and created WEBI (web service) report, using same universe, upon refresh got error: Unable to connect to the InfoStore.

Note:

I've looked at numerous OSS Notes, and SCN tidbits, and I'm seeing conflicting information on how to resolve this, so at this point grabbing at straws.

>I've also tried adding both a 32 bit and 64 bit DSN on the server.  I've tried adding the same on both my servers.

>Also tried configuring the DSN path to reflect the relative network path instead of physical server location.  Note: after changing this to the relative network path, it is no longer available in the DSN list when checking the connection in IDT.

Any help is greatly appreciated.

[Former Member · Accepted solution]

I figured it out.  After I stopped all my split APS services, I fired up the Master APS Server.  Successfully ran the published report from the BI Launchpad!

[Former Member]

Hi Leah,

Take a look at

Regards,

Rogerio