Simple stored Proc in Macro

[ashishh]

Hi All,

I am new to Impromptu and here is what I need to do.

Before running a report, run an Oracle Stored Proc (which does not require any input or output parameters).

I am trying to do this as a macro but do not know how do I go about doing this.

I have read the threads which explain how stored procs are used in Impromptu but not sure of the following :

1. Can I not just run the already existing database stored procedure by doing a call within this macro?

2. Do I have to go through the CreateStoredProcedure first?

Any help will be highly appreciated.

Thanks,
Ashiish

 

[griffindm]

Ashishh,

Thread401-71396 covers the bases fairly well. The CreateStoredProcedure and Execute calls are no longer effective. Cognos stopped support for them in version 5 or 6. They do not give an error, but they don't do anything else either.

You have basically three options:

1. Call the stored procedure via the stored procedure template in Impromptu. Once this process completes, open your final report and run it for the refreshed data.

2. Ditto of above, but have the procedure return a result set directly. This lets you get fresh data and use the results within the same report. I don't do it this way myself, but others here can provide some guidance.

3. Use an ODBC DSN and the SQLExecute command in the macro language to call the stored procedure and refresh the reporting data. Then, within the same macro, run the final report.

Any of these methods should work. I've heard that creating the report directly from the returned rows is a little more constricting, but I can't say for sure.

Hope some of this helps you.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[ashishh]

Dave,

Thanks a bunch for the detailed response.

I appreciate it.

Ashish