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

Check list of all posts

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

Download report specification (version 10.2) for review

Query

Detail

total ([Revenue])

total ([Revenue] FOR [Region])

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

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

Result

TUPLE in dimensional package

Download report specification (version 10.2) for review

Query

Detail:

[Americas]

set([Camping Equipment],[Golf Equipment])

[Sales (analysis)].[Products].[Products].[Product type]

Revenue

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]))

Result

OVER in database

Comparison

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)