Cognos Report output to be saved to a table 1

[recog]

Hi, I have a requirement where the output of a report needs to be saved to a database table. This is a simple list report which would output like 5 columns and the o/p will serve as i/p to some other process external to cognos. Any help is appreciated.

Thanks

 

[blom0344]

There is no direct way to write the output to a table, but you can save the list as a .csv file and import this into your database.

The alternative is to use the SQL generated by the report and use this as a view definition in the source database. Depending on your RDBMS you can then bypass Cognos and work between the databases.

(For one-time exercises we have sometimes simply used MS Access as an intermediate platform to upload data to a RDBMS)

Ties Blom

 

[recog]

Thanks for your reply... Our requirement is that users have access to report studio where they can change the filters and build their own reports, but the report columns will remain the same. This report data will act as i/p to program external to cognos. Please let me know if you have any suggestions.

Thanks

 

[blom0344]

Perhaps your user community is a very special one, but I do not see a scenario where a user runs a report and is supposed to feed the output to another program. This is a typical example of an interface build by the appropriate software (in any case not a BI tool like Cognos Report Studio).
If you really must pursue this course then saving as .csv output and using a little Access upload-tool (to the receiving database) would be a possibility

Ties Blom

 

[MaheshGS]

I think you can accomplish this with Stored Procedure.What you can do is
a)Write a stored Proc as per your reproting Requirements.
b)By calling Stored Proc,generate report in Report studio.
c)If user wants to save this report o/p then provide SAVE option in report Studio which will be handled in stored Proc.
Please let me know if you have any further queries.

 

[sln007]

recog,

We currently use the Report Studio o/p to .csv from one of our ERP data warehouses. (in ORACLE)
Then open the file in EXCEL 2007, then save as a Tab Delimited .txt file.
Then FTP that file to our mainframe, and a DB2 load utility reads the file, as a delimited file, and loads directly to a totally separate DB2 database for use.
A bit of a manual process since the source is ORACLE, in a totally separate datacenter, as is the target.

It works well. Once the .txt file is located on the network, automated FTP processes take over. It even automatically kicks off the mainframe job.

I hope this helps.

SLN

 

[blom0344]

To summarize the options: You may need an ETL-solution for transferring and transforming data. Using a report will always mean having to perform a cycle of manual actions to get the data were you want it.

Ties Blom

 

[sln007]

blom0344 has it right. An automated DB or ETL solution is far more desired than manual actions/tasks......but sometimes necessary.

SLN