AX2057
Whether you are using an in-sheet calc method or the calc method library for an Axiom query, the calc method and the field definition for the query must logically complement each other.
To help understand this concept, imagine a field definition row layered on top of a calc method row. The combination of both items is how the data records will appear when the file is refreshed. The following is a very simple example:
The field definition is set up to bring in data from the database in columns C-F:
The calc method has a sum formula in column G, and then formatting-only in the other columns:
When the query is refreshed, the calc method is applied to each database record brought in from the query, resulting in the following rows:
NOTE: This example uses a vertical Axiom query, but the same concept applies to horizontal queries.
If there is a conflict between entries in the calc method and the field definition, the field definition takes priority. For example, if a cell in the calc method contains a formula, but the corresponding cell in the field definition contains a database column code, then the records in the Axiom query will be populated with the data from the database column, not the formula from the calc method.
If you are using an in-sheet calc method, then you just need to make sure that single calc method works with the field definition. If you are using a calc method library, then you must make sure that all calc methods that will be used in the query work with the field definition.
Considerations for multiple-row / multiple-column calc methods
You can use multiple-row calc methods with vertical Axiom queries, and multiple-column calc methods with horizontal Axiom queries. The only limitation is that the number of rows or columns in the calc method must be equal to or greater than the number of rows or columns in the field definition. For example:
- You could use a single-row field definition together with a two-row calc method. For each single record of data queried from the database, two rows would be brought into the sheet. The second row of the calc method could be just for formatting purposes (a "spacer" row between data records), or it could contain formulas that reference the data brought in on the first row.
- You could use a multiple-row field definition—for example, one row to bring in data from this year, and a second row to bring in data from last year. In this case the calc method can have two or more rows, such as an additional row for variance calculations (comparing last year to this year), and an additional row to use as a "spacer" row between each block of data.
When the calc method is an in-sheet calc method, it is a relatively simple matter to imagine the field definition layered over the calc method, and ensure that each row (or column) in both items corresponds logically. However, if you are using a calc method library, then you must consider how each possible calc method used in the query will work with the field definition.
The calc methods in the library do not have to have the same number of rows, but they all need to have the same data requirements if they will be used in the same Axiom query, because the data will all come from the same field definition.
For example, imagine that the field definition for the Axiom query is a single row, and the calc method library has two calc methods—one with a single row, and the other with three rows. In both cases, the first row of each calc method will be populated with the data from the single-row field definition. For the three-row calc method, the other two rows will be brought in as well, but no data from the database will come into those rows—which is fine if those two rows are designed for additional data inputs, calculations, or formatting. However, if the three-row calc method is designed to use queried data in more than one row, then it could not be used in the same Axiom query as the single-row calc method, because there is no way to bring in one row of data for one calc method, and multiple rows of data for a different calc method (within the same query).
Formatting considerations
When a calc method is inserted into a sheet, only the formatting properties set for the entire cell are applied. Any formats applied to individual characters in the cell are ignored. For example, if the cell font is set to red or bold in the calc method, then that format will be applied to the cell when the calc method is inserted into the sheet. But if the cell in the calc method contains text and only one word of that text is set to red or bold, then that formatting is ignored when the calc method is inserted into the sheet, and instead the cell-level formatting is applied.
AX2057
