AX2286

Spreadsheet design considerations for Axiom files

While designing reports, file group templates, and other Axiom files, keep in mind the following design considerations regarding use of spreadsheet features.

Arrays

Axiom Software features do not support use of array formulas and constants. Any time Axiom Software performs actions on a file (such as running Axiom queries, applying action codes, etc.), this activity may remove or otherwise adversely impact the array.

You can check for array use in a file by running the Analyze All Excel Formulas diagnostics test. For more information, see Using file diagnostics for troubleshooting and optimization.

Conditional formatting

When designing files, be aware of the amount of conditional formats used in the file. Use of many conditional formats can dramatically slow file performance when performing various activities such as refreshing Axiom queries, taking snapshot copies, or drilling. This is primarily an issue when using the Windows Client.

As a guideline, we recommend limiting usage to around 50 conditional formats per sheet. Anything more than this should be tested thoroughly to gauge the impact on performance. There are no definitive limits that are known to cause issues – the number of acceptable formats will depend on the overall file setup and the environment it is run in.

Each rule listed in Excel’s Conditional Formatting Rules Manager counts as an individual conditional format. For best results, conditional format rules should apply to ranges rather than individual cells. Note the following:

  • When setting up conditional formats for use in calc method libraries, the format should be applied to the entire row or column instead of the individual cell. For example, instead of applying the format to =$E$5, it should apply to =$E:$E. This ends up reducing the individual conditional formats applied in the resulting file when using a calc method library.

  • When setting up conditional formats for use in an in-sheet calc method, the format can be applied to individual cells in the calc method row(s). When the AQ is refreshed, the format will be applied as a range within the AQ data range. For example, if you apply a conditional format to =$E$5 (where row E is the in-sheet calc method row), and 20 rows are brought into the AQ data range, then the conditional format will be applied as something like $E$11:$E$30.

You can check the number of conditional formats used in a file by running the Conditional Format Count diagnostics test. Make sure to run the test after the file has been refreshed and all calc methods have been inserted, so that all of the expected conditional formats are in the file. For more information, see Using file diagnostics for troubleshooting and optimization.

Also note that the Windows Client has additional limitations around conditional formats. The Windows Client does not support any conditional formatting features that were new in Excel 2007 or later. For example, the Windows Client does not support conditional formats with cross-sheet references and is limited to three formats per cell. Make sure to test your files in the Windows Client to be sure the conditional formats will operate as expected in both the Excel Client and the Windows Client.

Merged cells

Merged cells can cause issues with certain Axiom Software processes, such as action codes and snapshot. If possible, we recommend avoiding use of merged cells. For more information on merged cells and snapshot, see Configuring snapshot options for Axiom files.

You can check for merged cells used in a file by running the Merged Cell Scan diagnostics test. For more information, see Using file diagnostics for troubleshooting and optimization.