How to turn Cognos BI reports into “Web forms” without using JavaScript

Check list of all posts

Context

Solution overview

How to do it...

Act on single text item

Act on a list

Act on a crosstab

Edit data

Drill through cross workspaces

Lesions learned

Unintended consequence of cache parameters

How to combine prompt and drill through together

Context

As known, Web Forms are pages that your users request through their browser, it is the user interface (UI) that gives your web applications their look and feel. The key of this UI is to allow user to input data and update database behind the scene.  Even the active report from Cognos 10.1 and Cognos 10.1.1 provide user a highly interactive interface, it can’t be used to input any data as operation UI. Certainly, we can develop web application, but with relative high cost. Is it possible to make Cognos report as web form?  The answer is yes!  This approach could

• gives end user a much better user interface
• Save a big cost for web development
• Provide a comphensive BI solution

Solution overview

The idea is to leverage Cognos key concepts and their relationships, including parameters, variables, conditional blocks, drill through and portal pages. The concept to turn Cognos BI reports into “Web forms” is illustrated below

1. Define a hidden value prompt as pBlock using static selection as Block 1, Block 2, … Block i,… Block N
2. Define a conditional variable as vBlock = ParamValue(‘pBlock’), and therefore variable value is   Block 1, Block 2, … Block i,… Block N
3. Define report as conditional Block 1, Block 2, Block 3, … with conditional variable vBlock assigned.
4. Define your action as drill through for all three cases including singleton, list and crosstab
5. Parameter pass data item pBlock, and therefore control what block to display.
6. If you want to go other page, then use portlet with global drill though channel.

How to do it...

Act on single text item

Please  get report specification for reference in detail

1. Follow the general step 1,2 and 3
2. Create a query with block 1,2, i and N

3. Drag any data item to anywhere you want in block or page area
4. Define drill thorough as follows, In real implementation, you will have more parameters besides Block ID, whether you use simple query or stored procedure to insert data.

5. Change drill data items to text, such as Click the link to perform…., or to an image

Act on a list

Please get report specification for reference in detail

1. Follow the general step 1,2 and 3
2. Create a query for list with block 1,2, i and N

3. Drag any data item to the list and put list colums Block 2,I and N together
4. Define drill thorough as follows; in real implementation, you will have more parameters besides Block ID, whether you use simple query or stored procedure to insert data.

Act on a crosstab

Please  get report specification for reference in detail

1. Follow the general step 1,2 and 3
2. Create a query for crosstab with block 2 as sample

3. Mark Block 2 to be retrieved

4. Define drill thorough as follows; in real implementation, you will have more parameters besides Block ID, whether you use simple query or stored procedure to insert data.

Edit data

Using the similar idea, you can edit data of table. The key to resolve this problem is to define a new stored procedure, which can accept some default variable to get corresponded action.  In other word, you use stored procedure to retrieve data, insert data and delete data by adding an additional action parameter.  Make this stored procedure in framework, note that all parameters should have default setting; otherwise, you’ll be asked to provide all variables each time

Drill through cross workspaces

Sometimes, user wants to keep some information on one window, other information is on another window. In this case, it is requested to drill to another window. Portlet is the best option to help. To make two windows to communicate each other, simply put two reports into portlet and specify report drill through channel. Please note that both report should be shared the same channel.

Lesions learned

Unintended consequence of cache parameters

In case when user is allowed to input data more than one time in interactive report, then the parameter value saves value from the first time. When trying to add or change data, report interface will use the saved value. To avoid this issue, there are two workaround:

1. Ask user to exit report when value inputted, and then reopen this report, and its cached parameter will be gone.  This is pure workaround and not a solution.
2. When using drill through, initialize the parameter as empty string and then pass it to the page, or block. In this case, the parameter value won’t be cached.

How to combine prompt and drill through together

There is no way to use single action to combine prompt and drill through together without using JavaScript. There are three approaches to resolve this issue:

1. Using JavaScript, you can embed prompt in script to get variable value and then act.
2. Have addition prompt before moving to the target page or block, this way enforces prompt when user act.
3. Use database stored procedure, where action is embedded as parameter in stored procedure.

     Simplify the process: If content of different blocks are similar, then we don't need to create different blocks with similar content, instead, we don't use block, and create a single data container with filters.