Using the Formula Drill Down

The Drill Down feature allows you to get detailed information regarding a Nectari formula. It will take all filters used for the formula and create a table with the fields that you have selected. If there are multiple formulas in a field, you can choose which formula to drill down in.

Profiles are used to select the levels of details you wish to see. You can select a different profile for each formula. You can create multiple profiles for the same data model. For example, you could have a profile that will break down the result  to show what are the document numbers along with its ledger posting and another profile to see the breakdown of the ledger posting for each department. Drill-down profiles will be included in the import/export template.

To create a formula drill down:

  1. Select a cell containing a formula (NECFORMULA) in your workbook.

  2. Click Drill Down Wizard from the Analysis menu. The Drill Down Wizard displays the environment and the data model where the formula is being used.

  3. Select the Profile to be used to show the detailed information about the formula.

    1. If there are no profiles created for the data model, you can create one by selecting New Profile from the list.

    2. Enter a name and click Save.

  4. Drag and drop the appropriate fields (dimensions, descriptions, and/or measures).

    • For each field, select whether the field will be part of the GROUP BY or will be aggregated. If the field is not selected to be grouped, a list of aggregation will be shown. If the field is selected to be grouped, the drop-down for aggregation will be hidden.

      Aggregation allows you to avoid having row repetitions in the drill down and to aggregate the results in one single row.

      By default, when you select a Dimension or Description, it will be included within the GROUP BY and Dimensions will have an aggregation set based on the subtotal set in the Data Model Designer.

    • For each field, select how it will be ordered: ASC, DESC, None.

    Note

    At any time, you can click the Reset Group By button to turn off all Group By toggles and set the Aggregation to None. This will create flat list without anything being grouped together.

  5. If you wish to hide the total row, click the Show Total Row slider.

  6. Click Update to only update the profile or click Update and Drill-down to both update the profile and display the formula drill-down. The drill-down table is displayed on the right-hand side.

To view the formula drill down:

  • Click Drill Down from the Analysis menu. The drill-down table is displayed on the right-hand side.

    When you click on another cell containing a formula, the drill-down table is automatically updated based on the profile.

  • You can switch profiles using the Profile drop-down list.

  • To copy the drill-down table in a new sheet, click .

  • To view filters used in the formula, click .

  • To toggle filters, click .

Note

If you've added hyperlinks to your data model, you can view them within the Drill Down from the Analysis menu.

The hyperlinks are active URLs that will open a new browser window when clicked. You can continue to use the hyperlink even when you choose to copy the drill down table in a new sheet.

To drill down with multiple formulas in a cell:

  1. Select a cell containing multiple formulas (NECFORMULA) in your workbook.

  2. From the Analysis tab, select Drill Down Wizard.

  3. In the Formula Drill Down window, the panel on the left displays all the formulas in the cell. Select the formula you want to drill down in.

  4. Set the appropriate Profile and fields to customize the drill down.

  5. Select Update and Drill Down. The drill down profile of the selected formula appears in a window to the right.

To view a drill down with multiple formulas in a cell:

Select the cell with multiple formulas. From there, you can:

  • Click the Analysis tab and select Drill Down. The drill-down table of the first formula in the cell is displayed on the right-hand side.

  • Change the formula by clicking the Formula drop-down and selecting another formula. The Formula Drill Down window will update with the new formula's drill down details.

To manage a profile:

  1. Select a cell containing a formula (NECFORMULA) in your workbook.

  2. Click Drill Down Wizard from the Analysis menu.

  3. Hover your mouse to the right side of the profile line. Three icons are displayed:

    • Set as Default Profile (Default tag): When you open the Drill Down Wizard, that profile will be selected. If there is no default profile selected, the Drill Down feature will use the first profile available for the data model and open the side panel with the details for the formula.

    • Rename (crayon)

    • Delete (cross mark)