Creating MS Excel Reports in Project Currencies

By default, currency values included in MS Excel reports are shown in the corporate currency. However, some projects may track their values in a local currency that is different than the corporate currency. To convert values into the project currency, use the Currency Report Template <version>.xltm base template available on the Base Templates Reference Page, or in the Template Library, as a starting point to build the report.

Note:  The information in this topic details how to create MS Excel reports if your currencies are defined using the general currency conversions and Corporate Currency system parameter method. If you use the recommended date-specific currency conversions method, you can create a report in a project currency using calculations based on the metrics defined to capture the project's currently selected currency. Before creating a report that uses a project's currency, define the currency conversions. See Defining the Currencies Used in Your Company for more information.

To add a currency conversion to a project report template, add a conversion table to a worksheet within the file and set cross references from the report cells to the conversions. You can also select the format in which currencies display.

Note:  An Administrator or Process Designer must also have All Rights to Reporting for access to create the Accolade Office Extensions report using the currency conversions reference table.

To create an MS Excel report in a project's currency:

Note:  The procedure below assumes that you have Accolade Office Extensions installed.

1. Save the Currency Report Template <version>.xltm file to your computer and open the file in Excel.
2. Within Accolade Office Extensions, click Data Reports and click Add New to add a report.
3. Select the Reference Tables subject.
4. Select Currency Conversions from the Available Reference Tables list, and select to include the Currency Code and Conversion Factor columns in the table.

These two columns enable you to select the correct conversion factor to convert the corporate currency values in the database into the project currency values you want to display in the report. You can select additional columns that may be helpful in identifying the currencies available.

5. Continue through the dialog options, select the worksheet and provide a table name that identifies the table as the currency conversion table, and click Finish to add the table.

Conversion tables are typically included on a separate worksheet so you can hide the worksheet before adding the report template to the Template Library.

6. Create the rest of the report, including the currency metrics to convert.
7. In the cell where you want to convert a currency value, enter the following formula:

=ConvertToCurrency(cell, "code")

where

• cell is the reference to cell containing the value to convert
• code is the three-digit currency code of the currency to convert to. The code must be contained in quotes.

For example, =ConvertToCurrency(A2, "JPY") to convert the currency from the corporate currency to Japanese Yen.

To format the value, see the procedure below.

8. Copy the formula down the right side of the table to every row containing values to be converted to the project currency and hide the column that contains the values to be converted.

Adding the formulas to the column to the right of the column containing the corporate currency values leaves the column of converted values in the original position of the now hidden column

9. Save the file with a name that clearly identifies the nature and purpose of the report.
10. Add the file to the Template Library.

To format a currency value in an MS Excel report:

1. Open a report that contains a currency conversion table (as created in the procedure above).
2. Open the Accolade menu in the MS Excel ribbon, select the conversion table you created in the procedure above and click Modify.
3. Select to include Format 1 and Format 2 and click Finish.
4. In the cells that contain the =ConvertToCurrency(cell, "code") formula (see step 6 above), add the format column heading parameter.

=ConvertToCurrency(cell, "code", "format column heading")

This parameter is either Format1 or Format2 in the Currency Conversion table you modified in step 3 that contains the numeric format to display.

5. Save the file and add it to the Template Library.