AX1394

Converting XML refresh variables to a RefreshVariables data source

In older versions, Axiom Software supported a different way to define refresh variables. This older method used an XML tag to define the variable settings. The following screenshot shows an example of how an XML-style variable appears within a file:

These XML-style variables have been deprecated in favor of using the RefreshVariables data source. Although existing files with XML-style variables will still work, it is strongly recommended to convert these older-style variables to the new RefreshVariables data source. The RefreshVariables data source provides greater flexibility to create dynamic refresh variables, and will be the focus of any new refresh variable enhancements moving forward.

Axiom Software provides a tool to easily convert your existing variables. This tool will create a RefreshVariables data source, then locate all existing variables in the file and convert the XML settings to [Variable] rows in the data source.

To convert existing refresh variables:

  1. Identify the sheet in the file where you want to place the new RefreshVariables data source. Right-click the cell where you want the data source to start (this is the cell where the primary [RefreshVariables] tag will be placed), and then click Axiom Wizards > Insert Refresh Variable Data Source.

  2. If the file contains any XML-style refresh variables, Axiom Software asks if you want to convert these variables. Click Yes.

Axiom Software inserts the data source tags and adds the existing variables to the data source (assuming there is enough room at the selected location—if there is not enough room, a message box will inform you how much room is necessary so that you can select a new location).

Alternatively, you can right-click an existing XML-style variable in the workbook and select Axiom Wizards > Convert XML Refresh Variables to Data Source. This will place the data source at the existing location of the variable, and will also convert all XML-style variables in the workbook and add them to the data source. However, in many cases there is not enough space at existing variable locations to add a full data source, so it is typically cleaner to start at a new location.

Note the following about the refresh variable conversion:

  • Target Cell to [SelectedValue]: The conversion from using a Target Cell to using the [SelectedValue] column works as follows:

    • If the original target cell contains a value, this value is copied to the selected value cell for the new variable.
    • A formula is placed in the original target cell that points to the selected value cell for the new variable.

    For example, imagine that the old refresh variable was defined on the sheet Info, and the variable's target cell was B1. The Info!B1 cell contained the text "Corporate". After the conversion, the Info!B1 cell now contains a formula something like =Variables!G15 (assuming that the new data source was inserted on the Variables sheet, and the [SelectedValue] column is in column G). The Variables!G15 cell contains the text "Corporate".

    This is done so that any existing formulas in the workbook that are pointing to the old target cell will still work. You can go forward with using the file like this, or you can adjust the existing formulas so that they point directly to the selected value cell instead of the old target cell.

  • Bracketed Cell References: The old XML syntax supported bracketed cell references to read information from the workbook. This was necessary because formulas could not be used in the old refresh variable properties. Bracketed cell references in the old variable are converted as follows:

    • Bracketed cell references in the Name property are converted to formulas in the [Name] column.
    • Bracketed cell references in the Column Filter property (for Column Value variables) are converted to formulas in the [ColumnFilter] column.
    • Bracketed cell references in the List Items property are moved to the [ListChoices] column as is. This is because the cell references may be combined with regular text items, and because the old List Items property supported use of ranges as well as single cell references. These bracketed cell references will continue to work in the new RefreshVariables data source. However, if it makes sense to change any of these references to use formulas instead, feel free to do so after conversion.

  • Deleting Old Variables: The conversion process does not delete the old XML-style variables from the workbook. (Except in the case where you convert at an existing variable location, in which case that single existing variable is overwritten by the data source.) Once you have reviewed the conversion and confirmed that the file is working as you expect, you should delete these old variables from the workbook.