Using DrillDown
DrillDown enables you to select a cell in your report and expand it according to new criteria contained in a special DrillDown layout. This is useful when reporting is based on summary ledgers, as it provides underlying details when and where they are needed.
Note: Although you create DrillDown layouts in PS/nVision on the Windows client, you can run DrillDown with a browser on any report that you have access to in Report Manager.
The following report was run based on the Emplsal layout delivered with the PeopleTools Demo database (PTDMO). By expanding the nPloded rows, you can review details about the data in the report. But what if you want to review the monthly salaries of individual employees, and this is not in your original report layout? With DrillDown, you can create a DrillDown layout that expands cell data to show the monthly employee salary rates.
Image: Selecting a cell for DrillDown in a sample layout
In this example, L5 is the cell we are drilling down on, representing the total monthly rate for the Human Resources department.
After selecting a cell, pick the DrillDown layout you want to use from the nVision, DrillDown menu. The results appear in a DrillDown report.
Image: Report resulting from DrillDown
This example shows the results in a DrillDown report.
The DrillDown layout inherits all the criteria of the selected cell on the original report and provides a subreport with the details you need.
Because DrillDown depends on child layouts, you might want to create a library of common layouts to use. These might include:
Accounts by department.
Products by cost center.
Accounts by period.
Departments by benefit plan.
Your system comes with a number of generic reports, and you can tailor or clone these reports as needed. Many of these layouts employ nPlosion, so you can view both summary and detail levels in your subreport.
You can also perform a series of DrillDowns on cells in reports until you have reached the level of detail that you need.
DrillDown is available from matrix layouts only. However, the layout that displays the results of the DrillDown can be either matrix or tabular. For example, you might produce a financial report using a matrix layout, and then select one of the amounts and drill down to another matrix layout that breaks down the departments and products that were summarized into that amount. From that report, you might select a department/product combination and drill down, this time using a tabular layout that queries the individual sales transactions. This is the end of the DrillDown trail, because you can drill further only from a matrix report.
Note: A PeopleTools upgrade may change the web server domain name, port number, or servlet path required to access web server resources in the PeopleSoft Pure Internet Architecture. As a result, PS/nVision drilldown operations on reports that were created before upgrade would fail, primarily because drilldown links are, by design, hard-coded into PS/nVision reports. This is UpdateNvsDrill.xls. We have provided a simple search and replace utility that you can use to replace old links with new ones. This Excel macro, along with all other Excel macros, is located in the PS_HOME\Excel directory.
The key to DrillDown is the passing of selection criteria from a parent cell to its child using the DrillDown layout. The child layout may have criteria of its own (possibly on other fields than those mentioned in the parent), but any conflicts in criteria must be resolved so that the child query accesses a subset of the data selected from the parent query.
Think of inherited criteria as the equivalent of a scope for the resulting report. The DrillDown layout can have criteria (including nPlosion) for fields that were not included in the original report, and it can have criteria for fields that defined the selected amount from the original report.
Criteria for fields from the original report override any specified in the DrillDown layout, with the exception of nPlosion options, which enable you to see more detail than in the original report.
Within a DrillDown layout, you can specify TimeSpan nPlosion without entering a TimeSpan, since the DrillDown layout inherits the TimeSpan of the original report.
Ledger Inheritance
Ledger criteria can be overridden in a child layout, either by ledgers specified in the child layout or by queries to access tables that do not contain the LEDGER field.
An example of a ledger-based DrillDown that expands ledger criteria compares budget to actual expenditures. Assume that you want to drill down from a budget variance report produced at the business unit level and compare the actual to budget for each department. You can construct a DrillDown layout with DEPTID nPloded in the rows and columns for actuals, budget, and variance. Because PS/nVision allows this layout to override the inherited ledger, you can see each department’s budget performance in a single picture.
Soft Inheritance
You can create a DrillDown with multiple TimeSpans. It is called soft inheritance for TimeSpans. To create a soft inheritance for TimeSpans, you define a DrillDown layout with multiple columns and various combinations of ledgers and TimeSpans.
For example, suppose you have a ledger total posted amount for the year 2001. You want to show year 2001’s detail amounts, which make up this total amount in one column, with 2000’s detail amounts in the column beside it. You define a DrillDown layout with two amount columns. One column has no TimeSpan, (although you might specify nPlode TimeSpans), and the other column specifies a TimeSpan, such as YTD-1YR. When you drill down, the first column inherits the TimeSpan, year 2001, from the parent cell; and the other overrides the TimeSpan from the parent cell and uses its own defined year 2000 TimeSpan.
Note: Soft inheritance only controls when you drill down to a tabular layout. It does not function for matrix layouts.
Because DrillDown instances may be numerous and temporary, PS/nVision does not save them automatically as it does for parent instances. Also, since these instances are generated without a report request, there are no directory and file name templates. Therefore, PS/nVision performs the following operations when creating the instances:
Saves the layout as a temporary template sheet (DRILL.XLT) in your TEMP directory.
Opens an instance of the template; this causes Excel to assign a name such as DRILL1.
Populates the instance as usual but neither saves nor closes it. You can save it (assigning a name at save time) or close it without saving it once you are finished using it.