This is perhaps the most frequently requested functionality by business users—writing some notes or comments back to database, for a particular entry on the report. Though there is no direct functionality provided in Cognos Report Studio for this, it is still possible to achieve it by putting together multiple tools. This recipe will show you how to do that.
The business wants to see sales figures by products. They want to then write some comments for the products from the same interface. The comments need to be stored in database for future retrieval and updating.
You would need access on the backend database and Framework Manager for this recipe.
Tip
As we are only concentrating on Report Studio in this book, we will not cover the Framework Manager options in depth. The power users and Report Studio developers need not be masters in Framework Modelling, but they are expected to have sufficient knowledge of how it works. There is often a Framework Manager Specialist or modeller in the team who controls the overall schema, implements the business rules, and defines hierarchies in the model.
Create a simple list report with Product key, Product name, and Sales quantity columns. Create appropriate sorting, aggregations, and prompts.
- We will start by creating a table in the database to store the comments entered by users. For that, open your database client and create a table similar to the one shown here.
In this recipe, I am using a simple table created in a MS SQL Server 2008 database using the SQL Server Management Studio. The table is defined as follows:
CREATE TABLE [gosalesdw].[ProductComments]( [ProductID] [int] NOT NULL, [Comment] [varchar](255) NULL, CONSTRAINT [PK_ProductComments] PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] - After creating the above table in the backend, we will now write a stored procedure that will accept Product Key and Comments. It will enter this information in the table and then return all the Product Keys and corresponding Comments back.
CREATE PROCEDURE [dbo].[InsertComment] @ProductID int, @Comments VARCHAR(255) AS BEGIN IF ((select count(*) from gosalesdw.ProductComments where ProductID = @ProductID) = 0) INSERT INTO gosalesdw.ProductComments VALUES (@ProductID,@Comments) ELSE UPDATE gosalesdw.ProductComments SET Comment = @Comments WHERE ProductID = @ProductID END Select ProductID,Comment from gosalesdw.ProductComments GO
- Please ensure that the user account used to access the database from Cognos, has been given
EXECUTEpermission on above Stored Procedure. On SQL Server, you can do that usingGRANTPERMISSIONcommand. - 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:
As you can see in the picture, @ProductID and @Comments are the Stored Procedure parameters. They have 'in' mode which means they accept input. For their value, we are defining prompts as ?ProductKey? and ?Comments? respectively.
- Verify the model and publish it.
- Now, we will create a new report which users will use to insert the Comments about product. For that start with a new list report.
- Use the InsertComment stored proc Query Subject for this report. Drag Product ID and Comment columns on this report.
- Create a prompt page for this report. Insert a Text Value type of prompt and connect it to the existing parameter called Comment.
- Save this report as drill report. We will call it as '2.5 Writing Back to Database – Drill' in this recipe.
- Now re-open the first report. Drag a Text Item as a new column on the report and define text as Insert Comment.
- Create a drill-through from this text column by clicking on the drill-through icon. Set '2.5 Writing Back to Database – Drill' as drill target. Check the option of Open in New Window.
- Edit the parameter for this drill by clicking the edit button.
- Map the ProductKey parameter to the Product key data item.
- Run the report to test it.
Cognos Report Studio on its own cannot perform data manipulation on a database. It cannot fire DML statements and hence can't write back to database.
However, Cognos allows reports to execute the Stored Procedures and show the result output on report page. For this, we need to import the Stored Procedure as Query Subject within Framework Manager. When a report that uses this query subject is run, Cognos executes the Stored Proc on database. We can use this opportunity to perform some DML operations, for example, inserting or updating rows in tables.
When we import a Stored Proc into Framework Model, it allows us to define an expression for every input parameter. In step 3 of this recipe, we defined the parameter value to be prompts. The prompt parameters, namely ProductKey and Comments then become visible in the report.
Once we have imported the Stored Proc in Framework Model, mapped the input parameter to prompts and published package, we are ready to start with reports.
We created a report (drill report) to use the stored proc and hence allow users to insert the comments. In this report, we created a text prompt and linked it to 'Comments' parameter. The Product Key is passed from main report. This way we achieve the write-back to the database.
After inserting/updating the row, Stored Proc returns all the records from the comments table. We show those records in a simple list report to users.