Monday, April 15, 2013

The Cognos Report Toolkit - Align series data with axis categories in chart

General note about The Cognos Report Toolkit
Report specification:  Relational, DMR, and Cube


Requirement
Below is chart with series data that is fully aligned with columns in chart. Please note that table below the chart is not generated by separated query. Chart and graph share the same query, therefore results in a very good performance.
Query
The idea is to have a query with three columns that are considered as categories, such as year, quarter and month. In this case you want to have all three categories as the same.
(DMR and Cube Queries are similar)

Year: [Sales (query)].[Time].[Year]
Year1: [Sales (query)].[Time].[Year]
Year2: [Sales (query)].[Time].[Year]
Revenue: [Sales (query)].[Sales].[Revenue]
Planned revenue: [Sales (query)].[Sales].[Planned revenue]

The query result

Layout
Step 1: Create normal chart as below, sorted by year

Step 2: Drag Year1 and Year 2 as parent for Year

Step 3: Change Year 1 and Year 2 from member caption to date item Label to Revenue and Planned Revenue


Step 4: customize legend position, and make it aligned with categories

2 comments:

  1. what version of cognos does support above functionality ?

    ReplyDelete
  2. this sample is built based on version 10.1.1

    ReplyDelete