Technical Blog Post
Abstract
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.
UID
ibm11276486