AX2059

Using calc method libraries with Axiom queries

When you are creating an Axiom query for use in a plan file (or any other file that supports use of calc method libraries), you can use the sheet's calc method library to apply formatting and formulas to the data rows, instead of using an in-sheet calc method. This approach is typically used to build out the planning rows of plan files. Users can then complete data inputs, add rows as necessary, and even change the calc method used on a row if desired.

To use an Axiom query in a plan file, you first create it in the template. Typically the query would be configured so that when plan files are built from templates, Axiom Software queries data for the applicable plan code, applies calc methods from the library, and populates the file with data. Usually the template itself does not contain many "fixed" rows of data, so the Axiom queries are used to dynamically insert the accounts or employees relative to the particular plan code for each plan file.

When using a calc method library, each record of data can be assigned a different calc method. For example, some accounts might use an "Input Monthly" calc method for budgeting, while others might use a "Spread Total" calc method. Different formatting and calculations are applied to different accounts as needed.

Each Axiom query in a template can use different calc method settings. For example, you could have one Axiom query that uses an in-sheet calc method to populate the top section of the sheet, and another Axiom query that uses the calc method library to populate the bottom section of the sheet.

The following requirements apply to using calc method libraries with Axiom queries:

• Calc method libraries only apply to templates and plan files. Report files and driver files do not have access to a calc method library, and therefore must use an in-sheet calc method for Axiom queries.
• Calc method libraries can only be used with standard, vertical Axiom queries. Calc method libraries are row-based, and therefore cannot be used with horizontal Axiom queries. If you want to use a horizontal Axiom query in a template or a plan file, then you must use an in-sheet calc method with that query.

IMPORTANT: In order to use a calc method library, the in-sheet calc method setting for the Axiom query must be blank. If an in-sheet calc method is specified for the Axiom query, then that setting will take priority over any other calc method settings for the query.

Calc method assignment options

In order to use a calc method library with an Axiom query, Axiom Software must have a way of determining which calc method to apply to each record in the query. There are several ways to configure this lookup.

• Look up assignments from a database table: You can configure the Axiom query to look up calc method assignments from a table in the database. There are two ways to do this:
  • Single assignment column: The simplest and most common approach is to set up a single assignment column in the appropriate database table (for example, the ACCT table), and then point the Axiom query to that column to look up the assignments.
  • Multiple assignment columns by plan code groupings: If calc method assignments need to vary by a plan code grouping, such as the type of department, then you can use multiple assignment columns. In this case, you create multiple assignment columns in the appropriate database table (such as the ACCT table). Then in the plan code table, you create a "master" assignment column that maps each plan code to its appropriate assignment column in the ACCT table.
• Read assignments from a sheet: You can configure the Axiom query to read calc method assignments from a sheet in the file, instead of (or in addition to) looking them up from a database column. This approach requires the most manual setup, but it is the most flexible and can be used for virtually any circumstance.
• Default calc method: You can use the default calc method on its own, or in conjunction with the other options. If a calc method's assignment is left blank using any of the options described above, then the default calc method will be used for that record. If desired, you can specify only the default calc method (leaving all other calc method options blank), and then the default calc method will be used for all records in the query.

Multiple calc method assignment options can be used in the same query. For example, you can use the sheet option, the database table option, and the default calc method option together. For each record of data in the query, the query first checks the sheet, then checks the database column, and lastly uses the default calc method.

All calc method library assignment options are specified on the Control Sheet for the Axiom query. You must edit the Control Sheet directly in order to use the calc method library, because calc method library settings do not display in the Sheet Assistant. The options are located in the Vertical Configuration section of the Axiom query, in the Calculation Methods subsection. To use any of these options, the In-Sheet Calc Method Row(s) setting must be left blank.

Using a single assignment column

To use a single calc method assignment column for an Axiom query:

1. In the appropriate database table, create a column to contain the calc method assignments for each item in the table.

   For example, if the Axiom query is being populated with accounts, you would create a column in the ACCT table and then enter valid calc method names into that column. The column can be named anything you like (for example, AssignCM).

   You can use the same assignment column for multiple Axiom queries if appropriate.

   NOTE: If all records in the table need to use the same calc method for this Axiom query, then you do not need to create an assignment column; you can use the default calc method setting instead.

2. Complete the following settings in the Control Sheet for the Axiom query. These settings are located in the Vertical Configuration section of the query, under the heading Calculation Methods.

   Item	Description
   Assign from data field	Enter the name of the column that contains the relevant calc method assignments for this Axiom query, using fully qualified Table.Column syntax. For example, ACCT.AssignCM. When data records are inserted into a data range, Axiom Software looks to this column to determine which calc method to use for each record.
   Default Calc Method	Optional. Enter the name of a calc method to be used if an entry in the assignment column is blank. If an entry in the assignment column is invalid, then the default calc method is not used; an error results instead.

The following screenshot shows an example configuration using a single calc method assignment column:

Using multiple assignment columns by plan code type

The basic implementation of the calc method assignment column assumes that all plan codes (for example, departments) use the same calc method for any single account. If necessary, you can configure the Axiom query to use different assignment columns depending on the type of plan code.

