How To
Summary
Cognos Framework Manager can use objects like a stored procedure in Oracle® as a query subject. Parameters defined in the stored procedure can be used in Cognos reports through the query subjects defined in the package.
Objective
This article provides an example of how to:
- Create a simple stored procedure in Oracle® that takes a parameter and returns result
- Create a package in Cognos Framework Manager that uses the stored procedure as a query subject
- Create a report in Cognos that uses the parameter defined in the stored procedure through the FM package
Environment
This example was tested on Oracle® 12.0.2, Cognos Analytics, and Framework Manager, Windows®
SQL Developer was used to create Oracle® table and stored procedure
The default System
tablespaceand System user was used to create the Oracle® objects
Steps
Stored procedure in Oracle®:
Connect to an Oracle® database and create a table (Table2 in screen capture)
Table script:
CREATE TABLE "SYSTEM"."TABLE2" ( "ID" VARCHAR2(20 BYTE) NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(30 CHAR), "QUANTITY" VARCHAR2(20 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" ;
Note: The default system user account and
tablespace
Similarly, create a stored procedure as shown in the following screen capture
Stored procedure script:
create or replace PROCEDURE TABLE2SP ( PARAM1 IN VARCHAR2, c1 IN OUT SYS_REFCURSOR ) AS BEGIN open c1 for select DESCRIPTION FROM TABLE2 where ID = PARAM1; END TABLE2SP;
Note: The
IN OUTParameter c1 of type SYS_REFCURSOR is used to return result set to Framework Manager. This parameter is necessary to run the select statement in the stored procedure, and for the Framework Manager query subject to work in data query mode
Framework Manager
Create a project in Framework Manager
Add a query subject to the model to represent the stored procedure as shown in the following screen
If you are prompted to select a data source connection, pick the Oracle® data source connecting to the Oracle® database where the table and the stored procedure were created.
Upon successful authentication of the data source, the Oracle® objects appear. Select the stored procedure that was created earlier as shown in the following screen capture
After the query subject is created, right-click to open the pop-up menu, and click "Edit definition". The following screen appears
Here, only the IN
parameter can be seen in the definition. Make sure that the query item is in 'Data Query' mode. This parameter is necessary for Framework Manager to retrieve resultset
from the SYS_REFCURSOR IN OUT
parameter defined in the stored procedure.
Click the ellipsis next to the "value" column and use the following code to define a prompt macro:
#prompt('PARAM1','varchar2')#
You can switch to the 'Test' tab and test the query subject against the stored procedure. If the tests are successful, save the project and publish the package
Cognos Report
Create a report that uses the package that was published from the Framework Manager. The query subject representing the stored procedure in the package appears as shown in the following screen:
Note: Since the 'Description' column appears in the published package as it was returned as the resultset
from the stored procedure. Drag 'Description' into a list in the report
Create a prompt page with a text prompt pass parameter to the stored procedure. In the screen that shows the message to specify prompt parameter, the IN
parameter of the stored procedure appears under existing parameters as shown in the following screen
Save and run the report
Additional Information
The report specification (Report spec.txt) and the SQL script used in this example is being attached to this document
Related Information
Product Synonym
Cognos Analytics
Was this topic helpful?
Document Information
Modified date:
12 August 2020
UID
ibm10729771