Drillthrough

Drillthrough is used to list the base data from the database that constitutes the cell on the spreadsheet.

The Drillthrough option is available on the right mouse button pop-up menu when:

• the currently active cell contains an XL3Lookup formula
• the currently active cell is part of the data area of an active Grid

To display the Drill Through dialog, select a cell and choose XLCubed > Drillthrough... from the right mouse button menu.

• The Destination field determines where the Drillthrough information will be placed on the worksheet.
• The Limit rows field allows you to restrict the returned result set.
• You can create a new worksheet to place the data on by clicking on the New Sheet button and entering a name for the worksheet.

The Drillthrough places the returned data directly onto the spreadsheet. The values inserted are values and not formulae.

Formatting

Header cells that are inserted by the drillthrough are formatted using the Label Format format cell on the XLCubedFormats sheet. Data cells are formatted using the default cell format format cell.

From XLCubed v7.5, additional formats specified in the XLCubedFormats sheet will also be applied. To do this, add a new row for each column that should be formatted:

• set the Dimension/Column cell to Column
• set the Member1 Name cell to the name of the drillthrough column to be formatted
• set the Data Format cell to be the desired format

Note that the column name can be a wildcard format. For example, *Date will format all columns ending in 'Date' with the format. The example below formats 2 columns, 'Date' is being set to date format, and 'Order Number' to text.

Custom Definition

You can customise the columns returned when running a drillthough on a grid. This is done by selecting “Custom Drillthrough Columns” in the Grid properties screen, on the Interaction tab.

On this screen you can press “+” to add a column, and then pick either a Level or Measure. If you chose a Level you can use the drop down below the tree to choose what property of the item to display (Key, Caption or Unique Name).