Friday, September 28, 2012

How to allow user to add comments using stored procedure

It is requested to add comment that associates with product group for individual week on report.  This comment should be saved into database, and therefore should appear on the report when opened next time. Please see diagram below

There are two kinds of comments in Cognos 8.4 and Cognos 10. The Cognos 8.4 comment is attached to report view, it will be there only when report views is kept in contents store, this approach can’t satisfy requirement; Cognos 10 gives collaboration by using comments in Business Insight, which looks like not a solution unless we changes requirement to have a high level dashboard to make have all comments about each product group. The only practical solution is to retrieve comment on report and to allow user to add it.

Solution in detail:

  1. Add table into database to save comment with product group and week, add to FM and incorporated into query in report, sample below
CREATE TABLE [dbo].[OPS_WeeklyInventory_Comment](
[ProductGrp] [varchar](30) NOT NULL,
[WeekKey] int,
[Comment] [varchar](512) NOT NULL,

  1. Create stored procedure as  CREATE PROCEDURE [dbo].[WeeklyInventory_Comment_Insert] (@ProductGrp varchar(30), @WeekKey int, @Comment varchar(512))

  1. Now open your Framework Model and import this Stored Procedure as a Stored Proc Query Subject. You need to configure the input parameters as Prompts. This is shown in the following screenshot:

  1. Create a drill target report (named: Weekly Inventory Comment): Use the stored proc Query Subject for this report. Drag Product group, week key and Comment columns on this report.  Then create a prompt control, Insert a Text type of prompt and connect it to the existing parameter called fm_Comment, save this report as drill report.

  1. Back to the main report, add text item (will be changed as better symbol) and then define drill through action as below

Lessons learned

  1. Optional prompt in framework manager using stored procedure.
When adding stored procedure in framework manager, we need to define all input parameter as prompt. These parameters become mandatory parameters. When adding new comment, there is no data in report to provide, therefore user will be asked for comment before he or she even comes to add comment page. The solution is to make comment as optional.

  1. Calculation in Framework manager to control who can add/change comment
Add a new calculation in Framework manager as below
(This solution demonstrates a few key points to use calculation in report:

  1. Calculation can be used in filter and column, but the key is that it is the best to define the whole calculation as a micros with ##. As some logic defined in FM can’t used in report;
  2. Variable can only defined by global parameters or query item, however, the query item has to be used in report. If it is NOT used, then it must marked and included in query )

Then add the calculation into main query and specify properties to include it into query as below
Then define a render variable, this variable will be used to decide whether add/change comment should be enabled


  1. Hello ,

    Below is the stored procedure I created for DB2
    CREATE PROCEDURE Insert_Comment (IN Product_Key VARCHAR(255), IN Comments VARCHAR(255))
    IF ((select count(*) from SPT1 where APPROVERNAME = Product_Key) = 0)
    INSERT INTO SPT1 VALUES (Product_Key,Comments);
    END IF;


    On importing the same in Framework manager and validating it . I get and error
    RQP-DEF-0179 The procedure..... unable to to return a result set .

    Any suggestion will be helpful .