Designing and Building Currency Conversion Applications

Organizations with offices in different countries generally do business in the currency of the host country (known as the local currency). World and regional headquarters of such organizations must convert data entered in local currencies to a common currency for consolidation and analysis. The Hyperion Essbase Currency Conversion option is designed to meet the needs of this complex business problem. This option can be licensed as an "add-on" to the Hyperion Essbase OLAP Server.

The Currency Conversion option includes a sample currency application that contains two databases: INTERNTL and XCHGRATE. This chapter provides background information, step-by-step instructions for building the sample currency application, and procedures for calculating and reporting currency conversions after you have built your own currency application.

This chapter contains the following topics:

Note:

Your Hyperion Essbase server installation includes the option to install the Sample databases, INTERNTL and XCHGRATE. If you don't have access to these databases, contact your Hyperion Essbase administrator. For information about installing sample applications, see the Hyperion Essbase Installation Notes.

Overview of the Business Problem

This chapter focuses on solving the business problems that arise as the sample company, The Beverage Company (TBC), expands its business outside the United States. This chapter builds on the business scenario introduced in Designing a Single-Server Application as TBC adds the following markets:

Three locations in Canada: Toronto, Vancouver, and Montreal
Four locations in Europe: the UK, Germany, France, and Spain

In addition, TBC adds a new member, US, which is a consolidation of data from the United States regions: East, West, South, and Central.

Data for each TBC market location is captured in local currency. Dollar values are derived by applying exchange rates to local values. These values must then be converted to a common currency (in this case, US$).

TBC needs to analyze actual data in two ways:

Actuals are converted at actual exchange rates.
Actuals are converted at budget exchange rates to analyze variances due to exchange rates.

When all actuals have been processed, budget data is converted with budget exchange rates.

Overview of the Currency Application's Structure

Currency conversion applications are defined by linking a currency database to a main application database, as illustrated in Figure 45-1. The Main Database contains the full database outline and associated data values; the Currency Database contains only exchange rates and other currency-related information. In the example provided in this chapter, TBC's Main Database is INTERNTL; its Currency Database is XCHGRATE. On your server, these databases are in the Sample application.

Figure 45-1: Currency Application Consists of Two Databases

Contents of the Main Database

The Main Database can be from 3 to n dimensions in size. At a minimum, the database must contain the following dimensions:

A dimension tagged as Time--In the TBC example this dimension is Year.
A dimension tagged as Accounts--Within an Accounts dimension, individual members may be tagged with different categories of exchange rates. This allows you to apply different exchange rates to various accounts or measures.
For example, P&L accounts may use exchange rates that differ from those used with balance sheet accounts. In addition, some accounts may not require conversion. For example, members such as Units, Headcount, and Margin% require no conversion.

A market-related dimension tagged as Country--In a market dimension, each member has an associated currency name. In the TBC example, the currency name US$ is defined for the Market dimension tagged Country, because all local currencies for the new markets must be converted to US$, the company's common currency.

Because many members can have the same currency name, the number of currency names is typically less than the total number of members in the dimension. As shown in Table 45-1, TBC uses only six currency names for its 15 Market dimension members. The children of the member Europe all use a different currency and, therefore, must be assigned individual currency names. However, in the case of the Country dimension and the US member and its four cities, all locations use the same currency. The same is true of the Canada member and its three cities. When the children of a given member share a single currency, you only need to define a currency name for the parent member.

*Table 45-1: TBC Currency Names*
Dimension/Member	Currency Name
Market - Country US East West South Central	US$
Canada Toronto Vancouver Montreal	CN$
Europe
UK	Pound
Germany	Mark
France	Franc
Spain	Peseta

A typical Main Database also contains an optional fourth dimension: "A dimension defined with a currency partition." Databases are usually partitioned in a Scenario dimension. Creating a currency partition allows Hyperion Essbase to internally track currency relationships and previously-converted values of the main and exchange rates databases.

Note:

A currency conversion partition applies only to the Currency Conversion option. It is not related to the Hyperion Essbase Partitioning option that enables data to be shared between databases using a replicated, linked, or transparent partition.

