Update database from Cognos

[globalbear]

Is there a way to insert/update/delete rows in the database using Report Studio/Event Studio or any other Cognos tool?

My wishlist is to make a Report studio report that shows a list with rows in one table. Then I would like to click on one row and be able to update or delete it from the database.

My guess is I would have to use a stored procedure to accomplish this. What I have read is that Report Studio does not support data modifiction stored procedures but Event Studio does.

Would be neat to make a drill-thru from report studio to an event studio event that uses the row from report studio as input and then deletes/updates the row in the database.

Am I on the right path, is there another way or is it simply not possible? The obvious not-cognos tool would be using SSIS.  But still - it would be nice if the cognos administrator could update some master tables directly from Cognos portal.

[Rajesh_Vanam]

Hi,

You can update database tables using Report Studio. We made such a report for our client.

As you said Report Studio wont support data modification Stored Procedure. As a work around create SP which will update database and also fetch data. Then create Stored Procedure Qury subjent with that SP and make use in your report.

Let me know if you need any further clarification.

FYI..Our reporting database is Oracle 10g.

Cheers,
Rajesh

[globalbear]

Thanks - it seem to do the trick!
;D

[srayburn]

Hi, Thanks for your post - we are trying to achieve similar functionality and are unable to see the SP Query Subject in Report Studio. Can you give us detailed steps of how you were able to update database from Cognos via Report Studio? Thanks in advance, Sue

[chandu.reddy]

We are trying to achive the same in RS ver 8.4/Oracle. Could you please provide me the step by step instructions. Thanks in advance.

[shaikshah009]

Can u pls send the steps for this..it is very urgent for us.
thanks in advance

[lira89]

Let me know if you need any further clarification.

Could you, please, write all steps for this in detail or give a link on it? Thanks :)

[Lynn]

Maybe this post will help you: http://www.cognoise.com/index.php?topic=26700.0

[globalbear]

I once made this work (2009... time does fly!)
If I remember I used a stored procedure that first does some update things and then returns some stuff that you don't neeed, e.g null.
Framework manager and Report studio thinks it needs the null stuff it so let's give Cognos some useless info...

Set up table MyTable with two columns; column1 varchar(255) and column2 varchar(255). Then populate it with some data.
Then set up this sp:

(MSSQL)
CREATE PROCEDURE [dbo].[SP_Update_MyTable]

   @column1 varchar(255),
   @column2 varchar(255)

AS

BEGIN

   SET NOCOUNT ON;
   UPDATE dbo.MyTable

   SET column2 = @column2
   WHERE column1=@column1
   
SELECT NULL AS Whatever -- this doesn't do anything useful but Cognos likes it...

END

GO

When the table and the sp is there try using it in Framework manager and publish it to a Framework package.

Then you should be able to use it inside a report with a prompt page. Put two prompts on the prompt page - one for column1 and one for column2.
When you run the report the prompts should be passed via the framework package down to the sp and update the line in the database.

Works fine for updating single values in the database but not for bulk updates. I used it for updating a period table once a month.

The trick is that Cognos doesn't support update stored procedures but only fetch data stored procedures.
If you put whatever code first and then the last line is some kind of fetch data thing, Cognos will think it is the whole sp is a fetch data thing, which it isn't.

Good luck!