Tip or Technique

Copyright Your use of this document is subject to the Terms of Use governing the Cognos software products and related services which you have licensed or purchased from Cognos. The information contained in this document is proprietary information of Cognos Incorporated and/or its licensors and is protected under copyright and other applicable laws. You may use the information and methodologies described in this document 'as is' or you may modify them, however Cognos will not be responsible for any deficiencies or errors that result from modifications which you make. Copyright 2006 (c) Cognos Incorporated. All Rights Reserved. You can print selected pages, a section, or the whole book. Cognos grants you a non-exclusive, non-transferable license to use, copy, and reproduce the copyright materials, in printed or electronic format, solely for the purpose of providing internal training on, operating, and maintaining the Cognos software. This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to BestPracticesProductandTechnology@cognos.com .

Report Studio.Dimensional Functions
5
1
1.2
2
Background Information
Below is an example hierarchy from the Product Dimension of the Great Outdoors Company PowerCube showing the members below the Camping Equipment Product Line.
Cognos Proprietary Information
. Please see the Administration and Security Guide for Cognos 8 regarding the steps to restore these samples. This deployment file is valid for Cognos 8 MR1. The provided deployment of sample reports can be found at the end of this document.
1. Applicability The functions and sample data listed below apply to Cognos 8. This dimension will be the basis of many of the subsequent examples.1
Introduction
Purpose This document provides additional detail and examples of the dimensional functions provided within Report Studio for Cognos 8. The PowerCube and deployment package for “Great Outdoors Company” are provided with the product samples.