The Hyperion Essbase Spreadsheet Add-in User's Guide provides examples of ad hoc currency reporting capabilities. Report scripts also let you define reports that convert data when the report is displayed, as discussed under Converting Data to a Different Currency in Reports.

Contents of the Currency Database

The Currency Database requires the following three dimensions:

A Currency Time dimension--Exchange rates are usually defined for each time period. Each member of the time dimension in the Main Database must be defined in the Currency Database. Values by time period in the Main Database are usually converted to the exchange rates of their respective time period from the Currency Database (although you can convert data values against any period's exchange rate).
Currency Name dimension--This dimension contains the names of currencies relevant to the markets (or countries) defined in the Main Database. Each currency name defined in the Main Database must also exist in the Currency Database. The currency names define the country-to-exchange rate mapping when conversion occurs.
Currency Category dimension--This dimension contains all the account categories of the Main Database. The category names define the exchange rate mapping when a conversion occurs. In the TBC example, the account categories included are P&L (Profit & Loss) and B/S (Balance Sheet).

A currency database typically includes an optional fourth dimension: "A Currency Type dimension" which contains members that identify various currency scenario types. Typically an application has different exchange rates for scenarios, such as actual, budget, and forecast. Members of the currency-type dimension are not directly mapped to members of the Main Database. Therefore, member names in this dimension are not required to match member names of the Main Database.

Conversion Methodologies

Different currency applications have different conversion requirements. Hyperion Essbase supports two currency database types, each with a different conversion method:

A database containing only converted data values. Some applications require only converted values to be stored in the Main Database. Local values are entered and the conversion operation overwrites the local values with a common currency. This method assumes that there is no requirement for reporting or analyzing local currencies.
Because this operation overwrites data, you must load local values and recalculate the data each time you perform a conversion. This method is useful only when you want to perform a single (not ongoing) conversion.
A database containing both local and converted data. Most applications require data to be stored in both local and master values. This method permits reporting and analyzing local data. In addition, data modifications and recalculations are easier to control. To use this method, you must define a currency partition (see Modify the Scenario Dimension). The Beverage Company (TBC) uses this method for defining their currency application.

Either of these two methods may also require a currency conversion to be applied at report time. This allows you to analyze various exchange rate scenarios without actually storing data in the database. The currency conversion module allows you to perform ad hoc conversions with the Spreadsheet Add-in, which is discussed in the Hyperion Essbase Spreadsheet Add-in User's Guide, or with a report script, as discussed under Converting Data to a Different Currency in Reports.

Steps for Creating a Currency Conversion Application

To accommodate the new markets TBC has added and to provide Hyperion Essbase with required currency-related information, TBC has modified their existing Hyperion Essbase database outline and created a currency conversion application. You can use the TBC model to create your own currency conversion application, revising the steps as needed to fit your specific requirements.

The TBC currency conversion application was created using the following steps:

Creating the Main Database Outline

To create the Main Database outline (INTERNTL), you need to open your existing Hyperion Essbase database outline, make changes to its contents, and then save the outline for use in your currency conversion application.

Open the Existing Database Outline

Begin creating the Main Database outline by opening TBC's existing outline, as follows:

Start Hyperion Essbase Application Manager and select Server > Connect.
Choose the Hyperion Essbase Server that contains the Sample application.
Select Sample from the Applications list box and open the database outline for the database INTERNTL, as shown in Figure 45-2.
Figure 45-2: Sample Database INTERNTL Hyperion Essbase Outline

TBC has modified the Measures, Market, and Scenario dimensions. The Year and Product dimensions require no changes because they have no information specifically related to currencies.

Note:

The Year dimension must be tagged as the Time dimension.

Modify the Measures Dimension

To create their currency conversion Main Database, TBC modified the Measures dimension using the following steps. You can follow along and revise the procedures as necessary to create your own currency application.

Tag Measures as an Accounts dimension.
You must have a dimension tagged as Accounts in your Main Database. To meet varying conversion and exchange rate requirements, individual members within the Accounts dimension can then be tagged with different categories of exchange rates.

All descendants of the Profit member, for example, use a special currency rate that applies to Profit and Loss accounts. Therefore, they are defined with a currency category of P&L.

Define the Profit member with a currency category name, as follows:

Select the Profit member and click the Data Dictionary button, , on the toolbar. The Member Specification dialog box is displayed.

Select the Category option button from the Currency Conversion group, and enter the category name of P&L (for Profit and Loss).

Note:

Each descendant of a member inherits the currency category tag of its ancestor. A member or sub-branch of members can also have its own category defined.

Because the Measures dimension shows data for Profit, it must also have the P&L currency category name applied.

Define the Measures dimension with a currency category name, as follows:
1. Select Prev to move to the previous member in the dimension and select Category from the Currency Conversion group. The name P&L is displayed from the previous entry.
2. Click OK to close the dialog box.
  All descendants of the Inventory member use a special rate that applies to balance sheet accounts. Define them with a currency category of B/S (for Balance Sheet).
Define the Inventory member with a currency category name, as follows:
1. Select the Inventory member and click the Data Dictionary button, , on the toolbar. The Member Specification dialog box is displayed.
2. Select Category from the Currency Conversion group, and enter the category name of B/S.
3. Click OK to close the dialog box.
  Ratio members do not require a currency conversion, because the local and converted values yield the same result.
Define the Ratio member as not requiring a conversion:
1. Select the Ratios member, and press the Data Dictionary button, , on the toolbar. The Member Specification dialog box is displayed.
2. Select No Conversion from the Currency Conversion group.
  
  Note: The children of a member defined with No Conversion do not inherit the No Conversion definition. Each member must be defined individually.
Define the children of the Ratio member (Margin % and Profit %) as not requiring a conversion:
1. Select Next to move down the member list to Margin %.
2. Define Margin % with the No Conversion option.
3. Select Next to move down the member list to Profit %.
4. Define Profit % with the No Conversion option.
5. Click OK to close the dialog box.

Figure 45-3 shows the resulting Measures dimension outline after making the modifications necessary for the TBC currency conversion application.

Figure 45-3: TBC Measures Main Database Outline

Modify the Market Dimension

TBC needed to make the following modifications to the Market dimension to accommodate their newly added markets and create their currency conversion application Main Database. Procedures for making these changes follow the bullet list.

Tag the Market dimension as Country. Remember, having a Country tag enables you to define the common currency to be used for converting local currency values. In the TBC example, local currency values for their new markets are converted to US$, the currency name defined for the Country dimension.
Add the US member, which consolidates data from existing United States region members East, West, South, and Central.
Add the Canada member and its children, Toronto, Vancouver, and Montreal.
Add the Europe member and its children, UK, Germany, France, and Spain.
Define currency names for each of the newly added dimensions/members, as necessary. Remember, when the children of a given member share a single currency, you only need to define a currency name for the parent member.

Modify the Market dimension as follows:

Tag Market as a Country dimension by selecting the Market dimension and clicking Country, , on the toolbar.
The existing members (East, West, South, and Central) belong in TBC's newly added US market. To make this outline change, TBC added the US member with the existing members as descendants.
Add the new US member:
1. Select the Market member and click the Add Child button, .
2. Type the name US in the Member Edit box and press Enter twice.
Make existing members East, West, South, and Central, children of US by clicking the Add Child button for each member.
Add Canada and its respective cities to the Market dimension:
1. Select the US member and click the Add Sibling button, .
2. Type the name Canada in the edit box and press Enter twice.
3. Select the Canada member and click the Add Child button.
4. Add the Toronto, Vancouver, and Montreal members and press Enter twice.
Add Europe and its respective countries to the Market dimension:
1. Select the Canada member and press the Add Sibling button.
2. Type the name Europe in the edit box and press Enter twice. Select the Europe member and click the Add Child button.
3. Add the UK, Germany, France, and Spain members and press Enter twice.
  You must now define currency names for each appropriate member. Because TBC required the database to be converted to US dollars, they defined the Market dimension with the US$ currency name.
Define currency names for the Market dimension and its members, as appropriate:
1. Select the Market dimension and click the Data Dictionary button, . The Member Specification dialog box displays.
2. Enter US$ in the Currency Name type in box.
3. Click OK to close the dialog box.
  All descendants of the Market dimension inherit the US$ currency name (unless a child branch has already been defined with another currency). Because members in the US branch use US$ as their local currency, there is no need to define a currency name for these members.
  
  Like the Market dimension, all descendants of Canada also use the same currency (CN$). You need to define a currency name for the Canada member only and its children inherit the CN$ currency name.
Define a currency name for the Canada member:
1. Select Canada and click the Data Dictionary button, . The Member Specification dialog box is displayed.
2. Type CN$ in the Currency Name box.
3. Click OK to close the dialog box.
  The children of Europe all use different local currencies. Unlike the Market dimension and the Canada member, you must define each Europe member with an individual currency name.
Define individual currency names for children of the Europe member:
1. Select the UK member name and click the Data Dictionary button, . The Member Specification dialog box is displayed.
2. Type Pound in the Currency Name box.
3. Click Next to define the currency name for next country (Germany).
4. Type Mark in the Currency Name box.
5. Click Next to define the currency name for next country (France)
6. Type Franc in the Currency Name box.
7. Click Next to define the currency name for next country (Spain).
8. Type Peseta in the Currency Name box.
9. Click OK to close the dialog box.

Figure 45-4 shows the resulting Market dimension outline after making the modifications necessary for the TBC currency conversion application.

Figure 45-4: TBC Market Dimension Database Outline

Modify the Scenario Dimension

In the TBC Main Database (INTERNTL), the Scenario dimension contains members for both local and converted values. TBC needed to make the following modifications to the Scenario dimension to create their currency conversion application Main Database:

Create a currency partition. This enabled TBC to store both converted and non-converted values in their Main Database (INTERNTL).
Define the converted members under a Dollars parent. This enabled TBC to track actuals converted at actual exchange rates.
Add a new member called Actual @ Bud XChg. This enabled TBC to track actuals converted at separate budget exchange rates to analyze variances due to exchange rates of their newly added markets.
Define members that can store data in local currency values (Actual and Budget).
Apply the Exclude member from consolidation tag to the Local, Bud member, because it doesn't store data.
Tag the Local member as a label, because it doesn't store data.
Tag the Scenario dimension as a label, because it also does not store data.

Modify the Scenario dimension as follows:

Set a Currency Partition tag on the Scenario dimension:
1. Select the Scenario dimension name and click the CurPartition button, .
2. Define the converted members under a parent called Dollars.
3. Add the member Dollars as a child of Scenario. Select the dimension Scenario and press the Add Child button.
4. Type Dollars in the member edit box and press Enter twice.
5. Move the Actual, Budget, Variance, and Variance % members as children of Dollars.
  The Dollars member does not store data and must, therefore, be tagged as a label.
Tag the Dollars member as a label by selecting it and clicking the Label button, .
TBC also wants to track actuals using the budget exchange rate. The Scenario dimension requires a new member to handle this data.
Add a new member, Actual @ Bud XChg, to the Scenario dimension Actual member:
1. Select the Actual member and click the Add Sibling button.
2. Enter the member Actual @ Bud XChg and press Enter twice.
  The newly added Actual @ Bud XChg member does not consolidate into the Dollars member and must, therefore, be tagged appropriately.
Apply the "Exclude member from consolidation" tag to the Actual @ Bud XChg member by pressing the No Consolidation button, .
Define members that can store the data in local currency values:
1. Add the member Local as a sibling of Dollars. Select the member name Dollars and press the Add Child button.
2. Enter the name Local in the Member Edit box and press Enter twice.
  Data only needs to be captured in local currency for Actual and Budget. These are the only children to define for Local.
Define Local member children to capture local data:
1. Select the Local member and click the Add Child button.
2. Enter the member names Act and Bud and press Enter twice.
  Values for the Act member consolidate to Local, but values for the Bud member do not.
Apply the "Exclude member from consolidation" tag to the Bud member by pressing the No Consolidation button, .
Tag the Local member (which does not store data) as a Label by selecting it and clicking the Label button.
Tag the Scenario dimension (which does not contain any consolidated results) as a Label by selecting it and clicking the Label button.
Figure 45-5 shows the resulting Scenario dimension outline after making the modifications necessary for the TBC currency conversion application.

Figure 45-5: Scenario Dimension Database Outline

Save the Main Database Outline Changes

Save the outline changes by clicking the Save button. If your database contains data values, Hyperion Essbase restructures the database to reflect changes to the outline. When you click Save, the Restructure Database dialog box shown in Figure 45-6 is displayed.

Figure 45-6: Restructure Database Dialog Box

When an existing outline is updated, you have several choices that let you restructure data values. Because no data has been loaded into the model, click OK to continue the outline update.

Creating the Currency Database Outline

Once you have verified and saved the Main Database outline, you can generate the currency outline. The currency outline contains dimensions, members, currency names, and currency categories previously defined in your Main Database outline. It is basically structured and ready to use after being generated but may require additions to make it complete.

Generate the Currency Database Outline

Follow these steps to generate the Currency outline:

Press the Generate Currency Outline button, .
If the Main outline is missing any required dimension tags (such as Time, Accounts, or Country), Hyperion Essbase displays an error box like the one shown in Figure 45-7.

Figure 45-7: Error Dialog Box Displayed When No Country Dimension Is Defined
If the Error dialog box is displayed after you attempt to generate the Currency outline, do the following:
1. Note the errors and click OK.
2. Tag the dimensions as required.
3. Attempt to generate the Currency outline again. If all tags are correctly defined, the dialog box shown in Figure 45-8 displays.
  Figure 45-8: Generate Currency Outline Dialog Box
  
  The Generate Currency Outline dialog box lets you define the database for which the outline is generated. The dialog box contains the following controls:
  - The Server list box, which lets you select a Hyperion Essbase Server
  - The Application list box, which lets you select an application
  - The Database list box, which lets you select a database (only Currency Databases is displayed in the list)
  - The Connect button, which lets you log in to a different server if necessary
  - The Create DB button, which lets you create a new Currency Database
    If you need to log in to a different server, click the Connect button. The dialog box shown in Figure 45-9 is displayed. If you do not need to log in to a different server, go to Step 5.
  Figure 45-9: Hyperion Essbase System Login Dialog Box
Select the correct server, enter your user name and password, and click OK to close the dialog box and return to the Generate Currency Outline dialog box.
Click the Create DB button. The dialog box shown in Figure 45-10 is displayed.
Figure 45-10: Create Currency Database Dialog Box
Type the database name in the Database text box. In the TBC example, we use the database name XCHGRATE.
Click OK to create the database.
When the Generate Currency Outline dialog box is displayed again, click OK to generate the outline.
The outline for the currency database XCHGRATE is displayed in the Outline Editor as shown in Figure 45-11.

Figure 45-11: Currency Database Outline

Review the Contents of the Currency Database Outline

After generating your Currency Database outline, review its contents and note the following items that are automatically created based on the contents of the Main Database that you defined previously:

The Year dimension is replicated from the Main Database. This allows you to define exchange rates for each time period.
The currency category names that were defined on some members of the Measures dimension in the Main Database become members of the CurCategory dimension.
The currency names from the Market dimension become members of the CurName dimension.
The CurType dimension, which contains no members, is also created. This dimension is not directly mapped to the Main Database.

Add New Members to the CurType Dimension

After you have generated the Currency Database, you can add members to any dimension. Because the TBC Currency Database (XCHGRATE) contained different exchange rates for actual and budget, they added two new members to the CurType dimension to apply to different scenarios in the Main Database:

Add the Act xchg member to the CurType dimension.
Add the Bud xchg member to the CurType dimension.
Exchange rates from these two members are applied to different scenarios from the Main Database, as follows:
- Actual (from the Main Database) is converted with a CurType member called Act xchg.
- Actual @ Bud Xchg (from the Main Database) is converted with a CurType member called Bud xchg.
- Budget (from the Main Database) is converted with a CurType member called Bud xchg.
  
  Note: For details about the currency conversion calculation process, see Calculating Currency Conversions.

Save the Currency Database Outline Changes

When you have reviewed the newly generated Currency Database outline and have made any necessary additions, you need to save your changes.

Save changes made to the Currency Database outline by pressing the Save button. The dialog box shown in Figure 45-12 is displayed.
Figure 45-12: Using the Latest Version on the Server Warning

Because the newly-generated outline contains all new members, you are given a choice of continuing or canceling the operation.
Click Yes to continue. The Restructure Database dialog box shown in Figure 45-13 is displayed.
Figure 45-13: Restructure Database Dialog Box

When an existing outline is updated, you have several choices that let you restructure data values.
Because no data has been loaded into the model, click OK to continue the outline update.
Close the Outline Editor for the Main and Currency Databases.

Linking the Main and Currency Databases

To perform a currency conversion calculation, Hyperion Essbase must recognize a link between the Main and Currency databases. Generating a currency outline does not automatically link a Main Database with a Currency Database.

To link a Currency Database to the Main Database:

Select the Main Database from the application's database list.
Choose Database > Settings. The General page of the Database Settings dialog box shown in Figure 45-14 is displayed.
Figure 45-14: Database Settings Dialog Box General Page
Select the Currency page. The Currency page shown in Figure 45-15 is displayed.
Figure 45-15: Database Settings Dialog Box Currency Page
Select a database from the Currency Database list box.
The Multiply and Divide options are enabled. Use these option buttons to define the conversion calculation method to be used. The Multiply option button multiplies each local data value by the exchange rate. The Divide option button divides each local data value by the exchange rate.
Select the Multiply option.
The Default Currency Type Member text box lets you define a currency type member to use as a default for currency conversion calculations.
Type Act xchg to assign this as the default.
Click OK twice to close the dialog box and save these settings.

After the databases are defined and linked, the next step is to load data into the Main Database and exchange rates into the Currency Database. After values have been entered, a currency conversion is calculated. This is accomplished by running a calc script (see Calculating Currency Conversions).

Calculating Currency Conversions

You convert data values from a local currency to a common, converted currency using the CCONV currExchMbr command in a calc script. For example, you might convert data from a variety of European currencies into US$.

Note:

You cannot use the CCONV command to convert data in a transparent partition.

You convert the data values back to the original, local currencies using the CCONV TOLOCALRATE CurType command.

You can convert all or part of your main database using the rates defined in your Currency database. You can overwrite the local values with the converted values, or you can keep both the local and converted values in your main database, depending on your tracking and reporting needs.

Overwriting Local Values with Converted Values

If you want to overwrite the local values, you do not need to create a CURPARTITION dimension in your main database. Use the CCONV command in a calc script to convert all the data in your database.

The following calc script converts the values in the database to US$.

CCONV US$;
CALC ALL;

If required, you can specify a currency name that contains the required exchange rate. The following calc script converts the values in the database to US$, using the exchange rate for Jan as defined in the Currency database.

CCONV Jan->US$;
CALC ALL;

The CALC ALL command is required in the examples shown, because the CCONV command only converts currencies. It does not consolidate or calculate members in your database.

The following calc script converts the values back to their original values in their local currencies using the "Act xchg" rate:

CCONV TOLOCALRATE "Act xchg";
CALC ALL;

For more information on the CCONV command, see the online Technical Reference in the DOCS directory.

Note: You cannot use the FIX command unless you are using a CURPARTITION dimension and the CCTRACK setting is TRUE in the ESSBASE.CFG file.

Keeping Local and Converted Values

You can keep both local and converted values in your database. In your main database you need to define the members that store the local and converted values. You do this by creating a CurPartition dimension (see Modify the Scenario Dimension). The CurPartition dimension has two partitions, one for local values and one for converted values.

You then need to create a calc script to complete the following steps:

Use the DATACOPY command to copy data from the local to the converted partition.
Use the FIX command to calculate only the converted partition and use the CCONV command to convert the data.

Note: When using a CurPartition dimension, you must FIX on a member of this dimension to use the CCONV command.
Use the CALC command to recalculate your database.

The following example is based on the Sample INTERNTL database and corresponding Sample XCHGRATE Currency database. Figure 45-16 shows the Currency Partition from the Sample INTERNTL database.

Figure 45-16: Calculating Local and Converted Currency Conversions

The following calc script performs three currency conversions for Actual, Budget and Actual @ Bud Xchg data values.

/* Copy data from the local partition to the master partition 
(for converted values) */
DATACOPY Act TO Actual;
DATACOPY Bud TO Budget;

/* Convert the Actual data values using the "Act xchg" rate */

FIX(Actual)
     CCONV "Act xchg"->US$;
ENDFIX

/* Convert the Budget data values using the "Bud xchg" rate */
FIX(Budget)
     CCONV "Bud xchg"->US$;
ENDFIX

/* Convert the "Actual @ Bud XChg" data values using the "Bud 
xchg" rate */
FIX("Actual @ Bud XChg")
     CCONV "Bud xchg"->US$;
ENDFIX

/* Recalculate the database */
CALC ALL;
CALC TWOPASS;

The following calc script converts the Actual and Budget values back to their original values in their local currencies:

FIX(Actual)
CCONV TOLOCALRATE "Act xchg";
ENDFIX
FIX(Budget)
CCONV TOLOCALRATE "Bud xchg";
ENDFIX
CALC ALL;

Note:

When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty for the purposes of Intelligent Calculation. This means that Hyperion Essbase recalculates all the converted blocks when you recalculate your database. For more information on Intelligent Calculation, see Using Intelligent Calculation to Optimize Calculation.

Converting Data to a Different Currency in Reports

You can calculate currency conversions in report scripts, using the CURRENCY target currency command to set the output currency and currency type. For the syntax and definitions of Report Writer commands, see the online Technical Reference in the DOCS directory.

Note:

Hyperion Essbase cannot perform "on the fly" currency conversions across transparent databases. If you have two transparent partition databases that are calculated using different conversions, you cannot calculate currency conversions in reports.

The following Sample report contains first quarter Budget Sales for colas, using the January exchange rate for the Peseta currency.

Illinois Sales Budget Jan Feb Mar ======== ======== ======== 100-10 3 3 3 100-20 2 2 2 100-30 #Missing #Missing #Missing 100 5 5 5 Currency: Jan->Peseta->Act xchg Currency: Jan->Peseta->Act xchg Illinois Sales Budget Jan Feb Mar ======== ======== ======== 100-10 3 3 3 100-20 2 2 2 100-30 #Missing #Missing #Missing 100 5 5 5

Illinois Sales Budget Jan Feb Mar ======== ======== ======== 100-10 3 3 3 100-20 2 2 2 100-30 #Missing #Missing #Missing 100 5 5 5 Currency: Jan->Peseta->Act xchg Currency: Jan->Peseta->Act xchg Illinois Sales Budget Jan Feb Mar ======== ======== ======== 100-10 3 3 3 100-20 2 2 2 100-30 #Missing #Missing #Missing 100 5 5 5

Use the following script to create the Sample currency conversion report:

<Page (Market, Measures, Scenario)
{SupCurHeading}
Illinois Sales Budget
       <Column (Year)
       <children Qtr1
<Currency "Jan->Peseta->Act xchg"
<Ichildren Colas
   !
{CurHeading}
Illinois Sales Budget
       <Column (Year)
       <children Qtr1
   !

Effects of CCTRACK Parameter on Conversion Calculations

The CCTRACK setting in your ESSBASE.CFG file controls whether exchange rates are tracked while Hyperion Essbase calculates currency conversions.

When CCTRACK is True, Hyperion Essbase tracks exchange rates that are applied to data as conversions are calculated, allowing conversion to occur at report time through the Spreadsheet Add-in or the Hyperion Essbase Report Writer.

Setting CCTRACK to False turns off the tracking system and has the following results:

A CCONV command assumes the data to be converted is always in local currency.
Currency report options assume that the data to be converted is always in local currency. This means that even if the data has already been converted in the database, it is reconverted at report time and can yield inaccurate results.
The restrictions on operations such as FIX or DATACOPY are removed for conversion operations. For example, when you have a currency partition and CCTRACK is set to True, you can't use the CCONV command outside of a FIX. Conversely, when you do not have a currency partition and CCTRACK is set to TRUE, Hyperion Essbase does not allow you to calculate without a FIX. Setting CCTRACK to False eliminates these restrictions.

For more information, refer to online Technical Reference in the DOCS directory.