Customizing a Workbook

To customize a workbook:

  1. To insert a formula, click Formula Wizard from the Analysis tab.

    1. Select the Environment from the drop-down list. All available environments in Web Server are listed. When set as Current Env., it will update the formula to retrieve the data of the environment selected.

      Note:  When you switch environments from the title bar, the data will be updated automatically everywhere EXCEPT for formulas created in specific environments.

    2. Select the Data Model, Field and Operator from their respective drop-down lists. You can also search the item by typing the name in the field for a quicker selection.

    3. Click Add Filter to add filters to the formula. Filters can be grouped using Add Group and can be set between an OR and AND. For formula examples, see Workbook Formula Examples .

      • Select the field, operator and value. The value can also be a referenced cell. You can use the prompt to select the field.

      • If you wish to reference a cell, click the underscore in the Value field and select the cell. You can also enter the value in the cell.

      • When you enter a character value, the workbook assumes that it is a cell reference by default. To specify the value as text, click the icon; its color then changes to blue .

        Note:  When selecting a character value using the prompt, the value is set to text automatically.

    4. You can click Preview Value to preview the formula result. If the formula is incorrect, a message will be displayed. When resolving the formula, a timeout happens when the querying reaches 300 seconds (5 minutes) or if the data results retrieved for the formula reaches 500,000.

    5. Click Save.

    The workbook generates a function that is visible in the formula bar. When clicking the formula bar, it displays a pop-up that shows the NECFORMULA function (similar to the NECACCESS function in the Excel Add-in).

    Note:  As opposed to Excel Add-in, you do not need to create a reference configuration. This is done automatically according to the created formula thanks to smart caching.

  2. When using more than one NECFORMULA within a cell, the Formula Wizard gives the ability to give a name to each formula.

    1. Select a cell with multiple NECFORMULA, then click Formula Wizard from the Analysis tab.

    2. In the list on the left, click the to rename the NECFORMULA of your choice.

    3. Once you save the changes, the custom names are added to the NECFORMULA in the cell.

      4. Note:  This feature only appears when there are multiple formulas within a single cell.

  3. If you wish to use a global variable or a global selection page variable, click Functions from the Analysis tab.

    1. Select a variable from the Global Variable or Global Selection Page Parameters list.

    2. Click Save. That variable can then be used in a formula if its cell is referenced.

    Note:  Global variables use @@ in front of the parameter. Selection Pages use ## in front of the parameter. Reports can use both types of parameters (## and @@). To view a selection page example, click here.

  4. When finished, click Save to keep the workbook changes.

    Example