AX2660

Configuring conversions for a table

You can set up data conversions for a data table, such as to perform currency conversions. To do this, you enable the table for conversions using the Conversions tab of the table properties.

Once a table has been set up for conversions, then you can query converted data using GetData functions or Axiom queries. The data is converted on-the-fly when queried and does not reside in the database.

Before enabling conversions for a table, you must have already done the following:

  • Manually created the conversion table that stores the rates for the data conversions. For more information, see Conversion table requirements.

  • Created a sequence for the columns that you want to be able to query as converted data. This may be the same column sequence that you are already using for calculated fields. For more information, see Column sequences.

  • If you want to use a grouping column to define the "from" and/or "type" values for the conversion, then you must have already set up that column on the relevant reference table. If the column does not yet exist, you can configure a fixed value for now, and then edit the configuration once the column has been created.

The ability to configure conversions on a table is limited to administrators and to users with the following security permissions: either the global Administer Tables permission, or the Allow changing table structure permission for individual tables.

To configure conversions for a table:

  1. On the Axiom tab, in the Administration group, click Tables. In the Table Library on the menu, navigate to the table that you want to edit, and then select Edit table structure.

  2. In the Edit Table dialog, select the Data Conversion tab.

    This tab is only available if the table classification is Data, and the index scheme is not Large Table.

  3. Select the Configure Data Conversion check box.

    This enables the conversion options so that you can set up the conversion. Later, if you want to disable conversions for this table, you can clear the check box.

  4. From the Choose Table list, select the table that contains the rates for this conversion.

    This list is limited to tables that meet the structural requirements of conversion tables (four key columns).

  5. In the Conversion Table Columns section, map the key columns of the conversion table according to their purpose.

    Item Description

    From

    Select the column that contains the "from" or "source" codes for the conversion.

    To

    Select the column that contains the "to" or "target" codes for the conversion.

    Type

    Select the column that contains the type codes for the conversion.

    Scenario

    Select the column that contains the scenario codes for the conversion.

  6. In the Sequence Map section, select the associated sequences for the conversion.

    Item Description

    Conversion Table

    Select the sequence that contains the conversion rates in the specified conversion table. This is the table specified in the Choose Table field.

    Current Table

    Select the sequence in the current table on which you want to convert data.

    The sequences must contain the same number of literal columns or effective columns. "Effective columns" takes into account the start period of the sequence. For example, if the sequence has 6 columns but the start period is 7, the sequence has 12 effective columns because the sequence covers periods 7-12. When you query the "first" column of the data table sequence, the data will be converted using the rate in column 7 of the conversion table sequence.

  7. In the Conversion Value section, select one of the following to specify the "from" or "source" value for the conversion. The "from" value is configured up front and applies to all conversion queries made to this data table. The "to" value is specified when you make a query via a GetData function or an Axiom query.

    • From: Select this option if every record in the data table uses the same start value. Enter the appropriate "from" value in the box. The value must be present in the specified From column.

    • Use Lookup: Select this option if different records in the data table use different start values. Select the appropriate column that holds the "from" value assignments. This column must be located in a lookup reference table. When a conversion query is made to the table, the appropriate "from" value will be looked up from the specified column.

    For example, if all of the departments in this data table use United States dollars (USD) as the base currency, you can select From and then specify USD directly. However, imagine that some departments use USD as the base currency, but other departments use Canadian dollars (CAD). In this case you would set up a column on the DEPT table, such as DEPT.Currency, and assign each department to either USD or CAD. You would then select Use Lookup and specify the DEPT.Currency column.

  8. In the Conversion Type section, select one of the following to specify the conversion type.

    • Type: Select this option if every record in the data table uses the same conversion type. Enter the appropriate "type" value in the box. The value must be present in the specified Type column.

    • Use Lookup: Select this option if different records in the data table use different conversion types. Select the appropriate column that holds the type assignments. This column must be located in a lookup reference table. When a conversion query is made to the table, the appropriate type will be looked up from the specified column.

    For example, if all of the accounts in this data table use end-of-month (EOM) as the conversion type, you can select Type and then specify EOM directly. However, imagine that some accounts should be converted using EOM, but other accounts should be converted using an average rate (AVG). In this case you would set up a column on the ACCT table, such as ACCT.ConvType, and assign each account to either EOM or AVG. You would then select Use Lookup and specify the ACCT.ConvType column.

    NOTE: The conversion type can be overridden when querying data via an Axiom query. When using GetData functions, the type will always be the type specified here.

  9. In the Scenario box, enter the name of the scenario to use for the conversions. The value must be present in the specified Scenario column. This value is required, even if all of the records in the conversion table use the same scenario value.

    NOTE: The scenario can be overridden when querying data via an Axiom query. When using GetData functions, the scenario will always be the scenario specified here.

  10. Click Apply or OK to save the conversion configuration.

Users can now query converted data for this table.

Disabling data conversions

If you later want to disable the ability to query converted data for this table, edit the Conversions tab to clear the Configure Data Conversion check box. Keep in mind that doing this will clear all configured conversion settings.