HFM

(1)

Hyperion

Financial Management 9.3.1

Create Rules

Student Guide D52786GC10 Edition 1.0 January 2008 D53155 ® ®

Use Only

(2)

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 RIGHTS

The 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.

Technical Contributors and Reviewers Keith Glide Jennifer Hough Daniel Tijerina Editor Susan Moxley Graphic Designer Carisa Cannan Publisher Judy Gaitan

Use Only

(3)

Preface

Course Objectives . . . vii

Course Structure . . . viii

Course Materials . . . viii

Student Guide . . . viii

Activity Guide . . . viii

Conventions . . . .ix

Module 1: Financial Management Rules Basics

Lesson 1: Reviewing Rules Syntax

About Rules . . . 1-2 Describing Objects and Functions . . . 1-3 Creating Rules Expressions. . . 1-8 HS.Exp Function . . . 1-8 Account Expressions . . . 1-10 Destination Expressions . . . .1-11 Source Expressions . . . .1-11 Adding Operators and Functions . . . 1-12 Period and Year Keywords . . . 1-13 Placing Other Functions Within Exp . . . 1-13 Identifying Rule Types . . . 1-14 Identifying Sub Procedures . . . 1-16 Adding Comments to Rules . . . 1-18 Inserting Line Continuations . . . 1-19 Creating Conditional Statements . . . 1-21 Applying If...Then...Else Statements . . . 1-22 Making Decisions with Select Case Statements . . . 1-24 Comparing Strings in Financial Management . . . 1-25 UCase Function . . . 1-26 LCase Function . . . 1-26

(4)

About VB Script Variables . . . 2-2 Creating Variables and Assigning Values . . . 2-3 Variables and Data Types . . . 2-5 Variables and Constants . . . 2-7 Creating Header Sections for Variables and Constants. . . 2-9 Point of View Variables . . . 2-10 Top and None Members for Custom and ICP Dimensions . . . 2-10 Global Accounts. . . .2-11 Conditional Statement Triggers . . . .2-11

Lesson 3: Managing the Scope of Rules

Subcubes and Data Retrieval . . . 3-2 Financial Management Subcubes . . . 3-3 Subcube Dimensions . . . 3-4 Subcubes and Performance . . . 3-5 Subcubes and Rules . . . 3-7 The Left Side of HS.Exp . . . 3-7 Page Dimensions and HS.Exp. . . 3-9 Managing the Scope of Rules . . . .3-11 Omitting Dimensions . . . 3-12 Specifying the Destination Dimensions . . . 3-13 Specifying Source Dimensions . . . 3-14 Mismatched Custom Dimensions (Example 1) . . . 3-15 Mismatched Custom Dimensions (Example 2) . . . 3-17 Executing Rules with the Value Dimension . . . 3-19 Sub Calculate and the Value Dimension . . . 3-21 Working with Total Members . . . 3-23

Lesson 4: Creating Rules for Dynamic Accounts

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

Use Only

(5)

Module 2: Creating Procedures, Loops, and Arrays

Lesson 5: Creating Custom Procedures

Creating Custom Sub Procedures . . . 5-2 Calling Custom Sub Procedures . . . 5-4 Creating Custom Function Procedures . . . 5-6 Calling Custom Function Procedures . . . 5-8 Troubleshooting with Logging Procedures. . . 5-9 Writing to Log Files . . . 5-10 Calling Write to File Procedures . . . 5-14 Managing Log Files . . . 5-16 DeleteFile Function . . . 5-17 FileExists Function. . . 5-17 GetFile Function. . . 5-17 MoveFile Function . . . 5-18 Size Function . . . 5-18

Lesson 6: Creating Arrays and Loops

Arrays. . . 6-2 Loops . . . 6-3 Arrays and Loops in Rules. . . 6-4 Creating Array Variables . . . 6-6 Filling Arrays Using Member Lists . . . 6-7 Creating Loops. . . 6-8 For...Next . . . 6-8 For Each...Next . . . 6-9 Do...Loop . . . 6-10 Assigning Values with Arrays and Loops . . . 6-12 Attributes in Loops . . . 6-14 Loops and Data Units . . . 6-15 Opening Data Units . . . 6-16 Creating Loops With Data Units. . . 6-18

