In this article, we will learn How to use Database Stored Procedure with IBM Cognos Framework Manager? For the demo, we are using two different types of Stored procedure. The first stored procedure is without the parameter. In the second stored procedure, we will use a parameter.
For the demo, we need to create following database objects.
Server: SQL Server
Simple Procedure: dbo.spGetUsers
Procedure with Paramters: dbo.spGetUserInfo
Download the following SQL File and execute it for creating above database objects in SQL Server.
|Procedure with IBM Cognos Framework Manager.sql||NA||3147||Login to Download|
After executing above SQL Script Import Procedure in Cognos Framework Manager.
Note: If database connection is not available create it first.
Use Run Metadata Wizard to import both the Stored Procedures.
After importing procedure Framework Manager will show error. To fix it, open the procedures one by one.
For procedure "dbo.spGetUsers" we don't need to do anything. Open it, test it and click on Ok to get all the columns. Finally, define the usage type for all the columns.
For procedure "dbo.spGetUserInfo" we need to create an argument for Email. Open it, for email argument adds prompt code "#prompt('pEmail','token')#". Remember the prompt name. We need to use it in Cognos Report Studio. Test the changes and click on Ok.
Note: Please don’t use code as shown in the below image.
Publish the FM Package. Now we will use both the procedures inside a report.
For procedure "dbo.spGetUsers" we don't need to do anything. Drag and drop all the data items for the first procedure on the report to create a list.
For the second procedure "dbo.spGetUserInfo", we need to add a prompt with parameter name same as we used in FM Model. Once we are done with the prompt, add a list for the second procedure as well. Finally, run the report.
Following are FM Package and Report XML File.
Please login to post your valuable comments.