Purpose
The LRDrillDown function allows you to double-click a cell to navigate to a detail worksheet and refresh data on it based on keys provided by the LRDrillDown function.
Syntax
LRDrillDown( display value , refresh action , destination cell 1, source cell 1, [ ...destination cell n ] , [ ...source cell n ] )
The LRDrillDown function syntax has these parts:
| Arguement | Description |
|---|---|
| display value | Required. This argument returns the value to be displayed in the cell containing the LRDrillDown function. |
| refresh action | Required. This argument specifies how data on the destination worksheet should be refreshed. Pass TRUE to refresh queries on the destination worksheet, pass FALSE to do nothing. Alternately, you can provide the name of a macro to execute upon drilling down. Note that in all cases, the key values will be copied from the source cells into the destination cells and the destination worksheet will be activated. |
| destination cell | At least one destination cell is required. The value from the following source cell will be copied into the destination cell. The worksheet containing the first destination cell will be activated upon double-clicking the cell containing the LRDrillDown function. |
| source cell | One source cell is required for each destination cell. The value from the source cell will be copied into the preceding destination cell. |
Recommended Practices
Use Named Cells vs. Cell Addresses
Excel allows you to name individual cells or ranges of cells in a worksheet. You can then use the name rather than the cell address. This makes it easier to work with formulas because they will be more readable and less prone to error. To name a cell, simply select the cell and then click inside the Name Box (the box directly to the upper left of cell A1). Type the name of the cell and press Enter. Here is an example of an LRDrillDown formula using cell addresses:
=LRDrillDown($C5, TRUE, Sheet2!$B$1, $B$1, Sheet2!$B$2, $A5)
Here is the the same formula using named cells:
=LRDrillDown($C5, TRUE, DetailJobIDParam, JobIDParam, DetailCostCodeParam, $A5)
User Indirect References to Destination Cells
An indirect reference is simply the cell address or name of the cell enclosed in double-quotes as text. When a cell is referenced in this manner, the cell being referenced does not trigger a calculation of the formula referencing it (indirectly) when its value is changed. Using a direct reference, on the other hand, would trigger a calculation. In the context of drill-down, direct references to the destination cells will cause numerous unnecessary calculations to occur whenever the destination cell values change. Here's the same formula from above modified to use indirect references:
=LRDrillDown($C5, TRUE, "DetailJobIDParam", JobIDParam, "DetailCostCodeParam", $A5)
Don't Nest Formulas
The first argument of the LRDrillDown formula should always be a cell reference or literal value, never another Liberty Reports database function such as DBLookup, or DBSum.
Reference Original Values
In general, don't create other formulas that depend on the result that is shown by the LRDrillDown formula. For example, if column D contains an LRDrillDown formula that is displaying the value from hidden column C, don't base other calculations on the value in column D. Use the value in column C instead.
Provide Visual Feedback
It is a good practice to advertise the ability to drill down by using consistent formatting that calls out the cells purpose. The convention used in Event 1 templates is to follow the display value with a right-facing chevron. If the cell returns a text value, then you can append a chevron using concatenation:
=LRDrillDown(C38,FALSE,AccountID,$A38) & "»"
For numeric values, you can apply formatting to accomplish the same end result. Here is an example of a custom format for a cell that returns a currency value:
_($* #,##0.00_)"»";_($* (#,##0.00)"»";_($* "-"??_)"»";_(@_)"»"
Add a Link to Navigate Back
Leveraging the visual feedback to drill down, you can provide a convenient link to return to the drill-down point by following these steps:
- Near the top of the destination worksheet, add the following text to a cell: « Back
- Select the cell.
- Click Formulas and then Name Manager.
- Click New.
- Enter BackLink for Name.
- Change Scope to the active worksheet.
- Click OK.
Remarks
The LRDrillDown function is not intended for use in conjunction with the LRKeep function. Drilling down to detail typically causes an entirely different set of data to be returned, thus preventing values from being retained.
See Also