By creating a dynamic column on an IBM Cognos Report Studio report, you can easily change the reporting information and use a single report to satisfy multiple requirements.
To create a dynamic column in IBM Cognos Report Studio, you should perform the following steps:
Step 1: Create the report 1. Using the GO Sales query package, create a list report with the following items:
Sales (query) > Time > Year Sales (query) > Sales > Quantity Sales (query) > Sales > Revenue 2. Select the Year column and click the Group button.
Step 2: Create a query calculation for the dynamic column
From the toolbox tab, click and drag Query Calculation to position between Year and Quantity.
Name the calculation Report By.
Type the following in the Expression Definition Window:
Case ?ReportBy? When 'OM' then [Sales (query)].[Order method].[Order method type] When 'RN' then [Sales (query)].[Retailers].[Retailer] When 'PL' then [Sales (query)].[Products].[Product line] When 'PT' then [Sales (query)].[Products].[Product type] When 'PN' then [Sales (query)].[Products].[Product] When 'S' then [Sales (query)].[Sales staff].[Staff name] else 'Totals' End
Each When portion of the expression will represent a different column that the user will select when they run the report.
Step 3: Add a value prompt to the report page
From the toolbox tab, select Value Prompt and drag so that it displays above the columns on the report layout.
From the Value Prompt Wizard, select Use existing parameter and then select Report By.
Select the Value Prompt on the report layout, and from the Properties pane, select Static Choices.
Add the following:
Tip: The use value in the Static Choices must be the same as what was set up in the case statement.
The results display as shown below:
Step 4: Add text to define the prompt
From the toolbox tab, select Text Item and drag to the left of the value prompt window on the report.
In the Text dialog window, type Report By: and click OK.
Step 5: Run the report
Select Run from the toolbar.
Type PL in the prompt dialog window.
The report displays with Product Line in the dynamic column.
By combining prompts and query calculations you can easily create interactive and powerful reports.
Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!