For example, most plan codes might plan for supplies by inputting a total annual value that is spread evenly over months. But for other plan codes, it might be more appropriate to calculate supplies based on a monthly statistic. You can configure the Axiom query so that all plan codes in the first category use a Spread Total calc method for the supplies account, whereas the plan codes in the second category use a Monthly Statistic calc method.

To use different calc method assignments by plan code:

1. Create multiple assignment columns in the appropriate table (for example, ACCT), and complete the columns with the calc method assignments appropriate for a particular type of plan code. The columns can be named anything you like.

   For example, you might have one column that contains the assignments for revenue departments (CMRevenue), and one column that contains the assignments for overhead departments (CMOverhead).

2. In the plan code table (for example, DEPT), create a "master" assignment column. The column can be named anything you like (for example, MasterAssignCM). In this column, for each department, enter the name of the appropriate assignment column from the ACCT table.

   For example, if the department is a revenue department, enter CMRevenue into the MasterAssignCM column.

   Every code in the plan code table must have an entry in the master assignment column (unless you are not creating a plan file for that code, or you know that the plan file for that code does not use this Axiom query).

3. In the Control Sheet settings for the Axiom query, in the Assign from data field box, enter the column name for the master assignment column, using fully qualified Table.Column syntax. Then, in parentheses, enter the name of the table containing the detailed assignment columns. The syntax is as follows:

   MasterTable.MasterAssignCM(DetailTable)

   For example, if the master column is in the DEPT table and is named MasterAssignCM, and the detailed columns are in the ACCT table, then you would enter the following:

   DEPT.MasterAssignCM(ACCT)

   When data records are to be inserted into a data range, Axiom Software first looks to the MasterAssignCM column in the DEPT table, and finds the entry for the current department. Axiom Software then finds the specified column in the ACCT table and uses that column to assign calc methods to accounts.

You can optionally complete the Default Calc Method setting. If an entry in the assignment column is blank for a particular account, the default calc method will be used. If an entry in the assignment column is invalid, then the default calc method is not used; an error results instead.

Reading calc method assignments from a sheet

Using this option, you set up a sheet in the file to contain the calc method assignments, and then you configure the Axiom query to read the assignments from the sheet.

You can use any methodology to create the list of assignments in the sheet. You can manually type in a list of assignments, or you can use an Axiom query to bring in a list that is stored somewhere in the database. If you set up the Axiom query to be dynamic based on criteria such as the current plan code, you can retrieve a different list of assignments for each plan code. This is more flexible than pointing the Axiom query at the database directly using the Assign from data field option, because you can store the assignments anywhere in the database, and use any criteria to retrieve them.

To read calc method assignments from a sheet:

1. Set up a sheet in the file to contain the calc method assignments. Within the sheet, one column must contain the relevant key codes (such as ACCT), and one column must contain valid calc method names.

   The sheet can be hidden or visible. Within the target columns, only rows that contain valid key codes will be evaluated for calc method assignments; content such as Axiom query field definitions and column headers will be ignored.

   IMPORTANT: If you are using an Axiom query to retrieve the list of assignments, you must make sure that query runs before the query that will use the assignments. For example, you might set the query to Refresh on Open. If the query does not need to refresh on open, or if both queries need to refresh on open, then make sure that the sheet for the assignment query is located before (to the left of) the sheet for the query using the assignments (on the Control Sheet, not the literal sheet order). If both queries are on the same sheet, then the assignment query must have a higher AQ number than the query using the assignments.

2. Complete the following Assign from sheet settings in the Control Sheet for the Axiom query. These settings are located in the Vertical Configuration section of the query, under the heading Calculation Methods.

   Item	Description
   Key column name	The name of the key column for the calc method assignments. For example, if data is being brought into the sheet by accounts, then enter ACCT.
   Sheet Name	The name of the sheet that holds the calc method assignments. For example, you may have created a sheet named CMAssign to hold the assignments.
   Key column in sheet	The column in the sheet that contains the key codes. For example, if the key column is ACCT, and the account codes are located in column C of the CMAssign sheet, then you would enter C.
   CM Assignment column in sheet	The column in the sheet that contains the calc method assignments. For example, if assignments are in column D of the CMAssign sheet, you would enter D.

   NOTE: If you do not see these settings in your Control Sheet, then you must update the Control Sheet. You can do this using the Sheet Assistant.

   All four settings are required in order to read assignments from the sheet. If you specify the key column name but leave any other settings blank, an error will result when the query is run. If you specify any other option but leave the key column blank, then all settings in this section are ignored.

You can also complete the following optional settings:

• Default Calc Method: If an entry in the sheet is blank for a particular account, or if a particular account is not listed in the sheet at all, then the default calc method will be used for that account. If an entry in the sheet is invalid, then the default calc method is not used; an error results instead.
• Assign from data field: If both the Assign from sheet and Assign from data field options are completed, then Axiom Software will look up the calc method assignment for each account as follows:
  • Axiom Software first checks the specified sheet for the account. If a match is found, that assignment is used.
  • If no match is found on the sheet, Axiom Software checks the specified table column and uses the assignment there. If the assignment is blank in that column, the default calc method is used.

The following screenshot shows an example configuration using the assign from sheet option:

AX2059