Using DrillDown
This chapter discusses how to:
Use DrillDown.
Use DrillDown navigation.
Use DrillDown layout formats.
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.
In the following illustration, 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, as shown in the following illustration:
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.
See Also
Using the PS/nVision DrillDown on the Web
Using Inherited Criteria
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 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.
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.
Using DrillDown Instances
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.
Using DrillDown Navigation
Unlike nPlosion, DrillDown does not require you to enable it before running your report. Run a report, select a cell to expand, select the DrillDown option from the nVision menu, and then click the Drill button on the toolbar, or use a predefined item on the Drill menu. The predefined Drill menu items are most convenient because you can personalize them to express common DrillDown actions for your organization.
To create a new DrillDown layout, select the New Layout option from the nVision menu and define your selection criteria just as you would a report layout. Save the layout in the directory identified as DrillDown Layouts on the nVision tab of the PeopleSoft Configuration Manager.
Remember that criteria from the selected cell in the parent instance are inherited in the child layout. Reports inherit criteria from the parent cell so you get the same summary amount in the detail report, but you also get the summary amount broken into its component details. This produces a single report instance containing a subset of the data selected in the parent instance, but the data is separated into one or two dimensions to show more detail.
After you have reviewed the result of a DrillDown report, the Jump Back command from the nVision menu (CTRL+SHIFT+J) returns you to the cell from which you drilled down. This occurs even if you closed the parent report.
To repeat a DrillDown starting from a different cell on the parent report, press CTRL+SHIFT+I. For example, suppose that you have drilled down from the current year’s Travel Expense amount, showing balances by detail account and department. Now you want to see the same breakout for last year’s amount. Jump back to the original report, select last year’s amount, and press CTRL+SHIFT+I.
For both ledger-based and query-based matrix reports, AutoDrill is a quick and easy DrillDown method. To use AutoDrill, define a default DrillDown layout within the parent layout by using the Excel Insert, Name, Define command to define the name NvsDefaultDrill as a string with the name of the default DrillDown layout in the parent layout.
This is an example of the Define Name dialog box:
After running a report, you can use AutoDrill in various ways:
Double-click one of the amount cells.
The Nvsuser front end generally sets double-click to call the PS/nVision AutoDrill function.
Select the AutoDrill option from the nVision menu in Microsoft Excel.
Click the AutoDrill button on the nVision toolbar.
AutoDrill checks whether the instance you are drilling from has a default DrillDown layout. If so, PS/nVision runs that layout.
Note. The default DrillDown layout may contain a matrix layout, a tabular layout, or both.
If no default DrillDown layout exists, PS/nVision checks the data source in the parent instance for the cell you are drilling from. If it is a ledger, PS/nVision asks you to select a DrillDown layout. If it is a query, PS/nVision adds the inherited criteria to the same query and then runs the query in QueryLink mode. To show the underlying detail from a query designed for matrix reporting, PS/nVision removes aggregate functions, such as Sum, from the query. This causes the query to show the lowest level of the detail from the database. This modification of the query happens only when drilling down in QueryLink mode.
To get an intermediate level of detail in a query-based report using AutoDrill, define a default tabular layout in NvsDefaultDrill. You might want to define a slightly different query to aggregate the intermediate level of detail you need, and then build the default DrillDown layout using this query.
See Also
Using DrillDown Layout Formats
Basic DrillDown layouts typically have one data row and one data column. These simple layouts take data already selected on a report and expand it in two dimensions. Both columns and rows may specify nPlosion and automatic labeling of inherited and nPloded data.
Note. A DrillDown layout can be as complex as you like. For example, if you are building a DrillDown layout you expect to be used from a corporate-level management report, you could design it with departments grouped into regions, with nPlosion to detail. If you drill down to this layout from a regional report, PS/nVision filters the layout criteria through the inherited criteria, and all the other regions have zeros.
Because DrillDown layouts are based on a simple matrix, you can set up a library of layouts based on the field and TimeSpan combinations most common for your reporting and analysis needs.
For example, if you frequently select an amount from a summary income statement and expand it to show that amount broken down by individual accounts, you could create a DrillDown layout that uses nPlosion to expand account detail in the rows and that breaks out a TimeSpan to the individual accounting periods in the columns. Alternatively, you might want to see the value broken out by department in the rows and product in the columns.
Let’s say that you specify All Detail Values in the DrillDown layout as the selection criterion for a field, and the cell you are drilling down from on the original report used a specific tree node for its criteria. In this case, the report from the DrillDown nPlodes only the detail values for that tree node, creating a row or column for each in addition to a summary column based on the tree node.
If the parent cell had no criteria for a field specified on the child as all detail values, the child report lists amounts for all values in the specified field.
DrillDown and Summary Ledger
When using DrillDown from a report based on a summary ledger, you can either translate summary ledger criteria into the corresponding detail ledger or drill down within the summary ledger.
To drill down within the summary ledger using the web, define the name NvsTranslateLedger in the DrillDown layout. PS/nVision reads this defined name at runtime.
If the value is Y, summary ledger criteria is translated into the corresponding detail ledger.
If the value is N, DrillDown within summary ledger is selected.
If NvsTranslateLedger is not defined, summary ledger criteria is translated into the corresponding detail ledger.
To define the name NvsTranslateLedger in the DrillDown layout:
Use the Excel Insert, Name, Define command to define the name NvsTranslateLedger.
Define the name as a string with either Y or N for the value.
Note. If you are using the Windows client, PS/nVision continues to ask whether to drill within the summary ledger or drill to the corresponding detail ledger.
DrillDown and Queries
When drilling down from a ledger-based report, you can use predefined queries (built into PeopleSoft Query) in the following ways:
Use a matrix DrillDown layout that specifies query, rather than ledger, criteria.
This provides a matrix report that accesses tables other than ledgers, but inherits all the field criteria (business unit, account, and so on) from the parent report.
Drill down to a tabular layout, inheriting the field criteria from the parent report.
This is useful for seeing the details of transaction data, such as journals and voucher lines.
Drill down to a query without a layout.
The query inherits the criteria and runs to Excel in QueryLink mode.
When drilling down from a query-based report, you can use another query in a matrix layout, in a tabular layout, or without a layout. The query you use must be capable of inheriting the criteria from the cell you are drilling down from.
In any case, PS/nVision bends the rules of inheritance slightly to enable you to see the needed data. The ledger construct (which implies a special query against a specific type of table defined in PeopleSoft General Ledger) is replaced by the query specification. The implied field criteria for the inherited ledger (for example, LEDGER=ACTUALS) may or may not be inherited. While drilling down to journals within the PeopleSoft General Ledger application requires criteria for the LEDGER field (because journals may exist for various ledgers), drilling down to Accounts Payable voucher data only makes sense from the Actuals ledger, and the voucher tables do not include the LEDGER field. PS/nVision thus looks at the records being queried and includes criteria for the Ledger field only if it is present.
Naming Conventions
Use a three-character naming convention for DrillDown layouts, so that the fields and accounting periods in the layout are easily identified in the Open Layout dialog box. The PS/nVision DrillDown layouts supplied with the system use the abbreviations described in the following table.
|
Abbreviation |
DrillDown Layout |
|
ACT |
Account |
|
BUS |
Business Unit |
|
DEP |
Department ID |
|
PRD |
Product |
|
PRJ |
Project |
|
PER |
Accounting Period |
Each layout is named RRRCCCXX.XNV, where RRR is the abbreviation for a field expanded in the rows, CCC is the abbreviation for a field expanded in the columns, and XX is an optional identifier for a specific layout or version of RRRCCC.
Note. With Windows 95, Windows NT, and other more recent versions of Windows, file names can be long and descriptive, but a consistent convention is still a good idea. For example, you might want to name a DrillDown layout DepartmentByProduct.
DrillDown Layout Directory
Store DrillDown layouts in a separate directory from the parent standalone layouts. You specify the directory path on the nVision tab of the PeopleTools Configuration Manager. The DrillDown layout path can contain multiple directories, which are searched in sequence. The DrillDown directory is also defined in the PeopleSoft Process Scheduler configuration for your report server.
See Also