Introduction
This tutorial covers how to implement drill-down functionality using Liberty Reports. The basic concept of drill-down involves a summary level report (or value) and an associated report that contains more detail. Without the use of any drill-down automation, you might otherwise accomplish this by creating a summary report on one worksheet and a detailed report on another worksheet. The detailed report might have parameters that control its output. In using these two worksheets, you would identify a value of interest to you on the summary worksheet, note the unique identifiers on that row (e.g., account number, cost code, job, etc.), then switch to the detailed worksheet, enter those values as parameters and refresh the detail report to view the associated detail information. The drill-down function of Liberty Reports uses the same concept but with automation (as will be described in this article).
Example Scenario
In this scenario, let's assume that Sheet1 contains a job-centric report that shows values by cost code. In other words, the user first enters or selects the job ID of the job they wish to report on. The report on Sheet1 is refreshed showing the job description and a list of the cost codes under the job. For each cost code, the cost to date is shown. The report might look something like this:
Next, let's assume that on Sheet2, you have a report that provides a list of the individual cost transactions for a specific cost code. That report might look something like this:
With these two reports, the user of the report can already perform a sort-of manual drill down for any cost code on Sheet1. The user would simply make note of the cost code of interest, click on Sheet2, enter the Job and Cost Code parameter values, and then refresh the query on Sheet2 so that it reflects all of the transactions for that cost code.
Adding the LRDrillDown Function
The LRDrillDown function is designed to automate the process described above (of switching to the detail sheet, manually keying the parameters, and refreshing the query). Consider the LRDrillDown function to be like an "instruction" to Liberty Reports to take action whenever a cell containing that function receives a double-click.
The first step is to add the LRDrillDown function in an adjacent column as shown here:
In this illustration, the first argument ("C5") represents the value that the LRDrillDown function should display. This will be the value you intend the user to double-click on.
The second argument tells Liberty Reports what to do after it copies the parameter values to the detail sheet. A value of TRUE indicates that it should proceed to refresh the query on the detail sheet. A value of FALSE means it should do nothing (leaving it up to the user to choose what to do next). You could also supply the name of a macro inside double-quotes if you want Liberty Reports to run some special macro function after copying the parameter values. In this example, we supplied a value of TRUE which is what you will ordinarily want to do in order to trigger the detail query to refresh automatically.
The rest of the arguments depend on the number of parameters on the detail report. In this case, the detail report has two parameters (Job and Cost Code). The remaining arguments provide pairs of "destination" and "source" cell references for each parameter. The first destination-source pair tells Liberty Reports that the job parameter on Sheet2 (Sheet2!$B$1) should be populated using the job parameter from Sheet1 ($B$1). The second detination-source pair tells Liberty reports that the cost code ID on Sheet2 (Sheet2!$B$2) should be populated using the cost code from column A ($A5). Pay particular attention to the dollar signs in these cell references because when you copy this formula down to subsequent rows, you don't want the row numbers in some of these references to increment.
The next step is to copy the LRDrillDown formula down to each row. To do this, just double-click the fill-handle box in the lower right corner of the cell border.
Finally, place a label over the drill-down column and hide the original column (since you obviously don't need to display the cost-to-date value twice).
Try It
At this point, you should be able to drill into any amount shown in column D. When you double-click a value, Liberty Reports will activate Sheet2, enter the appropriate parameters, and refresh the detail query.
More Information & Best Practices
Refer to the function documentation for more details about the LRDrillDown function as well as recommended best practices. The function documentation also includes step-by-step instructions for including a "Back" link to the detail sheet that will jump back to the cell you originally double-clicked:
LRDrillDown Function
https://www.event1software.com/services/kb/product-documentation/liberty-reports/function-reference/miscellaneous-functions/lrdrilldown-function/