Sometimes users ask me if it's possible to add comments to their report data, in order to clarify its status. Just as simple as possible, without having to use another application.
Using Cognos BI, this certainly is possible. In this post I want to show you a technique I have developed using the Great Outdoors demo environment.
In summary these are the steps, implementing a commentary update application:
1. In the database, create a Stored Procedure, allowing you to update the comment column in a table record.
2. In the Cognos Framework, create a Query Subject using this stored procedure.
3. Create a prompt report prompting for the comment in a prompt page, and showing the modified comment in the report page.
4. In the list report, add a drill through to the prompt report, passing the selection values.
1. Stored procedure
The stored procedure could look like this:
CREATE PROCEDURE SalesTargetCommentAdd @intYear smallint, @strPRODUCT nvarchar (50), @strComment nvarchar(2048)
AS
IF rtrim(@strComment) = ''
UPDATE SALES_TARGET_COMMENT
SET Comment = NULL
WHERE [YEAR]=@intYear and PRODUCT=@strPRODUCT
ELSE
IF EXISTS (SELECT * FROM SALES_TARGET_COMMENT WHERE [YEAR]=@intYear and PRODUCT=@strPRODUCT)
UPDATE SALES_TARGET_COMMENT
SET Comment = @strComment
WHERE [YEAR]=@intYear and PRODUCT=@strPRODUCT
ELSE
INSERT INTO SALES_TARGET_COMMENT ([YEAR], PRODUCT, COMMENT)
VALUES (@intYear, @strPRODUCT, @strComment)
SELECT [YEAR], PRODUCT, Comment
FROM SALES_TARGET_COMMENT
WHERE [YEAR]=@intYear and PRODUCT=@strPRODUCT
GO
Mind that the database user used in the Cognos data store Connection must have execute privilege on the stored procedure and update privilege on the table!
2. Framework Manager Query Subject
The Framework Manager Query Subject contains the store procedure and the parameters:
3. The prompt report
The prompt report shows in the prompt page the current comment (using a normal query subject) and prompts for the new comment using a textbox prompt object:
The report page shows the comment item from the stored procedure query subject:
The HTML item has this a-tag code to close the window on click:
href="javascript: self.close ()"
4. The list report
The list report shows data from the normal query subject and contains a text item object called "update" in the list frame next to the comment item, with a drill down to the prompt report:
