Importing data into Cognos via continuously updating Excel Report

[new2Cognos8]

Hi there,

We have an excel report that is updated regularly by our end users. The continuously updated information needs to be a source for a column in a Cognos 8 report.

Any ideas?

 

[BICentre]

Hello, yes you would have to set the .xls file as an ODBC connection and use that as your data source.




Wallace Taylor

http://bicentre.blogspot.com

 

[CognosProfessional]

Watch for locks, if you get locking, you need to put the excel file in an access db and set DDE...

Set up Access Database:

Create a new Access database in a location that is accessible by ReportNet and can access the Excel Spreadsheet.
In Access, from the File menu, click Get External Data, and then click Link Tables.
Set the File Time to Microsoft Excel, then find and select the spreadsheet to use. Set this up as appropriate The linked spreadsheet will appear in the list of tables.
Select Queries from the list of objects, and create a new Query. Set this query up as appropriate to return the data to use in ReportNet.
Click Tools in the menu, and go to the "Advanced" tab.
Clear the Enable DDE refresh and Open databases using record-level locking options.
Close Microsoft Access.
Set up ODBC Connection

On the ReportNet server (and also on the Framework Manager computer, if it's not the same machine), open the ODBC Connection editor (typically found in the Control Panel).
Create a new System DSN to access the Microsoft Access file. Set (and remember) the Data Source name, and select the Access database file created above.
Configure ReportNet

Log into ReportNet as a user capable of modifying DataSources.
In the Tools menu, click Directory. The directory will be displayed.
Click the Data Sources tab to display the list of datasources.
Create a new datasource. Type a name as appropriate, and select ODBC as the type.
Set the data-source name as entered above for the ODBC connection.
Test the connection, and click Finish.
ReportNet should now be able to access the data in the spreadsheet without locking the file.


CP