pg比特淘金网址是什么

書名： IBM Cognos 10 Report Studio Cookbook（Second Edition）
作者名： Ahmed Lashin Abhishek Sanghani
本章字數： 1077字
更新時間： 2021-08-06 17:03:26

Writing back to the database

Writing back to the database 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 then want to write some comments for the products from the same interface. The comments need to be stored in the database for future retrieval and updating.

You will 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 Modeling, but they are expected to have sufficient knowledge of how it works. There is often a Framework Manager Specialist or modeler in the team who controls the overall schema, implements the business rules, and defines hierarchies in the model.

Getting ready

Create a simple list report with Product key, Product, and Sales Quantity columns. Create appropriate sorting, aggregation, and prompts.

How to do it...

To complete this recipe, we will use a stored procedure to do the write back action to the database. This is illustrated in the following steps:

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 later. In this recipe, we are using a simple table created in an MS SQL Server 2008 database using the SQL Server Management Studio. The table is defined as follows:
```
CREATE TABLE [dbo].[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 table in step 1 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 as shown in the following code:

CREATE PROCEDURE [dbo].[InsertComment] @ProductID int, @Comments VARCHAR(255)
AS
BEGIN
IF ((select count(*) from 
[dbo].ProductComments
where ProductID = @ProductID) = 0)
INSERT INTO [dbo].ProductComments VALUES (@ProductID,@Comments)
ELSE
UPDATE [dbo].ProductComments
SET Comment = @Comments WHERE ProductID = @ProductID
END
Select ProductID,Comment from [dbo].ProductComments
GO

Please ensure that the user account used to access the database from Cognos has been given the EXECUTE permission on the stored procedure. On SQL Server, you can do that using the GRANT PERMISSION command.
Now open your Framework Model and import this stored procedure as a Stored Procedure Query Subject. You need to configure the input parameters as Prompts. This is shown in the following screenshot:
As you can see in the previous screenshot, @ProductID and @Comments are the stored procedure parameters. They have in mode which means they accept input. For the values we will be defining prompts so that we can use them inside the reports.
For the parameter @ProductID, click on the Value button. A new pop-up window will appear that will help you to set the value as you can see in the following screenshot:
Click on Insert Macro and define the macro as #prompt('?ProductKey?','integer')#.
Repeat the same for the @Comments parameter and define another macro for the ?Comments? parameter as well.
Verify the model and publish it.
Now, we will create a new report which users will use to insert the comments about the product. For that start with a new list report.
Use the InsertComment stored procedure query subject for this report. Drag Product ID and Comment columns on this report as shown in the following screenshot:
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 reopen the first report. Drag a Text Item as a new column on the report and define the text as Insert Comment as shown in the following screenshot:
Create a drill-through from this text column by clicking on the Drill-through icon. Set Writing Back to Database – Drill as drill target. Check the option of Open in New Window.
Edit the parameter for this drill by clicking on the Edit button.
Map the ProductKey parameter to the Product key data item as shown in the following screenshot:
Run the report to test it.

How it works...

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 the database.

However, Cognos allows reports to execute the Stored Procedure and show the result output on the 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 Procedure on the database. We can use this opportunity to perform some DML operations, for example, inserting or updating rows in tables.

When we import a Stored Procedure 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 Procedure in Framework Model, mapped the input parameter to prompts and published the package, we are ready to start with reports.

We created a report (drill report) to use the Stored Procedure and hence allow users to insert the comments. In this report, we created a text prompt and linked it to the Comments parameter. The Product Key is passed from the main report. This way we achieve the write-back to the database.

After inserting/updating the row, Stored Procedure returns all the records from the comments table. We show those records in a simple list report to users.

There's more...

This recipe is a very basic example to demonstrate the capability. You can build upon this idea and perform more sophisticated operations on the database.

官术网_书友最值得收藏!

IBM Cognos 10 Report Studio Cookbook（Second Edition）

Writing back to the database

Tip

Getting ready

How to do it...

How it works...

There's more...