Saturday, May 11, 2013

How to understand Cognos Summary functions - Compare FOR option in relational package, TUPLE function for dimensional package and OVER option for database

It is relatively difficult to understand concept of FOR option with relational query. This document is intended to provide an idea to understand this concept by comparing with TUPLE and OVER window function. The same result is produced by these three methods below

FOR in relational package



total ([Revenue])

total ([Revenue] FOR [Region])

total ([Revenue] FOR [Region],[Product line])

total ([Revenue] FOR [Region],[Product line],[Product type])


TUPLE in dimensional package

set([Camping Equipment],[Golf Equipment])
[Sales (analysis)].[Products].[Products].[Product type]
tuple ([Revenue])
tuple ([Revenue],currentMember ([Sales (analysis)].[Retailers].[Retailers]),[Products])
tuple ([Revenue],currentMember ([Sales (analysis)].[Retailers].[Retailers]),parent(currentMember([Sales (analysis)].[Products].[Products])))
tuple ([Revenue],currentMember ([Sales (analysis)].[Retailers].[Retailers]),currentMember([Sales (analysis)].[Products].[Products]))


OVER in database


Revenue itself:
FOR: total ([Revenue])
TUPLE: tuple ([Revenue])
OVER: Revenue

Revenue for region:
FOR: total ([Revenue] FOR [Region])
TUPLE: tuple ([Revenue],currentMember ([Sales (analysis)].[Retailers].[Retailers]),[Products])
OVER: sum(Revenue) over (Partition by Region)

Revenue for region and product line:
FOR: total ([Revenue] FOR [Region],[Product line])
TUPLE: tuple ([Revenue],currentMember ([Sales (analysis)].[Retailers].[Retailers]),parent(currentMember([Sales (analysis)].[Products].[Products])))
OVER: sum(Revenue) over (Partition by Region,ProductLine)

Revenue for region, product line and for region and product Type:
FOR: total ([Revenue] FOR [Region],[Product line],[Product type])
TUPLE: tuple ([Revenue],currentMember ([Sales (analysis)].[Retailers].[Retailers]),currentMember([Sales (analysis)].[Products].[Products]))
OVER: sum(Revenue) over (Partition by Region,ProductLine,ProductType)

No comments:

Post a Comment