AX2060
You can apply data conversion to an Axiom query, so that data returned from the query is converted according to the specified conversion target. For example, if a particular table stores data in U.S. dollars, you can convert the data as part of the query and return the values in Canadian dollars.
The table that you are querying must already be configured to support data conversions.
Setting up an Axiom query for data conversions
To convert data in an Axiom query, complete the following settings for the query on the Control Sheet:
| Field | Description |
|---|---|
|
Conversion Target |
Enter the target for the conversion. For example, if the "source" data in the table is U.S. dollars and you want to convert the data to Canadian dollars, you would enter CAD as the conversion target. You can enter a fixed value that will apply to all records, or you can look up the value on a record by record basis from a grouping column in a lookup reference table. The fully qualified name must be used—for example, NOTE: If you use a grouping column, it is recommended to also include the grouping column in the field definition for the query, to identify the conversion that was applied for each individual record. In either case, the conversion target must be a value that is found in the "to" column for the associated conversion table. The conversion table is determined on a column by column basis (for each data column entry in the field definition), using the conversion configuration for the associated table. |
|
Conversion Scenario Override |
If desired, specify a conversion scenario to override the default scenario for the data conversion. The entry must be a value that is found in the "scenario" column for the conversion table. |
|
Conversion Type Override |
If desired, specify a conversion type to override the default type for the data conversion. The entry must be a value that is found in the "type" column for the conversion table. |
If an Axiom query is configured for data conversions, then at least one of the data tables in the query must be enabled for data conversions, or else the query is invalid and an error will result. If multiple tables are being queried, and some are enabled for conversions and some are not, the conversion settings will only apply to the tables that are enabled for conversions. Tables that are not enabled for conversions will return their data as is.
If the conversion target or either of the overrides are invalid, then zero values are returned. Data conversions will also return zero values if the conversion configuration for the table has invalid values, or if the conversion table itself has no valid rate entry for the relevant conversion and period (for example, blank entries in a rate column).
Multiple data tables and data conversions
Special considerations apply if you want to use data conversions with an Axiom query that queries multiple data tables.
Any data table that is configured for data conversions will be converted when the query is run. Axiom Software evaluates the data conversion on a column by column basis (for each data column in the field definition) and applies the unique conversion configuration for each associated table. So each table in the query can be associated with different conversion tables and can use different conversion settings, such as the default conversion type and scenario. However, every data table to be converted must use the same designated conversion target.
Example
Imagine that you want to query column CYA1 of the GL2019 table and column LYA1 of the GL2018 table, and you want to convert all of the data from USD to CAD. Because the two tables cover different years of data, they likely use different conversion tables to allow for the different currency conversion rates for each year. Axiom Software will honor the conversion configuration for each individual table, and will apply the appropriate rates to the conversion. However, both conversion tables must have a "to" entry of CAD, because each Axiom query can have only one conversion target that applies to the entire query.
If one of the tables did not contain the "to" entry of CAD, the columns for that table would return zero values in the Axiom query. Whenever a conversion setting is invalid (such as pointing to a "to" entry that does not exist in the conversion table), the result is zero values.
Similarly, if you specify a scenario override or a type override, the specified override value must exist in all of the associated conversion tables for each data table in the query.
You can set up an Axiom query that includes converted tables and non-converted tables. If a table is not enabled for data conversions, then the conversion settings are ignored and data is returned "as is" for those tables. However, if a table is enabled for data conversions, then it will always be converted if the Axiom query conversion settings are used. You cannot selectively apply conversions on a table by table or column by column basis within a single Axiom query.
If you want to query converted and non-converted data from tables that are enabled for conversions (for example, to show USD and CAD values side-by-side, for the same period), then you can do one of the following:
- Use GetData functions instead of an Axiom query. Each individual GetData function can be flagged to convert or not to convert.
- Use two parallel Axiom queries, with one query configured to convert and one not to convert. For example, the first query would be set to "rebuild" and would populate the report with the desired data records and the unconverted values. The second query would be set to "update" and would update each record to add the converted values.
AX2060