1
Function Definitions
_firstFromSet Syntax: _firstFromSet ( set_exp.[Prod uct line].2. If the set has a large number of members then only the numeric_exp_max members will be returned as the remainder of the set is greater than the specified overflow value.2. For a set that has only a few members more than the specified numeric_exp_max the numeric_exp_overflow allows the small set of extra members to be included.2) result: Camping Equipment Golf Equipment
3.8) result: Camping Equipment Golf Equipment Mountaineering Equipment Outdoor Protection Personal Accessories _firstFromSet([great_outdoors_company].[Products]. Example: _firstFromSet([great_outdoors_company].[Products].[Products].[Prod uct line]. numeric_exp_overflow ) Description: Returns the first members found in the set up to numeric_exp_max + numeric_exp_overflow. If numeric_exp_max + numeric_exp_overflow is exceeded.2
_remainderSet Syntax: _remainderSet ( member_exp. then only the max number of members are returned. numeric_exp )
Cognos Proprietary Information
.Dimensional Functions
6
3
3. numeric_exp_max.[Products]. set_exp.

[Products].100)
result:
3. level | integer ) Description: Returns the ancestor of the specified member at either the specified (named) level or the specified number of levels above the member.[Products]. 'Product Aggregate'.[great_outdoors_co mpany]. [great_outdoors_company]. 1) result: Cooking Gear ancestor([TrailChef Water Bag].[great_outdoors_company]. a new member will be generated if the number of members in set_exp larger than the specified numeric_exp Example:
_remainderSet(member(aggregate( currentMeasure WITHIN SET [great_outdoors_company]. 2) result: Camping Equipment ancestor([TrailChef Water Bag].Dimensional Functions
7
Description: The member expression will be included in the returned set when the size of the set_exp set is greater than numeric_exp.[Products].[Products]).[Product line].[great_outdoors_co mpany].[Products].[Products]. Note: The result is not guaranteed to be consistent when there is more than one such ancestor. level | integer )
Cognos Proprietary Information
.3
ancestor Syntax: ancestor ( member.[Products].[Product line]).1)
result:
_remainderSet(member(aggregate( currentMeasure WITHIN SET [great_outdoors_company].[Products]. [great_outdoors_company].[Products].[Products].[Product line]).4
ancestors Syntax: ancestors ( member.[Products].e. Example: ancestor([TrailChef Water Bag]. 'Product Aggregate'. 'Product Aggregate'.[Product type]) result: Cooking Gear
3.[Products]. 'Product Aggregate'.[Products].[Products]).[Product line]. i.

Dimensional Functions
8
Description: Returns all the ancestors of a member at a specified level. and returns the bottom "index_exp" members. or distance above the member.891. 2) result: Camping Equipment ancestors([TrailChef Water Bag].[Mountaineering Equipment]). 1) result: Cooking Gear ancestors([TrailChef Water Bag].6 bottomPercent Syntax: bottomPercent ( set_exp .171.[Produ ct line].[Products].) Example: ancestors([TrailChef Water Bag].[Products].2.[Products]. numeric_exp1 .350.[Golf Equipment].2.60 3.[Products].[Product type]) result: Cooking Gear 3. index_exp . but some support more than one. bottomCount(set([Camping Equipment].[great_outdoors_company].92 Mountaineering Equipment $20. numeric_exp2 )
Cognos Proprietary Information
. Example: Based on a crosstab report using the intersection of [2006] and the default measure [Revenue] to determine numeric_exp. numeric_exp ) Description: This function sorts a set according to the value of "numeric_expression" evaluated at each of the members of "set_exp". bottomCount([great_outdoors_company].[Revenue]) result: Outdoor Protection $3.114. (Most data sources support only one ancestor at a specified level.5 bottomCount Syntax: bottomCount ( set_exp .[2006])
result:
Based on a list report using a direct reference to the [Revenue] measure for numeric_exp. Hence the result is a member set.

[great_outdoors_company].7
bottomSum Syntax: bottomSum ( set_exp .[Golf Equipment].[Golf Equipment]. numeric_exp2 ) Description: This function sorts on "numeric_exp2".[Mountaineering Equipment]). Golf Equipment and Mountaineering Equipment return the members whose percentage total are greater than or equal to 40% for the tuple 2006
9
result:
bottomPercent(set([Camping Equipment].[Measures]. Example: Based on a crosstab report bottomPercent(set([Camping Equipment].[ Gross profit]))
result:
3.[Mountaineering Equipment]).tuple([2006]. but the threshold is "numeric_exp1" percent of the total.40.[2006]) For the set of Camping Equipment. evaluated at the corresponding member of "set_exp". numeric_exp1 .20. numeric_exp1 ranges from 0 to 100. and picks up the bottommost elements whose cumulative total is at least “numeric_exp1”.Dimensional Functions Description: This function is similar to bottomSum. Example:
Based on a crosstab report
Cognos Proprietary Information
.

[Measures].[Products].[Product line]). The caption is not necessarily unique.10 closingPeriod Syntax: closingPeriod ( level [. Example: children([Camping Equipment])
result: Cooking Gear
Tents Sleeping Bags Packs Lanterns 3.[Product s].9
children Syntax: children ( member ) Description: Returns the set of children of a specified member.[Products].[Gross profit]))
result:
3.6000000. The caption for a month may return the month name without further year details to make the value unique.[Products]. member ] ) Description:
Cognos Proprietary Information
. Example: caption([TrailChef Water Bag]) result: TrailChef Water Bag caption([great_outdoors_company]. The caption is the string display name for an element and does not necessarily match the unique identifier used to generate the business key or member unique name for the element.Dimensional Functions 10 bottomSum(members([great_outdoors_company].[Product line]) result: Camping Equipment Mountaineering Equipment Personal Accessories Outdoor Protection Golf Equipment
3.[great_outdoors_company].8
caption Syntax: caption ( level | member | set_exp ) Description: Returns the caption values of the specified element. tuple([2006].

completeTuple implicitly includes the default member from all dimensions not otherwise specified in the arguments.[Years]. This replicates the example
Cognos Proprietary Information
. rather than the current member. Likewise.[Quantity sold]. The value of this cell can be obtained with the "value" function. the root member. Example: closingPeriod([great_outdoors_company]. Typically used with a time dimension. member } ) Description: Similar to "tuple".[Fax]) The second column uses a more precise expression: completetuple([Mountaineering Equipment].[Years].[Years]. However.currentMember([great_outdoors_company].[Years]. Completetuple will use the default measure rather than the currentMeasure in the query if the measure is not defined in the completetuple function.[Years]. the first column displays Revenue rather than Quantity Sold because the Revenue measure is the default from the Measures dimension.Dimensional Functions 11 Returns the last sibling among the descendants of a member at a specified level. The values in the first column are identical across each year because the default member of the Years dimension.[Years].[Fax].11 completeTuple Syntax: completeTuple ( member { .[Years]))
result:
The completetuple does not pick up the currentMember by default as the tuple function does. Example: Below.[Month]) result: 2006/Dec closingPeriod([great_outdoors_company]. The second column in the above output specifies that the completetuple function is to use the currentMember of the Years dimension and the Quanityt sold measure. the first column uses the expression: completetuple([Mountaineering Equipment].[Year]) result: 2006 closingPeriod([great_outdoors_company]. is used rather than the current member. each of which must be from a different dimension. identifies a cell location (intersection) based on the specified members.[Years].[Month].[20 06 Q 4]) result: 2006/Dec 3.

Dimensional Functions 12 below that is used for the Tuple function where the currentMember and currentMeasure are selected by default.[Camping Equipment]) result: Sleeping Bags 3. 3.13 currentMember Syntax: currentMember ( hierarchy ) Description: Returns the current member of the hierarchy during an iteration.[Products])
results:
Cognos Proprietary Information
.[Camping Equipment])
result: Cooking Gear
cousin([Putters].12 cousin Syntax: cousin ( member1 . member2 ) Description: Returns the child member of member2 with the same relative position as the member1 is under its parent. Example: cousin([Irons].[Products]. its default member is assumed. If the specified hierarchy is not present in the context in which the expression is being evaluated. Example: currentMember([great_outdoors_company].

By creating a data item that contains the emptyset function it is possible to build complex expressions that can later be revised by redefining the emptyset data item. boolean_exp ) Description: Returns the set resulting from filtering a specified set based on the boolean condition.18 filter Syntax: filter ( set_exp . Each member is included in the result if and only if the corresponding value of “boolean_exp" is true.[Products].[Products])) results:Camping Equipment Golf Equipment Mountaineering Equipment Outdoor Protection Personal Accessories except Syntax: except ( set_exp1 . Example:
3. set([Camping Equipment]. Example: except([great_outdoors_company]. Duplicates are retained only if the optional keyword ALL is supplied as the third argument.[Products]. Example: except(set([Camping Equipment].[Mountaineering Equipment]).17
Source Data:
Cognos Proprietary Information
.ALL] ) Description: Returns the members of "set_exp1" that are not also in "set_exp2".16 emptySet Syntax: emptySet ( hierarchy ) Description: Returns an empty member set for the specified hierarchy. This is most often used as a placeholder during development or with dynamic report design (either with the SDK or via report design).[Golf Equipment])) result: Mountaineering Equipment 3.[Product line].emptyset([great_outdoors_company]. set_exp2 [.Dimensional Functions 16 3.[Products].

[Quantity sold] within set [2005]). hierarchy Description: Defines a member based on the specified expression in the specified hierarchy. string2 [ .[Product
Cognos Proprietary Information
. string1 [ .[Measures].Dimensional Functions 23 levels([great_outdoors_company].[Products]. if it is absent.1)
result: Camping Equipment
Mountaineering Equipment Personal Accessories Outdoor Protection Golf Equipment 3. In that case the calculation is assumed to belong to that hierarchy. the measure dimension is assumed.'Quantity sold 2005')
result:
Or rolling up all the products that start with the letter B member(total(currentMeasure within set filter([great_outdoors_company].[Products]. "string1" is used to identify the member created by this function it must be unique in the query.[Products]. "string2" is used as the caption of the member. otherwise the results are not predictable.[Products]. caption.2) result: Cooking Gear Sleeping Bags Packs Tents … Irons Putters Woods Golf Accessories levels([great_outdoors_company].34 member Syntax: member ( value_exp [ . unique id. and must be different from any other member in the same hierarchy. hierarchy ] ] ] ) Amember + b Member .'Quantity2005'. Example: member(total([great_outdoors_company]. Note: All calculations used as grouping items whose sibling items are other calculations or member sets should be explicitly assigned to a hierarchy using this function.[Products]. The only exception to this is where the calculation involves only members of the same hierarchy as the siblings. If the hierarchy is omitted. the caption is empty.[Products].

Example: members([great_outdoors_company].[Products]. Example:
Cognos Proprietary Information
. set_expr2 ) Description: Returns the set of members of set_expr2 evaluated in the context of the current member of set_expr1. In the case of a hierarchy.'B Products'.35
members Syntax: members ( hierarchy | level ) Description: Returns the set of members in a hierarchy or level.[great_outdoors_company].[Products])
result:
3. if a predictable order is required.caption([great_outdoors_company].[Years].36
nestedSet Syntax: nestedSet ( set_expr1 .'BProducts'.[Prod uct name]) starts with 'B')).[Years]) result: Years 2004 2004 Q 1 2004/Jan 2004/Feb 2004/Mar 2004 Q 2 2004/Apr … 2006 Q 3 2006/Jul 2006/Aug 2006/Sep 2006 Q 4 2006/Oct 2006/Nov 2006/Dec members([great_outdoors_company]. the order of the members in the result is not guaranteed.[Products].[Products].[Product line]) result: Camping Equipment Mountaineering Equipment Personal Accessories Outdoor Protection Golf Equipment
3.Dimensional Functions 24 name].[Products].[Products]. an explicit ordering function (such as hierarchize) must be used.

[Products].[Products]).[Product name] ). member ] ) Description: Returns the first sibling member among the descendants of a member at a specified level. nestedSet(members([Product line]). topCount( descendants( currentMember( [great_outdoors_company].39 order Syntax:
Cognos Proprietary Information
.[Years]. Example: nextMember([Outdoor Protection])
result: Golf Equipment
3.[Years].[Years].37
nextMember Syntax: nextMember ( member ) Description: Returns the next member in the level to which the specified member exists. 2.Dimensional Functions 25 The following example calculates the top 2 products by revenue for each product line.[Products].[Month]) result: 2004/Jan openingPeriod([great_outdoors_company].[Years].38 openingPeriod Syntax: openingPeriod ( level [ . [great_outdoors_company].[Products].[Years]. Example: openingPeriod([great_outdoors_company].[2 006 Q 4]) result: 2006/Oct 3.[Years]. Typically used with a time dimension.[Month]. [Revenue]))
result:
3.[Year]) result: 2004 openingPeriod([great_outdoors_company].

Dimensional Functions 26 order ( set_exp .[Product]. where B stands for "break hierarchy").[Product]. Example: order(members([Great Outdoors Company].[Product type]). In the absence of an explicit specification. Then it orders the children of each member according to "value_exp". The non-hierarchized ordering arranges members in the set without regard to the hierarchy. The hierarchized ordering first arranges members according to their position in the hierarchy. ASC | DESC | BASC | BDESC ] ) Description: Arranges members of a specified set.[Quantity sold]. as determined from the set of values created by evaluating "value_exp" for each value of the set. and modified by the third parameter. ASC is the default.[Product].[Product type]).[Product]. ASC)
result:
3. BASC) and order(members([Great Outdoors Company]. value_exp [ . There are two varieties of order: hierarchized (ASC or DESC) and nonhierarchized (BASC or BDESC.[Quantity sold]. Example:
Cognos Proprietary Information
.40
ordinal Syntax: ordinal ( level ) Description: Returns the zero-based ordinal value (distance from the root level) of the specified level.

Example: parallelPeriod([great_outdoors_company].
Cognos Proprietary Information
.[Product line]) result: 1 ordinal([great_outdoors_company].43 periodsToDate Syntax: periodsToDate ( level .2. member ) Description: Returns a set of sibling members from the same level as a given member.1. but is more closely related to time series.[Years]. [2006/Aug] ) result: 2006/May parallelPeriod([great_outdoors_company].41 parallelPeriod Syntax: parallelPeriod ( level .[Products].[Quarter]. Example: parent([Cooking Gear]) result: Camping Equipment 3. It takes the ancestor of "member" at "level" (call it "ancestor"). as constrained by a specified level.[Years]. and returns the descendants of that sibling in the same relative position as the specified member as under "ancestor".[Products].Dimensional Functions 27 ordinal([great_outdoors_company].[Years].[Product type]) result: 2 3.[Products]. This function is similar to the "Cousin" function.[Years].[Quarter]. Typically used with a time dimension. then it takes the sibling of "ancestor" that is offset (follows) by "int exp" positions.[Years].[20 06/Aug] ) result: 2004/Aug 3.42 parent Syntax: parent ( member ) Description: Returns the member that is the parent of the specified member.[Products].[Year]. It locates the ancestor of "member" at "level". and returns that ancestor's descendants at the same level as "member".[2006/Aug] ) result: 2006/Nov parallelPeriod([great_outdoors_company]. int_exp .[Years]. member ) Description: Returns a member from a different period in the same relative position as a specified member.1. up to and including "member".

[Years]. The second argument is optional only in a number of limited circumstances."2004-12-31") ("2005-01-01". (For dimensionally modelled relational data sources.) Intrinsic roles that are defined for members of all data source types include: _businessKey.[great_outdoors_company].44 prevMember Syntax: prevMember ( member ) Description: Returns the member that immediately precedes the specified member in the same level.[Year]. _memberUniqueName."2006-12-31") roleValue('_memberUniqueName'. For example. rather than by query item ID.[great_outdoors_company]. Example: prevMember ([Outdoor Protection]) result: Personal Accessories prevMember ([2005]) result: 2004 3. _memberDescription.45 roleValue Syntax: roleValue ( string [ . The property could then be referenced independently of the actual column name by using the roleValue function.[Years]. [2004/Feb].[Years]. Example: roleValue('_businessKey'."2005-12-31") ("2006-01-01".[ Year]) result: ("2004-01-01".[Years]. _memberCaption. assignment of roles is the modeller's responsibility. [2004/Mar] ) result: returns the value for [2004/Jan]. a Product type level may have an attribute column called “Type Shipping Container” and the Product level may have a “Product Shipping Container” attribute. [2004/Mar] 3. Additional roles can be defined in Framework Manager for each level in a hierarchy. member | set_exp ] ) Description: Returns the value of the attribute that is associated with the role whose name is specified by "string" within the specified context.[Years]. [Years]. Applications can be made portable across different data sources and models by accessing attributes by role. Each of these could be assigned a custom role in Framework Manager called “Container”.Dimensional Functions 28 Example: periodsToDate([great_outdoors_company].[Year])
Cognos Proprietary Information
. where it can be derived from other context.

Golf Equipment and Mountaineering Equipment return the members whose percentage total are greater than or equal to 40% for the tuple 2006
topPercent(set([Camping Equipment]. but the threshold is "numeric_exp1" percent of the total. This function works as follows: For a given set find the members whose sum percentage is greater than equal to a value based on a tuple numeric_exp1 ranges from 0 to 100.713.[Mountaineering Equipment]).[Products].990.tuple([2006].52 topPercent Syntax: topPercent ( set_exp .465. numeric_exp2 ) Description: This function is similar to topSum.92 Personal Accessories $31. Example: topPercent(set([Camping Equipment]. topCount([great_outdoors_company].[ Gross profit]))
result:
Cognos Proprietary Information
.[Golf Equipment].[Product line].[great_outdoors_company].Dimensional Functions 31
Based on a list report using a direct reference to the [Revenue] measure for numeric_exp.86 3.894.40.[2006]) For the set of Camping Equipment.2.[Golf Equipment]. numeric_exp1.[Mountaineering Equipment]).[Products].70.[Revenue]) result: Camping Equipment $89.[Measures].

[great_outdoors_ company]. set_exp2 [ .Dimensional Functions 32 3. The result retains duplicates only when the optional keyword ALL is supplied as the third argument.[Gross profit]))
result:
3. numeric_exp2 ) Description: This function sorts on "numeric_exp2".[Measures]. each of which must be from a different dimension. member } ) Description: Identifies a cell location (intersection) based on the specified members. numeric_exp1 . [Fax])
3.
Cognos Proprietary Information
. Implicitly includes the current member from all dimensions not otherwise specified in the arguments.16000000. The value of this cell can be obtained with the "value" function. Example:
Based on a crosstab report topSum(children([Products]).54
tuple Syntax: tuple ( member { . Example: tuple([Mountaineering Equipment].55
union Syntax: union ( set_exp1 .53 topSum Syntax: topSum ( set_exp . The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. evaluated at the corresponding members of "set_exp".tuple([2006]. and picks up the topmost elements whose cumulative total is at least "numeric_exp1". ALL ] ) Description: This function returns the union of 2 sets "set_exp1" and "set_exp2".

[Golf Equipment]).[Years (Root)]. set([Golf Equipment].56
value Syntax: value ( tuple ) Description: Returns the value of the cell identified by a tuple. [Mountaineering Equipment]). Many times there will be an implicit value returned and the value function may be unnecessary.[Measures]. [Camping Equipment] .[Years].50 value(tuple([2004].88
Cognos Proprietary Information
.750.[Years].[great_outdoors_company].471. [Golf Equipment]).Dimensional Functions 33 Example: union(set([Camping Equipment].328. Note that the default member of the Measures dimension is the Default Measure. [Mountaineering Equipment]))
result:
union(set([Camping Equipment].[2004010120041231]. In the examples below it is possible to remove the value function and achieve the same results.563. all)
result:
3.[Year]>:[PC]. set([Golf Equipment]. Example: value(tuple([great_outdoors_company]. [Revenue]) ) result: $20.[Revenue])) result: $34.