Dynamic table selection in a Cognos report

Body

Case 1:

The report should query a certain table based on the input selection on the prompt page of the report.

Example:

A table should be queried based on the selection of summarization (Hourly, Daily, Weekly, Monthly, Quarterly, Yearly) in the prompt page of the report.

Procedure:

1. In the report, add the drop-down field in the prompt page with the parameter name as Summarization.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 1

 

Displayed as below during runtime:

 

 

 

 

 

 

 

 

Figure 2

 

2. In the Cognos data model, add a parameter map to hold the table/view name for each summarization value.

 

 

 

 

 

 

 

 

 

 

Figure 3

 

3. In the model, update the query items of the query subjects where the Win_CPU_Usage table/view names are hard coded.

 

Example:

[Database View].[Win_CPU_Usage_D].[Server_Name]

 

should be changed to

 

[Database View].#$[CPU_Usage_Map]{prompt('Summarization','token')}#.[Server_Name]

 

4. Save the model and publish it. Run the report by selecting the Summarization and data will be loaded from the respective table.

 

---

Case 2

The report should query a certain table based on multiple input selections on the prompt page of the report.

1. In the report, create 2 drop downs in the prompt page of the report. One for Summarization, and another for say, Operating System (Windows and Linux).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 4

 

 

Displayed as below during runtime:

 

 

 

 

 

Figure 5

 

2. In the Cognos data model, add a parameter map to hold the combination of Summarization and the Operating System table/view name as key and set the corresponding table name as value.

 

 

 

 

 

 

 

 

 

 

Figure 6

 

3. In the model, update the query items of the query subjects where the Win_CPU_Usage table/view names are hard coded.

 

Example:

[Database View].[Win_CPU_Usage_D].[Server_Name]

 

should be changed to

 

[Database View].#$[Combo_Map]{prompt('Summarization','token') + '_' + prompt('Operating System','token')}#.[Server_Name]

 

4. Save the model and publish it. Run the report by selecting the Summarization and the Operating System and data will be loaded from the respective table.