Drill: Customer Aging Report

Overview

In this example, you will be setting up a simple drill to the customer orders history. It is a great use case for creating your first drill.

If you are following the Training Labs, this report file can be found in the Report Library at Training Labs > Lab 4 Drilling To Data > Lab 4.1 Customer Aging.

Unfreezing the Excel Sheet

Step 1: You begin with a special version of the Customer Collections Report that has the drill removed.

Step 2: Unfreeze the panes. Go to the View tab, find the Freeze Pane option, and select Unfreeze Panes.

Step 3: When you scroll up to rows 7 through 9, you will see where this file was set up to have 3 ReportDrill() functions. This is where you will create your first drill formula for this example.

Build the Drill

Step 1: To begin, select cell C7 and type =ReportDrill(). Then click the _fx _ button to bring up the Function Wizard.

Step 2: Now select the ReportCellToRun argument and type in CustomerOrderHistory!C10 . This is the cell location of the Report Formula in the target report that you will be drilling to. In the screenshot below, you can see the specific cell you are drilling to in the CustomerOrderHistory tab.

You are going to skip the ReportCodeToRun argument, since that is used only when drilling to a separate workbook from the Report Library.

Step 3: Next, we will use the TransferPairs argument to note which cell values in the source worksheet will be transferred to the target worksheet during the drill operation. To do this we use special functions to pair the source cells to the target cells. Type PairGroup(Pair()) in the TransferPairs argument to get it started. You will return to add more to this argument.

Step 4: Then you will specify the name for the drill. In this case you will type "Drill to Customer Order History".

Step 5: Complete the TransferPairs argument. In the Formula Bar, click inside the word Pair() within the text PairGroup(Pair()). See the illustration below. Once this is done, the Function Wizard will automatically change to help with the Pair() function. Type B22:B24 in the From argument. This is the column where the CustomerID is going to be presented in the source report. By noting a range from row 22 to 24 in column B, Interject will expand that range to the data presented in this source report.

Step 6: Select the Target input field and navigate to the CustomerOrderHistory tab, where you will choose which cell you want to receive the value. In this case you want C23. Excel should automatically fill in the argument with the text CustomerOrderHistory!C23 . Click OK to finish updating the function, and it will automatically go back to the source worksheet.

Step 7: Now that you have created the drill, you can use Pull Data to bring in the customers to the source worksheet.

Below is when the data is pulled.

Final Results

Step 1: To demonstrate the new drill, click on one of the customer rows and select Drill on Data. Click on the button Do Drill and Interject will navigate to the target worksheet, bring in the CustomerID selected by the filter, then run the report. The screenshot below should resemble the result.

You completed the drill. You can return to the source worksheet, Customer Aging , and re-freeze the panes, clear the report, and save it. Now it is ready to be published to the users.

Finally, clear the reports, refreeze the panes, and upload it to the Report Library.

Do not upload this file to the Report Library if you are following the Training Labs. You may save the file locally, however, if you want to keep a local copy.