In Cognos v8.4.1 Report Studio report I would like to insert some value in input box and pass this argument to DB2 SQL stored procedure that will insert the value to the DB2 database. Then I would like to display this inserted value in List report.
in db2 v9.7 on Windows I have created stored procedure
CREATE TABLE ADMIN.TAB (ID INTEGER)@
CREATE PROCEDURE ADMIN.SP
IN INPUT_PARAMETER INTEGER
RESULT SETS 1
DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT ID FROM ADMIN.TAB;
INSERT INTO ADMIN.TAB (ID) VALUES (INPUT_PARAMETER);
In Cognos BI v8.4.1 Framework Manager I did: 1. File | New and enter project name e.g. sp 2. "Select Language" dialog "English" and OK button. 3. "Data Source" and Next. 4. "SAMPLE_DB2" (my db2 database connection) and Next. 5. Clicked on plus at ADMIN schema and plus at Procedures and checked the "SP" stored procedure, Next button, Import button, Finish button. 6. In "Project Viewer" click on plus at SAMPLE_DB2 namespace. 7. "SP" stored procedure is listed. Right mouse click on it and "Edit Definition". 8. "Query Subject Definition - SP" window opens. On Definition tab at bottom click on three dots at INPUT_PARAMETER argument in Value column. 9. "Edit Argument" window opens. Click on "Insert Macro" button. 10. "Macro Editor" opens in Macro definition type in:
11. Click OK button two times to close two sub-dialogs. Now in "Query Subject Definition - SP" window click on Test tab.
12. "Prompt Values" window opens in first row in Value column type in e.g. 1 and click on OK button.
13. Go to DB2 Command Window and type in "select * from admin.tab" and number 1 is displayed, so stored procedure is working fine in Framework Manager.
14. Click on OK button (in Framework Manager) to close "Query Subject Definition - SP" window.
15. In "Project Viewer" window click on plus at "Data Sources" and click on displayed data source "SAMPLE_DB2" in my case.
16. In Properties window (at the middle bottom window) in Properties tab change Query Processing setting from Database Only to Limited Local.
17. In Project Viewer window right click on Packages and Create | Package.
18. In "Create Package" window in Name field type in package name e.g. sp and click Next button, Next button and Finish button.
20. Package is successfully created. On Question 'open the Publish Package wizard' click on Yes button.
21. Uncheck "Enable model versioning" and Next button.
22. User Access, just leave default and Next button and Publish button.
23. Package successfully published click on Finish.
Now open browser (e.g. Firefox):
1. Type in URL of Cognos BI and login.
2. In Cognos Connection select Launch (top right) and select Report Studio.
3. Select the SP package.
4. Create new report and select Blank.
5. Blank report is started.
6. From Insertable Objects window select Toolbox (at the bottom of window).
7. Scroll-down and drag Text Box Prompt to the canvas.
8. Prompt Wizard window opens. In "Create a new parameter" field type in INPUT_PARAMETER and Finish button.
9. Drag Prompt Button to the canvas.
10. Click on Button on canvas and from Properties window change General | Type from Next to Finish.
11. From menu select Run | Run Report - HTML.
12. Report is displayed. In input-box type in 2 and click on Finish button.
13. Finish button is displayed as clicked. No info or error returned.
14. Go to DB2 Command Window and type in "select * from admin.tab" only value 1 is displayed (from Framework test), but I expected to see also number 2.
For test I have also added List report and tried to display the inserted value, but no data is displayed.
Can you help me out. What am I doing wrong?