Interview Questions   Tutorials   Discussions   Programs   

Cognos - How to create a dynamic column name in Cognos?

    Next Topic

asked mar September 20, 2014 07:44 AM  

How to create a dynamic column name in Cognos?


1 Answers

answered By vishnoiprem   0  

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

  1. From the toolbox tab, click and drag Query Calculation to position between Year and Quantity.

  2. Name the calculation Report By.

  3. 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

  1. From the toolbox tab, select Value Prompt and drag so that it displays above the columns on the report layout.

  2. From the Value Prompt Wizard, select Use existing parameter and then select Report By.

  3. Click Finish.

  4. Select the Value Prompt on the report layout, and from the Properties pane, select Static Choices.

  5. 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

  1. From the toolbox tab, select Text Item and drag to the left of the value prompt window on the report.

  2. In the Text dialog window, type Report By: and click OK.

Step 5: Run the report

  1. Select Run from the toolbar.

  2. 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.

   add comment

Your answer

Join with account you already have



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!