Macro automation equivalent to OFFICE VB/VBA

[Therese]

Hi Guys,

I have an excel spreadsheet which I have automated to open at 3am, open an Access database and run the code in access db to retrieve a report attachment from a mailbox (attachments are from Vendor and a Cognos report I wrote), overwrite these into a tables and do a comparison. Once the comparison is run the db then emails all the comparison data to specific users as excel. Once all complete database closes itself down.

Is there a way to do all this through Cognos 11 or would I still need to incorporate some vb/vba to automate this still?

Basically can Cognos cover office automation that access and excel have?

[CognosPaul]

I remember making something similar before I started with Cognos. I'd get dozens of emails a day and wrote scripts to dump everything into an Access database, scripts to construct reports, and scripts to email stuff. I felt so incredibly proud of myself for doing it, and at the time it was an impressive piece of programming. 11 years later, knowing what I know now, I'd like nothing more to reach back in time and smack myself in the head. The error rate was down significantly from when things were done manually, but errors still snuck in. The solution required my computer to be on and available 24/7.

The first thing to consider is the source of information. From what you've written it looks like you have two:
1. Vendor (received as an email)
2. Internal (processed in Cognos)

Ideally the vendor should be able to directly or indirectly load their data into the same database (not necessarily the same table) that your Cognos is pulling from. Directly would be granting them write access to some tables. Indirectly might be allowing them to save files onto an FTP site and having an ETL run to load those files into your database.

The keyword here is ETL. Having a well governed system in place to retrieve, validate, load, and integrate vendor data into your own system is key to ensuring consistently valid results.

That being said, I am very well aware that setting up a decent ETL may be difficult or impossible, so let's look at other possibilities.

1. Connect Cognos to your Access database. As long as the database is stored on a network drive, this should work. Set up an event to check for new data and run the report as necessary.
2. Use local data in report studio. Upload data and run the report manually.

[dougp]

For CognosPaul's option 2:
In the External data dialog, there is a box to Allow the server to automatically load the file.  It's only available if you use Internet Explorer.  Use this option.  You vendor could write to a location in the file system (or work with your ECM or BPM team to scrape the attachment from the email) where the service account running Cognos has permission to read.  Then your report can run on a schedule.

[Therese]

Thanks guys this is awesome advice, I will test some options and reply which one works best.