Stored Procedures in Cognos Framework Manager

Cognos Framework Manager supports importing Stored Procedure as one of the Query Subject into the model. In order to import any stored procedure from the underlying data source, the first thing we need to do is to create a new one as per the requirement and then import. Please note that, Cognos only support user-defined stored procedures but not system stored procedures.Lets create a sample stored procedure in SQL Server under gosales database for this article. 

1. Open SQL Server Management Studio --> navigate to databases --> expand "gosales" database --> navigate to Programmability --> Stored Procedures.

2. Right-click on the white space and select New Stored Procedure.

 

3. Just write the following script on the editor.

USE [gosales]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Description:   Using one query item to show mulitiple parameters.

Create PROCEDURE [gosales].[TestSP]

-- Add the parameters for the stored procedure here

@PROD_LINE_CD NVARCHAR(10)
AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT

PRODUCT_LINE_CODE,

PRODUCT_LINE_EN

FROM gosales.PRODUCT_LINE

WHERE

gosales.PRODUCT_LINE.PRODUCT_LINE_CODE = @PROD_LINE_CD

END

4. Click on Execute and close the window. Now the Stored Procedure with the name TestSP has been created in the database.

5. Open Cognos Framework Manager and use Run Metadata Wizard option to import the created Stored Procedure from the database.

     
       
 

       
       

Note: make sure you provide proper value based on the data type and size defined.

Note: We can also put a macro here instead of giving a hardcoded value.

 

We can also use this stored procedure in report studio using native or pass-through SQL as shown below:

{EXEC [gosales].[TestSP] #Prompt('Prod_Line_CD','Integer')#}

Or

EXEC [gosales].[TestSP] #Prompt('Prod_Line_CD','Integer')#

Continue >>