Tuesday, April 15, 2008

Commenting on your report data



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:



13 comments:

Anonymous said...

Nice solution there Marc! Good to see the blog is still alive - please post more ;-)

Fighter said...

Hi Marc!
great post , i tried doing something similar but got stuck in the "prompt report".
If i understand correctly it is the "prompt page" that invokes the stored procedure. What is the sql that is issued from this page ??

Marc said...

Hi sumit,

Thanks for your comment. The SQL issued from the prompt report page, is actually an Execute of the stored procedure declared in the query subject (see step 2 in my post). So there's no select statement issued, but an execute statement.

In Framework manager, you can create 3 types of query subjects: "Model", "Data source", and "Stored Procedure". Choose "Store Procedure", then provide the prompts in the parameters (see picture step 2).

Hope this helps, good luck!

BI and Cognos Authors said...

Woow.. That looks like a great technique! So in the report page of the 'prompt report', you use the Stored Proc QS passing it the year and product from Source Report (received as drill params) and the Comment from the prompt page of 'Prompt Report'. Right?

What is that HTML item for? Is it just a close button?

I remember having issues with Stored Proc QS (especially for IN type of params) with Cognos 7 and 8.1 - But it seems the situation has improved now, as you could implement it. Do you remember which version it was? and against which database?

Thanks for sharing!

Marc said...

The HTML item is a link to close the window: <a href="javascript: self.close ()"close></a>

I'v built this solution in ReportNet, and later ported it to 8v4. I suppose the intermediate versions would be able to handle it too.

The underlying database was SQL Server 2000 (in combination with ReportNet) and 2006 (using 8v4).

Anonymous said...

Hi Marc,

I work for a University in Australia, and I'm very interested in your posting regarding adding comments to a Cognos report.

Our finance department is contemplating using Cognos for financial reporting (we currently us it with our Data Warehouse), and they have a need to add comments to financial reports (eg. to explain variances).

As you may imagine, this is a very, very high priority requirement for them that will heavily influence their decision on whether or not to support the use of Cognos. So I'm very eager to run a small Proof of Concept to see if it can be done.

Would it be possible for you to email me material you produced? i.e. the FM Model, Report Studio report, etc.

I hope I'm not asking too much.

Michael Gibson
Data Warehouse Manager
Deakin University
michael.gibson@deakin.edu.au

earthrenewal.org said...

Hi Marc,
I independently came up with a similar approach. However,
I am having a serious problem when after the report is run.

To summarize, I:

(1) Created update/insert stored procedure. The last statement in the stored procedure is a select * statement.

(2) Added SP to Framework Manager with parameters.

(3)Created a List report off of query subject created in (1) above.

(4) List report returns inserted/update values (good)

(5) Immediately attempt to run a select statement against the updated table. Query times out and/or may take a few minutes to return results for just one row (unacceptable).

(6) When table is "released" and available to be queried- the updates are gone!

Any help would be most appreciated.

Thanks,

Lowell

Marc said...

Hi Lowell,

I'm not a database guru, but maybe you should add a commit statement in your SP to be sure the record is stored in the table? Otherwise I wouldn't have a clue. Someone else maybe?

Anonymous said...

Hi Marc

Great solution. Tried to do the same with Cognos 8.4 Fixpack 2. Everything works fine as long as i have existing comments. When i have a dataset without a comment, before i get to the prompt report, it prompts for the promptComment, although i defined the parameter as optional. Any ideas?

Thank you for your help. Nico.

Marc said...

Hi Nico,

Maybe it's because somewhere in your report definition (query item?) you refer to the prompt...

Haven't used this technique in 8v4 fixpack 2 myself...

Unknown said...

Hi Marc,
I use Sybase as my backend and I want to use the Stored Procedures which require 2 inputs begin date and end date. No matter what I do I cannot get this to work in Framework Manager. Any suggestions?

Vish

Marc said...

Hi Vishal,

I'd say it should work the same way as against MS SQL Server (since SQL Server is a derivate from Sybase), but I haven't tried that.

Maybe you could test a sample generated SQL statement in a Sybase SQL console?

Unknown said...

G'day Marc,

Your solution to using comments in your report data looks interesting & i've been trying to replicate this for a few days now with no luck. I followed your instructions to the point of the prompt section (which has left me confused on what to do). Can you please email me the XML, SP & the Framework Manager files; really appreciate it & enjoy reading your blogs! :)
Nick nickmills7@gmail.com