Drill: Inventory Report

Overview

In this example, you will view drilling between reports using the same Inventory reports created during the Real World Inventory Walkthrough. You will set up a drill from the Inventory by Category tab to the Inventory by Detail tab of the workbook. You will also set up a hyperlink so the drill can be more intuitive to users.

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.2 Inventory Report.

Unfreezing the Report

Step 1: First, open the Inventory Report Template. This file has been prepared specifically for this exercise. Once opened, it should look like the screenshot below.

Step 2: Now unfreeze the panes to access the report formulas.

Setting up the Drill

Step 1: Type =ReportDrill() into cell C5. Then, click the fx button to bring up the Function Wizard.

Step 2: Now type InventoryByDetail!C4 into the ReportCellToRun argument to specify the range you want to navigate to. You will be skipping the ReportCodeToRun argument since that is only used for drilling to other workbooks in the Report Library.

Step 3: Next 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, use special functions to pair the source cells to the target cells. Type PairGroup(Pair()) in the TransferPairs argument to get it started.

Step 4: In the Formula Bar, click within the word Pair() inside the text PairGroup(Pair()) while the Function Wizard is open. See the illustration below. Once this is done, the Function Wizard will automatically change to help with the Pair() function. Type B15:B23 in the From argument as shown below. Column B is where the CustomerID will be shown in the source report. By noting a range from row 15 to 23 in column B, Interject will expand that range to the data that is presented in this source report.

Step 5: Next, select the Target argument and navigate to the InventoryByDetail tab. You want to place the CustomerID in cell E11 during the drill operation. Excel will fill in the formula automatically based on where you click. Click OK to finish updating the function and it will take you back to the source report.

After pressing OK, the report formula should look as it does in the image below.

Creating Hyperlinks as Drills

Step 1: Now you are going to create hyperlinks for the drill. First, highlight the cells you want to setup the hyperlink, then right click and choose Link. In some versions of Excel it will show as Hyperlink.

Note: Each drill will need to be linked individually, not all at once. If they are linked all at once then the drills will not work as it will drill everything at once, rather than one at a time.

Step 2: In the Hyperlink pop-up window, you will select Place in This Document. Then select ScreenTip, type Interject Drill, and press OK. Although this technically sets up a hyperlink to cell A1 in the same tab, Interject will override the event so the Interject drill will activate.

After you select OK, select on each of the remaining cells one by one and press CTL-Y to apply the last used action (in this case applying the link). Then the cells will be linked to the drill, as shown below.

When the panes are refrozen, the report should look like the image below.

Step 3: Pull Data to see data for each category you just linked.

Here, you have the report pulled and are ready to go.

Step 4: Now that you have the data, and can click the hyperlink. As shown in the animated GIF below, click on Totals for Grains/Cereals and Interject will drill to the detail of that category in the target worksheet. Hyperlinks only show the Drill window when there is more than one drill option setup. In this case, you only setup one drill and it goes there automatically.

Hyperlinking Drills is a simple way to make Interject reports faster and more user-friendly. Click here for the Financial Report Drill walkthrough.