Cognos Analytics

I need help with a proof of concept that I swear I did once before but don't remember how I did it. Looking for some help.

I have an Oracle table named TEST with one column (string) called NAME. I create a stored procedure to taking a string parameter named pNAME and inserting a record to the TEST table. I can run it all day from SQL Developer so I know the procedure works for Oracle.

Now I want to put a text prompt on a Cognos prompt page using parameter ?Name? where I want the user to enter a string, click a button, and it executes the stored procedure to use the value for ?Name? to run the SP.  How do I get this to work?

I've tried ways of calling the stored procedure in a Cognos SQL query without any luck.

 I've imported the SP to the framework as an SP query subject and switched it to Data Modification, added the macro to the parameter and tested it on the framework just fine. But when I publish the package I can never see the query subject of this SP appear. 

Having a hard time figuring out how to make this POC work. If I could just get it to work I can take it the rest of the way, but I'm clearly missing something here about how SPs function in Cognos or how to wire them up.

I need help with a proof of concept that I swear I did once before but don't remember how I did it. Looking for some help.

I have an Oracle table named TEST with one column (string) called NAME. I create a stored procedure to taking a string parameter named pNAME and inserting a record to the TEST table. I can run it all day from SQL Developer so I know the procedure works for Oracle.

Now I want to put a text prompt on a Cognos prompt page using parameter ?Name? where I want the user to enter a string, click a button, and it executes the stored procedure to use the value for ?Name? to run the SP.  How do I get this to work?

I've tried ways of calling the stored procedure in a Cognos SQL query without any luck.

 I've imported the SP to the framework as an SP query subject and switched it to Data Modification, added the macro to the parameter and tested it on the framework just fine. But when I publish the package I can never see the query subject of this SP appear. 

Having a hard time figuring out how to make this POC work. If I could just get it to work I can take it the rest of the way, but I'm clearly missing something here about how SPs function in Cognos or how to wire them up.

Hi Logan,

The best way to test a stored procedure (only user-defined stored procedures) is first, with Framework Manager.

There are 2 types of stored procedures: Data Query (Issues a read-only transation) or Data Modification (Writes a record to the data source. Use this type when you want to use the stored procedure in Event Studio.)

Here is a link to the documentation:

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=subjects-creating-modifying-stored-procedure-query

Best regards,

I need help with a proof of concept that I swear I did once before but don't remember how I did it. Looking for some help.

I have an Oracle table named TEST with one column (string) called NAME. I create a stored procedure to taking a string parameter named pNAME and inserting a record to the TEST table. I can run it all day from SQL Developer so I know the procedure works for Oracle.

Now I want to put a text prompt on a Cognos prompt page using parameter ?Name? where I want the user to enter a string, click a button, and it executes the stored procedure to use the value for ?Name? to run the SP.  How do I get this to work?

I've tried ways of calling the stored procedure in a Cognos SQL query without any luck.

 I've imported the SP to the framework as an SP query subject and switched it to Data Modification, added the macro to the parameter and tested it on the framework just fine. But when I publish the package I can never see the query subject of this SP appear. 

Having a hard time figuring out how to make this POC work. If I could just get it to work I can take it the rest of the way, but I'm clearly missing something here about how SPs function in Cognos or how to wire them up.

I need help with a proof of concept that I swear I did once before but don't remember how I did it. Looking for some help.

I have an Oracle table named TEST with one column (string) called NAME. I create a stored procedure to taking a string parameter named pNAME and inserting a record to the TEST table. I can run it all day from SQL Developer so I know the procedure works for Oracle.

Now I want to put a text prompt on a Cognos prompt page using parameter ?Name? where I want the user to enter a string, click a button, and it executes the stored procedure to use the value for ?Name? to run the SP.  How do I get this to work?

I've tried ways of calling the stored procedure in a Cognos SQL query without any luck.

 I've imported the SP to the framework as an SP query subject and switched it to Data Modification, added the macro to the parameter and tested it on the framework just fine. But when I publish the package I can never see the query subject of this SP appear. 

Having a hard time figuring out how to make this POC work. If I could just get it to work I can take it the rest of the way, but I'm clearly missing something here about how SPs function in Cognos or how to wire them up.

Hi Logan,

I also use STP to write back data into the DWH. The concept I use is to leave the STP in Framework Manager for "Data Queries", not for "Data Modification". The trick is to include a variable/parameter into your procedure that returns a value so Cognos can use it to display data. The procedure than appears in Cognos only with the returned column available and whenever you want to "see" its content, Cognos would have to send the appropriate parameters to the STP, runs it and returns the data.

One of my simple procedures have the values: two as input and one as output (STATUS). Whenever the procedure is run, it returns a string called STATUS. Works fine.

To achieve that I work with conditional blocks that check whether a parameter is filled or not.Otherwiese the procedure is run every time with the report itself.

I need help with a proof of concept that I swear I did once before but don't remember how I did it. Looking for some help.

I have an Oracle table named TEST with one column (string) called NAME. I create a stored procedure to taking a string parameter named pNAME and inserting a record to the TEST table. I can run it all day from SQL Developer so I know the procedure works for Oracle.

Now I want to put a text prompt on a Cognos prompt page using parameter ?Name? where I want the user to enter a string, click a button, and it executes the stored procedure to use the value for ?Name? to run the SP.  How do I get this to work?

I've tried ways of calling the stored procedure in a Cognos SQL query without any luck.

 I've imported the SP to the framework as an SP query subject and switched it to Data Modification, added the macro to the parameter and tested it on the framework just fine. But when I publish the package I can never see the query subject of this SP appear. 

Having a hard time figuring out how to make this POC work. If I could just get it to work I can take it the rest of the way, but I'm clearly missing something here about how SPs function in Cognos or how to wire them up.

I need help with a proof of concept that I swear I did once before but don't remember how I did it. Looking for some help.

I have an Oracle table named TEST with one column (string) called NAME. I create a stored procedure to taking a string parameter named pNAME and inserting a record to the TEST table. I can run it all day from SQL Developer so I know the procedure works for Oracle.

Now I want to put a text prompt on a Cognos prompt page using parameter ?Name? where I want the user to enter a string, click a button, and it executes the stored procedure to use the value for ?Name? to run the SP.  How do I get this to work?

I've tried ways of calling the stored procedure in a Cognos SQL query without any luck.

 I've imported the SP to the framework as an SP query subject and switched it to Data Modification, added the macro to the parameter and tested it on the framework just fine. But when I publish the package I can never see the query subject of this SP appear. 

Having a hard time figuring out how to make this POC work. If I could just get it to work I can take it the rest of the way, but I'm clearly missing something here about how SPs function in Cognos or how to wire them up.