COGNOiSe.com - The IBM Cognos Community

We have a reference table that needs to be 'relatively' easy to update/maintain that contains portfolio ids and data relative to these portfolios (name of owner, ...).

The report should for each ptf id, extract transactions for the last business day and save them in separate tabs in an Excel spreadsheet according to their type (cash, futures, securities, ...)

<<all the sql already exists and could be done into as many stored procs as there are types>>.

For some of these tabs, the data should be enriched by the data from the reference table and the layout should be adapted to contain more information on the top (fixed layout by the standard).


Finally all reports (one Excel per portfolio) should be emailed, if possible to different adresses.

Most of it is already done as an Excel macro.

Just wondering if reportnet can do this
Any help would be appreciated.

J

Hello J

Yes this can be done but here is the catch...you will need the SDK.

I did something very similar to this using VB and Impromptu a while ago.

You will need to write code that will run the report and save to an Excel sheet.

Once you are done running all reports you will have to run another piece of code that will collect all Excel sheets and integrate them into separate tabs in an Excel workbook.

To start with your report will need atleast one prompt for the Portfolio Id. You will need to write a controller routine in your code that will extract the ids from the reference table and send them one at a time in multiple iterations to another routine that runs the ReportNet report.

ReportNet is a great choice for big jobs as you can run it asynchronously. I had to struggle to do this with VB, use Java and also watch out for excessive Excel manipulations. Excel behaves erratically under excessive iterations...

Long winded response but HTH

NKT