《Cognos+8+MDX-OLAP+Functions资料》由会员分享,可在线阅读,更多相关《Cognos+8+MDX-OLAP+Functions资料(35页珍藏版)》请在金锄头文库上搜索。
1、 Tip or Technique OLAP Functions Product(s): Cognos 8 BI Area of Interest: Report Design OLAP Functions 2 Cognos Proprietary Information 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
2、 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 C
3、ognos 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, cop
4、y, 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
5、 additions to Best Practices, Product and Technologies. OLAP Functions 3 Cognos Proprietary Information Contents 1 INTRODUCTION 5 1.1 PURPOSE5 1.2 APPLICABILITY.5 2 BACKGROUND INFORMATION 5 3 FUNCTION DEFINITIONS 6 3.1 _FIRSTFROMSET.6 3.2 _REMAINDERSET6 3.3 ANCESTOR.7 3.4 ANCESTORS.7 3.5 BOTTOMCOUNT
6、8 3.6 BOTTOMPERCENT8 3.7 BOTTOMSUM.9 3.8 CAPTION.10 3.9 CHILDREN.10 3.10 CLOSINGPERIOD.10 3.11 COMPLETETUPLE.11 3.12 COUSIN12 3.13 CURRENTMEMBER.12 3.14 DEFAULTMEMBER13 3.15 DESCENDANTS14 3.16 EMPTYSET.16 3.17 EXCEPT16 3.18 FILTER.16 3.19 FIRSTCHILD.17 3.20 FIRSTSIBLING17 3.21 GENERATE.18 3.22 HEAD.
7、18 3.23 HIERARCHIZE.19 3.24 HIERARCHY.19 3.25 ITEM.20 3.26 INTERSECT20 3.27 LAG.21 3.28 LASTCHILD21 3.29 LASTPERIODS.21 3.30 LASTSIBLING.22 3.31 LEAD.22 3.32 LEVEL22 3.33 LEVELS.22 3.34 MEMBER.23 3.35 MEMBERS24 3.36 NESTEDSET.24 3.37 NEXTMEMBER25 3.38 OPENINGPERIOD.25 3.39 ORDER.25 3.40 ORDINAL26 3.
8、41 PARALLELPERIOD27 3.42 PARENT27 3.43 PERIODSTODATE27 3.44 PREVMEMBER.28 3.45 ROLEVALUE.28 3.46 ROOTMEMEBERS.29 3.47 SET.29 OLAP Functions 4 Cognos Proprietary Information 3.48 SIBLINGS29 3.49 SUBSET29 3.50 TAIL30 3.51 TOPCOUNT30 3.52 TOPPERCENT.31 3.53 TOPSUM.32 3.54 TUPLE32 3.55 UNION.32 3.56 VAL
9、UE33 4 APPENDIX A FUNCTION GROUPING 34 4.1 BLOCK.34 4.2 DATE.34 4.3 FAMILY34 4.4 HIERARCHY.34 4.5 MEMBER.34 4.6 SET.34 4.7 TOP AND BOTTOM.35 4.8 VALUE.35 5 DEPLOYMENT OF FUNCTION SAMPLES 35 OLAP Functions 5 Cognos Proprietary Information 1 Introduction 1.1 Purpose This document provides additional d
10、etail and examples of the OLAP functions provided within Report Studio for Cognos 8. 1.2 Applicability The functions and sample data listed below apply to Cognos 8, Report Studio. The PowerCube and deployment package for “Great Outdoors Company” are provided with the product samples. Please see the
11、Administration and Security Guide for Cognos 8 regarding the steps to restore these samples. The provided deployment of sample reports can be found at the end of this document. This deployment file is valid for Cognos 8 MR1. 2 Background Information Below is an example hierarchy from the Product Dim
12、ension of the Great Outdoors Company PowerCube showing the members below the Camping Equipment Product Line. This dimension will be the basis of many of the subsequent examples. OLAP Functions 6 Cognos Proprietary Information 3 Function Definitions 3.1 _firstFromSet Syntax: _firstFromSet ( set_exp,
13、numeric_exp_max, 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, then only the max number of members are returned. For a set that has only a few members more than the s
14、pecified numeric_exp_max the numeric_exp_overflow allows the small set of extra members to be included. 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. Example: _firstFromSet(gre
15、at_outdoors_company.Products.Products.Prod uct line,2,8) result: Camping Equipment Golf Equipment Mountaineering Equipment Outdoor Protection Personal Accessories _firstFromSet(great_outdoors_company.Products.Products.Prod uct line,2,2) result: Camping Equipment Golf Equipment 3.2 _remainderSet Syntax: _remainderSet ( member_exp, set_exp, numeric_exp ) OLAP Functions 7 Cognos Proprietary Information Description: The member expression will be included in the returned set when the size of the set_exp set is greater than num