Module 3: Working with Data and Hierarchies

(6)

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

Lesson 8: Working with Dimension Hierarchies

Working with the Calendar. . . 8-2 Preventing Prior and Next from Crossing into Invalid Years . . . 8-3 Populating Days in Period Accounts . . . 8-5 Managing Calculation Status . . . 8-6 Managing Movement Accounts and Data Views . . . 8-8 Avoiding Circular Calculations in Hierarchies . . . 8-10 Creating Allocations Using Sub Allocate . . . 8-12 Running the Sub Allocate Procedure. . . 8-13 Using 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-2 Currency and Exchange Rate Settings . . . 9-3 Translation Methods . . . 9-5 Default Translation Process. . . 9-7 Creating Custom Translation Rules . . . 9-9 Translation Functions. . . .9-11 Calculating Exchange Differences . . . 9-13 Working with Currency Overrides . . . 9-15

Lesson 10: Creating Consolidation and Elimination Rules

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

Use Only

(7)

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 Objectives

After 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

(8)

Course Structure

Hyperion 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 Materials

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

Student Guide

The 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 Guide

The activity guide has two sections—exercises and exercise solutions.

Exercises

(9)

Conventions

The 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.

(10)

(11)

Overview

In 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

(12)

(13)

Objectives

At 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

(14)

About Rules

You use rules to perform calculations that you cannot definethrough 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.

About Rules

Calculate data that cannot be calculated through a hierarchical aggregation, such as ratios or variance analyses

Perform complex currency conversions and calculate exchange rate differences or other calculations necessary for consolidation

Prevent data entry for a specific cell

Perform allocations from a parent entity to a list of base entities Enable data entry to a parent entity

Perform custom consolidations for statutory reporting requirements Hyperion Financial Management rules provide powerful, customized calculations that you can use to perform the following tasks:

(15)

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.

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.

(16)

The following table lists the functions by object for Financial Management rules: Object Function HS ABSExp Alloc CalcStatus Clear Con Exp Dynamic GetCell GetCellNoData GetCellRealData GetCellType GetRate ImpactStatus Input NoInput NoRound OpenDataUnit ReviewStatus Round SetData SetDataWithPOV Trans TransPeriodic Account AccountType C1...4 Top IsBase IsChild IsConsolidated IsDescendant IsICP List NumBase NumChild NumDescendant PlugAccount SecurityClass

(17)

AppSettings Currency ICPWeight PVAForBalance PVAForFlow RateForBalance RateForFlow Currency Scale Custom1, Custom2, Custom3, and Custom4 IsBase IsDescendant List NumBase NumChild NumDescendant SecurityClass SwitchSign SwitchType UD1...3 DataUnit GetItem GetItemIds2 GetNumItems Entity AllowAdjs AllowAdjsFromChildren DefCurrency Holding IsBase IsChild IsDescendant IsICP List Member NumBase NumChild NumDescendant SecurityAsPartner SecurityClass UD1...3 ICP List Object Function

Use Only

(18)

Node Consol1...3 DOwn IsBase IsChild IsDescendant List Method NumBase NumChild NumDescendant PCon POwn Parent DefCurrency Holding IsBase IsChild IsDescendant IsICP List Member NumBase NumChild NumDescendant UD1...3 Period IsFirst IsLast List Member NumBase Number Scenario ConsolidateYTD DefaultFreq DefaultView List Member NumPeriods Object Function

Use Only

(19)

Value Currency IsTransCur IsTransCurAdj Member Year IsFirst IsLast Member Object Function

Use Only

(20)

Creating Rules Expressions

You 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 Function

You can create rules expressions to assign values to accounts: You use the HS.Exp function with an account expression to assign

values 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"

(21)

Use this syntax:

HS.Exp “DestinationValue = SourceValue”

