In this article, we will learn How to write back to the database in IBM Cognos 10 Reports? Following is the demo What we will create in this article.
What is the requirement? We need to create a list report. In the list, we have a column which shows whether the user has admin access privileges or not. If any particular user doesn't have admin privileges, the will shows "Allow Access". On click on Allow Access, the respective user privileges must be updated and the list will show the updated privileges status. Similarly, if any user has access to display "Remove Access". On click on Remove Access, all the special privileges must be removed.
To create this report, we need to first create the database items. Download the following SQL file. It is for Microsoft SQL Server. You need to download the file and execute it. In the SQL file, we don't select any database. It will execute the script on the currently selected database. Also, the script contains the code to drop and create the required objects in this video.
|Login to Download|
The above script will create a database table and a stored procedure. We are interested in "dbo.spUpdateUserAccess" procedure. This stored procedure requires two arguments. First is @UserID, and another is @AccessType. UserID is for which user we need to update the Access. @AccessType is for Allowing and removing the special admin privileges. Finally, the stored procedure returns data from "dbo.tblUserAccess" database table.
After the database objects, we need to import the stored procedure in Framework Manager (FM). If you want to know How to use Stored Procedure with Framework Manager, you can check this article.
One important thing to remember about the stored procedure in Framework Manager. If you want to use a stored procedure in report studio, you must need to add it with type Data Query. For the type Data Query stored procedure must return something. In our demo procedure which we created above is to update the user privileges and retrieve the data in a single stored procedure. Following images shows How to import dbo.spUpdateUserAccess.
First use run metadata wizard to import the new procedure.
Next, edit the procedure. We need to define the Macro for all the available arguments. Following is the code for both the arguments. Finally, test the procedure. I tested the procedure with -99 as a user ID and -99 for the access type.
Finally, publish the FM model.
Our FM package is ready. What we need to do next. Open the report studio with the FM we published. Create a list and add the columns from the stored procedure.
Great, now to run our report, we must need to add two prompts. We will add two text boxes prompt with a default value -99 and the parameter names as we used in the stored procedure macros. We used pUserID and pAccessType. Note it is not important where to place the above two prompts. We will hide those two prompts. Why? Because we will use these two prompts for internal processing only. User inputs are not required for these two prompts.
Our report is executed successfully with the above setup. Next, we need to work on the AllowAccess column. First, we need to convert 0 and 1 into "Allow Access" and "Remove Access". For this unlocks the report and convert the AllowAccess source type to report expression with the following code.
CASE [Query1].[AllowAccess] WHEN 0 THEN 'Allow Access' ELSE 'Remove Access' END
Following is the report output with the above changes.
Save the report. We are almost done now we will create a drill through on the same report. To drill through, we will use AllowAccess column and need to pass data item values for UserID and AllowAccess.
We are done. Sort the list based on the User ID column. Save the report and run it.
What is next? We didn't work on the cosmetic changes. You need to work on the cosmetic changes to achieve the result we shared at the top of this article.
Please login to post your valuable comments.