AX2058
In-sheet calc methods are used to apply formatting and formulas to data that is inserted into a sheet using an Axiom query. For report files, the in-sheet calc method is the only way to apply formatting and formulas to inserted data. For file group files, the in-sheet calc method can be used as an alternative to using a calc method library.
Specifying the location of the in-sheet calc method
The location of the calc method is defined on the file's Control Sheet. If the query is a standard vertical query, you specify one or more calc method rows. If the query is a horizontal query, you specify one or more calc method columns.
You can use the Sheet Assistant to define these settings, or you can manually edit the Control Sheet. The Sheet Assistant updates dynamically depending on whether the query is vertical or horizontal:
|
Vertical query |
Horizontal query |
In the Control Sheet, you must expand the appropriate section to reach the setting, either Vertical Configuration or Horizontal Configuration.
Vertical configuration
Horizontal configuration
Keep in mind that once you have specified a row or a column location, it does not update automatically if you insert rows or columns into the sheet, or if you move your calc method in the sheet. You may want to first design the query on the sheet, figuring out where you want to place items such as headers and subtotals (if needed), and then complete the query settings. If you do change the location in the sheet, you must update the Control Sheet setting.
Creating the in-sheet calc method
Within the specified rows or columns of the in-sheet calc method, define any formulas and formatting that you want applied to data records as they are inserted into the data range of the Axiom query. For example, you might:
- Format cells that will contain financial data with the desired number or currency formats.
- Add variance calculations or other formulas to be applied to the data.
The in-sheet calc method and the field definition must complement each other. As you are constructing both, imagine the field definition layered on top of the in-sheet calc method. The combination of both is how the data records will appear when the file is refreshed. If there is a conflict, the field definition takes priority. For example, if a cell in the in-sheet calc method contains a formula, but the corresponding cell in the field definition contains a database column code, then the records in the data range will be populated with the data from the database column, not the formula from the calc method.
The number of rows or columns in the in-sheet calc method must be equal to or greater than the number of rows or columns in the field definition. For example, if the field definition is two rows, then the in-sheet calc method can be two rows, or three rows, but it cannot be one row. If the in-sheet calc method is smaller than the field definition, it will be ignored and no calc method will be applied to the query when it is run.
NOTES:
-
The calc method is only applied when data is inserted into a data range; it is not applied when existing data is updated. If the refresh behavior for an Axiom query is rebuild, then the calc method is always applied because existing data is deleted and new data is inserted. If the refresh behavior is update and insert, then the calc method is only applied to any new records that are inserted; the calc method is not applied to existing data records that are updated.
-
The calc method's row height or column width is only applied to the data range when the insertion behavior is Insert. If the behavior is InsertRange, then existing row height and column width is left as is, regardless of the calc method settings.
Example in-sheet calc method
The following example screenshot shows how an in-sheet calc method was applied to the data in the data range. In addition to cell formatting and formulas, this calc method also uses Data Validation to alternate the cell shading for each row, and to flag each row with a green light or a red light, depending on whether the row's variance is within acceptable ranges.
AX2058