The following example sets the Cash account to 50,000: HS.Exp "A#Cash = 50000”

(22)

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 Keywords

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 or hash sign (#), and

dimensions are separated by dots. For example:

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

(23)

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

Destination Expressions

The 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 Expressions

When 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

Use Only

(24)

Adding Operators and Functions

You 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:

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

(25)

Period and Year Keywords

To 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 Exp

If 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

(26)

Identifying Rule Types

Rules 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

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

Use Only

(27)

• 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.

(28)

Identifying Sub Procedures

A 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 Executed

Calculate() and Dynamic() When you calculate or consolidate data

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

Use Only

(29)

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

<All calculate rules are displayed here.> End Sub

Sub Dynamic()

<All dynamic rules are displayed here.> End Sub

Sub Translate()

<All translate rules are displayed here.> End Sub

Sub Allocate()

<All allocation rules are displayed here.> End Sub

Sub NoInput()

<All no input rules are displayed here.> End Sub

Sub Consolidate()

<All consolidate rules are displayed here.> End Sub

Sub Input

<All input rules are displayed here.> End Sub

Sub 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

(30)

Adding Comments to Rules

Documentation 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:

Adding Comments to Rules

You can insert comments in rules files to:

Document the purpose and results of procedures

Provide detail information for other administrators who may inherit rules files you created

Act 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

Use Only

(31)

Inserting Line Continuations

You 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.

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 the underscore. Line Continuation Syntax Concatenation

Use Only

(32)

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.

(33)

Creating Conditional Statements

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

Creating Conditional Statements

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

Select Case…Else

(34)

Applying If...Then...Else Statements

The 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

Applying the If…Then…Else Statement

The most common form of the If...Then...Elsestatement is the block form which allows for several lines of code to be executed or several

comparisons to be made, within the one statement.

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

Is executed only if the current member is Budget

If HS.Scenario.Member = "Budget" Then HS.EXP "A#Sales = A#UnitsSold * A#Price" ElseIf HS.Scenario.Member = "Actual" Then

HS.EXP "A#Price = A#Sales / A#UnitsSold" Else

HS.EXP "A#Sales = A#Sales.P#Prior * 1.1" End If

Uses ElseIf to test for more than one condition

(35)

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.

(36)

Making Decisions with Select Case Statements

The 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.

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.

(37)

Comparing Strings in Financial Management

String 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

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, RtnString

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

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

(38)

UCase Function

Returns 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 Function

Returns 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 Function

Returns 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.

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

(39)

Example

Dim Product1, RtnString

Product1 = "Financial Management" ' Define string RtnString = Left(Product1, 1) ' Returns "F"

RtnString = Left(Product1, 9) ' Returns "Financial"

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

Right Function

Returns 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.

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

Syntax

Right(String,Length)

Example

Dim Product1, RtnString

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

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

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

(40)

Summary

In 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

(41)

Objectives

At 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

(42)

About VB Script Variables

Variables are placeholders that temporarily store values when therules 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.

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.Number vCurYear=HS.Year.Member

(43)

Creating Variables and Assigning Values

You 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,

Creating Variables and Assigning Values

Declare variables explicitly using Dim statements Create variables on the fly

Enclose values in quotation marks to enter a literal text string

You can concatenate variables with literal text strings vAcc1=”A#NetSales”

vSalaryRate=300

pov_entity=HS.Entity.Member

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

(44)

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=300

pov_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.

(45)

Variables and Data Types

In 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 Description

CBool Converts nonzero values to TRUE and zero valuesto FALSE. CDate Converts an expression to a Date value.

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

Use Only

(46)

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.

(47)

Variables and Constants

Variables 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.

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"

(48)

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

(49)

Creating Header Sections for Variables and Constants

It 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

Creating Header Sections

for Variables and Constants

These are some typical types of information stored in header section variables:

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 False vIs_base = HS.Entity.IsBase("","") 'This yields a True or False pov_entity = HS.Entity.Member

pov_scenario = HS.Scenario.Member pov_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"

(50)

Point of View Variables

Information 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 Dimensions

Custom 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

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.

(51)

Global Accounts

You 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 Triggers

Financial 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 Then

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

(52)

These functions arefrequently 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.

IsBase 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..

(53)

Summary

In 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

(54)

(55)

Objectives

At 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

(56)

Subcubes and Data Retrieval

An 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.

Subcubes and Data Retrieval

267 Gross Sales of Fruit Soda in Jan

267 73 131 166 116 150 149 182 211 145 120 143 Products Diet Cola Root Beer Cream Soda Fruit Soda Period Jan Feb Mar Accounts Gross Sales Discounts Returns

Use Only

(57)

Financial Management Subcubes

The 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 members California ->2006 -> Actual -> Parent currency members

Financial Management Subcubes

Parent Currency California, Actual, 2006

Entity Currency Proportion

(58)

Subcube Dimensions

Each 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 ->

Subcube Dimensions

300 April Periodic [None] Retail Wood [None] [ICP None] NetSales April April April Period 25 Periodic [None] Retail Wood [None] [ICP None] Returns 25 Periodic [None] Retail Wood [None] [ICP None] Discount 350 Periodic [None] Retail Wood [None] [ICP None] GrossSales View C4 C3 C2 C1 ICP Account

Each subcube contains all members of the subcube dimensions. California, Actual, 2006, Entity Currency

(59)

Subcubes and Performance

The 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.

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.

Q1 Mar Feb Jan 35 25 60 200 200 400 360 20 20 40 200 150 350 310 160 60 Products 1050 300 Products 70 25 Hardware Returns 90 35 Software Sales Net Sales 600 450 910 200 Software 100 Hardware 240 Aggregations:

Hardware & Software to Products

Sales-Products & Returns-Products to Net Sales Net Sales Jan, Feb, Mar to Q1

(60)

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.

(61)

Subcubes and Rules

The 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

Subcubes and Rules

California Budget 2006 <Entity Currency>

307.5 April Periodic [None] Retail Wood [None] [ICP None] NetSales April April April Period 17.5 Periodic [None] Retail Wood [None] [ICP None] Returns 25 Periodic [None] Retail Wood [None] [ICP None] Discount 350 Periodic [None] Retail Wood [None] [ICP None] GrossSales View C4 C3 C2 C1 ICP Account

If HS.Entity.Member=“California” and HS.Scenario.Member=“Budget” Then

HS.Exp “A#Returns.C2#Wood.C3#Retail=A#GrossSales.C2#Wood.C3#Retail *1.05” End If

(62)

Bulk Assignment of Values

The 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 assignvalues 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.

(63)

Page Dimensions and HS.Exp

Data 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

Page Dimensions and HS.Exp

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

Variance ₌ Budget - Actual

(64)

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.

(65)

Managing the Scope of Rules

HS.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.

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”

Use Only

(66)

Omitting Dimensions

If 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).

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=

Use Only

(67)

Specifying the Destination Dimensions

If 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.

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 =

Use Only

(68)

Specifying Source Dimensions

If 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

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 =

Use Only

(69)

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.

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 C1: None C2: Top C3: Top C4: None

A#Profit A#RetainedInc Member Member Member Member Member Member C3: [None] C4: [None] =

Custom1 is valid for RetainedInc.

Custom2 and Custom3 are valid for Profit.

(70)

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.

(71)

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.

Mismatched Custom Dimensions (Example 2)

A#Sales A#Packaging C3: None C2: Prod1 C2: Prod3 C2: Prod2 C3: TotalMarkets C2: Prod1 C2: Prod3 C2: Prod2 C3: 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”

(72)

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

(73)

Executing Rules with the Value Dimension

The data input to the Entity Currency member of the Value dimension is potentially transformed several timesbefore 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.

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]

Data stored Calculated on the fly – data not stored

Sub Calculate Sub Calculate

Sub Calculate Sub Calculate Sub Translate Sub Calculate Sub Consolidate Sub Calculate Sub Consolidate Sub Calculate Sub Calculate

Use Only

(74)

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.