Additional Calculations

---

With version 5.1, BusinessObjects introduced two new calculations via the pull-down menus, Variance and Euro. Variance calculations are also available via the Report toolbar. Neither is available via the Slice and Dice Panel. Users could previously accomplish these calculations only via custom formulas that you would need to create for each detail row and break level.

Whereas the calculations in the Slice and Dice Panel deal with aggregates that appear as subtotals in a footer, variances are additional rows or columns in a table or crosstab. Euro conversions replace values in an existing column.

Variance

Variance calculations calculate either the absolute difference or the percentage difference between two dimension values; they will not allow you to calculate the difference between two measure columns. In this respect, your ability to use these built-in calculations will very much depend on how the universe and/or data mart tables are structured. If the measure objects contain built-in time period intelligence such as Current Year Sales, Last Year Sales, then you are not able to use the Variance calculations. Instead, the universe designer should provide variance objects as part of the universe or you will need to create a custom formula. However, if your universe contains objects such as Sales and Time, in which Time contains rows for current year and last year, then you may use the built-in Variance calculations.

Variance calculations are not limited to time periods; you can create the variance in profitability between two stores, sales between two products, salaries between two employees, and so on, as shown in the next table. In order to insert a variance calculation, you select the dimension values you wish to compare. The variance is inserted for all measures within a report. You can insert a variance calculation into a table block, in which case BusinessObjects will insert a new row in the table; however, you may find the variance analysis easier to read in a crosstab report.

If you want to know the variance for a measure such as:	Then select these dimension values:
Sales between two quarters	Q1, Q2
Salary between genders	Male, Female
Profitability between stores	Store1, Store2
Sales between products	Product1, Product2
Expenses between scenarios	Actual, Budget

The next example uses the same crosstab report shown in Figure 18-7. You can access the Variance calculations via the pull-down menu or the Report toolbar. To enable the Report toolbar, select View | Toolbars, then choose Report.

Figure 18-7: Euro Exchange Rate table

Variance Difference

In the following example, you will calculate the difference in sales between two quarters:

1. Within the table, select the dimension values you wish to compare. In this example, Q1, Q2. Use CTRL-click to select multiple cells. If you are working with a crosstab report, these may be the column headings, as shown on the left or the individual rows, as shown on the right.

   

2. From the pull-down menu, select Data | Calculations | a-b Variance or click a-b from the Report toolbar. If you have not properly selected two and only two dimension values, these menu options and buttons will remain grayed out.  

3. BusinessObjects inserts a new column labeled Q1-Q2. If you inserted the variance into a table block, then a new row labeled Q1-Q2 is inserted in the Quarter column.

   

Within the body of the block, Variance inserts the following formula:

=<Sales revenue> Where (<Quarter>="Q1") - <Sales revenue> Where (<Quarter>="Q2")

Within the footer of the block, variance creates the following formula. Note that this calculation contains the aggregate SUM function:

=(Sum(<Sales revenue>)) Where (<Quarter>="Q1") - (Sum(<Sales revenue>)) Where (<Quarter>="Q2")

Percentage Variance

To insert a percentage variance:

1. Within the table, select the dimension values you wish to compare.

2. From the pull-down menu, select Data | Calculations | %a-b Variance or click %a-b from the Report toolbar. If you have not properly selected two and only two dimension values, these menu options and buttons will remain grayed out.

3. In a crosstab report, BusinessObjects inserts a new column labeled Q1-Q2. If the table contains both variance differences and percentage variances, relabel this column %Q1-Q2. You can either double-click the column heading and insert a % symbol or, if the Formula toolbar is displayed, select the column heading and insert a % in the formula toolbar.

   

The % Variance calculation inserts the following formulas in the body and footer of the block, respectively:

=(<Sales revenue> Where (<Quarter>="Q1") <Sales revenue> Where (<Quarter>="Q2")) / (<Sales revenue> Where (<Quarter>="Q2")) =((Sum(<Sales revenue>)) Where (<Quarter>="Q1")  - (Sum(<Sales revenue>)) Where (<Quarter>="Q2"))  / ((Sum(<Sales revenue>)) Where (<Quarter>="Q2"))

Euro

Although the Euro has been an active trading currency for many years, the European Union fixed the exchange rates for all participating countries January 1, 1999. The Euro coin and notes went into circulation later, on January 1, 2002. Not all members of the European Union agreed to participate in the Euro currency; for example, Greece only began participating in mid-2001, and Great Britain still uses pounds as the official currency. If you are accustomed to seeing measures stated in a local currency, it may not be easy to identify trends or fluctuations when viewing data in the relatively new Euro. Because the exchange rates for Euros are fixed, BusinessObjects provides you with an exchange rate table. You can use this table to add new currencies. You also can use it to convert from other foreign currencies to the Euro; however, this exchange rate will be valid only for a particular time period.

Exchange Rate Table

The internal exchange rate table (Figure 18-7) is always stated in terms of 1 Euro to a foreign currency. To display the exchange rate table, select Data | Euro | Display Conversion Rates. These rates are fixed for countries that participate in the Euro currency. You also can add other exchange rates, such as U.S. Dollars, but they refer to a rate for a particular time period. BusinessObjects does not provide a way of storing rates for various periods, although you could create a formula to do so. The rate for 1 Euro to U.S. Dollars is 1.04724 as of January 6, 2003.

In Figure 18-7, the Euro to Greek Drachma conversion is missing, as Greece joined the currency participation at a later date. To add Greek Drachmas to the conversion table, select Add from the Conversion Rate table. Enter the following information:

• Currency The three-digit trading symbol, GRD.

• Rate Either the rate at which the Euro is fixed for participating countries or the exchange rate for 1 Euro that applies to a particular time period for nonparticipating countries. The rate for Drachmas is fixed at 340.75.

• Decimals The number of decimal places that you want to display when you convert a column in a report to Euros.

• Format The currency symbol to display for the values or an abbreviation.

• Label A long description for the currency.

  

Click OK to close the Add Conversion Rate dialog box. The new rate is displayed in the Conversion Rate table. Click OK to accept the new rate. This rate is now available in all reports and documents.

Converting to/from Euros

You can convert an existing measure column to Euros, or if the measure column is stated in Euros, you can convert the values to a local currency. When you convert a column to or from Euros, the values in the column are replaced. Therefore, if you want to display both the Euro value and the local currency value, first insert a new column and then convert. In the following table, you will convert Wine prices that are stated in U.S. Dollars to Euros and then to French Francs:

1. From within the Report window, select the measure column you wish to convert. In this example, Unit Price.

2. From the pull-down menu, select Insert | Column. When prompted, select Insert Column to the Right of the Selection and click OK. Alternatively, you can display the Structure toolbar (View | Toolbars, then select Structure) and select the Insert Column After button.  

3. Copy the Unit Price column to the newly created blank column by selecting Copy and Paste from the Standard toolbar.

4. You should now have two columns that display the same values. Ensure that the second column is still selected. Select Data | Euro | Convert to Euros.

   

5. BusinessObjects prompts you to Select A Currency. Click the drop-down arrow to select a currency and click OK.

   

6. Enter a column heading to reflect the correct currency name: Euro Unit Price.

Converting from Euros

If your original measure is displayed in Euros, then you can use Data | Euro | Convert From Euros to another currency in the exchange rate table. In some cases, however, BusinessObjects may not prompt you to select a target currency. In this case, you need to create a formula to finish the conversion. The following formula first converts a Unit Price in dollars to Euros, then to French Francs:

=(EuroConvertFrom(EuroConvertTo(<Unit Price> ,"USD" ,2) ,"FRF" ,2))

---