Friday, April 12, 2013

The Cognos Report Toolkit - Top N + All Others chart

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


Create a simple chart with top 3 product types and all other product types based on revenue.


Chart is based on simple layout as below, to emphasize All Others product types, we mark this series as red color.


Three different queries are separated described, as they are very different.


Query1 base

  • Year:  [Sales (query)].[Time].[Year]
  • Product type:  [Sales (query)].[Products].[Product type]
  • Revenue:  [Sales (query)].[Sales].[Revenue]
  • TotalRevenue4ProductType:  Total([Revenue] for [Product type])
  • RankProductType:  Rank([Revenue] at [Product type])

  • Year:   [Query1  base].[Year]
  • Product type:  if ([Query1  base].[RankProductType]<=3) then ([Query1  base].[Product type]) else ('All Others')
  • Revenue:  [Query1  base].[Revenue]


  • Year: [Sales (analysis)].[Time].[Time].[Year]
  • Top N: topCount ([Sales (analysis)].[Products].[Products].[Product type],3,[Revenue])
  • Others: except ([Sales (analysis)].[Products].[Products].[Product type],[Top N])
  • All Others***: (Type: Calculated Member)  Aggregate(currentMeasure within set [Others])
  • Product type: union ([Top N],[All Others])
  • Revenue: (Type Measure)
*** there is another manual way to do it: a) define Aggregate(currentMeasure within set [Others]);   b) define Member member ([Others],'All Others','All Others',[Sales (analysis)].[Products].[Products]);   c) set ([All Others Ex])


Cube based Query is the same as DMR

No comments:

Post a Comment