How to allow user to add comments using stored procedure

Check list of all posts

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



Analysis:
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,

CONSTRAINT [PK_OPS_WeeklyInventory_Comment] PRIMARY KEY CLUSTERED

(

[ProductGrp],

[WeekKey]

)

)

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

3. 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:

4. 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.

5. 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.

2. 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