In this issue of my database and business intelligence blog I want to explain how it is possible to write data from Cognos Report Studio back to the database. I will work on an Oracle database but the techniques are quite similar on other DBs.
First I need a table. I’ll create a new one, that looks like this:
|Column Name||Data Type||Description|
|TEXT_ID||NUMBER||Just an id.|
|TEXT||VARCHAR2(2000)||The column to store the Comment.|
If we have a table we then need a function which will write a value to that table. This function looks like this in Oracle:
|CREATE OR REPLACE FUNCTION F_RS_TEST(i IN NUMBER, t IN VARCHAR) RETURN NUMBER AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO TBL_RS_TEST VALUES(i,t); COMMIT; RETURN 1; END;|
This is just a very simple function. It just takes two parameters (id, text) and stores them to the corresponding columns in TBL_RS_TEST. Important is the second line. This is needed in order to do a commit within the function. And as we are going to use this function in a SELECT statement the commit is needed within the function. Then we are just inserting a new row to the table, commiting it and returning 1. It’s just a very simple example. Of course you could do an UPDATE or a DELETE as well and of course you can also have some more complex logic here. Whatever is needed in your case.
Then you have to run the complete script in script running mode on the Oracle DB. If the function was successfully created you can just check it with a statement like this:
|SELECT F_RS_TEST(1, ‚Hallo‘) FROM dual;|
It should just return 1 as a result. And if you check the table TBL_RS_TEST afterwards there should appear a new line with 1 – Hallo. That’s all we had to do on database level.
Now we can step over to the Cognos part. We open Report Studio and then we create a pretty simple report. I am using a SQL item in the query with the following properties:
- SQL-Syntax: Pass Through
- Datasource: Just take a datasource that has a connection to the database where you have created the table
The SQL itself looks like this:
|SELECT F_RS_TEST(2, ‘Test 2’) AS TEST FROM dual|
When you have created the query with the SQL item then you just have to drag the column TEST to the report page in the list or singleton or whatever container you prefer. This is important otherwise the query wouldn’t be executed. Finally just run the report and check on database level if you find the new value in the table.
This was just a very simple example. In the example of this blog article I’ve added a report which has a prompt page where you can type in an id and text. These parameters are then passed to the SQL via the macro function prompt and are then written to the database table via the function F_RS_TEST. You find the example in a zip-file at the end of this article. You need to change the package and datasource so that it can run in your environment. In a soon following part I will extend this example to a solution to store comments on values on an existing planned turnover table.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!