d53155 hfm 9.3.1_rules_student_guide

198
Hyperion Financial Management 9.3.1 Create Rules Student Guide D52786GC10 Edition 1.0 January 2008 D53155 ® ® Oracle Internal & Oracle Academy Use Only

Upload: ariffsharif

Post on 22-Jan-2015

9.392 views

Category:

Education


26 download

DESCRIPTION

This is the nice book for HFM rules

TRANSCRIPT

Page 1: D53155 hfm 9.3.1_rules_student_guide

HyperionFinancial Management 9.3.1Create RulesStudent Guide

D52786GC10

Edition 1.0

January 2008

D53155

®®

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 2: D53155 hfm 9.3.1_rules_student_guide

Copyright © 2008, Oracle. All rights reserved.

Disclaimer

This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle.

The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free.

Restricted Rights Notice

If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTSThe U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.

Trademark Notice

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Author

Mark Mitsock

Technical Contributors and Reviewers

Keith Glide

Jennifer Hough

Daniel Tijerina

Editor

Susan Moxley

Graphic Designer

Carisa Cannan

Publisher

Judy Gaitan

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 3: D53155 hfm 9.3.1_rules_student_guide

Table of Contents 0

Preface

Course Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viiCourse Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viiiCourse Materials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii

Student Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viiiActivity Guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii

Conventions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix

Module 1: Financial Management Rules Basics

Lesson 1: Reviewing Rules Syntax

About Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2Describing Objects and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3Creating Rules Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-8

HS.Exp Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-8Account Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-10Destination Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1-11Source Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1-11

Adding Operators and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-12Period and Year Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-13Placing Other Functions Within Exp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-13

Identifying Rule Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-14Identifying Sub Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-16Adding Comments to Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-18Inserting Line Continuations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-19Creating Conditional Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-21

Applying If...Then...Else Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-22Making Decisions with Select Case Statements . . . . . . . . . . . . . . . . . . . . . . . . . 1-24

Comparing Strings in Financial Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-25UCase Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-26LCase Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-26Left Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-26Right Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-27

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 4: D53155 hfm 9.3.1_rules_student_guide

Table of Contents

iv Hyperion Financial Management 9.3.1: Create Rules

Lesson 2: Reducing Maintenance with Variables

About VB Script Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2Creating Variables and Assigning Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3Variables and Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5Variables and Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7Creating Header Sections for Variables and Constants. . . . . . . . . . . . . . . . . . . . . . . . 2-9

Point of View Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10Top and None Members for Custom and ICP Dimensions . . . . . . . . . . . . . . . . . 2-10Global Accounts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-11Conditional Statement Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-11

Lesson 3: Managing the Scope of Rules

Subcubes and Data Retrieval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2Financial Management Subcubes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3Subcube Dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-4

Subcubes and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-5Subcubes and Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7The Left Side of HS.Exp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7Page Dimensions and HS.Exp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-9

Managing the Scope of Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3-11Omitting Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-12Specifying the Destination Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-13Specifying Source Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-14Mismatched Custom Dimensions (Example 1) . . . . . . . . . . . . . . . . . . . . . . . . . . 3-15Mismatched Custom Dimensions (Example 2) . . . . . . . . . . . . . . . . . . . . . . . . . . 3-17

Executing Rules with the Value Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-19Sub Calculate and the Value Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-21Working with Total Members . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-23

Lesson 4: Creating Rules for Dynamic Accounts

Dynamic Account Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2Sub Dynamic Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-3Creating Rules for Dynamic Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-4

Functions Valid with HS.Dynamic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-6

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 5: D53155 hfm 9.3.1_rules_student_guide

Table of Contents

Hyperion Financial Management 9.3.1: Create Rules v

Module 2: Creating Procedures, Loops, and Arrays

Lesson 5: Creating Custom Procedures

Creating Custom Sub Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2Calling Custom Sub Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4Creating Custom Function Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-6Calling Custom Function Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-8Troubleshooting with Logging Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-9

Writing to Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-10Calling Write to File Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-14

Managing Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-16DeleteFile Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-17FileExists Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-17GetFile Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-17MoveFile Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-18Size Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-18

Lesson 6: Creating Arrays and Loops

Arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3Arrays and Loops in Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-4

Creating Array Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6Filling Arrays Using Member Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-7Creating Loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-8

For...Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-8For Each...Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-9Do...Loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-10

Assigning Values with Arrays and Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-12Attributes in Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-14Loops and Data Units . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-15

Opening Data Units . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-16Creating Loops With Data Units. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-18

Module 3: Working with Data and Hierarchies

Lesson 7: Managing Financial Management Data

Retrieving and Writing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-2

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 6: D53155 hfm 9.3.1_rules_student_guide

Table of Contents

vi Hyperion Financial Management 9.3.1: Create Rules

Testing for No Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-4Setting Accounts to No Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-6Setting Parent Entities to Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-8Rounding and Scaling Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-10Rounding and Scaling with HS.Round. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-12

Lesson 8: Working with Dimension Hierarchies

Working with the Calendar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-2Preventing Prior and Next from Crossing into Invalid Years . . . . . . . . . . . . . . . . . 8-3Populating Days in Period Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-5Managing Calculation Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-6

Managing Movement Accounts and Data Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-8Avoiding Circular Calculations in Hierarchies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-10Creating Allocations Using Sub Allocate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-12

Running the Sub Allocate Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-13Using the Alloc Function to Create Allocation Rules . . . . . . . . . . . . . . . . . . . . . . 8-15

Module 4: Creating Custom Translations and Consolidations

Lesson 9: Creating Currency Translation Rules

About Translating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-2Currency and Exchange Rate Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-3Translation Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-5Default Translation Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-7

Creating Custom Translation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-9Translation Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9-11Calculating Exchange Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-13Working with Currency Overrides . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-15

Lesson 10: Creating Consolidation and Elimination Rules

Default Consolidation Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-2Creating Consolidation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-4

Consolidation Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-5Consolidation Procedure Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-9Testing for Eliminations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10-11

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 7: D53155 hfm 9.3.1_rules_student_guide

Preface 0

Welcome to Hyperion Financial Management 9.3.1: Create Rules!

Before you begin, please take a moment to review this section. The preface presents an overview of:

• The course objectives

• The course structure

• The course materials

• Writing conventions used in this manual

• Additional resources to enhance your learning

• Relevant follow-up courses you might want to attend in the future

Course ObjectivesAfter completing this course, you should be able to:

• Reduce database size and calculation time by proper use of the custom and value dimensions in rules

• Calculate percentages and ratios using rules for dynamic accounts

• Create custom procedures, functions, loops, and arrays in rules

• Create rules with proper data handling techniques

• Create allocation rules

• Create custom currency translation rules

• Create custom consolidation and elimination rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 8: D53155 hfm 9.3.1_rules_student_guide

Preface

viii Hyperion Financial Management 9.3.1: Create Rules

Course StructureHyperion Financial Management 9.3.1: Create Rules is a 3-day, instructor-led training course consisting of lectures, demonstrations, and hands-on exercises. In this course, the instructor presents a topic conceptually by explaining its purpose, demonstrating how it works, and then guiding the students through the exercises. Demonstrations and hands-on exercises reinforce the concepts and skills introduced during lectures.

Course MaterialsYou use two books in class—the student guide and the student workbook. The instructor may also give you handouts.

Student GuideThe student guide is designed to be used by students and the instructor during lecture time. It has four modules:

• Module 1 describes the basics of Financial Managment rules.

• Module 2 describes how to create custom Sub and Function procedures. You learn to create rules using arrays and loops.

• Module 3 describes how to work with Financial Management data and hierarchies.

• Module 4 describes how to customize the default currency translation and consolidation calculations.

Each module contains lessons. Each lesson begins with a list of objectives followed by the presentation of slides and accompanying text. The lesson ends with a summary of the topics covered in the lesson.

A glossary provides definitions of terms used during the course.

Activity GuideThe activity guide has two sections—exercises and exercise solutions.

ExercisesA critical part of the learning process is the challenge of completing real tasks associated with each lesson. Each exercise is an opportunity to apply your new knowledge.

Exercise SolutionsThe exercise solutions present the detailed steps to successfully complete the exercises.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 9: D53155 hfm 9.3.1_rules_student_guide

Preface

Hyperion Financial Management 9.3.1: Create Rules ix

ConventionsThe following text conventions are used in this course book:

• Text to be typed, options to be selected, names of files and modules, and menu selections are displayed in bold type. Examples:

- Select Clear Profile.

- To clear the profile, click Yes.

• Keyboard shortcuts are displayed as follows:

Ctrl+Enter

For the example, you would press the Ctrl key and the Enter key at the same time.

Tips and Notes are used to direct your attention to different types of information.

N O T E

A note provides related information, common mistakes, and cautions about the current topic.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 10: D53155 hfm 9.3.1_rules_student_guide

Preface

x Hyperion Financial Management 9.3.1: Create Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 11: D53155 hfm 9.3.1_rules_student_guide

M O D U L E 1

Financial Management Rules Basics 0

OverviewIn this module you learn the basics for creating Financial Management rules.

Lessons in this module include:

• Reviewing Rules Syntax

• Reducing Maintenance with Variables

• Managing the Scope of Rules

• Creating Rules for Dynamic Accounts

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 12: D53155 hfm 9.3.1_rules_student_guide

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 13: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 1

Reviewing Rules Syntax 1

ObjectivesAt the end of this lesson, you should be able to:

• Explain the purpose of rules in Financial Management

• Describe objects and functions in expressions

• Create rules expressions

• Identify Financial Management rule types

• Distinguish between Sub procedures

• Add comments and line breaks

• Create conditional statements and compare strings

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 14: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-2 Hyperion Financial Management 9.3.1: Create Rules

About RulesYou use rules to perform calculations that you cannot define through parent-child relationships in the dimension hierarchy. For example, you can create a rule to calculate the value of the Salaries member by multiplying the Headcount member by the SalaryRate member.

Copyright © 2008, Oracle. All rights reserved.

About Rules

Calculate data that cannot be calculated through a hierarchical aggregation, such as ratios or variance analysesPerform complex currency conversions and calculate exchange ratedifferences or other calculations necessary for consolidationPrevent data entry for a specific cellPerform allocations from a parent entity to a list of base entitiesEnable data entry to a parent entityPerform custom consolidations for statutory reporting requirements

Hyperion Financial Management rules provide powerful, customizedcalculations that you can use to perform the following tasks:

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 15: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-3

Describing Objects and Functions A programming language consists of its own vocabulary and grammar. Objects are equivalent to nouns in programming languages. For example, in Financial Management, the Entity object represents the Entity dimension, the Account object represents the Account dimension, and so on. Functions are the verbs that express the actions of a programming language.

To create rules, you should be familiar with these basic principles about objects:

• Objects contain their own sets of functions. For example, you can use certain functions only with the Period object.

• Objects can be children of other objects. For example, the top-level object in Financial Management is named HS. The HS object contains some functions and several other objects.

• When you write rules, you use dot notation (dot) to separate objects from other objects and functions.

Copyright © 2008, Oracle. All rights reserved.

Describing Objects and Functions

Objects represent elements of a Financial Management application, such as dimensions.Functions represent actions that a rule can perform, such as retrieving a value.You write rules statement by applying functions to objects.Not all functions are valid for all objects.

HS.Entity.DefCurrency

Object Function

This statement uses the DefCurrency function to retrieve the default currency of an entity.

HS.Scenario.DefCurrency The DefCurrency function is invalid here because scenarios do not have currencies.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 16: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-4 Hyperion Financial Management 9.3.1: Create Rules

The following table lists the functions by object for Financial Management rules:

Object FunctionHS ABSExp

AllocCalcStatusClearConExpDynamicGetCellGetCellNoDataGetCellRealDataGetCellTypeGetRateImpactStatusInputNoInputNoRoundOpenDataUnitReviewStatusRoundSetDataSetDataWithPOVTransTransPeriodic

Account AccountTypeC1...4 TopIsBaseIsChildIsConsolidatedIsDescendantIsICPListNumBaseNumChildNumDescendantPlugAccountSecurityClassUD1...3ValidationAccountXBRLTags

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 17: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-5

AppSettings CurrencyICPWeightPVAForBalancePVAForFlowRateForBalanceRateForFlow

Currency Scale

Custom1, Custom2, Custom3, and Custom4

IsBaseIsDescendantListNumBaseNumChildNumDescendantSecurityClassSwitchSignSwitchTypeUD1...3

DataUnit GetItemGetItemIds2GetNumItems

Entity AllowAdjsAllowAdjsFromChildrenDefCurrencyHoldingIsBaseIsChildIsDescendantIsICPListMemberNumBaseNumChildNumDescendantSecurityAsPartnerSecurityClassUD1...3

ICP List

Object Function

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 18: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-6 Hyperion Financial Management 9.3.1: Create Rules

Node Consol1...3DOwnIsBaseIsChildIsDescendantListMethodNumBaseNumChildNumDescendantPConPOwn

Parent DefCurrencyHoldingIsBaseIsChildIsDescendantIsICPListMemberNumBaseNumChildNumDescendantUD1...3

Period IsFirstIsLastListMemberNumBaseNumber

Scenario ConsolidateYTDDefaultFreqDefaultViewListMemberNumPeriodsSecurityClassUD1...3

Object Function

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 19: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-7

Value CurrencyIsTransCurIsTransCurAdjMember

Year IsFirstIsLastMember

Object Function

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 20: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-8 Hyperion Financial Management 9.3.1: Create Rules

Creating Rules ExpressionsYou create rules expressions by using Financial Management objects and functions to perform these types of tasks:

• Calculating data

• Consolidating data

• Setting accounts to read-only

HS.Exp FunctionThe most frequent use of a rule expression is assigning values to accounts. Use the HS.Exp function to assign values to accounts.

Copyright © 2008, Oracle. All rights reserved.

You can create rules expressions to assign values to accounts:You use the HS.Exp function with an account expression to assignvalues to accounts.HS.Exp expects a destination value on the left side and a source value on the right side of the equal sign.

You can use account expressions within Exp to specify the source and destination values.

Creating Rules Expressions

HS.Exp "Destination=Source"

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 21: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-9

Use this syntax:

HS.Exp “DestinationValue = SourceValue”

The following example sets the Cash account to 50,000:

HS.Exp "A#Cash = 50000”

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 22: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-10 Hyperion Financial Management 9.3.1: Create Rules

Account Expressions Account expressions identify cells in the database by specifying one or more dimension members. The Exp function requires an account expression on the left (destination) side of the equal sign. The right (source) side of the equal sign can be an account expression, a constant value, or any function that returns a numeric value.

