[Solved] Using Excel as external Data for Reporting

[charon]

Hi Everybody :)

Im working on some reports for our MIS, and there is some data that exist on Excel only. Also, some of these informations are geting changed manualy from time to time.
So i figured to use the new feature of import external data. BUT: I am not able to open and edit the excel file once ive configured it to be a data source.
what i did:

1.created excel sheet that holds my data starting in Column/ Row A1
2. On the server, created a 32 bit odbc connection with system dms for the excel file
3. in cognos connection/ administration, added the new odbc connection as data source. test-> successful
4. in Framework manager, used the metadatawizard to import the new data source. test -> successful
5. mapped some information (product_id) from my excel data data and my db.
6. created a package and published it

Is there a way to edit the data in my excel sheet and update the reports based on the published package containg these excel data?

thx and cheerz :P
charon

[MFGF]

Hi Charon,

Once Cognos BI has connected to your spreadsheet, it holds the connection open for a set period of time (even when you are not using the connection) to reduce the overhead of having to re-establish the connection later on if you want to read the data again. It is this open connection to the spreadsheet that is locking it and preventing updates.

It is possible to reduce the connection pool timeout by editing an XML config file, but this will be for all data sources, not just Excel, so it's probably not something you want to mess with.

Cheers!

MF.

[charon]

Ah, alright i see...ty for your reply mr. mupett.

Thought about sth like this because after some time i was able to open it again.
Hm...is there any other way to use an external data source like excel for reporting?  ???

[MFGF]

Whatever data source you use, Cognos will hang onto its connection for a default period of time. With a database that's probably ok since you can control the transaction isolation level Cognos uses and thereby prevent locking. With a text file or spreadsheet, it's just accessed as a file, though, and if Windows sees a process holding it open, it will not allow it to be modified or deleted.

The best option would be to deliver the data from the spreadsheet into a database table, but you might not have the luxury of being able to do that.

Regards,

MF.

[charon]

"but you might not have the luxury of being able to do that"
Exaclty, my luxury is being as incompetent as possible, so my collegues has to do that  ;D
Thx for your fast reply mr. muppet and have a nice evening.
Mystery solved.