Dimension KeywordsAn account expression uses a dimension keyword to specify a value or a set of values. A dimension keyword is separated from its values by a pound or hash sign (#), and dimensions are separated by dots. For example:

A#Cash.P#January.E#USA.C1#OpeningBalance

Copyright © 2008, Oracle. All rights reserved.

An account expression uses a dimension keyword to specify a value or a set of values.A dimension keyword is separated from its values by a pound sign (#).

Dimensions are separated by periods.

Account Expressions

A#NetIncome

A#Cash.P#January.E#USA.C1#OpeningBalance

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 23: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-11

The table lists the dimension keywords that you can use to specify dimension members:

Destination ExpressionsThe destination for the Exp function is specified by the Account, Custom, and ICP members specified on the left side of the equal sign. The destination Entity, Period, Year, Value, members are determined by the current point of view (POV) members or by the cells selected on the data grid or form. The members for the currently selected cells on the grid override the current POV members. The destination View member is the current scenario default view, regardless of the currently select POV members.

Source ExpressionsWhen you use an account expression on the right side of the equal sign with Exp, you can specify any dimension member. If you do not specify a Entity, Period, Year, or Value dimension member, the current POV member or the current cells selected on the grid or data form are used. If you do not specify a View member, the source is the current scenario default view, regardless of the currently select POV member.

Keyword Description

S# Scenario

Y# Year

P# Period

V# Value

E# Entity

W# View

A# Account

I# Intercompany Partner

C1# Custom1

C2# Custom2

C3# Custom3

C4# Custom4

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 24: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-12 Hyperion Financial Management 9.3.1: Create Rules

Adding Operators and FunctionsYou can add, subtract, multiply, and divide on the right side of the equal sign. You must use the following standard VBScript characters: + - * /

If you multiply or divide with an account that has a NoData status, the data in the account on the left side of the equal sign is not changed. Zero is considered data. An account that contains 0.00 as data does not have a NoData status.

The following example sets the amount in the StateTax account. This example calculates the StateTax amount by multiplying the amount in the Sales account for 2005 by the rate in the StateRate account for 2005:

HS.Exp "A#StateTax = A#Sales.Y#2005 * A#StateRate.Y#2005"

Copyright © 2008, Oracle. All rights reserved.

Adding Operators and Functions

You can use standard math operators on the right side of the equal sign.

You can use Period and Year keywords for dynamic time calculations.

You can embed other HS functions within Exp.

HS.Exp "A#Sales = A#Units * A#Price"

HS.Exp "A#MiscPast = A#Misc.Y#Cur-2"

HS.Exp "A#AvgSales = A#Sales/HS.Entity.NumBase(USA)"

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 25: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-13

Period and Year KeywordsTo create dynamic rules, you can use the following keywords, instead of member names, to represent members of the destination Year or Period dimension:

You can use plus (+) and minus (-) with the Period and Year keywords. The following example sets the MiscPast account to the amount in the Misc account two periods before the current period:

HS.Exp "A#MiscPast = A#Misc.P#Cur-2"

Placing Other Functions Within ExpIf an HS function returns a single numeric value, you can nest the function in the Exp function. However, if you nest a function that contains a string argument, you cannot enclose the string in quotation marks. In the following example, the NumBase function is nested in the Exp function to retrieve the number of base entities for the Regional member:

HS.Exp "A#SalesAlloc = A#Sales/HS.Entity.NumBase(Regional)"

Notice that the Regional string is not enclosed in quotation marks.

Keyword Description

Cur Current period or year

First First period or year that is valid for the application

Last Last period or year that is valid for the application

Next Period or year that follows the current period or year

Prior Period or year that precedes the current period or year

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 26: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-14 Hyperion Financial Management 9.3.1: Create Rules

Identifying Rule TypesRules fall into eight types:

• Calculate rules are executed when you perform calculations and consolidations. You use calculate rules for these tasks:

- Set and clear values from accounts

- Perform ad hoc calculations

- Conform to statutory requirements

• Dynamic rules enable you to create ratios that accurately calculate parent values for the Period, VIew, and custom dimensions. Parent values for percentages for these dimensions are not accurately calculated by the aggregation of base member values.

• Translate rules execute when you perform translations. These rules can override default translation calculations.

Copyright © 2008, Oracle. All rights reserved.

Identifying Rule Types

There are eight types of rules in Financial Management:— Calculate— Dynamic— Translate — Allocation— Input— No Input — Consolidate— Transactions

You place the rules for each type in a separate sub procedure in the rules file.

Sub Calculate() HS.Exp "A#TargAcct=A#SourceAcct“End Sub

Sub NoInput()HS.NoInput"A#Sales.S#Budget“End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 27: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-15

• Allocation rules execute when you allocate data from a single source to multiple destinations.

• Input rules enable input at the parent entity level.

• No input rules prevent input at the base entity, account, and custom level.

• Consolidate rules perform nonstandard consolidations. These rules are most commonly found in statutory applications.

• Transactions rules enable posting to accounts and scenarios from the Intercompany Transactions module.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 28: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-16 Hyperion Financial Management 9.3.1: Create Rules

Identifying Sub ProceduresA Sub procedure can take arguments (constants, variables, or expressions passed by a calling procedure). If a Sub procedure has no arguments, its Sub statement must include an empty set of parentheses ().

Rules of each type are grouped into Sub procedures in the rules file. Rules statements are grouped into the following procedures:

Routine ExecutedCalculate() and Dynamic() When you calculate or consolidate data

Translate() When you translate data

Allocate() When you use the Allocate option in a data grid

Input() When the application is opened.

Copyright © 2008, Oracle. All rights reserved.

Identifying Sub Procedures

A Sub procedure is a series of VBScript statements (enclosed by Sub and End Sub statements) that performs actions but does not return a value.

Sub Calculate() HS.Exp "A#TargAcct=A#SourceAcct“End Sub

Sub NoInput()HS.NoInput "A#Sales.S#Budget“End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 29: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-17

The routines are created in any order. Use the following syntax to define each routine:

Sub Calculate()<All calculate rules are displayed here.>End SubSub Dynamic()<All dynamic rules are displayed here.>End SubSub Translate()<All translate rules are displayed here.>End SubSub Allocate()<All allocation rules are displayed here.>End SubSub NoInput()<All no input rules are displayed here.>End SubSub Consolidate()<All consolidate rules are displayed here.>End SubSub Input<All input rules are displayed here.>End SubSub Transactions() <All transactions rules are displayed here.>End Sub

NoInput() When the application is opened. Automatically prevent you from entering data in specific cells

Transactions() When the application is opened.

Consolidate() When you run a consolidation.

Routine Executed

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 30: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-18 Hyperion Financial Management 9.3.1: Create Rules

Adding Comments to RulesDocumentation in Financial Management rules files is considered almost as important as the code itself. Without documentation, administrators have a difficult time interpreting and troubleshooting another administrator’s procedures. Even if you are the only one maintaining the rules files, without proper documentation you can easily forget the intent of the procedure.

Text preceded with an single quote(‘) is interpreted as a comment. Although you can start comments anywhere within a line, it is recommended that you place a comment on its own line. This ensures that the line is properly validated.

This is an example of inserting comments in your rules files:

' * Copy Actual Q1 rates to Q_ForecastIf HS.Scenario.Member = "Q_Forecast" and HS.Period.IsFirst Then

HS.Clear "A#ALL"HS.Exp "A#ALL = S#Actual"End If 'HS.Scenario.Member = "Q_Forecast" and HS.Period.IsFirst

Copyright © 2008, Oracle. All rights reserved.

Adding Comments to Rules

You can insert comments in rules files to:Document the purpose and results of proceduresProvide detail information for other administrators who may inherit rules files you createdAct as a reminder of what you may need to modify in future releases of Financial Management

‘Routine is executed when user ‘calculates or consolidates data.Sub Calculate() HS.Exp "A#TargAcct=A#SourceAcct“End Sub

Sub NoInput()HS.NoInput"A#Sales.S#Budget“End Sub

Comment

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 31: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-19

Inserting Line ContinuationsYou can use line continuation so that the entire string can be viewed without having to scroll to the right of the code window.

If you must break a line into multiple strings, place the line continuation character between the strings, and then concatenate them using the ampersand (&). It is critical to preserve all spaces in the string when it is concatenated.

N O T E

You cannot use the line continuation character in comments, you must repeat the comment character apostrophe (‘) at the beginning of each comment string line.

Copyright © 2008, Oracle. All rights reserved.

Inserting Line Continuations

Line Continuation:Uses an ampersand (&) for concatenation Uses an underscore (_) for line continuation

Note: You must have a space between the ampersand and theunderscore.

Line Continuation Syntax

Concatenation

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 32: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-20 Hyperion Financial Management 9.3.1: Create Rules

Line Continuation Tips

• Break complex formulas at a point where a line performs an action.

• If you include long dimension names in the code line, break the line to show one account per line.

• Insert the mathematical operators at the beginning of the line so that you can identify what type of action is being applied to the account in the formula. When the line break is applied, the operators and account are split in a logical manner.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 33: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-21

Creating Conditional StatementsYou can control the flow of your script with conditional statements. You can write VBScript that makes decisions and repeats actions.

Copyright © 2008, Oracle. All rights reserved.

Creating Conditional Statements

The following condition statements are used in rules files:If…Then…Else Select Case…Else

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 34: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-22 Hyperion Financial Management 9.3.1: Create Rules

Applying If...Then...Else StatementsThe If...Then...Else statement allows several lines of code to be executed or several comparisons to be made within the one statement.

You use the If...Then...Else statement to evaluate whether a condition is true or false and, depending on the result, to specify one or more statements to run. The condition is typically an expression that uses a comparison operator to compare one value or variable with another. You can nest If...Then...Else statements to as many levels as needed.

• Use and ElseIf...Then statement to add conditions.

• For If...Then...Else statements that have multiple conditions, the first statement that evaluates to true is executed. Any conditions that follow are not evaluated or executed.

• If you include an Else statement, the lines of script after the Else statement execute if none of the condition are met.

Copyright © 2008, Oracle. All rights reserved.

Applying the If…Then…Else Statement

The most common form of the If...Then...Else statement is the block form which allows for several lines of code to be executed or severalcomparisons to be made, within the one statement.

If HS.Scenario.Member = "Budget" ThenHS.EXP "A#Sales = A#UnitsSold * A#Price"

End If

Is executed only if the current member is Budget

If HS.Scenario.Member = "Budget" ThenHS.EXP "A#Sales = A#UnitsSold * A#Price"

ElseIf HS.Scenario.Member = "Actual" ThenHS.EXP "A#Price = A#Sales / A#UnitsSold"

ElseHS.EXP "A#Sales = A#Sales.P#Prior * 1.1"

End If

Uses ElseIf to test for more than one condition

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 35: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-23

The examples shown on the slide illustrate If...Then...Else statements:

• The first example verifies whether the scenario member is equal to Budget. If equal (true), a calculation multiplies Units Sold by Price, and the result is applied to the Sales account. If the condition evaluates to false, no calculation is performed.

• The second example applies the ElseIf statement if the scenario member is equal to Actual instead of Budget. If equal (true), a calculation divides Sales by UnitsSold and applies the result to Price. If the scenario is any member other than actual or budget, the calculation following the Else statement is performed.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 36: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-24 Hyperion Financial Management 9.3.1: Create Rules

Making Decisions with Select Case StatementsThe Select Case structure provides an alternative to If...Then...ElseIf for selectively executing one block of statements from among multiple blocks of statements. A Select Case statement provides capability similar to the If...Then...Else statement, but makes code more efficient and readable when there are a large number of conditions to evaluate. The first Case statement that evaluates to true is executed. Any Case statements that follow are not evaluated or executed.

Copyright © 2008, Oracle. All rights reserved.

Making Decisions with Select Case Statements

A Select Case structure works with a single test expression that is evaluated once, at the top of the structure.

The result of the expression is then compared with the values for each Case in the structure.

If there is a match, the block of statements associated with that Case is executed, as in the following example.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 37: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-25

Comparing Strings in Financial ManagementString comparisons are important in Financial Management for these reasons:

1. To avoid simple errors in uppercase or lowercase strings when conditional statements are used. For example, the string 'Budget' is used as the dimension member, but you enter 'budget' for the comparison (one uses an upper case ‘B’ and the second one uses a lower case ‘b’). With string comparison, if you always type lowercase and then convert variables and function results to lowercase you will not have mismatches.

2. You can use string comparisons to check for one or more characters that use a prefix or suffix in member names. This is useful for chart of accounts or entity structures that use coding in labels. For example, You use a product dimension and all hardware product member labels start with H100. You can create a string comparison to check the first 4 characters of the member label and if it equals H100 then do the calculation.

Copyright © 2008, Oracle. All rights reserved.

Comparing Strings in Financial Management

The following are four frequently used string functions:UCase

LCase

Left Function

Right Function

If UCase(HS.Entity.Member)="NONE" Then…

If LCase(HS.Scenario.Member)= "budget" Then...

Dim Product1, RtnStringProduct1 = "Financial Management" ' Define string.RtnString = Left(Product1, 9) ' Returns "Financial"

Dim Product1, RtnStringProduct1 = "Financial Management" ' Define string.RtnString = Right(Product1, 10) ' Returns “Management"

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 38: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-26 Hyperion Financial Management 9.3.1: Create Rules

UCase FunctionReturns a string that was converted to uppercase.

Syntax

UCase(String)

Arguments

String: A text string or a function that returns a text string.

Example

If UCase(HS.Scenario.Member)=”ACTUAL” Then

Only lowercase letters are converted to uppercase; all uppercase letters and nonletter characters remain unchanged.

LCase FunctionReturns a string that was converted to lowercase.

Syntax

LCase(String)

Example

If LCase(HS.Scenario.Member)=”actual” Then

Only uppercase letters are converted to lowercase; all lowercase letters and nonletter characters remain unchanged.

Left FunctionReturns a string containing a specified number of characters from the left side of a string.

A left function contains two required parts:

• Use String to return the requested values.

• Use Length (numeric value) to determine the number of characters to return.

Syntax

Left(String,Length)

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 39: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

Hyperion Financial Management 9.3.1: Create Rules 1-27

Example

Dim Product1, RtnStringProduct1 = "Financial Management" ' Define stringRtnString = Left(Product1, 1) ' Returns "F"RtnString = Left(Product1, 9) ' Returns "Financial"RtnString = Left(Product1, 20) ' Returns "Financial Management"

Right FunctionReturns a string containing a specified number of characters from the right side of a string.

A Right function contains two required parts:

• Use String to return the requested values.

• Use Length (numeric value) to determine the number of characters to return.

Syntax

Right(String,Length)

Example

Dim Product1, RtnStringProduct1 = "Financial Management" ' Define string.RtnString = Right(Product1, 1) ' Returns "t".RtnString = Right(Product1, 10) ' Returns "Management".

RtnString = Right(Product1, 20) ' Returns "Financial Management".

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 40: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 1 Reviewing Rules Syntax

1-28 Hyperion Financial Management 9.3.1: Create Rules

SummaryIn this lesson, you should have learned to:

• Explain the purpose of rules in Financial Management

• Describe objects and functions in expressions

• Create rules expressions

• Identify Financial Management rule types

• Distinguish between Sub procedures

• Add comments and line breaks

• Create conditional statements and compare strings

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 41: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 2

Reducing Maintenance with Variables 2

ObjectivesAt the end of this lesson, you should be able to:

• Create variables and constants

• Set up variables header sections for the Point of View

• Set up variables header sections for custom dimensions, intercompany partner (ICP) dimensions, and global accounts

• Set up a variables header section for conditional triggers

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 42: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-2 Hyperion Financial Management 9.3.1: Create Rules

About VB Script VariablesVariables are placeholders that temporarily store values when the rules script is being executed. You can change the value of variables as many times as needed during execution.

Variables simplify your script by letting you give short, descriptive names to data used in your rules. For example, pov_entity instead of HS.Entity.Member

Variables improve performance because you can retrieve application data once and then reuse the data throughout a procedure. For example, you could retrieve the year total for the Sales account from your Financial Management application and store it in a variable. You can then use the variable in a series of calculations in your procedure, instead of retrieving the value from the application each time.

Copyright © 2008, Oracle. All rights reserved.

About VB Script Variables

Variables temporarily store values when your script is running. Variables simplify rules scripts. Variables improve rules performance.

Dim vCurPeriod, vCurYear

vCurPeriod=HS.Period.NumbervCurYear=HS.Year.Member

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 43: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

Hyperion Financial Management 9.3.1: Create Rules 2-3

Creating Variables and Assigning ValuesYou can create variables explicitly using one or more Dim statements at the start of a subroutine. This method, called declaring the variables, enables you to look in a single place in a procedure when you want to reuse variables and need to remember their names.

You can also create variables on the fly. However, they are scattered throughout the procedure. This method makes it difficult to check variable names when you want to reuse them.

Dim Statement Syntax:

Dim VariableName

For example,

Dim vAcc1

Copyright © 2008, Oracle. All rights reserved.

Creating Variables and Assigning Values

Declare variables explicitly using Dim statementsCreate variables on the flyEnclose values in quotation marks to enter a literal text string

You can concatenate variables with literal text strings

vAcc1=”A#NetSales”vSalaryRate=300pov_entity=HS.Entity.Member

HS.EXP "A#RetainedIncome=A#Profit" &vCustomTops& ".I#[ICP None]"HS.EXP "A#SalaryExpense=A#Headcount *" &vSalaryRate

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 44: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-4 Hyperion Financial Management 9.3.1: Create Rules

Variable name guidelines:

• Must begin with an alphabetic character

• Cannot contain an embedded period

• Must not exceed 255 characters

• Must be unique in the scope in which it is declared

You can create multiple variables with a single Dim statement by separating the variable names with commas.

Example

Dim vAcc1, vAcc2, vAcc3

You assign values to variables using an equal sign (=), with the variable name on the left and the value you want to assign the variable on the right. You can assign literal text strings, numeric values, return values of functions, or return values of expressions. If the variable does not exist, it is created on the fly. To assign a literal string value, you enclose the string in quotation marks. You do not need quotation marks to assign numeric values, function results, or expression results.

vCustomTops=”.C1#TopC1.C2#TopC3.C3#TopC3.C4#TopC4”vSalaryRate=300pov_entity=HS.Entity.Member

You can concatenate variables with literal text strings:

HS.EXP “A#RetainedIncome=A#Profit"&vCustomTops&“.I#[ICP None]”

HS.EXP “A#SalaryExpense=A#Headcount *"&vSalaryRate

Notice that when the variable is at the end of the HS.Exp statement, it does not require a closing quotation mark.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 45: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

Hyperion Financial Management 9.3.1: Create Rules 2-5

Variables and Data TypesIn VBScript, you cannot specify in advance that a variable holds only a particular data type. Instead, you must use a variable known as a variant to store any data type.

When you assign a value to the variable, VBScript automatically assigns the data type. Sometimes you may need to override the default data type. For example, you may need to store all values as integers.

You can use conversion functions to explicitly set the data type:

Function DescriptionCBool Converts nonzero values to TRUE and zero values to FALSE.

CDate Converts an expression to a Date value.

CDbl Converts an expression to a Double value (a 64-bit floating point number).

Copyright © 2008, Oracle. All rights reserved.

Variables and Data Types

VBScript assigns data types automatically.You can use conversion functions to force a data type.— Cbool— CDate— CDbl— CInt— CLng— CSng— CStr

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 46: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-6 Hyperion Financial Management 9.3.1: Create Rules

This example converts the result of the calculation to an integer and stores it in the variable vGM_Pct:

vGM_Pct=CInt(vMargin/vNetSales*100)

CInt Converts an expression to an Integer value. If the fractional part of the expression is .5, CInt rounds the value to the nearest even number. For example, 3.5 is rounded to 4, and 6.5 to 6. The value of integer data can range from -32,768 to 32,767.

CLng Converts an expression to a Long value (an integer that can store a value from -2,147,483,648 to 2,147,483,647).

CSng Converts an expression to a Single value (a 32-bit floating point number).

CStr Converts an expression to a String value.

Function Description

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 47: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

Hyperion Financial Management 9.3.1: Create Rules 2-7

Variables and ConstantsVariables can be used only in the Sub procedure in which they are created. Constants are similar to variables, but with these differences:

• You can use constants in all Sub procedures within the script.

• After you define a constant (that is, after it has been assigned a value), you cannot change it.

You can declare constants anywhere in the script file. If constants are declared at the beginning of the file, outside of any procedure, they are available to all procedures at all times. If constants are declared within a procedure, they are available only for that procedure.

Copyright © 2008, Oracle. All rights reserved.

Variables and Constants

You declare constants at the beginning of rules files.They are available to all procedures at all times.After you assign a value to a constant, you cannot change it.You can use constants anywhere in your code in place of actual values, just as you use variables.

const ALL_NONE = ".I#[ICP None].C1#[None].C2#[None].C3#[None]"const ALL_TOPS = ".I#[ICP Top].C1#TopC1.C2#TopC2.C3#TopC3"

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 48: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-8 Hyperion Financial Management 9.3.1: Create Rules

You can use constants to store application information that you use frequently but that does not change. For example, you can create constants to store member names that are used frequently within account expressions. By using a short constant name in place of a long string of member names, you reduce the likelihood of errors. In Financial Management rules, you typically use constants to store information that does not vary with the Point of View settings for which the rules are run.

Unlike variables, you must explicitly declare constants. They cannot be created on the fly.

Syntax

const Name=Value

where Name is the name of the constant and Value is the value of the constant. The rules for naming constants are the same as for variables. This example creates a constant named AVE and assigns it a string as a value:

Example

const AVE=”.A#AverageRate”

A naming convention for constants is to use uppercase for names and underscores as separators, as in this example:

const PRIOR_YEAR_RATE=75

You cannot use functions to assign values to constants. This statement returns an error:

const CURRENT_ENTITY=HS.Entity.Member

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 49: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

Hyperion Financial Management 9.3.1: Create Rules 2-9

Creating Header Sections for Variables and ConstantsIt is a useful practice to create a standard header section in your Sub procedures with variables for frequently used information for your application. For application information that does not changed based on the Point of View, you can create a constants header section at the beginning of the rules file.

These are some common types of information to include in a header section:

• Current Point of View members for page dimensions

• Top and None members for custom and ICP dimensions

• Global account members

• Conditional statement triggers

Copyright © 2008, Oracle. All rights reserved.

Creating Header Sections for Variables and Constants

These are some typical types of information stored in header sectionvariables:

The current POV members for the POV dimensions

Top and [None] members for custom and ICP dimensions; global accounts

Triggers for conditional statements

vIs_Trans = HS.Value.IsTransCur 'This yields a True or FalsevIs_base = HS.Entity.IsBase("","") 'This yields a True or False

pov_entity = HS.Entity.Memberpov_scenario = HS.Scenario.Memberpov_value = HS.Value.Member

ALL_NONE = ".I#[ICP None].C1#[None].C2#[None].C3#[None].C4#[None]"ALL_TOPS = ".I#[ICP Top].C1#TopC1.C2#TopC2.C3#TopC3.C4#TopC4"

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 50: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-10 Hyperion Financial Management 9.3.1: Create Rules

Point of View VariablesInformation about the current Point of View members for the Entity, Scenario, Year, and Value dimensions is typically used throughout a Sub procedure. Instead of repeatedly retrieving this information from the application, you can retrieve it once at the beginning of the procedure and store it in a variable. You can then use the value stored in the variable when a rule requires Point of View information.

You retrieve the Point of View using the Member function. For example, HS.Entity.Member retrieves the current Entity POV member. Because the values change based on the current Point of View, you should use variables rather than constants.

Top and None Members for Custom and ICP DimensionsCustom and ICP dimensions in account expressions often need to be set to the top member or the [None] member. This can result in a long expression that is difficult both to type and to read.

Example

HS.EXP “A#RetainedIncome=A#Profit".I#[ICP Top].C1#TopC1.C2#TopC2&” _ “.C3#TopC3.C4#TopC4"

To simplify your code, you can store the text string for custom and ICP members in a variable or constant, as in this example:

const All_TOPS=”.I#[ICP Top].C1#TopC1.C2#TopC2.C3#TopC3.C4#TopC4"

You can then use the constant or variable in the account expression in place of the string:

HS.EXP “A#RetainedIncome=A#Profit" &All_TOPS

Because the custom and top member names do not change when the Point of View changes, you can use constants instead of variables.

T I P

For the variable for the current period, you can use HS.Period.Number instead of HS.Period.Member. Because the fiscal year can start on different months in different applications, if you use period numbers rather than member names, it is easier to reuse your rules in more than one application.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 51: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

Hyperion Financial Management 9.3.1: Create Rules 2-11

Global AccountsYou frequently need to refer to global accounts in your rules, such as the accounts used to store exchange rates or head count. You can create variables or constants for these accounts and then use them throughout your file. For example:

vHead=”.A#HeadCount”vEfx=”.A#EndingRate”

Because the global member names do not change when the Point of View changes, you can use constants instead of variables.

Conditional Statement TriggersFinancial Management provides a number of functions that return a value of true or false. You can use these functions as tests in conditional statements. For example, before executing a rule, you might test whether it is true or false that the current year is the first year in the application or that the current entity is a base entity.

To make your rules file more efficient, you can perform the test once and store the result in a variable in your header section. For example:

vIsBase = HS.Entity.IsBase("","")

You can then use the variable as needed in conditional statements. Because they are Boolean values, a value of True is assumed as the test.

If vIsBase Then

HS.EXP “A#Sales=A#UnitsSold * A#Price”End If

You can use the Not keyword to test for a false condition. This statement executes only if the entity is not a base member:

If Not vIsBase ThenHS.EXP “A#Sales=A#UnitsSold * A#Price”

End If

For clarity in your code, you can specify True or False as the condition:

If vIsBase=True ThenHS.EXP “A#Sales=A#UnitsSold * A#Price”

End If

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 52: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-12 Hyperion Financial Management 9.3.1: Create Rules

These functions are frequently used as test for conditional statements.

Because the results returned by these functions can change based on the Point of View, you must use variables rather than constants.

Function DescriptionIsBase Determines if the current member or a specified member is a base

member of the application or of the specified parent.

IsCalculated Determines if the current Account dimension member or a specified account member is a calculated account.

IsChild Determines if the current member or a specified member is a child of the specified parent.

IsConsolidated Determines if the current Account dimension member or a specified account member is a consolidated account.

IsDescendant Determines if the current member or a specified member is a descendant of the specified parent.

IsFirst Determines if the current period or year is the first period or year of the application. The default frequency of the current scenario is used to determine if the current period or year is the first period or year of the application.

IsICP Determines if the current Account or Entity dimension member or a specified account or entity member is an intercompany partner (ICP).

IsLast Determines if the current period or year is the last period or year of the application. The default frequency of the current scenario is used to determine if the current period or year is the last period or year of the application.

IsTransCur Determines if the current Value dimension member is a translated currency member.

IsTransCurAdj Determines if the current Value dimension member is a translated currency Adj member..

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 53: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

Hyperion Financial Management 9.3.1: Create Rules 2-13

SummaryIn this lesson, you should have learned to:

• Create variables and constants

• Set up variables header sections for the Point of View

• Set up variables header sections for custom dimensions, ICP dimensions, and global accounts

• Set up a variables header section for conditional triggers

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 54: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 2 Reducing Maintenance with Variables

2-14 Hyperion Financial Management 9.3.1: Create Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 55: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 3

Managing the Scope of Rules 3

ObjectivesAt the end of this lesson, you should be able to:

• Describe the effect of the subcube structure on Financial Management rules

• Manage the scope of rules with the Account, ICP, and custom dimensions

• Manage the scope of rules with the Value dimension

• Work with total members in the Value dimension

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 56: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-2 Hyperion Financial Management 9.3.1: Create Rules

Subcubes and Data RetrievalAn understanding of Financial Management subcubes is helpful for writing efficient rules. When processing rules or displaying data, Financial Management retrieves data from the Financial Management database in blocks called subcubes, rather than in records or rows. You can think of a subcube as a grid or spreadsheet with the dimension members on the rows and columns.

Data is stored at the cells formed by the intersection of the members of the different dimension members in the subcube. All aggregations and calculations are performed on data in subcubes retrieved into RAM on the application server.

The slide example shows a simple subcube with only three dimensions, Accounts (3 members), Products (4 members), and Periods (3 members). The subcube has a cell for each unique combination or intersection of members from the three dimensions, to create 36 cells for storing data.

Copyright © 2008, Oracle. All rights reserved.

Subcubes and Data Retrieval

267 Gross Sales of Fruit Soda in Jan

267

73

131

166

116

150

149

182

211

145

120

143

ProductsDiet ColaRoot BeerCream SodaFruit Soda

PeriodJanFebMar

AccountsGross SalesDiscountsReturns

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 57: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-3

Financial Management SubcubesThe number of subcubes in a Financial Management database is determined by the number of members in the Entity, Scenario, and Year dimensions. For each unique combination of members from these dimensions, there are three subcubes: one for local or (entity) currency Value dimension members, one for parent (translated) currency Value dimension members, and one for the proportion or contribution Value dimension members. The Entity, Scenario, Year, and Value dimensions are referred to as page dimensions.

For example, for the combination of members represented by California (entity), Actual (scenario), 2006 (year), there are three subcubes:

California ->2006 -> Actual -> Local currency membersCalifornia ->2006 -> Actual -> Parent currency membersCalifornia ->2006 -> Actual -> Proportion members

Copyright © 2008, Oracle. All rights reserved.

Financial Management Subcubes

Parent Currency

California, Actual, 2006

Entity Currency Proportion

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 58: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-4 Hyperion Financial Management 9.3.1: Create Rules

Subcube DimensionsEach subcube always includes all members of the Account, custom, ICP, Period, and View dimensions. These dimensions are referred to as the subcube dimensions. Each cell in a subcube represents an intersection of the page dimension members for the subcube with a unique set of subcube dimension members.

Subcube data is retrieved in an all-or-nothing manner. If a data grid, data form, or rule requests data from a cell in a subcube, Financial Management retrieves the entire subcube into memory.

For example, a data grid or rule requests the value in the cell for Calfornia -> Budget -> 2006 -> Entity Currency -> Cash -> OpeningBalance -> ICP None -> Periodic -> Feb. Financial Management loads the entire California -> Budget -> 2006 -> Entity Currency subcube into memory. It then retrieves the value from the Cash -> OpeningBalance -> ICP None -> Periodic -> Feb cell from this subcube.

Copyright © 2008, Oracle. All rights reserved.

Subcube Dimensions

300AprilPeriodic[None]RetailWood[None][ICP None]NetSales

April

AprilApril

Period

25Periodic[None]RetailWood[None][ICP None]Returns

25Periodic[None]RetailWood[None][ICP None]Discount350Periodic[None]RetailWood[None][ICP None]GrossSales

ViewC4C3C2C1ICPAccount

Each subcube contains all members of the subcube dimensions.

California, Actual, 2006, Entity Currency

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 59: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-5

Subcubes and PerformanceThe Financial Management subcube structure is designed for highly efficient processing of data. Processing data in RAM is much more efficient than retrieving it from disk; therefore performance is improved by reducing the number of times data needs to be read from the database on disk.

The Financial Management subcube structure is designed to anticipate the data needed for aggregations and calculations and preload it into RAM. Because many dependencies typically exist between data for members of the Account and the custom dimensions, a change to data in one Account/custom dimension combination is likely to require recalculation of data in other Account/custom dimension member combinations. If all Account and custom members in the subcube are loaded into RAM, this increases the likelihood that all data needed for aggregations and calculations will be available.

Copyright © 2008, Oracle. All rights reserved.

Subcubes and Performance

Aggregations and calculations are most efficient when all members needed are preloaded in RAM.The subcube structure is designed to preload the members most likely to be needed for calculation and aggregations.

Q1MarFebJan

35

25

60

200

200

400

360

20

20

40

200

150

350

310

16060Products

1050300Products

7025HardwareReturns

9035Software

Sales

Net Sales

600

450

910

200Software

100Hardware

240

Aggregations:Hardware & Software to ProductsSales-Products & Returns-Products to Net SalesNet Sales Jan, Feb, Mar to Q1

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 60: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-6 Hyperion Financial Management 9.3.1: Create Rules

Similar considerations hold for data in the Period dimension. The current period’s values frequently depend on prior period values. If you include data for all periods, you reduce the number of times that the data must be read from disk to calculate data for periods.

Period members are part of the subcube in RAM. Therefore, when you run a rule for the current period in the Point of View, Financial Management calculates values for all prior periods and creates derived values for future periods with minimal performance impact.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 61: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-7

Subcubes and RulesThe Financial Management subcube structure affects how rules work in Financial Management. The effect of the subcube is particularly clear in how you assign values to accounts using HS.Exp.

The Left Side of HS.Exp The subcube to which a rule writes data is determined by the currently selected page dimension members in the Point of View. You cannot specify the subcube in the rule itself. This is why the left-side or destination side value of the HS.Exp function can contain only Account, custom, ICP, and View dimension members.

You can specify the subcubes for a rule indirectly, however, by using conditional statements. For the example on the slide, the HS.Exp function is executed only if the current Point of View includes California and Budget members.

Copyright © 2008, Oracle. All rights reserved.

Subcubes and Rules

California Budget 2006 <Entity Currency>

307.5AprilPeriodic[None]RetailWood[None][ICP None]NetSales

April

AprilApril

Period

17.5Periodic[None]RetailWood[None][ICP None]Returns

25Periodic[None]RetailWood[None][ICP None]Discount350Periodic[None]RetailWood[None][ICP None]GrossSales

ViewC4C3C2C1ICPAccount

If HS.Entity.Member=“California” and HS.Scenario.Member=“Budget” ThenHS.Exp “A#Returns.C2#Wood.C3#Retail=A#GrossSales.C2#Wood.C3#Retail *1.05”

End If

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 62: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-8 Hyperion Financial Management 9.3.1: Create Rules

Bulk Assignment of ValuesThe Financial Management subcube structure explains why HS.Exp permits bulk assignment of values across the Account, custom, and ICP dimensions, but not across the Entity, Scenario, or Year dimension. For example, you can use A#ALL to assign a value to all accounts in the current subcube. Because all members of the Account, custom, and ICP dimensions for the current subcube are in RAM, you can assign values in bulk without degrading performance.

Bulk assignment of values across entities, scenarios, or years is not permitted. Page dimension members have separate subcubes. If you assign values to multiple members, you must load many subcubes from disk into memory, thereby degrading performance. For example, in an application with 1,000 entities, if you assign E#ALL in an HS.Exp expression, 1,000 subcubes would have to be opened.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 63: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-9

Page Dimensions and HS.ExpData for the current subcube in the Point of View often depends on data in other subcubes. For example, the current year opening balances could be derived from the prior year closing balances, and data for different years are stored in separate subcubes. For this reason, you can specify page dimension members on the right side of the HS.Exp function, to retrieve values from other subcubes as needed.

For the example on the slide, the HS.Exp function is executed if Variance is the current scenario in the Point of View. Account values in the Variance scenario are derived from the values in the Actual and Budget scenarios. Scenario values are stored in separate subcubes. Therefore, to calculate the values for the Variance scenario, you must also load the Actual and Budget scenario subcubes into RAM.

If the Account and custom dimensions have a large number of members, you can affect rule performance if you open additional subcubes. You need to take this into consideration when writing rules.

Copyright © 2008, Oracle. All rights reserved.

Page Dimensions and HS.Exp

If HS.Scenario.Member=“Variance” ThenHS.Exp “A#All = S#Budget - S#Actual”End If

Variance Budget Actual= -

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 64: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-10 Hyperion Financial Management 9.3.1: Create Rules

You should also take the page dimensions into consideration when you run rules. For example, you could have a data grid with 20 entities in the row and 3 scenarios in the columns. If you want to calculate all rows and columns in the grid, you must open and calculate 60 subcubes. Performance is best when the subcube dimensions are on the rows and columns and the page dimensions are on the Point of View.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 65: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-11

Managing the Scope of RulesHS.Exp is a powerful function that enables you to assign values to thousands of Account, custom, and ICP dimension members with a single expression. When you assign values, using the HS.Exp function, you specify Account, ICP, and custom dimension dimensions on the left and right sides of the equal sign. How you specify the dimensions on each side of the equals sign determines the range of members to which values are assigned.

Copyright © 2008, Oracle. All rights reserved.

Managing the Scope of Expressions

You should understand how HS.Exp behaves when the account dimension, the ICP dimension, or a custom dimension:— is omitted from both sides of the equal sign— is specified on the left side of the equal sign only— is specified on the right of the equal sign only

100

100

100

100

100

100

100

100

100

100

100

100

HS.Exp “A#ALL=100”

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 66: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-12 Hyperion Financial Management 9.3.1: Create Rules

Omitting DimensionsIf you omit the Account dimension, ICP dimensions, or a custom dimension from both sides of the equal side of the HS.Exp function, members of the omitted source dimension are mapped to the corresponding members in the destination dimension. If a custom member in the source is not valid for the destination account, that custom member is skipped.

For the example on the slide, because the Custom2 dimension is omitted from both sides of the equal sign, members of the Custom2 dimension for February, GrossSales (source), are mapped to the corresponding Custom2 dimension members in March, GrossSales (destination).

In the following example, the Account dimension is omitted. The source is the ClosingBalance member of the Custom1 dimension of the prior period, and the destination is the OpeningBalance member of the Custom1 dimension for the current period. Because the Account dimension is omitted from both sides of the equal sign, the accounts for the source are mapped to the corresponding accounts for the destination.

HS.Exp “C1#OpeningBalance=C1#ClosingBalance.P#Prior

Copyright © 2008, Oracle. All rights reserved.

Omitting Dimensions

HS.Exp “A#GrossSales = A#GrossSales.P#Prior * 1.1”

Product 2

Product 3

Product 1

Custom2

A#GrossSales

Product 2

Product 3

Product 1

Custom2

A#GrossSales.C2#Product 2=

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 67: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-13

Specifying the Destination DimensionsIf you specify a member from the Account, ICP, or custom dimension on the destination side or left side of HS.Exp, the value of the corresponding member for that dimension in the source or right side is retrieved. You need not specify the source dimension.

For the example on the slide, the Steel member from the Custom2 dimension is specified as the destination of HS.Exp. The value for Steel is retrieved from the source, even though no member for Custom2 is specified as the source.

Copyright © 2008, Oracle. All rights reserved.

Specifying the Destination Dimensions

HS.Exp “A#GrossSales.C2#Steel = A#GrossSales.P#Prior * 1.1”

Product 2

Product 3

Product 1

Custom

A#GrossSales

Product 2

Product 3

Product 1

Custom

A#GrossSales.C2#Product 2=

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 68: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-14 Hyperion Financial Management 9.3.1: Create Rules

Specifying Source DimensionsIf you specify a member from the Account, ICP, or custom dimension source side (right side) of HS.Exp expression, the member value is copied to all valid members of that dimension in the destination.

For the example on the slide, the Steel member from the Custom2 dimension is specified as the source. The value for Steel is copied to the cells for all Custom2 members of the destination account.

You should be extremely cautious when specifying only source dimensions. Because the value for the dimension is copied to all valid members for that dimension in the destination, you can easily fill the database with incorrect values or with zero values, causing a data explosion.

For the example on the slide, assume that Custom2 has 1,000 members, Custom3 has 200 members, and both dimensions are valid for the GrossSales account. The value for Steel would be copied to all valid intersections of Custom2 and Custom3, which would fill 200,000 cells with data.

Copyright © 2008, Oracle. All rights reserved.

HS.Exp “A#GrossSales=A#GrossSales.C2#Steel.P#Prior*1.1”

Specifying Source Dimensions

Data explosion!

Product 2

Product 3

Product 1

Custom

A#GrossSales

Product 2

Product 3

Product 1

Custom

A#GrossSales.P#Prior=

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 69: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-15

Mismatched Custom Dimensions (Example 1)Custom dimension members valid for the source account of an HS.Exp expression frequently do not match the members valid for the destination account. In this case, you cannot rely on the HS.Exp function to automatically map source dimension members to custom dimension members. You must analyze the valid custom dimension members to determine which destination cells should receive values from the source.

Typically, the source account stores more custom detail than the destination account. You must copy summarized values from the source to the destination. For the example on the slide, Profit is an income statement account that tracks profit by product (Custom2) and market (Custom3). Custom1 and Custom4 are not valid for the Profit account. RetainedInc is a balance sheet account that stores movement information in the Custom1 dimension, but for which the other custom dimensions are not valid.

Copyright © 2008, Oracle. All rights reserved.

Mismatched Custom Dimensions (Example 1)

HS.Exp “A#RetainedInc.C1#Movement=A#Profit.C1#[None].C2#TopC2.C3#TopC3.C4#[None]”

C2: [None]

C1:Movement C3: Top C4: NoneC2: TopC1: None

A#ProfitA#RetainedInc

Member

Member

Member

Member

Member

Member

C3: [None]

C4: [None]

=

Custom1 is valid for RetainedInc.Custom2 and Custom3 are valid for Profit.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 70: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-16 Hyperion Financial Management 9.3.1: Create Rules

For the Profit account:

• The top-level members are selected for the Custom2 and Custom3 dimensions to arrive at a single rolled-up value for Profit for all products and markets.

• Custom1 and Custom4 are set to None since they are not valid for the Profit account.

For the RetainedInc account:

• The Movement member is selected for Custom1.

• Because Custom2, Custom3, and Custom4 are not valid for RetainedInc, the None member is automatically selected for these dimensions and they can be omitted from the destination (left) side of the equal sign.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 71: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-17

Mismatched Custom Dimensions (Example 2)This example shows a partial overlap of valid custom dimension members for the source and destination accounts. Sales are budgeted by product and market. Packaging is budgeted by product as a percentage of the total sales. The Products hierarchy of Custom2 and the Markets hierarchy of Custom3 are valid for the Sales account, but only the Products hierarchy is valid for the Packaging account. Further, Sales is an intercompany account, but Packaging is not.

For the Sales account (source):

• Custom3 is set to the TotalMarkets member, which retrieves the total for all markets for each product.

• The ICP dimension is set to ICP Top, to roll up all ICP transactions.

• Custom2 is omitted, so that the Product members for Custom2 for Sales is mapped to the corresponding members for Packaging.

Copyright © 2008, Oracle. All rights reserved.

Mismatched Custom Dimensions (Example 2)

A#SalesA#Packaging

C3: None C2: Prod1

C2: Prod3

C2: Prod2

C3: TotalMarketsC2: Prod1

C2: Prod3

C2: Prod2C3: None

C3: None

C3: TotalMarkets

C3: TotalMarkets

=

==

Custom 2 is valid for Packaging.Custom2 and Custom3 are valid for Sales.Sales is an intercompany account, Packaging is not.

HS.Exp “A#Packaging=A#Sales.C3#TotalMarkets.I#[ICP Top] * .05”

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 72: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-18 Hyperion Financial Management 9.3.1: Create Rules

For the Packaging account (destination):

• Custom2 is omitted, so that the Product members for Custom2 for Sales is mapped to the corresponding members for Packaging.

• The ICP dimension is omitted. Because Packaging is not an intercompany account, the ICP None member is selected automatically.

• Custom1 and Custom3 are omitted from both sides of the equal sign because they are invalid for both accounts.

For clarity in your expressions, you can include all dimensions explicitly on both sides of the expression. The following expression is equivalent to the expression in the slide example:

HS.Exp “A#Packaging.C1#[None].C3#[None].C4#[None].I#[ICP None]=”& _ “A#Sales.C1#[None].C3#TotalMarkets.C4#[None].I#[ICP Top]”

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 73: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-19

Executing Rules with the Value DimensionThe data input to the Entity Currency member of the Value dimension is potentially transformed several times before it is ready for consolidation to a parent entity:

• If journal entries are entered for the data, the journal adjustments are applied.

• If it uses a different currency than its parent, the data is translated to the parent’s currency.

• If the parent ownership of the entity is less than 100%, the data is adjusted to reflect percent ownership.

• If the data is from an intercompany transaction with another entity, it may need to be eliminated.

Copyright © 2008, Oracle. All rights reserved.

Executing Rules with the Value Dimension

The Value Dimension

[Contribution Adjs]

<Entity Curr Adjs>

[Parent Adjs]

<Parent Curr Adjs>

[Elimination]

<Entity Currency>

<Parent Currency>

[Parent]

[Proportion]

[Contribution Total]

[Contribution]

[Parent Total]

<Parent Curr Total>

<Entity Curr Total>

Data stored Calculated on the fly – data not stored

Sub Calculate Sub Calculate

Sub CalculateSub CalculateSub Translate

Sub CalculateSub Consolidate

Sub CalculateSub Consolidate

Sub Calculate

Sub Calculate

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 74: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-20 Hyperion Financial Management 9.3.1: Create Rules

The calculations required for these transformations are accomplished by Financial Management rules.The result of each of these transformations is stored in a separate Value dimension member. Of the 14 Value dimension members, 8 members store data.The slide example shows the rules procedures that are executed for each member.

Notice that the Sub Calculate procedure is executed for the 8 Value dimension members that store data.

The members of the Value dimension for which rules are not executed are on-the-fly aggregations of Value dimension members below them. Data is not stored for these members. Rules can retrieve data from these Value dimension members, but they cannot write to them.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 75: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-21

Sub Calculate and the Value DimensionBecause Financial Management runs the Sub Calculate procedure for the eight Value dimension members that store data, Sub Calculate potentially runs eight times for each Entity-Year-Scenario combination that you calculate or consolidate.

If you do not manage the scope of the Value dimension in your rules, these problems can arise:

• Degraded performance—Rules required by only one Value dimension member are executed for all eight Value dimension members.

• Incorrect results—Rules might be executed for Value dimension members for which they were not designed, leading to incorrect results. For example, a rule that calculates translation adjustments should not be executed for the Entity Currency member.

Copyright © 2008, Oracle. All rights reserved.

Sub Calculate and the Value Dimension

For each rule in the Sub Calculate procedure, determine the Value dimension members to which it applies.Use conditional statements to create sections in your Sub Calculate procedure within which to place the rules for specific Value dimension members.

‘ This section contains rules for the Entity Currency and Entity Curr Adjs membersIf HS.Value.Member="<Entity Currency>" or HS.Value.Member="<Entity Curr Adjs>" Then

HS.Exp "A#Sales=A#UnitsSold * A#Price"HS.Exp "A#Taxes=A#IncomeBeforeTaxes.C1#TopC1.C2#TopC2.C3#Top3.I#[ICP Top]”& _ “* A#TaxRate /100"

End if

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 76: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-22 Hyperion Financial Management 9.3.1: Create Rules

For each rule, you should determine the Value dimension members that it applies to. You should then place it within a conditional statement so that it is executed only for the correct Value dimension members.

Most rules in the Sub Calculate procedure need to be executed only for the Entity Currency and Entity Curr Adjs members. The remaining Value dimension members are calculated by the Sub Translate and Sub Consolidate procedures. A good practice is to use an If...Then statement to create a section in which to place all rules for Entity Currency and Entity Currency Adjs. You can use additional If...Then statements to create sections for rules for other Value dimension members as needed.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 77: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-23

Working with Total MembersThe Entity Curr Total, Parent Curr Total, and Contribution Total members of the Value dimension contain the adjusted totals for the Entity Currency, Parent Currency, and Proportion members, respectively. You often need to use adjusted totals in calculations. Because rules are not executed at the total members, you must override the default source (right) Value dimension member side of the equal sign of HS.Exp to retrieve the values from these members.

For the example on the slide, if no Value dimension member is specified as the source, the value for Entity Currency, the current POV member when the rule is executed, is retrieved by default. Because SalesTax must be calculated based on the adjusted total for Sales, Entity Curr Total is specified for the source Value dimension, overriding the default.

Copyright © 2008, Oracle. All rights reserved.

Working with Total Members

<Entity Curr Adjs><Entity Currency>

<Entity Curr Total>

Rule executes for Entity Currency, but you need to use Entity Curr Total in your calculation.

If pov_value=“<Entity Currency>" ThenHS.Exp "A#SalesTax=A#Sale.V#<Entity Curr Total> * .06"

End if

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 78: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-24 Hyperion Financial Management 9.3.1: Create Rules

If you want to write rules that use the correct Value dimension total member for whatever Point of View is selected, you can create a variable whose value is set to a total member based on the current Point of View. You can then use the variable as needed in your rules.

This example sets the value of the variable vTotal to the correct total member based on the current Point of View for the Value dimension:

If pov_value = "<Entity Currency>" Then vTotal = ".V#<Entity Curr Total>"

ElseIf HS.Value.IsTransCur Then vTotal = “V#” &HS.Value.Currency& " Total" ElseIf pov_value = "[Proportion]" Then vTotal = ".V#[Contribution Total]" End If

You can use the vTotal variable in any rule that needs to retrieve a value from a total member.

HS.Exp "A#Acc1=A#Acc2” "& vTotal

N O T E

When referring to total members in a rule, you should check the order in which the rules are calculated. In the preceding example, because Acc1 uses the total calculated for Acc2, any rule that calculates Acc2 must precede the rule for Acc1. Otherwise, the total for Acc2 might not be valid.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 79: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

Hyperion Financial Management 9.3.1: Create Rules 3-25

SummaryIn this lesson, you should have learned to:

• Describe the effect of the subcube structure on Financial Management rules

• Manage the scope of rules with the Account, ICP, and custom dimensions

• Manage the scope of rules with the Value dimension

• Work with total members in the Value dimension

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 80: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 3 Managing the Scope of Rules

3-26 Hyperion Financial Management 9.3.1: Create Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 81: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 4

Creating Rules for Dynamic Accounts 4

ObjectivesAt the end of this lesson, you should be able to:

• Describe dynamic accounts

• Describe the Sub Dynamic procedure

• Create rules for dynamic accounts

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 82: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

4-2 Hyperion Financial Management 9.3.1: Create Rules

Dynamic Account TypesDynamic accounts are accounts whose values are dynamically calculated when the data is requested. Ratios and percentages are the most common type of dynamic calculations. Only base accounts can be dynamic.Dynamic accounts ignore the following account attributes:

• ISConsolidated• EnableCustom1...4Aggr• ISCalculated• UsesLineItems

The IsConsolidated and EnableEnableCustom1...4Aggr attributes do not apply to dynamic accounts because dynamic accounts are recalculated at the parent level; they are not aggregated. The IsCalculated and UseLineItems attributes do not apply because data for dynamic accounts is calculated, not stored.

Copyright © 2008, Oracle. All rights reserved.

Dynamic Account Types

Accounts that use the Dynamic account type have this behavior:Values are not stored; they are calculated as the data is requested.Parent totals for accounts, custom dimensions, and time periods are calculated dynamically, they are not aggregated from children.Period-to-date views calculate correctly

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 83: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

Hyperion Financial Management 9.3.1: Create Rules 4-3

Sub Dynamic ProceduresYou use Sub Dynamic procedures to create rules for dynamic accounts.

Syntax

Sub ProcedureName()‘Type your Dynamic rule here

End Sub

Example

This example uses the account GM_PCT to store the results of the formula for GM divided by Sales and then multiplied by 100:

Sub Dynamic()HS.Dynamic "A#GM_PCT = A#GM / A#Sales * 100"

End Sub

Copyright © 2008, Oracle. All rights reserved.

Sub Dynamic Procedures

Dynamic rules are stored in a procedure called Sub Dynamic() in the rules file. Sub Dynamic() procedures are executed when you calculate or consolidate data.

Sub Dynamic( )<All dynamic rules are displayed here.>End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 84: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

4-4 Hyperion Financial Management 9.3.1: Create Rules

Creating Rules for Dynamic AccountsDynamic account values are calculated on the fly as data is requested from Sub Dynamic procedures. You use the HS.Dynamic function within the procedures to create rules for dynamic accounts.

Syntax

HS.Dynamic "DestPOV = Expression"

Guidelines:

• The right side of the equation (source) cannot reference the Scenario, Year, or Entity dimensions.

• Only dynamic accounts and View dimension members are valid on the left side of the equation (destination).

• You cannot use dynamic accounts as the source.

Copyright © 2008, Oracle. All rights reserved.

Creating Rules for Dynamic Accounts

You use the HS.Dynamic function to create rules for dynamic accounts.You can use HS.Dynamic only in Sub Dynamic procedures.HS.Dynamic is executed for the current Point of View for Entity,Scenario, and Year.You cannot use conditional statements with dynamic rules.

Sub DynamicHS.Dynamic "A#GM_PCT=A#GM/A#Sales*100"

End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 85: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

Hyperion Financial Management 9.3.1: Create Rules 4-5

• If you do not specify the View dimension as the destination, the calculation is executed for all views. If you specify the View dimension, the calculation is executed only for the specified view.

• You cannot use conditional statements within Sub Dynamic procedures.

• Statements in Sub Dynamic procedures are executed sequentially.

The HS.Dynamic function can reference data only in the current subcube. If you want to reference data from a different subcube, you may need to create a "parking" account to store information from the other cube. For example, to reference a prior year's data in the formula, create a memorandum account to store last year's data in the current year’s subcube and reference the memorandum account in the dynamic calculation.

The table lists the expected results for the GMPercent account, assuming the Product custom member is the parent of P1, P2 and P3. Notice that Product custom member is calculated by the formula; it is not aggregated from its children.

You can include the View dimension on the left side of the equal sign as the destination to limit the calculation to a specific view. In this example, the GMPercent calculation is executed only if you set the Point of View to periodic.

Sub DynamicHS.Dynamic "A#GMPercent.W#Periodic = A#GM / A#Sales * 100"

End Sub

Custom1 Sales GM GM% Dynamic Calc. Calculation from FormulaProduct 600 140 23.33% 140 / 600 * 100

P1 100 10 10% 10 / 100 * 100

P2 200 40 20% 40 / 200 * 100

P3 300 90 30% 90 / 300 * 100

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 86: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

4-6 Hyperion Financial Management 9.3.1: Create Rules

Functions Valid with HS.DynamicBecause HS.Dynamic expressions are dynamically calculated for summary time periods, you can create formulas for different views, such as quarter-to-date or half-year-to-date. You can use the following functions with HS.Dynamic to create calculations for views.

HS.View.PeriodNumber Returns the period number within the view for the data being retrieved. It can be used only in dynamic rules.

Syntax

HS.View.PeriodNumber

Copyright © 2008, Oracle. All rights reserved.

Functions Valid with HS.Dynamic

These functions can be embedded within HS.Dynamic:HS.View.PeriodNumberHS.Period.NumPerInGenHS.Period.Number

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 87: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

Hyperion Financial Management 9.3.1: Create Rules 4-7

Example

This example shows possible values for different views:

HS.Period.NumPerInGenReturns the number of periods within the generation for the period being processed. This function is used only in Dynamic rules.

SyntaxHS.Period.NumPerInGen

ExampleIf the current period is April, and April is the fourth generation within the calendar file (monthly generation), the number of periods in the generation is 12. If the current period is Q2, and Q2 is the third generation within the calendar file (quarterly generation), the number of periods in the generation is 4.

Monthly generation: (Fourth generation)• Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec• The system returns 12 for the number of periods in this generation.

Quarterly generation: (Third generation)• Q1, Q2, Q3, Q4• The system returns 4 for the number of periods in this generation.

Half-yearly generation: (Second generation)• HY1, HY2• The system returns 2 for the number of periods in this generation.

Yearly generation: (First generation)• Year• The system returns 1 for the number of periods in this generation.

HS.Period.NumberReturns the current period number.

SyntaxHS.Period.Number

J F M Q1 A M J Q2 H1 J A S Q3 O N D Q4 H2 YPeriodic 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1

YTD 1 2 3 1 4 5 6 2 1 7 8 9 3 10 11 12 4 2 1

QTD 1 2 3 1 1 2 3 1 1 1 2 3 1 1 2 3 1 2 1

HYTD 1 2 3 1 4 5 6 2 1 1 2 3 1 4 5 6 2 1 1

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 88: D53155 hfm 9.3.1_rules_student_guide

Module 1 Financial Management Rules BasicsLesson 4 Creating Rules for Dynamic Accounts

4-8 Hyperion Financial Management 9.3.1: Create Rules

SummaryIn this lesson, you should have learned to:

• Describe dynamic accounts

• Identify Sub Dynamic procedure

• Create rules for dynamic accounts

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 89: D53155 hfm 9.3.1_rules_student_guide

M O D U L E 2

Creating Procedures, Loops, and Arrays 4

OverviewIn this module you learn how to create custom Sub and Function procedures. You learn to create rules using arrays and loops.

Lessons in this module include:

• Creating Custom Procedures

• Creating Arrays and Loops

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 90: D53155 hfm 9.3.1_rules_student_guide

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 91: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 5

Creating Custom Procedures 5

ObjectivesAt the end of this lesson, you should be able to:

• Create custom Sub procedures

• Create custom Function procedures

• Troubleshoot rules script with custom logging procedures

• Managing log files

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 92: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-2 Hyperion Financial Management 9.3.1: Create Rules

Creating Custom Sub ProceduresIn addition to using the eight predefined Financial Management Sub procedures, you can define custom Sub procedures. You can execute, or call, custom procedures from within the predefined Financial Management procedures or from another custom procedure.

For example, at line 15 in the Sub Calculate procedure, you could call a custom procedure named Sub OpeningBalances. The Sub Calculate procedure stops executing and the statements in Sub OpeningBalances are executed. When the statements in Sub OpeningBalances finish executing, the Sub Calculate procedure resumes execution at line 16.

Custom Sub procedures ease organization and maintenance of rules files. Instead of working with a single procedure that may contain hundreds of lines of script for different tasks, you can create multiple Sub procedures, each of which performs a single task.

When you define a custom Sub procedure, you can specify one or more variables to receive values passed from the calling procedure.

Copyright © 2008, Oracle. All rights reserved.

Creating Custom Sub Procedures

Custom procedures provide these benefits:They make rules files easier to read.They simplify troubleshooting by separating script into logical units.They allow rules to be reused by multiple calling procedures

Sub OpeningBalance()......

End Sub

Function ExpenseCalc()......

End Function

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 93: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-3

Syntax

Sub ProcedureName (Var1,Var2,Var3,...)......

End Sub

Arguments

ProcedureName

The name of the procedure

Var1,Var2, Var3,...)

A list of variable names to receive values passed from the calling procedure

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 94: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-4 Hyperion Financial Management 9.3.1: Create Rules

Calling Custom Sub ProceduresTo call a Sub procedure, you use the call keyword followed by the Sub procedure name and parentheses, with values to be passed, if any, within the parentheses. Alternatively, you can omit the call keyword and simply use the Sub procedure name. If you omit the call keyword, the parentheses are optional. Using the call keyword, however, adds clarity to your script by showing explicitly that a Sub procedure is being called.

You can pass values from the calling procedure to the custom Sub procedure. You can pass literal values or variables that exist in the calling procedure. If you are passing a literal text string, enclose it in quotation marks. If you are passing a numeric value or a variable, do not use quotation marks. The values passed from the calling procedure must correspond to variables defined in the Sub statement of the custom Sub procedure.

Copyright © 2008, Oracle. All rights reserved.

Calling Custom Sub Procedures

Sub Calculate()ExpRate=500call ExpenseCalc("A#Travel",ExpRate)ExpRate=20call ExpenseCalc("A#Supplies",ExpRate)

End Sub

Sub ExpenseCalc(Acct,Rate)HS.Exp Acct &"= A#Headcount *"&Rate

End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 95: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-5

For the example on the slide, the Sub ExpenseCalc procedure is called twice from the Sub Calculate procedure. The first time it is called, the literal text string A#Travel is passed to the Acct variable of Sub ExpenseCalc. The current value of ExpRate (500) is passed to the Rate variable of Sub ExpenseCalc. When Sub ExpenseCalc is called again, the literal text A#Supplies is passed to the Acct variable and 20, the current value of ExpRate, is passed to the Rate variable.

When you pass a variable to a procedure, you can pass it by reference or by value:

• By reference—If the called procedure changes the value of the variable, the value of the variable is changed in the calling procedure as well. By default, VBScript passes variables by reference.

• By value—If the called procedure changes the value of the variable, the value of the variable is not changed in the calling procedure. You must enclose the variable in parentheses.

By default, VBScript passes variables by reference. To pass variables by value, surround the variable with parentheses. In the following example, the Acct variable is passed by value and the Rate value is passed by reference:

Sub ExpenseCalc((Acct),Rate)

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 96: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-6 Hyperion Financial Management 9.3.1: Create Rules

Creating Custom Function ProceduresFunction procedures perform an operation and return the result of the operation to the calling procedure. As with Sub procedures, the calling procedure can pass values to the Function procedure. For example, the calling procedure might pass two text strings to the Function procedure. The Function procedure might then concatenate the two text strings and pass the concatenated string back to the calling procedure as a return value.

When you define a custom Function procedure, you can specify one or more variables to receive values passed from the calling procedure. Within the function, you use the function name as a variable to store the return value.

Copyright © 2008, Oracle. All rights reserved.

Functions perform an operation and return the result of the operation to the calling procedure.

Creating Custom Function Procedures

'Calculates the change between current period'and opening balanceFunction Change(Acct) If UCase(HS.Account.AccountType(Acct))="ASSET" ThenChange="(A#" &Acct& ".P#Last.Y#Prior-A#" &Acct&")"

ElseChange="(A#" &Acct& "-A#" &Acct& ".P#Last.Y#Prior)"

End IfEnd Function

This value is returned to the calling procedure.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 97: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-7

Function

Function FunctionName (Var1,Var2, VarN,...)...FunctionName=ReturnValue...

End Function

Arguments

FunctionName

The name of the Function procedure

Var1,Var2, VarN

A list of variable names that receive values passed from the calling procedure

ReturnValue

The return value for the function (a literal value or an expression that returns a value.)

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 98: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-8 Hyperion Financial Management 9.3.1: Create Rules

Calling Custom Function ProceduresTo call a function, place the function name at the location in the script where you want to insert the return value. When passing values to the variables, use the same syntax as for Sub procedures.

Copyright © 2008, Oracle. All rights reserved.

Calling Custom Function Procedures

Sub Calculate()HS.EXP "A#Travel=" &HeadCountCalc(500)HS.EXP "A#Supplies="&HeadCountCalc(20)

End Sub

Function HeadCountCalc(Rate)HeadCountCalc=HS.GetCell("A#Headcount") * Rate

End Function

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 99: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-9

Troubleshooting with Logging ProceduresYou can create custom logging procedures to troubleshoot rules scripts. A logging procedure writes a text-based log file that records information regarding the execution of the rules script.

Typical information to include in a log file:

• Start time and end time of each executed procedure (Helps determine which procedures have performance problems.)

• Point of View for which the procedure was executed

• Values written to the database (Helps verify that calculations are correct.)

Copyright © 2008, Oracle. All rights reserved.

Troubleshooting with Logging Procedures

You use a write to file procedure to create custom log files.You use custom log files to verify:— Execution time for a procedure— Point of View for a procedure— Calculations

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 100: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-10 Hyperion Financial Management 9.3.1: Create Rules

Writing to Log FilesSeveral VBScript functions enable you to create text files and write information to them. You can use these functions with the VBScript File System and File objects.

To write to log files:

These steps explain how to use functions to write to log files:

1. Use a Sub statement to create a logging procedure. Include a variable to receive a text string from a calling procedure. For the example on the slide, the Sub procedure is named WriteToFile and the variable is txtStringtoWrite.

2. Declare variables for a File System object and a File object. For the example on the slide, the variables fso and f are declared.

Copyright © 2008, Oracle. All rights reserved.

Writing to Log Files

Sub WriteToFile(txtStringToWrite)Dim fso,fSet fso=CreateObject("Scripting.FileSystemObject")Set f=fso.OpenTextFile("c:\ruleslog.txt",8,True)f.WriteLine txtStringToWrite & " " & Now()f.Close

End Sub

1

2

3

4

5

6

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 101: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-11

3. Create a File System object and assign it to the file system object variable. Use the Set statement with the CreateObject function to create a File System object. For the example on the slide, the set command assigns a File System object to the fso variable.

4. Use the OpenTextFile function to open an existing text file; alternatively, create the file if it does not exist. For the example on the slide, the file c:\ruleslog.txt is opened and assigned to the File object variable declared in step 2.

5. Write one or more lines of text to the file using the WriteLine function with the File object. For the example on the slide, the WriteLine function writes the value of the txtStringtoWrite variable, concatenated with the current system data and time.

6. Close the file using the Close function with the file object.

This section describes the syntax for the functions used in the logging procedure.

Close Closes a file

Syntaxobject.Close

ArgumentsObject

A File object

CreateObject("Scripting.FileSystemObject") Creates a VBScript File System object

SyntaxSet Var = CreateObject("Scripting.FileSystemObject")

ArgumentsVar

A variable for the object

N O T E

The file location for the log file must be a server and directory to which the Financial Managment DCOM/ADMIN user has full modify access.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 102: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-12 Hyperion Financial Management 9.3.1: Create Rules

Now Returns the current system data and time

Syntax

Now()

OpenTextFile FunctionOpens a text file or creates a new file

Syntax

Set Var = object.OpenTextFile(Filename,Iomode,Create[,Format])

ArgumentsVar

A variable for the File object

Object

A File System object

File

The name and path for the text file

IOmode

1 for read-only, 2 to overwrite the existing contents of the file, or 8 to append to the file

Create

True to create a file or False to not create the file

Format

1 for unicode, 0 for ASCII, or -2 to use the system default setting. If omitted, the format is ASCII.

WriteLine FunctionWrites a line to a text file

Syntax

object.WriteLine Text

Arguments

object

The File object to which to write the text

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 103: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-13

Text

The text to write to the file. The text is written as a new line in the file. Enclose literal text strings in quotation marks.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 104: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-14 Hyperion Financial Management 9.3.1: Create Rules

Calling Write to File ProceduresYou can call a write to file procedure from any location in a rules script. Before calling the file, you should store the information in variables. You can then pass the variables to the write-to-file procedure. If you open the text file in append mode, you can call the procedure repeatedly during script execution. Each line is appended to the end of the file.

Copyright © 2008, Oracle. All rights reserved.

Calling Write to File Procedures

Sub Calculatepov_entity=HS.Entity.Memberpov_scenario=HS.Scenario.Member

Call WriteToFile("Calc procedure started for " &pov_entity & _" " &pov_scenario)

HS.Exp "C1#Opening=C1#Closing.Y#Prior.P#Last"Call WriteToFile("Calc procedure ended")End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 105: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-15

For the example on the slide, the first call to the write-to-file procedure passes the entity and scenario for which the Calculate procedure is being run. The second call to the write to file procedure indicates the end of the procedure.

N O T E

Because writing to a log file impacts performance, you should use custom logging only during development and testing of your application. You should disable all calls to write-to-file procedures before the application goes to production. An easy way to disable call is to place apostrophes in front them. This turns them into comment lines.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 106: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-16 Hyperion Financial Management 9.3.1: Create Rules

Managing Log FilesLog files can quickly fill with large amounts of data, but you can manage log files with VBScript functions. These are some of the tasks you can perform:

• Check for the existence of a file

• Check the file size

• Move a log file to a new location

• Delete a log file

The example on the slide shows a routine that checks the size of the log file and deletes it if it exceeds a specified limit. The GetFile function assigns the log file c:\ruleslog.txt to the variable f. The Size function retrieves the size of c:\ruleslog.txt. If the size is larger than the size specified in the MaxSize variable, the FileDelete function deletes the file.

The following sections describe the functions for managing files.

Copyright © 2008, Oracle. All rights reserved.

Managing Log Files

VBScript provides functions that enable you to manage log files.

Dim FileSize, fso, f, s, MaxSize=1048576Set fso = CreateObject("Scripting.FileSystemObject")Set f = fso.GetFile("c:\ruleslog.txt")s = f.sizeIf MaxSize < s thenSet f = fso.OpenTextFile(c:\ruleslog_deleted.txt,8, True)f.WriteLine "c:\ruleslog.txt reached size limit" f.WriteLine "of " & MaxSize & " and was deleted at" &Now()f.Closefso.DeleteFile("c:\ruleslog.txt")

End If

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 107: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-17

DeleteFile FunctionDeletes a file.

Syntax

object.DeleteFile(filespec[,force])

Arguments

Object

A File System object

filespec

The path and name of a file

force

True if read-only files should be deleted, or False if they should not. If this argument is omitted, the default is False.

FileExists FunctionReturns True if the specified file exists or False if it does not.

Syntax

object.FileExists(filespec)

Arguments

Object

A File System object

filespec

The path and name of a file

GetFile FunctionReturns a File object corresponding to the file in a specified path

Syntax

object.GetFile(filespec)

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 108: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-18 Hyperion Financial Management 9.3.1: Create Rules

Arguments

Object

A File System object

filespec

The path and name of a file

MoveFile FunctionMoves a file from one location to another

Syntax

object.MoveFile source, destination

Arguments

Object

A File System object

source

The path and name of the file to move

destination

The path to which to move the file

Size FunctionReturns the size of a File object

Syntax

object.Size

Arguments

A File object

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 109: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

Hyperion Financial Management 9.3.1: Create Rules 5-19

SummaryIn this lesson, you should have learned to:

• Create custom Sub procedures

• Create custom Function procedures

• Troubleshoot rules scripts with custom logging procedures

• Manage log files

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 110: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 5 Creating Custom Procedures

5-20 Hyperion Financial Management 9.3.1: Create Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 111: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 6

Creating Arrays and Loops 6

ObjectivesAt the end of this lesson, you should be able to:

• Describe arrays and loops

• Create arrays

• Fill arrays using member lists

• Create loops

• Assign values with arrays and loops

• Work with member attributes in loops

• Open data units

• Create loops with data units

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 112: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-2 Hyperion Financial Management 9.3.1: Create Rules

ArraysYou can create these types of variables:

• Standard variables, also called scalar variables, store a single value.

• Array variables store a list of values.

Copyright © 2008, Oracle. All rights reserved.

Arrays

Arrays are variables that hold multiple values.Each array value has an index number.You write values, to or read values from, the array by referencing the index number.

aProducts

Brick2

Steel0

Plastic3

Wood1

Dim aProducts(4)aProducts(0)=“Steel”aProducts(1)=“Wood”aProducts(2)=“Brick”aProducts(3)=“Plastic”

Lower bound

Upper bound

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 113: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-3

LoopsLoops can reduce the size of your script. For example, instead of creating 20 lines of script to calculate 20 account values, you can create a loop that repeats a single line of script 20 times, specifying a different account for each iteration.

Copyright © 2008, Oracle. All rights reserved.

Loops

Loops are sections of code that repeat execution for a specified number of times or until a condition is met.

Dim CounterFor I = 1 to 100Counter=Counter + 1Next

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 114: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-4 Hyperion Financial Management 9.3.1: Create Rules

Arrays and Loops in RulesAs you learned earlier, the HS.Exp function can write data to either a single member of a subcube dimension or to all valid members of the dimension:

• If you specify a dimension member on the destination (left) side of HS.Exp, it writes data only to that member of the dimension.

• If you omit the dimension from the destination (left) and source (right) sides of HS.Exp or include it only on the source (right) side, it writes to all valid members of the dimension.

But frequently you need a rule to act on a subset of dimension members. For example, you might need a rule that writes values to all accounts that are descendants of the Total Expenses account, but to no others. If there were 50 descendants of Total Expenses, you would need 50 HS.Exp statements.

Copyright © 2008, Oracle. All rights reserved.

Arrays and Loops in Rules

Frequently you need to write rules that execute for a subset of members in a dimension.You cannot always specify the subset precisely with HS.Exp. Loops and arrays enable you to write concise rules that perform actions on large subsets of dimension members.

Pensions5

Stationery3Phone2

aOver

Salaries4

Travel0

Sundry6

RentRates1

For i = 0 to 6 HS.Exp "A#Expenses.C2#"& (aOver(i))& “=A#Expenses.P#Prior”

Next

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 115: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-5

Similarly, you must frequently limit the members of the Entity, Scenario, Year, or Period dimension for which a rule executes. Using an If...Then or Select...Case statement is convenient if there are a small number of members, but it becomes problematic with many members. For example, you might need a rule that executes only for entities that have Eur as their default currency. If there were 25 entities with Eur as their default currency, you would need an If...Then statement or a Select...Case statement with 25 conditions, which would be difficult to write and maintain.

You can use loops and arrays to work with large subsets of members. You first load the members into an array. You then use a loop to repeat the execution of a rule for each member in the array.

For the example on the slide, the Overhead members of the Custom2 dimension are stored in an array variable named aOver. A loop is used to perform a calculation for each member in the array.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 116: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-6 Hyperion Financial Management 9.3.1: Create Rules

Creating Array VariablesYou create array variables the same way that you create standard variables, except you specify the number of items for the array in parentheses next to the variable name.

Array variables use a zero-based index; that is, the index number for the first item is zero. As a result, array variables contain one more item than the number specified in parentheses. For the example on the slide, the aProducts variable would contain items 0 to 5.

You assign data to each element of the array by using an index into the array. Similarly, the data can be retrieved from any element by using an index into a particular array element.

This example sets the value of the ninth item in the aEntity array variable to Europe: aEntity(8)=Europe

This example retrieves the tenth item from aEntity and stores it in the vCurrEntity variable:vCurrEntity=aEntity(9)

Copyright © 2008, Oracle. All rights reserved.

Creating Array Variables

Creating an array variable is similar to creating a scalar variable, but you include parentheses with the number of items for the array.

To add items to or retrieve items from the array, specify the index number for the item in parenthesis.

Dim aProducts(5) Dim aAccts(10)

Adds three items to aAccts and assigns them index numbers

Retrieves the Returns item from aAccts and stores it in vTarget

aAccts(0)=“Taxes”aAccts(1)=“Discounts”aAccts(2)=“Returns”

vTarget=aAccts(2)

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 117: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-7

Filling Arrays Using Member ListsYou can use the HS.List function to fill an array variable with the members from a member list.

Syntax

HS.Dimension.List("Parent","Listname")

Parameter DescriptionDimension A dimension name.

Parent Optional. The name of the top parent member for a system-defined list.

Listname The name of a valid system list or a user-defined member list.

Copyright © 2008, Oracle. All rights reserved.

Filling Arrays Using Member Lists

Examples of HS.List:

aAccts=HS.Account.List("","")

All accounts in the Account dimension:

aAccts=HS.Account.List("", "[Base]")

All base accounts in the Account dimension:

aEntity=HS.Entity.List("France","[Children]")

All children of the entity France:

aAccts=HS.Account.List("","Taxable")

All accounts in the user-defined member list Taxable:

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 118: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-8 Hyperion Financial Management 9.3.1: Create Rules

Creating LoopsYou can create several types of loops in VBScript.

For...Next Repeats the execution of a block of statements a specific number of times. For loops use a counter variable whose value is automatically increased or decreased with each repetition of the loop. You specify the initial value for the counter variable and the maximum value the counter can reach for the block of code to be executed.

Syntax

For Counter = StartVal To EndVal......

Next

Copyright © 2008, Oracle. All rights reserved.

Creating Loops

You can use these statements to create loops:

For...Next: Using a counter to run statements a specified number of timesFor Each...Next: Repeating a group of statements for each object in a collection Do...Loop: Looping while or until a condition is true

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 119: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-9

Example

The following loop makes the computer beep 21 times. The For statement specifies the counter variable name as x. It specifies 0 as the start value for x and 21 as the end value. The Next statement increments the counter variable by 1 after each iteration.

For x = 0 To 20 BeepNext

For Each...Next Repeats a block of statements for each element in an array. All statements execute for each element in the array until there are no more elements. You specify a variable that returns the index for the current element in the array.

Syntax

For Each Item in ArrayVariable......

Next

Parameter

Item

A variable name for the counter variable.

ArrayVariable

The name of an array variable

Example

In the following script, Item is the counter variable and BeepCount is an array variable. The computer beeps for as many items as there are in the BeepCount array variable.

For Each Item in BeepCount BeepNext

Parameter DescriptionCounter A name for the counter variable

StartVal The initial value for counter variable.

EndVal The maximum value for counter variable.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 120: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-10 Hyperion Financial Management 9.3.1: Create Rules

Do...LoopRuns a block of statements an indefinite number of times. The statements are repeated either while a condition is true or until a condition becomes true.

Repeating Statements While a Condition is TrueYou use the While keyword with a Do loop to run a block of statements while a condition is true.

Syntax

Do While Condition ......

Loop

Parameters

Condition

An expression that evaluates to true or false

Example

In the following script, the statements inside the loop run while the value of myNum is 10 or less.

myNum = 0Do While myNum < 11

myNum = myNum + 1Loop

Repeating Statements Until a Condition Becomes TrueYou use the Until keyword with a Do loop to run a block of statements until a condition becomes true.

Syntax

Do Until Condition ......

Loop

Parameters

Condition

An expression that evaluates to true or false

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 121: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-11

Example

In the following script, the statements inside the loop execute until the value of myNum reaches 11.

myNum = 0Do Until myNum = 11

myNum = myNum + 1Loop

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 122: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-12 Hyperion Financial Management 9.3.1: Create Rules

Assigning Values with Arrays and LoopsYou can use arrays and loops to assign values to accounts in Financial Management.

To assign values with arrays and loops:

1. Assign a system or user-defined member list to an array variable by using the HS.List function.

2. Create a loop that executes for each element in the array variable.

3. Use the loop counter variable to specify the index for the array variable.

Because the loop counter variable is incremented by one each time the loop executes, each iteration of the loop retrieves a different member from the array variable. You can insert the array variable in any location in your script where an array member name is required.

Copyright © 2008, Oracle. All rights reserved.

Assigning Values with Arrays and Loops

aQTDAccts = HS.Account.List("", "QTD_Accounts")For i=LBound(aQTDAccts) to UBound(aQTDAccts)HS.Clear("A#"& aQTDAccts(i)&".C2#QTD")

Next

aProds = HS.Custom2.List("","[Base]")For Each ProdItem in aProdsHS.Exp "A#SalesTax.C2#" &ProdItem& "=A#Sales*.06"

Next

The counter variable increments the index number of the array variable to retrieve a different member from the array at each iteration of the loop.Use the Lbound and Ubound functions to retrieve the number of items in the array.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 123: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-13

With For...Next loops, you must specify a start number and an end number for the counter variable. But often you do not know the number of items in the array. You can use the Lbound function to retrieve the index number of the first item in the array and the Ubound function to retrieve the index number of the last item.

For Each...Next loops automatically repeat for each member in the array, so the Lbound and Ubound functions are not needed.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 124: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-14 Hyperion Financial Management 9.3.1: Create Rules

Attributes in LoopsThere may not be a system or user-defined member list that defines the precise subset of members that you need to include in a loop. You can use member attributes as additional criteria for selecting members for which rules are executed.

For the example on the slide, the Custom2 dimension contains products. The rule should execute only for base-level members of Custom2 that have the user-defined attribute ‘Taxable’. To accomplish this:

• A system member list is used to fill the array variable aProds with the base-level members of the Custom2 dimension.

• A For...Each loop is used to loop through each of the members in the array variable. The counter variable for the loop is ProdItem.

• At each iteration of the loop, an If...Then statement retrieves the value of the UD1 attribute for the current array item and tests whether it contains the text ‘Taxable’. Notice that ProdItem is used to retrieve the item from the array variable.

Copyright © 2008, Oracle. All rights reserved.

Attributes in Loops

You can use member attributes to select the members of an array for which a rule is executed.

aProds = HS.Custom2.List("","[Base]")For Each ProdItem in aProdsIF HS.Custom2.UD1(ProdItem)="Taxable" ThenHS.Exp "A#SalesTax.C2#" &ProdItem& "=A#Sales*.06”

End IfNext

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 125: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-15

Loops and Data UnitsMember lists return a list of members in a dimension regardless of whether data exists for those members. But a rule often needs to execute only for intersections of members that have data. Financial Management provides functions that enable you to limit the scope of rules to intersections that have data. You do this by creating loops with data units. A data unit is the set of intersections that contain data for a specified Point of View.

For the example on the slide, the data unit includes all member intersections for the Returns account that have data for the current Point of View.

Copyright © 2008, Oracle. All rights reserved.

Loops and Data Units

A data unit includes records for all intersections that have data for a specified Point of View.Data units provide an alternative to member lists for specifying a subset of data for a loop.You can use data units for rules that only apply to account intersections that already have data.

California Budget 2006 April <Entity Currency>

NODATAPeriodic[None]RetailWood[None][ICP None]Returns

500Periodic[None]RetailPlastic[None][ICP None]ReturnsNODATAPeriodic[None]RetailSteel[None][ICP None]Returns

350Periodic[None]RetailBrick[None][ICP None]Returns

ViewC4C3C2C1ICPAccount

Set DataUnit=HS.OpenDataUnit("A#Returns")

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 126: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-16 Hyperion Financial Management 9.3.1: Create Rules

Opening Data UnitsYou use the Set command with the OpenDataUnit function to open a data unit.

Syntax

Set DataUnit=HS.OpenDataUnit("DimensionMembers")

Parameter

DataUnit

A variable for the data unit

DimensionMembers

The Point of View for the data unit

Copyright © 2008, Oracle. All rights reserved.

Opening Data Units

Use HS.OpenDataUnit to open a data unit.If you omit an Account, ICP or Custom dimension, all base members with data for that dimension are included in the data unit.

Set TaxData=HS.OpenDataUnit("E#East.A#Taxes")

Use the GetItem function to retrieve an item from a data unit.

I=5HS.TaxData.GetItem (I,Acct,ICP,Cust1,Cust2,Cust3,Cust4,Val)HS.Exp "A#" &Acct& ".C1#" &Cust1& "=" &Val

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 127: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-17

You do not need to specify all dimensions in the Point of View:

• If you omit the Scenario, Year, Period, or Value dimensions, the current Point of View member is used.

• If you omit the View dimension, the default view of the specified scenario member (if defined) or the current scenario is used.

• If you omit an Account, ICP or Custom dimension, all members with data for that dimension are included in the data unit.

For the example on the slide, member intersections with data for the Tax account for the East entity are included in the TaxData data unit.

After you open a data unit, you use the GetItem function to retrieve data for items in the data unit.

Syntax

call DataUnit.GetItem (Item,Acct,ICP,C1,C2,C3,C4,dData)

Parameters

For the example on the slide, the GetItem function is used to retrieve the fifth item in the data unit. The Acct, Cust1, and Val variables are used to insert the Account member, Custom1 member, and the data for the item into the HS.Exp expression.

Parameter DescriptionDataUnit A variable for the data unit.

Item The index number of the item to retrieve.

Acct A variable to store the Account member for the item.

ICP A variable to store the ICP member for the item.

C1, C2, C3, C4 Variables to store the custom dimension members for the item.

dData A variable to store the data for the item.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 128: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-18 Hyperion Financial Management 9.3.1: Create Rules

Creating Loops With Data UnitsLooping through a data unit is similar to looping through an array created with a member list. The first index for the first element in the data unit array is always zero. To determine the total number of items in the array, use the GetNumItems function. Because numbering of the items starts at zero, the index of the last item in the array will be the total number of items minus 1.

In the example on the slide, the value for the Returns account for each product with the code 001 for the Forecast scenario is calculated as a 10% increase over the prior year actual values. Because there is no need to perform the calculation for products for which prior year actual data does not exist, the OpenDataUnit function is used to retrieve just those products that have data. The following is a line by line explanation:

Line 1: An If...Then statement is used to limit the rule to the Forecast scenario.

Copyright © 2008, Oracle. All rights reserved.

Creating Loops with Data Units

Use the GetINumItems function to determine the number of items in the data unit.Use the same variable for the loop counter and the GetItem index.

If HS.Scenario.Member="Forecast" thenSet ActData=HS.OpenDataUnit("S#Actual.A#Returns.Y#Prior")vNumItems=ActData.GetNumItemsIf vNumItems > 0 ThenFor i=0 to vNumItems-1Call ActData.GetItem(i,vAcct,vICP,vCust1,vCust2,vCust3,vCust4,vData) If HS.Custom2.UD1(vCust2)="001" ThenHS.Exp "A#Returns.C2#" &vCust2& ".C3#" &vCust3& "=" &vData& "* 1.10"

End IfNext End IfEnd If

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 129: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

Hyperion Financial Management 9.3.1: Create Rules 6-19

Line 2: The OpenDataUnit function is used to retrieve all prior year actual data for the Returns account. The data unit is assigned to the variable ActData.

Line 3: The number of items in the data unit is stored in the variable vNumItems.

Line 4: If prior year actual data for Returns does not exist, the OpenDataUnit function does not return any items. This line verifies that there are items to be processed.

Line 5: A For...Next statement is used to create a loop that repeats for every item in the data unit. The variable i is used as a counter.

Line 6: The GetItem function is used to retrieve items from the data unit. The variable i is used for the index variable so that it is incremented at each iteration of the For...Next loop. Notice that vCust2 is the variable for the Custom2 member, which is the dimension that contains products.

Line 7: The calculation should execute only for products with the code 001. The user UD1 attribute of the Custom2 member is checked to see if its value is 001. The vCust2 variable is used to specify the current member of custom2 returned by the GetItem function.

Line 8: The HS.Exp function is used to calculate the value for the Returns account. Because Custom1 and Custom4 are not valid for the Returns account, they are omitted from the HS.Exp function. The vData variable stores the prior year Actual value returned by the GetItem function.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 130: D53155 hfm 9.3.1_rules_student_guide

Module 2 Creating Procedures, Loops, and ArraysLesson 6 Creating Arrays and Loops

6-20 Hyperion Financial Management 9.3.1: Create Rules

SummaryIn this lesson, you should have learned to:

• Describe arrays and loops

• Create arrays

• Fill arrays using member lists

• Create loops

• Assign values with arrays and loops

• Work with member attributes in loops

• Open data units

• Create loops with data units

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 131: D53155 hfm 9.3.1_rules_student_guide

M O D U L E 3

Working with Data and Hierarchies 6

OverviewIn this module you learn how to work with Financial Management data and hierarchies.

Lessons in this module include:

• Managing Financial Management Data

• Working with Dimension Hierarchies

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 132: D53155 hfm 9.3.1_rules_student_guide

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 133: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 7

Managing Financial Management Data 7

ObjectivesAt the end of this lesson, you should be able to:

• Retrieve data with the GetCell function

• Write data with the SetDataWithPOV function

• Test for no data

• Set accounts to no input or input

• Round and scale account values

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 134: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-2 Hyperion Financial Management 9.3.1: Create Rules

Retrieving and Writing DataSo far, you have used the Exp function to read and write account values. Financial Management rules provide other functions for reading and writing data to data intersections.

The Exp function retrieves data from a source account or accounts and writes it to a destination account or accounts. However, sometimes you need to store data to a variable instead of an account. For example, you might want to retrieve the value for TotalHeadcount and store it in a variable, which you then use for a series of calculations. The GetCell function enables you to retrieve data from a specified data intersection or cell and store it in a variable.

Copyright © 2008, Oracle. All rights reserved.

Retrieving and Writing Data

GetCell function: Retrieves data from a single data intersection or cell; stores the data in a variable to use in a calculation or to assign to an account.

SetDataWithPOV function: Writes data to a single data intersection or cell; accumulates or replaces data.

Dim vData, vCustomsvCustoms=".C1#[None].C2#TopC2.C3#TopC3.C4#[None].I#[ICP None]"vData=HS.GetCell("A#Sales" &vCustoms)

Dim vDatavData=500vPOV="A#Acc1.I#[ICP None].C1#[None].C2#[None].C3#[None]"& _

".C4#[None]"Call HS.SetDataWithPOV(vPOV,vData,True)

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 135: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

Hyperion Financial Management 9.3.1: Create Rules 7-3

GetCell FunctionRetrieves data from a specified data intersection or cell and stores it in a variable.

Syntax

HS.GetCell("POVExpression")

Parameter

POVExpression

A valid Point of View. You can include any dimension in the POV expression. If you omit the Entity, Scenario, Year, or Period dimensions, the current Point of View is used.

SetDataWithPOV FunctionYou may need to add to the existing values for an account rather than overwrite them. You can use the SetDataWithPOV option to write data to a specified data intersection or cell. You can choose to overwrite the data for the cell or add to the existing data.

Writes data to a specified data intersection or cell.

Syntax

HS.SetDataWithPOV(POV,Data,Add)

Parameter

Parameter DescriptionPOV A valid expression. You must include members for the Account,

custom, and ICP dimensions. No other dimensions are valid.

Data The data value to set.

Add True to accumulate the data or False to replace the data.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 136: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-4 Hyperion Financial Management 9.3.1: Create Rules

Testing for No DataIf there is no data for the Point of View from which you retrieve data, the GetCell function returns zero.

• If you write the results returned by GetCell to the database, zeros may be written unnecessarily. The result is an increased database size.

• If you use the value returned by GetCell in a calculation, the result may be division by zero errors.

You should verify that there is data before writing data returned by GetCell to the database or using it as a divisor in a calculation:

• Conditional test—You can add a simple conditional test for zero data before writing to the database. For the example on the slide, the value retrieved by GetCell is stored in the vData variable. An If...Then statement verifies that vData does not equal zero before the value for vData is written to the database.

• Variations of GetCell—Financial Management provides the GetCellNoData and GetCellRealData functions to test for no data.

Copyright © 2008, Oracle. All rights reserved.

Testing for No Data

If the source for HS.Exp is an account expression, HS.Exp detects if there is no data and does not write to the destination.If the source for HS.Exp is a constant, HS.Exp cannot detect if there is no data and can potentially write zeros to the database.You can use a conditional to test for no data or for zeros.

Use the GetCellNoData or GetCellRealData function to test for no data.

Dim vDatavCustoms=".C1#[None].C2#Wood.C3#C_Retail.C4#[None].I#[ICP None]"vData = HS.GetCell("A#Sales" &vCustoms)* 1.1If vData <> 0 ThenHS.Exp "A#Sales"& vCustoms"="&vData

End IF

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 137: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

Hyperion Financial Management 9.3.1: Create Rules 7-5

GetCellNoData FunctionGets the data contained in a cell and indicates if the cell contains no data.

Syntax

HS.GetCell("POVExpression,Var")

Parameter

POVExpression

A valid Point of View. You can include any dimension in the POV expression.

Var

A variable that returns True if there is no data and False if there is real data or derived data.

Example

vCustoms=".C1#[None].C2#Wood.C3#C_Retail.C4#[None].I#[ICP None]"vData=HS.GetCellNoData(“A#GrossSales” &vCustoms,IsEmpty)*1.1If Not IsEmpty Then

HS.Exp "A#Sales” &vCustoms& "=" &vDataEnd If

GetCellRealData FunctionGets the data contained in a cell and indicates if the cell contains real data.

Syntax

HS.GetCellRealData("POVExpression,Var")

Parameters

POVExpression

A valid Point of View. You can include any dimension in the POV expression.

Var

A variable that returns True if there is real data and False if there is no data or derived data.

Example

vCustoms=".C1#[None].C2#Wood.C3#C_Retail.C4#[None].I#[ICP None]"vData=HS.GetCellRealData(“A#GrossSales” &vCustoms,IsReal)*1.1If IsReal Then

HS.Exp "A#GrossSales” &vCustoms& "=" &vDataEnd If

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 138: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-6 Hyperion Financial Management 9.3.1: Create Rules

Setting Accounts to No InputWhen setting up calculated accounts using the IsCalculated attribute, in effect, you prevent users from inputting to the accounts. However, you must often enable data input for only some dimension intersections. You can use the Sub NoInput procedure with the NoInput function to disable input for specified data intersections. For example, you can input data for a set of accounts in the Actual scenario and have it calculated in the Forecast scenario.

Sub NoInput Procedure

When used with the NoInput function, disables input to accounts for specified data intersections

Syntax

Sub NoInputHS.NoInput "POVExpression"End Sub

Copyright © 2008, Oracle. All rights reserved.

Setting Accounts to No Input

Sub NoInput procedure:Can be used for accounts where some data intersections must be calculated and others must be inputDoes not recognize the current Point of ViewAllows use of loops and member lists

Sub NoInputHS.NoInput "S#Budget.Y#2006.A#Sales"HS.NoInput "S#Forecast.Y#2006.A#Sales"

End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 139: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

Hyperion Financial Management 9.3.1: Create Rules 7-7

Parameters

POVExpression

Any valid Point of View

Description

The Sub NoInput procedure executes when the application is opened. Therefore, a large Sub NoInput procedure increases (sometimes dramatically) the time required to open the application.

Because the NoInput rules execute when the application is opened, there is no concept of current Point of View. You must explicitly specify dimension members in the POVExpression parameter of the NoInput function; they do not default to the current Point of View if omitted.

When setting many intersections to no input, you can use loops with member lists within the Sub NoInput procedure.

Example

For the example on the slide, input is disabled for cells that intersect the Sales account and the Budget and Forecast scenarios for the year 2006.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 140: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-8 Hyperion Financial Management 9.3.1: Create Rules

Setting Parent Entities to InputIf you want to enter data for some entities at the parent level, you can use the Sub Input procedure with the Input function to enable data input into parent entities at base accounts. For example, you can enter a Sales budget target for the parent entity California and allocate the target to its children.

Sub Input Procedure

When used with the Input function, enables data input at the parent entity level

Syntax

Sub InputHS.Input "POVExpression"End Sub

Copyright © 2008, Oracle. All rights reserved.

Setting Parent Entities to Input

Sub Input procedure:You can use it to enable data entry for parent entities.Children contributions do not roll up to the parent's Entity Currency member in the Value dimension.

Sub InputHS.Input“E#California.S#Forecast"

End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 141: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

Hyperion Financial Management 9.3.1: Create Rules 7-9

Parameters

POVExpression

Any valid Point of View

Description

Only the Entity Currency member of the Value dimension is supported. When you use this function, the value at the parent entity level is not equal to the sum of its children’s contributions. Because this function enables input at the parent entity level, the children contributions do not roll up to the parent entity’s Entity Currency member. However, the parent-child contribution value is stored, and you can still post journals to the Contribution Adjustments member.

Because the Input rules execute when the application is opened, there is no concept of current Point of View. You must explicitly specify dimension members in the POVExpression parameter; if omitted, they do not default to the current Point of View.

Example

For the example on the slide, input into parent entities is enabled for the cells that intersect the Sales account and the Budget scenario.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 142: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-10 Hyperion Financial Management 9.3.1: Create Rules

Rounding and Scaling OverviewData is frequently loaded into the system at a greater precision than is required for reporting purposes. The result is footing problems at the parent level in reports. You can use the VBScript Round function to resolve this problem.

The VBScript Round function does not use financial rounding. If the number to the left of the decimal point is even, values of 5 are rounded down, not up. For example, 3.5 rounds to 4, but 2.5 rounds to 2.

The Round function cannot scale values by rounding whole numbers; for example, hundreds to thousands.

Round Function

Rounds values to a specified level of decimals or to an integer before storing them

Copyright © 2008, Oracle. All rights reserved.

Rounding and Scaling Overview

Rounding or Scaling the stored base-level values prevents footing problems. The VBScript Round function enables you to round to a specified number of decimal places, but does not use financial rounding and does not support scaling.The HS.Round function enables you to round and scale stored values using financial rounding.

110110,390Steel381381,150Total

150150,440Wood

Scaling

120

Scaled

120,320Brick

Stored

150150.35Steel

103103.48Wood

Rounding

359

104

Rounded

359.26Total

104.43Brick

Stored

With scaling and rounding differences, parent totals may not foot.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 143: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

Hyperion Financial Management 9.3.1: Create Rules 7-11

Syntax

Round(Expression [,NumDecimalPlaces])

Parameters

Expression

The numeric expression being rounded.

NumDecimalPlaces

Number indicating how many places to the right of the decimal are included in the rounding. If this parameter is omitted, an integer is returned.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 144: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-12 Hyperion Financial Management 9.3.1: Create Rules

Rounding and Scaling with HS.RoundWhen rounding is enabled with HS.Round, all values written to the database from the following line of script onward are rounded or scaled.

HS.Round Function

Rounds and scales stored values using financial rounding

Syntax

HS.Round(Unit)

Copyright © 2008, Oracle. All rights reserved.

Rounding and Scaling with HS.Round

HS.Round is a switch that turns rounding or scaling on or off for sections of the rules script.If HS.Round is enabled, all values written to the database are rounded or scaled to a specified precision.

HS.Round 1000HS.Exp "A#GrossSalesRnd=A#GrossSales"HS.Exp "A#DiscountsRnd=A#Discounts"HS.NoRound

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 145: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

Hyperion Financial Management 9.3.1: Create Rules 7-13

Parameters

Unit

A factor for rounding or scaling. For example, a value of 0.1 rounds to the nearest tenth. A value of 100 rounds to hundreds.

Description

To switch off scaling, use the HS.NoRound function, which takes no parameters. You can also switch off rounding by using the HS.Round function with a factor of zero.

Example

For the example on the slide, the HS.Round function enables rounding to thousands. Rounded values for GrossSales and Discounts are written to rounding accounts. The HS.NoRound function then disables rounding.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 146: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 7 Managing Financial Management Data

7-14 Hyperion Financial Management 9.3.1: Create Rules

SummaryIn this lesson, you should have learned to:

• Retrieve data with the GetCell function

• Write data with the SetDataWithPOV function

• Test for no data

• Set accounts to no input or input

• Round and scale account values

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 147: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 8

Working with Dimension Hierarchies 8

ObjectivesAt the end of this lesson, you should be able to:

• Work with the calendar

• Manage movement accounts and data views

• Avoid circular references in hierarchies

• Create allocations

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 148: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-2 Hyperion Financial Management 9.3.1: Create Rules

Working with the Calendar When you create an application, the Period, Year, and View dimensions are generated based on the application profile. The Period, Year, and View dimensions comprise the Financial Management calendar.

Copyright © 2008, Oracle. All rights reserved.

Working with the Calendar

Preventing Prior and Next from Crossing into Invalid YearsPopulating days in period accountManaging calculation status

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 149: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-3

Preventing Prior and Next from Crossing into Invalid YearsIn rules, you use the First, Last, Prior, and Next keywords to dynamically select earlier and later periods and years. Year and Period are separate dimensions. However, if the current period is the first period in a year, the Prior function, used with the Period dimension, crosses to the last member of the previous year. For example, if the current period is January, 1996, the syntax P#Prior retrieves the value for December, 1995. Similarly, if the current period is the last period in a year, the P#Next crosses to the next year.

The Prior and Next keywords, however, do not distinguish between interim years and first and last years. Therefore, if a rule tries to retrieve data from a year prior to the first year or after the last year, an error is returned. For example, if the current year is the first year, the syntax Y#Prior returns an error. Similarly, if the current year is the first year and the current period is the first period, the syntax P#Prior returns an error.

Copyright © 2008, Oracle. All rights reserved.

Preventing Prior and Next from Crossing into Invalid Years

Rules can reference members in earlier and later periods.The Prior and Next keywords can cross years but cannot distinguish between interim years and first and last year.

If Not HS.Period.IsFirst ThenHS.Exp "A#SalesChange=A#Sales–A#Sales.P#Prior"

End IF

If HS.Year.Member<>"2005" ThenHS.Exp "A#Opening=A#Closing.P#Prior"

End IF

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 150: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-4 Hyperion Financial Management 9.3.1: Create Rules

When creating rules that use the Prior or Next keywords, you should include tests to ensure that the rules do not reference invalid years. You can use either member names or the IsFirst and IsLast functions to test for first or last year or period.

For the first example on the slide, the rule calculates the change in Sales between periods. If the current period is the first period, the IsFirst function, used with the Period dimension, prevents execution of the calculation and thus prevents the calculation from crossing into earlier years, even if the earlier year is not the first in the application.

For the second example on the slide, opening balances are calculated as equal to prior period closing balances during all years but the first year. An If...Then statement is used with the member name of the first year to exclude the first year from the calculation. The IsFirst function could have been used.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 151: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-5

Populating Days in Period AccountsThe Period dimension does not recognize the number of days in each period. For rules that require the number of days in a period, you create days in period accounts and use rules to populate the accounts.

• Because days in period data is typically global data valid for all entities, set the None member of the Entity dimension as the destination.

• If your rules need to accommodate leap years, include a leap-year test. Use the VB Script mod operator to divide the number of days of the current year by 4. Mod returns just the remainder of a division. If the remainder value returned is zero, the current year is a leap year.

Copyright © 2008, Oracle. All rights reserved.

Populating Days in Period Accounts

If Hs.Entity.Member="[None]" ThenSelect Case UCase(HS.Period.Member)Case "FEB"IF HS.Year.Member mod 4=0 ThenHS.Exp "A#DAYS.W#Periodic=29"

ElseHS.Exp "A#DAYS.W#Periodic=28"

End IFCase "APR", "JUN", "SEP", "NOV"

HS.Exp "A#DAYS.W#Periodic=30"Case Else

Hs.Exp "A#DAYS.W#Periodic=31"End Select

End if

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 152: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-6 Hyperion Financial Management 9.3.1: Create Rules

Managing Calculation StatusWhen you update data for a period, calculation statuses of future periods of the current year are impacted, but calculation statuses of future years are not impacted. For example, if the calculation status for January, 2006 is OK and values are changed for December, 2005, the calculation status for January, 2006 remains OK.

Often, calculations defined in rules include dependencies between years. If you want data updates to impact future years, you specify a year, period, entity, and scenario and use the ImpactStatus function.

Rules for opening balances are an example of when you need to use the ImpactStatus function. For balance sheet accounts, opening balances are typically retrieved from the last period of the prior year using a rule. If data for the last period of the prior year changes, opening balances for the current year need to be recalculated. You can use the ImpactStatus function to ensure that, when a rule modifies data for the last period of a year, the calculation status of the first period of the following year is impacted.

Copyright © 2008, Oracle. All rights reserved.

Managing Calculation Status

Impact Status function:Flags a period as needing calculationIs used to manage future-year calculation status

If Not HS.Year.IsLast ThenIf HS.Period.IsLast Then

HS.ImpactStatus "Y#NEXT.P#FIRST"End If

End If

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 153: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-7

SyntaxHS.ImpactStatus “POVExpression”

Parameters

POV Expression

Any combination of valid Scenario, Year, Period, Entity, and Value members. You can use the First, Last, Prior, and Next keywords with the Period and Year dimensions.

Remarks

If the scenario in the POVExpression parameter is the same as the current point of view, the year and period combination must identify a future period.

If the default frequency of the scenario from which the ImpactStatus function is executed has a different default frequency than the scenario impacted by the function, a period must be explicitly specified in the POVExpression parameter.

Example

For the example on the slide, if the current period is the last period in the year and the current year is not the last year in the application, the calculation status of the first period of the following year is impacted.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 154: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-8 Hyperion Financial Management 9.3.1: Create Rules

Managing Movement Accounts and Data ViewsTo show balance sheet movement, custom dimensions typically contain a mix of balance and flow members:

• The member for the closing balance is a parent.

• The members for opening balance and movement are children of the closing balance member.

• The movement members are flow members that show increases and decreases for the current period.

Because the closing balance member is aggregated from its children, it does not need to be calculated by a rule.

Copyright © 2008, Oracle. All rights reserved.

Managing Movement Accounts and Data Views

Parent accounts aggregate YTD values, even in periodic view.You can make the closing balance member a base member if you need to display balance sheet information in periodic view.

The parent member incorrectly uses the year-to-date values of movement accounts.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 155: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-9

A limitation to this approach is that, when a hierarchy contains a mix of flow and balance accounts, the parent aggregates the year-to-date values of the account, even if the view is set to periodic. This aggregation can result in incorrect results being displayed in data grids and data forms.

If you need to display balance sheet movement results in periodic views, you place the closing balance member at the hierarchy level of the opening and movement members. You can then use a rule to calculate the closing balance.

The illustration on the slide shows a grid in periodic view. The Opening member is set by a rule to equal the ClosingBalance of the prior period. ClosingBalance is a parent member of a custom dimension. The values for ClosingBalance are aggregated from the values for Opening, Increases, and Decreases. Notice that the ClosingBalance member aggregates the year-to-date values of the Increase and Decrease members, rather than the current period values, and therefore shows an incorrect total.

The Closing member is a base level member that is calculated using the following rules.

HS.Exp "C4#Opening=C4#Closing.P#Prior"HS.Exp "C4#Closing=C4#Opening+C4#Increases-C4#Decreases"

The Closing member correctly uses the periodic values.

To support display of balance sheet movement in year-to-date view, you can set the opening balance for all periods equal to the closing balance of the prior year:

HS.Exp "C4#Opening=C4#Closing.Y#Prior.P#Last"

This provides a correct aggregation for closing balance when year-to-date values for movement member are displayed.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 156: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-10 Hyperion Financial Management 9.3.1: Create Rules

Avoiding Circular Calculations in HierarchiesIf the source account in an account expression is a parent level account, its value is aggregated on-the-fly. If the destination is a child of the source account, its value is included in the aggregated parent value, resulting in a circular reference. Some common examples where circular references may occur are offset accounts for balance sheet differences and plug accounts for allocations.

Whenever possible, you should define you hierarchies to avoid circular references. In situations where you cannot avoid rules with circular references, clear the child account by placing the HS.Clear function before the rule that includes the circular reference. The HS.Clear function clears data for a specified point of view.

Syntax

HS.Clear "POVExpression"

POVExpression identifies the members of the Account, ICP, and custom dimensions for which data should be cleared.

Copyright © 2008, Oracle. All rights reserved.

Avoiding Circular Calculations in Hierarchies

Child member calculations:May depend on parent valuesMay result in circular calculations

HS.Clear "A#BSDifference" HS.Exp "A#BSDifference=A#CapitalEmployed–A#Shareholders"

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 157: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-11

All members of omitted dimensions are cleared. You can use the ALL keyword with the Account dimension to clear all accounts.

Example

The following example clears the values for the Steel and Retail custom dimension members for all accounts:

HS.Clear “C2#Steel.C3#Retail”

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 158: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-12 Hyperion Financial Management 9.3.1: Create Rules

Creating Allocations Using Sub AllocateTo obtain correct results, you may need to calculate allocations separately from consolidations and other calculations. For example, you may need to perform this sequence of calculations:

1. Consolidate data to obtain a total for an expense at the parent level.

2. Run an allocation rule to distribute the expense to child entities.

3. Consolidate again to update the parents of the entities.

Placing allocation rules in the Sub Allocate procedure enables you to run them separately from consolidation and calculation rules.

Copyright © 2008, Oracle. All rights reserved.

Creating Allocations Using Sub Allocate

Allocations redistribute data from an entity to a group of entities.For example, you could allocate a lump sum total for heating expenses from the Facilities entity to a list of entities.

The Sub Allocate procedure enables you to run allocation rules separately from consolidations.

Heating 60,000Facilities

Marketing

Development

Sales

20,000

20,000

20,000

Expenses Plug -60,000

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 159: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-13

Running the Sub Allocate ProcedureYou can run a Sub Allocate procedure from the shortcut menu of a data grid or by calling it from another procedure, such as Sub Calculate. The following considerations apply to the Sub Allocate procedure:

• Sub Allocate runs only for members with a calculation status of OK.

• For Sub Allocate rules, Entity Currency is the only valid destination member of the Value dimension.

• When a Sub Allocate procedure runs for and modifies data of a child entity, the consolidation status of the parent entities is impacted.

Copyright © 2008, Oracle. All rights reserved.

Running the Sub Allocate Procedure

Rules in Sub Allocate:Update the Entity Currency member of the Value dimension only.Run only for members with a calculation status of OK.Impact parent calculation status.Sub Allocate()dim vAllocAmountdim vAll_NonesvAll_Nones=".I#[ICP None].C1#[None].C2#[None].C3#[None].C4#[None]"If HS.Entity.IsBase("EastUS","") ThenvAllocAmount=HS.GetCell("A#AdminExpenses.E#EastAdmin" &vAll_Nones)HS.Exp "A#AdminExpAlloc" &vAll_Nones& "="& _

vAllocAmount& "/HS.Entity.NumBase(EastUS)"End IfIf HS.Entity.Member="EastAdmin" ThenHS.Exp "A#AdminExpPlug" &vALL_Nones&"="& -vAllocAmount

End IfEnd Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 160: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-14 Hyperion Financial Management 9.3.1: Create Rules

An allocation rule typically references a parent value, to determine the allocation percentage for each child entity. It also typically writes a reversing entry to a plug account, to ensure that allocated data is not counted twice. For the example on the slide, the NumBase function is used with the EastUS parent entity to retrieve the number of its base members. This number is then used to perform an even split of the data in the AdminExpense account of the EastAdmin entity between the base members of EastUS. A reversing entry for AdminExpense is written to a plug account for EastAdmin.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 161: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-15

Using the Alloc Function to Create Allocation Rules You can use the Alloc function to allocate a value to a list of entities. The Alloc function is unique in that it can write values to multiple destination entities, rather than writing to only the current point of view entity.

Syntax

HS.Alloc ("SourcePOV","DestPOV","EntityList","AllocExp","PlugAccount")

Copyright © 2008, Oracle. All rights reserved.

Using the Alloc Function to Create Allocation Rules

The Alloc function:Facilitates creating allocation rulesIs valid only in the Sub Allocate procedure

Sub Allocate()call HS.ALLOC ("E#Admin.A#Rent","A#RentAlloc","US_Entities","& _"A#Headcount/A#Headcount.E#UnitedStates","E#Admin.A#PLUG")End Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 162: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-16 Hyperion Financial Management 9.3.1: Create Rules

Parameter DescriptionSourcePOV Source point of view for the data to be allocated. You must specify an

Account member, and you can specify ICP and custom members. If you do not specify ICP and custom members:• The default ICP member is [ICP Top].• The default custom member is the top member for the account.If you do not specify an entity, the current point of view entity is used.

DestPOV Destination point of view for the data to be allocated. You must specify an Account member, and you can specify ICP and custom members. Note the following usage rules:• If you do not specify an ICP member, the default is [ICP None].• If you do not specify a custom member, the default is [None].• If you specify an Entity member and the EntityList parameter is [Base],

the member is used as the parent.

EntityList Member list that identifies the entities to which the data is to be allocated. You can use the system-defined entity list [Base] or a user-defined list. If you use the system-defined list [Base], the system uses the entity specified in the destination point of view as the parent member. If you use a user-defined list that contains parent members, the parent members are skipped.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 163: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

Hyperion Financial Management 9.3.1: Create Rules 8-17

Example

For the example on the slide, the balance in the Rent account is allocated to the RentAlloc account for the members in the US_Entities member list. The allocation percentage is calculated by dividing the headcount of each member by the total headcount of the United States. A reversing entry for the Rent account is written to the Plug account.

AllocExp Expression that yields a factor to use to determine the amount to allocate to each entity. The SourcePOV value is multiplied by this factor. This expression can contain the following types of values:• Numbers• Account expressions that identify a numeric value. You can specify an

Account member, and you can specify members of the ICP and custom dimensions. Note the following usage rules:

- If you do not specify a custom member, the default is <Top Member>.

- If you do not specify an ICP member, the default is [ICP Top].- If you do not specify Scenario, Year, Period, View, or Value

members, the default is <Current>.- If you do not specify an Entity member, the default is the

destination entity.

PlugAccount Name of a plug account. This optional argument is used to reverse the source point-of-view amount, considering the attribute of the source point-of-view account versus the attribute of the plug account.You must specify an Account member, and you can specify ICP and custom members. Note the following usage rules:• You cannot use this argument if the source point-of-view Entity member

is a parent or the source point-of-view Value member is not <Entity Currency>.

• If you do not specify an ICP member, the default is [ICP None].• If you do not specify custom member, the default is [None].

Parameter Description

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 164: D53155 hfm 9.3.1_rules_student_guide

Module 3 Working With Data and HierarchiesLesson 8 Working with Dimension Hierarchies

8-18 Hyperion Financial Management 9.3.1: Create Rules

SummaryIn this lesson, you should have learned to:

• Work with the calendar

• Manage movement accounts and data views

• Avoid circular references in hierarchies

• Create allocations

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 165: D53155 hfm 9.3.1_rules_student_guide

M O D U L E 4

Creating Custom Translations and Consolidations 8

OverviewIn this module, you learn to customize the default currency translation and consolidation calculations.

Lessons in this module include:

• Creating Currency Translation Rules

• Creating Consolidation and Elimination Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 166: D53155 hfm 9.3.1_rules_student_guide

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 167: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 9

Creating Currency Translation Rules 9

ObjectivesAt the end of this lesson, you should be able to:

• Explain Financial Management currency translation

• Explain the default translation calculation

• Create rules using translation functions

• Calculate exchange differences

• Work with currency overrides

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 168: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-2 Hyperion Financial Management 9.3.1: Create Rules

About Translating DataCurrency translation converts account data from one currency to another. When you consolidate data, currency translation occurs if the parent entity has a different default currency than the child entities. Data in the Entity Currency Total member of the child is translated to the parent’s currency and the result is stored in the corresponding child currency member. For example, if EntityA has a default currency of USD and its parent, Europe, has a default currency of EUR, data in the Entity Currency Total member of EntityA is translated to EUR and is written to the EUR member of EntityA. The data in the EUR member of EntityA is then consolidated to the Entity Currency member of Europe.

N O T E

Translated values are never written to the Parent Currency member of the Value dimension. The Parent Currency member is a read-only member that displays the translated currency member of the child.

Copyright © 2008, Oracle. All rights reserved.

About Translating Data

EUR

USDEurope

EntityAParent

Currency = EUREntity Currency Total = USD

Entity Currency = EUR

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 169: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-3

Currency and Exchange Rate SettingsThe settings for the following properties determine the default translation behavior for the application.

Property DescriptionDefCurrency Specifies a default currency for each entity. During

consolidation, the DefCurrency property of the child is compared to the DefCurrency property of the parent to determine whether translation is needed.

DefaultCurrency Specifies the default currency for the entire application. This property is set in the Application Settings module.

Copyright © 2008, Oracle. All rights reserved.

Currency and Exchange Rate Settings

Default currency for each entityDefault currency for the applicationExchange rates accounts for flow and balance accountsTranslation method

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 170: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-4 Hyperion Financial Management 9.3.1: Create Rules

Exchange rates can be entered into the exchange rate accounts either at an individual entity or at the [None] entity. The system follows this sequence in determining the exchange rates to use:

1. The system looks in the current entity for an exchange rate.

2. If a rate is not found in the current entity, the system looks at the [None] entity for the rate.

3. If a rate is not found in the None entity, the system derives the rate by triangulation, using the application currency in the [None] entity.

4. If triangulation fails, the entity is not translated.

Triangulation is performed using the default application currency. For example, suppose that you are converting EURO to YEN, but the system cannot find an exchange rate to perform the translation. Assume that exchange rates to translate balances into USD exist for both the EURO and the YEN. Using triangulation, the system converts the EURO balance to USD and then converts the USD balance to YEN.

DefaultRateForBalance Specifies the account containing the exchange rates to use for Asset and Liability accounts. This property is set in the Application Settings module.

DefaultRateForFlow Specifies the account containing the exchange rates to use for Income and Expense accounts. This property is set in the Application Settings module.

UsePVAForBalance Use the PVA translation method for all Asset and Liability accounts. This property is set in the Application Settings module.

UsePVAForFlow Use the PVA translation method for all Revenue and Expense accounts. This property is set in the Application Settings module.

Property Description

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 171: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-5

Translation MethodsFinancial Management provides two translation methods. The value at rate (VAR) method translates the entire current period balance at the current period exchange rate. The periodic value add (PVA) method translates only the current month change in the YTD value at the current period exchange rate. It then adds the translation result to the balance from the previous period, translated at the exchange rate of the previous period.

For the example on the slide, the January YTD balance in local currency is 50 and the exchange rate is 1:3. The YTD local currency balance for February is 75 and the exchange rate is 1:2. To determine the February translated value:

• The value at exchange rate method translates the entire February local currency balance (75) at the February exchange rate (1:2), for a translated balance of 150.

• The PVA method calculates the change between the January and February local currency balance (25) and translates the change at the February rate (1:2), for a translated value of 50. It adds this result to the translated January balance (150), for a February translated balance of 200.

Copyright © 2008, Oracle. All rights reserved.

Translation Methods

The VAR method translates the entire balance at the current period rate.

The Periodic Value Add (PVA) method: 1. Translates just the YTD change from the previous period at the current period’s

exchange rate.2. Adds this to the previous month translated balance.

200150150150Translated YTD

PVA MethodVAR

50

1:3

Jan

75

1:2

Feb

75

1:2

Feb

50Local Currency YTD

1:3Exchange Rate

Jan

Current period increase/decrease at current period rate plus prior period balance

75 – 50 = 25

X 2

50

+ 150

200

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 172: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-6 Hyperion Financial Management 9.3.1: Create Rules

You set the PVA method in the Application Settings module. You can set the PVA method for flow accounts, for balance accounts, or for both. If you do not set the PVA method, the VAR method is used.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 173: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-7

Default Translation ProcessThe following steps describe the translation process during consolidation.

1. The DefCurrency property of the child being consolidated is compared to the DefCurrency property of the parent. If the currencies are different, translation is initiated.

2. The exchange rate account and translation method are selected based on the account type:

• For Revenue and Expense accounts, the rate account specified by the DefaultRateForFlow property is used. If PVAForFlow is enabled, the PVA method is selected; otherwise the VAR method is selected.

• For Asset and Liability accounts, the rate account specified by the DefaultRateForBalance property is used. If PVAForBalance is enabled, the PVA method is selected; otherwise the VAR method is selected.

Copyright © 2008, Oracle. All rights reserved.

Default Translation Process

USDUnitedStatesEUREurope

DefCurrency

EndRateDefaultRateForBalance

AvgRateDefaultRateForFlow

United States April

420240

EUR

300Cash200Gross Sales

Entity Currency Total

April

1.4EndRate

1.2AvgRate

Europe April

420Cash240Gross Sales

Entity Currency

NoUsePVAForBalanceYesUsePVAForFlow

Compare default currencies

1

2 3

4

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 174: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-8 Hyperion Financial Management 9.3.1: Create Rules

3. The value in the Entity Currency Total member is translated using the selected rate account and translation method. The result is stored in the Value dimension currency member that corresponds to the parent’s default currency.

4. The translated value is consolidated to the Entity Currency member of the parent.

DefaultRateForFlow, DefaultRateForBalance, UsePVAForFlow, and UsePVAForFlow are application-wide settings that apply to all accounts. Custom translation rules are frequently used to override these settings. For example, you might create a custom translation rule to use a historical rate for some balance sheet accounts but not for others.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 175: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-9

Creating Custom Translation RulesYou use translation rules to perform calculations on nonstandard translations. You place translation rules in the Sub Translate procedure.

• If you add a translation rule for an account to the Sub Translate procedure, the translation rule overrides the default translation for the account.

• All accounts for which rules do not exist in the Sub Translate procedure are translated using the default translation.

Although you can perform other calculations in the Sub Translate procedure, it is typically used only for translations. Because the Sub Calculate procedure runs automatically after the Sub Translate procedure, you can place any calculations on the translated data in the Sub Calculate procedure.

Copyright © 2008, Oracle. All rights reserved.

Creating Custom Translation Rules

Sub Translate()........ End Sub

Sub Calculate()

If HS.Value.IsTransCur Then......

End If

End Sub

Rules in Sub Translate execute during consolidation. They can also be run from a Data Grid or Data Form

Rules in this section of Sub Calculate execute only on translated values.

Source currency member: Entity Currency Total.

Destination currency member: Current point of view or parent default currency.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 176: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-10 Hyperion Financial Management 9.3.1: Create Rules

You can use a conditional statement with the IsTransCurr function to create a section in Sub Calculate that runs only for translated data. If the current Value dimension member is a translated currency, IsTranCurr function returns a value of TRUE.

For rules within the Translation procedure, the default source member for the value dimension is Entity Curr Total. The destination member for the Value dimension is always the default currency member of the parent currency if the translation occurs during a consolidation or the currently select point of view member if the translation is run from a data grid.

Data is translated using the default exchange rate accounts define in the Application Setting module, unless you override the defaults using a translation function. For example, if you use an HS.Exp expression in the Sub Translate procedure, the source data is automatically translated using the default exchange rate account when it is written to the destination.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 177: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-11

Translation FunctionsYou use the Trans and Transperiodic functions to override the default translations for an account. The parameters for the two functions are identical. The only difference between the functions is the translation method used.

Copyright © 2008, Oracle. All rights reserved.

Translation Functions

Two translation functions are available,Trans and Transperiodic:— Trans translates using the VAR method— Transperiodic translated using the PVA method

Using translation functions, you can:— Specify the translation method— Specify the exchange rate account— Specify source and destination accounts— Calculate differences due to exchange rates

HS.Trans "C1#Movement","","A#AvgRate",""HS.TransPeriodic "A#SaleForecast","A#Sales","A#ForecastRate",""

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 178: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-12 Hyperion Financial Management 9.3.1: Create Rules

SyntaxHS.Trans "DestPOV","SourcePOV","Rate1","Rate2"

HS.Transperiodic "DestPOV","SourcePOV","Rate1","Rate2"

Remarks• The source Value dimension member is always Entity Curr Total. The destination

Value dimension member is always the parent member when Sub Translate runs during consolidation or the current point of view currency member if Sub Translate is run from a data grid or a data form.

• If the same destination point of view is used in multiple rules with Trans or Transperiodic, the results accumulate in the destination account. Results do not accumulate between the Trans and Transperiodic functions, however. For example, if a rules that uses the TRANS functions writes to a destination account that was previously written to by a rule using the Transperiodic function, the previous result is overwritten.

ExamplesFor the first example on the slide, the Movement member of the Custom1 dimension is translated using the rates in the AvgRate account. Because no account member is specified for the SourcePOV parameter, the rule is executed for all accounts for which the Movement member is valid.

For the second example on the slide, the source and destination account are different. The source account, Sales, is translated using the exchange rates in the ForecastRate account. The translated data is written to the destination account, SalesForecast.

Parameter DescriptionDestPOV The destination point of view. The destination can be any combination of

Account, Custom1...4, or ICP members. For each unspecified dimension, the system writes to all valid members of the dimension. For each specified dimension, the system writes to the specified member only.

SourcePOV The source point of view. The source can be any combination of dimensions. If the Account, Custom1...4, and ICP dimensions are unspecified, the system uses the same member as the Destination member. If the Scenario, Year, Period, and Entity dimensions are not specified, the system uses the current members. If the Value dimension is not specified, the system uses the Entity Curr Total member. If SourcePOV is left blank, the system uses the destination point of view as the source point of view.

Rate1 The exchange rate. The rate can be a constant, an exchange rate account, or a specific cell.

Rate2 An exchange rate to use for calculating an exchange variance.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 179: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-13

Calculating Exchange Differences When currency translation take place, changes in account balances are frequently due solely to fluctuations in exchange rates. For the example on the slide, the balance of 500 for Cash is unchanged between January and February for the local currency. The translated values, however, show an increase of 100 for Cash in February. Examining the exchange rates shows that the increase stems from a difference in the rates for January and February.

Copyright © 2008, Oracle. All rights reserved.

Calculating Exchange Differences

700500

Feb

600Cash -EUR500Cash - Entity Currency Total

Jan

1.4Feb Closing Rate

1.2Jan Closing Rate

Entity Currency100FXOEUR

1.4EndRate1.2OpenRate

Difference of 100 is due to exchange rates.

HS.Trans "A#Cash.C1#FXO","A#Cash.C1#Opening","A#EndRate","A#OpenRate"

(Cash x EndRate) - (Cash x OpenRate) = 700 - 600 = 100

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 180: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-14 Hyperion Financial Management 9.3.1: Create Rules

You can use the translation functions to calculate differences due to exchange rates. If you include a second exchange rate account in the Rate2 parameter of the Trans or Transperiodic function, the following calculation is performed:

1. The source account is translated using the exchange rate account specified in the Rate1 parameter.

2. The source account is translated using the exchange rate account specified in the Rate2 parameter.

3. The difference between the result calculated using Rate1 and the result calculated using Rate2 is written to the destination account.

The slide example shows a rule to calculate the effect of exchange rates on the opening balance for Cash. For balance sheet accounts, data is typically translated at the current period end rate. Opening values, however, are taken from the prior period and therefore should be translated at the prior period end rate. For the example on the slide, the value for the Opening member for Cash is translated using both the EndRate account and the OpenRate account. The OpenRate account contains the prior period exchange rates. The translation difference is written to an adjustment member in the Custom1 dimension, FXO.

You can also take advantage of the cumulative nature of the translation functions to accumulate adjustments to a single currency translation adjustment account. In the following example, balance sheet accounts with the user defined attribute “HIST” are translated at a historical rate. Exchange differences for these accounts are accumulated into the CTA account.

dHistRate = HS.GetRate("A#HistRate")dBSRate = HS.GetRate("A#BSRate")sBSAccts = HS.Account.List("BALANCESHEET","[Base]")For Each sBSAcct In sBSAcctsIf UCase(HS.Account.UD2(sBSAcct)) = "HIST" ThenHS.Trans "A#" & sBSAcct, "", dHistRate, ""HS.Trans "A#CTA", "A#" & sBSAcct, dHistRate, dBSRate

End IfNext

T I P

Note the use of the GetRate function in the previous example. The GetRate function retrieves the exchange rate for a specified rate account and point of view. If no point of view is specifed, the current point of view is used.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 181: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-15

Working with Currency OverridesSome balance sheet accounts have static data that rarely changes, for example, paid-in capital. For these accounts, it is sometimes more efficient to use already translated historical data than to translate values each period. This is called a currency override.

When you use currency overrides, you load the translated historical values to the Entity Currency member of a statistical account. Typically you use an account type of Balance or Balance Recurring. Data for these account types does not get translated. During translation, you retrieve the already translated historical values from the statistical account instead of translating the local currency data. Because you do not need to translate the data, you can use the HS.Exp function to pull the values.

Copyright © 2008, Oracle. All rights reserved.

Working with Currency Overrides

Sub Translate()If HS.GetCell("A#PaidInCap_USD")<>0 ThenHS.EXP "A#PaidInCap=A#PaidInCap_USD"

ElseHS.EXP "A#PaidInCap = P#Last.Y#Prior"

End IfEnd Sub

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 182: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-16 Hyperion Financial Management 9.3.1: Create Rules

For the example on the slide, the PaidInCapital_USD account contains translated historical USD values for the PaidInCapital account. It is a Balance Recurring account, so it does not get translated. The GetCell function verifies that there is data in the Entity Currency member of PaidInCapital_USD for the current period:

• If there is a value for the current period, it is copied to the USD currency member for PaidInCapital.

• If there is no value for the current period, the closing value from the previous year for PaidInCapital is used. It will be translated using the default rate account .

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 183: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

Hyperion Financial Management 9.3.1: Create Rules 9-17

SummaryIn this lesson, you should have learned to:

• Explain Financial Management currency translation

• Explain the default translation calculation

• Create rules using translation functions

• Calculate exchange differences

• Work with currency overrides

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 184: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 9 Creating Currency Translation Rules

9-18 Hyperion Financial Management 9.3.1: Create Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 185: D53155 hfm 9.3.1_rules_student_guide

L E S S O N 1 0

Creating Consolidation and Elimination Rules 10

ObjectivesAt the end of this lesson, you should be able to:

• Explain the default consolidation calculation

• Create consolidation rules

• Create elimination rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 186: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-2 Hyperion Financial Management 9.3.1: Create Rules

Default Consolidation CalculationsConsolidation is the process of gathering data from child entities and aggregating the data to parent entities. When you run a consolidation, each child’s contribution to the parent is derived by using the default application calculations, and the results are written to the Entity Currency member of the parent.

The following steps describe the procedure that Financial Management uses to calculate the child’s contribution to the parent for each account:

1. Check the IsConsolidated property of the account to verify that it should be consolidated.

2. Apply the consolidation percentage in the PCON account to the data in the Parent Total member. The Parent Total member is the sum of the Parent Curr Total and Parent Curr Adjs members. If there is no data in the PCON account, use 100 percent.

Copyright © 2008, Oracle. All rights reserved.

Entity Currency of Parent = Proportion + Elimination + Contribution Adjs

Default Consolidation Calculations

EntityAICP

IC_PlugPlugAcct

YesIsIcp

Parent total x PCON

If Interco, then -(Parent total x PCON)

Input through journals

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 187: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

Hyperion Financial Management 9.3.1: Create Rules 10-3

3. Write the result to the Proportion member of the Value dimension.

4. Run the Sub Calculate procedure for the Proportion member.

5. Determine whether an intercompany elimination is required. These four criteria must be met:

• The IsICP property of the account is selected.

• The PlugAcct property of the account specifies a plug account.

• The ICP member of the Value dimension specifies an intercompany partner.

• The specified Intercompany partner is a descendant of the current parent.

6. If the criteria listed in step 5 are met, perform the elimination:

• Write a reversing entry for the data in the Proportion member to the Elimination member.

• Write an entry for the data in the Proportion member to the Elimination member of the Plug account.

7. Run Sub Calculate for the Elimination member.

8. Run Sub Calculate for the Contribution Adjs member.

9. Write the sum of the Proportion, Elimination, and Contribution Adjs members of the child to the Entity Currency member of the parent.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 188: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-4 Hyperion Financial Management 9.3.1: Create Rules

Creating Consolidation RulesYou can override the default consolidation calculations by creating your own consolidation rules. To use consolidation rules, you must enable the ConsolidationRules option in the Application Settings module. When the ConsolidationRules option is enabled, the default consolidation calculations are disabled. You must create a Sub Consolidate procedure in the rules file with the necessary rules for consolidation. Accounts for which you do not include rules in the Sub Consolidate procedure do not get consolidated.

You create consolidation rules to derive the values for the Proportion and Elimination members of the value dimension. The aggregation of the Proportion, Elimination, and Contribution Adjs members to the Entity Currency member of the parent takes place automatically, even when the ConsolidationRules option is enabled.

Copyright © 2008, Oracle. All rights reserved.

Creating Consolidation Rules

Sub Consolidate()......End Sub

!APPLICATION_SETTINGSDefaultCurrency=USDUsePVAForBalanceAccounts=NUsePVAForFlowAccounts=NConsolidationRules=YOrgByPeriodApplication=NNodeSecurity=Entity

To create consolidation rules:Enable the ConsolidationRules option. Add Sub Consolidate to the rules file.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 189: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

Hyperion Financial Management 9.3.1: Create Rules 10-5

Consolidation FunctionsThe following sections describe functions that are commonly used in consolidation rules.

PConThe PCon function retrieves the consolidation percentage from the PCON system account for a specified parent and child. It is used with the HS.Node object.

Syntax

HS.Node.PCon("S#Scenario.Y#Year.P#Period.E#Parent.Child")

Copyright © 2008, Oracle. All rights reserved.

Consolidation Functions

PCon retrieves the consolidation percentage.Con updates the Proportion and Elimination members.— The source is the Parent Total member for the current entity, year, and

scenario.— The destination is either the Proportion or the Elimination member.

HS.Parent Member returns the current parent member label.

vPCon=HS.Node.PCon("")HS.Con "", vPCon, ""

HS.Con "V#[Elimination]",-1, ""

Example 1

Example 2

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 190: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-6 Hyperion Financial Management 9.3.1: Create Rules

Parameters

Scenario, Year, Period

Specifies the point of view for which to retrieve the consolidation percentage. If a dimension is omitted, the value from the current point of view is used.

Parent, Child

The parent and child for which to retrieve the consolidation percentage. If this parameter is omitted, the current parent and child are used.

ConThe Con function multiplies the value of the Parent Total member by a specified factor and updates either the Proportion member or the Elimination member of the Value dimension with the result. This function is valid only in the Sub Consolidate procedure.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 191: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

Hyperion Financial Management 9.3.1: Create Rules 10-7

Syntax

HS.Con "DestinationPOV", Factor, "Nature"

Remarks

You cannot specify a source for the Con function. The source is always the Parent Total member of the Value dimension for the current scenario, year, period, and entity. The Con function is used in conjunction with the OpenDataUnit function, and the source account for Con is always the account for the current item in the data unit.

You can specify a destination account. If the destination account has a different debit/credit attribute from the source account, the system reverses the sign in the destination. For example, if the source is a Revenue account and the destination is an Expense account, the sign is reversed.

Examples

For example 1 on the slide, the Parent Total member is multiplied by the value returned by the PCon function. If there is no data in the PCON account for the current point of view, a default of 100 percent is used. Because no destination point of view is specified, the Proportion member, which is the default destination member, is updated with the result.

Parameter DescriptionDestinationPOV Any combination of members for the following dimensions:

• Account• Intercompany• Custom1...4• Entity• ValueValid destination members for the Value dimension are Proportion and Elimination. If no value dimension member is specified, the Proportion member is used as the destination.

Factor A factor by which to multiply the data in the Parent Total member. This factor can be a constant, a function, or an expression using mathematical operators (+ - * /). The PCon function is typically used to specify the factor.

Nature A text string used for audit purposes. This string is stored and provides information about the accounting purpose of the transaction. To allow users to view consolidation source and destination transaction detail after running a consolidation, you must include text in this parameter. If you do not include text, the transaction detail is not stored.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 192: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-8 Hyperion Financial Management 9.3.1: Create Rules

If you are not using consolidation percentages in your application, you can use a factor of 1 to consolidate all children at 100 percent, as in the following example:

HS.Con "", 1, ""

For the second example on the slide, the Elimination member is specified as the destination. Because this is a reversing entry, a factor of -1 is used.

HS.Parent.MemberThe HS.Parent.Member function returns the parent of the current entity. This function does not have any parameters.

Examples

The following example stores the current parent in a variable:

vParent=HS.Parent.Member

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 193: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

Hyperion Financial Management 9.3.1: Create Rules 10-9

Consolidation Procedure ExampleWhen you create a consolidation procedure, you typically first create rules to perform the default consolidation calculations. You then modify the default calculations for those entities or accounts that need special treatment. The example on the slide shows a Sub Consolidate procedure that includes rules to perform the default consolidation and elimination calculations. The following steps explain the example procedure:

1. Opens a data unit for the current point of view and stores it in the ConsUnit variable. ConsUnit contains records for all accounts that have data for the current entity, scenario, year, and period.

2. Creates a For...Next loop to loop through each item in ConsUnit.

3. Uses the GetItem function to retrieve an item from ConsUnit. The account is stored in the variable vAcc and the intercompany partner is stored in vICP.

Copyright © 2008, Oracle. All rights reserved.

Consolidation Procedure Example

Sub Consolidate()Set ConsUnit = HS.OpenDataUnit("")NumItems = ConsUnit.GetNumItemsFor i = 0 to NumItems -1Call ConsUnit.GetItem(i, vAcc, vICP, vC1, vC2, vC3, vC4, vData)If HS.Account.IsConsolidated(vAcc) And vData <> 0 ThenHS.Con "", HS.Node.PCon(""), ""vPlug = HS.Account.PlugAcct(vAcc)If CanEliminate(vPlug,vICP)= True ThenHS.Con "V#[Elimination]", -HS.Node.PCon(""), ""HS.Con "A#" &vPlug& ".V#[Elimination]", HS.Node.PCon(""), ""

End IfEnd If

NextEnd Sub

1

2

345

6

7

8

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 194: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-10 Hyperion Financial Management 9.3.1: Create Rules

4. Verifies that the IsConsolidate property is TRUE for the current account and that it does not contain a zero. If these conditions are not met, the rest of the procedure is skipped.

5. Uses the Con function to derive the value for the Proportion member:

• The HS.Node.Pcon("") function retrieves the consolidation percentage for the current child to use as the multiplication factor.

• Because the destination parameter of HS.Con is blank, the result is written to the default destination, which is the Proportion member.

6. Retrieves the plug account for the current account and stores it in the vPlug variable. The vAcct variable from the GetItem function is used to specify the current account. The vPlug variable is used in a later step as part of the intercompany elimination.

7. Calls the CanEliminate custom function. This function returns a value of TRUE if the account requires an intercompany elimination. This function is explained in a later topic.

8. If the CanEliminate function returns a value of TRUE:

• The Con function is used to write a reversing entry to the Elimination member. To create the reversing entry, the Elimination member is specified as the destination, and the sign for the consolidation percentage is reversed.

• The elimination amount is written to the Elimination member of the plug account.

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 195: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

Hyperion Financial Management 9.3.1: Create Rules 10-11

Testing for Eliminations Data that require intercompany elimination must meet the following criteria:

• The account must be an intercompany account.

• A plug account must be specified.

• There must be an intercompany partner.

• The intercompany partner must be a descendant of the current parent.

Although you can include rules for these tests in the Sub Consolidate procedure, it is common to create a custom function for this purpose.

Copyright © 2008, Oracle. All rights reserved.

Testing for Eliminations

Function CanEliminate(vPlug, vICP)vPar=HS.Parent.MemberCanEliminate = TRUEIf vPlug="" ThenCanEliminate=FALSE

ElseIf vICP="[ICP None]" ThenCanEliminate=FALSE

ElseIf Not HS.Entity.IsDescendant(vPar, vICP) ThenCanEliminate=FALSE

End IfEnd Function

12

3

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 196: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-12 Hyperion Financial Management 9.3.1: Create Rules

The example on the slide shows a custom function named CanEliminate that returns a value of TRUE if the criteria are met. The intercompany partner and the plug account are passed as variables from the Sub Consolidate procedure.

1. The parent member is retrieved and is stored in the vPar variable. The vPar variable is used in a later step to verify that the intercompany partner is a descendant of the current parent.

2. The value for the CanEliminate variable is set to an initial state of TRUE.

3. An If...Then...Else statement is used to test for each of the criteria. If a criterion is not met, the CanEliminate variable is set to FALSE. Otherwise the CanEliminate variable remains set to TRUE.

• The first condition verifies that the account’s PlugAcct property is not empty.

• The second condition verifies that an intercompany partner is selected in the ICP dimension. At the same time, this condition verifies that the account is an intercompany account.

• The third condition verifies that the intercompany partner is a descendant of the current parent member.

• Explain the default consolidation calculation

• Create consolidation rules

• Create elimination rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 197: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

Hyperion Financial Management 9.3.1: Create Rules 10-13

SummaryIn this lesson, you should have learned to:

• Explain the default consolidation calculation

• Create consolidation rules

• Create elimination rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly

Page 198: D53155 hfm 9.3.1_rules_student_guide

Module 4 Creating Custom Translations and ConsolidationsLesson 10 Creating Consolidation and Elimination Rules

10-14 Hyperion Financial Management 9.3.1: Create Rules

Oracle In

ternal & O

racle Aca

demy

Use O

nly