Drill Down

Drill downs in CDD are used to connect information in one report to information in another report. This information might be based on retrieving more detailed or more refined data, or simply as a tool to relate one report to another.

When a report is run to the screen, a drill down allows you to instantly review underlying information by drilling down to it. Pop-up prompts may be designated to occur prior to the drill down so that the path of the drill down is displayed as you go.

For example: if Report A presents information about employees sorted by hire date but does not display salary information and Report B presents the same list of employees with salary information, a drill down can be created based on the employee Social Security Number to take you from Report A to Report B so that salary information can be reviewed.

Create a Drill Down

When you create a drill down you need to:

Define the position of the drill down report
Link two reports to establish the drill down path.

The following procedure describes how to define a drill down. In the example, we will define a drill down from a name to a phone number report. This drill down allows a user to double-click a name and display the associated phone number.

For this example, imagine the PE Addresses Report has been run to the screen. It contains:

PE ID
PE Name
PE Address (PE Addr1)
PE City
PE State Code (State CD)
PE Zip

To create a drill down:

Right-click on the data element for which the drill down is being defined (PE Name), then select Add Drill Down.
In the Drill To Report window, select the report to which you want the drill down to lead (PE Phone), then click OK.
In the Drill Down window, select Add to establish selection criteria for the drill down.
In the Report Item window, select a table and field upon which to base the selection criteria. You may also make decisions about the Relational Operator and the Response Type. For example, the selection criteria can be based on the PEID being equal to the drill field on both reports.
Click Next.
Select the field to relate; select the field to which you are establishing the relationship (for example, Select PE_ID where PE_ID is equal to PE_ID).
Click Finish.
The Drill Down window appears, displaying the newly defined selection criteria.
If you are finished creating selection criteria for your drill down, click OK.
Click Save to finish creating the new drill down. The Save Drill Definition window appears. The Drill Downs section displays existing drill downs, if available. You can select and apply these drill downs to the report you are currently working on.
To load existing drill downs, click Load. The Load Drill Down window displays previously defined drill downs.
Any existing drill downs are displayed in the Load Drill Down window. Assign the new drill down a name and click OK.
You will be returned to the Drill Down window where you can establish additional selection criteria if desired.

Use an Existing Drill Down

To use an existing drill down, run the report from which the drill down is to occur. Direct the system to display the report to the screen.

Use the mouse to position the cursor on the data element for which the drill down is to be performed. The cursor may appear as a magnifying glass or a drill, possibly accompanied by a camera, depending on what type of drills lay behind the item on the screen. A magnifying glass or drill indicates that you can double-click the item to drill to a second report.

You have two choices in the look of the cursor when accessing a drill down. By default, the cursor becomes a magnifying glass. To change the magnifying glass to a drill, select Edit then Preferences from the main menu. Choose the Report tab and select Drill. You will need to restart CDD for this change to apply.

The magnifying glass or drill cursor indicates that a drill down report is defined for that data element.

Double-click the data element and the drill down report is displayed as a layer on top of the original report. Some of the original report remains in the background.
Right-click the data element to display the drill down options menu, which provides you with three choices:
- Add Drill Down
- View Drill Downs
- Drill Down.

Multiple drill downs may be defined for a single data element. When more than one drill down is defined for a particular data element, a list of drill names appears. Select the particular drill down desired.

After reviewing the drill down information presented, you may drill down to another level, or close the drill down and return to the original report.

Properties and ParentProperties Function

If the drill down report requires information that only exists in the parent report, use the Properties and ParentProperties function. In the Parent CDD Report, system fields or custom variables can be set in the Sub Initialize Macro region, for example:

' ********** Region: Initialize **********
Sub Initialize
Properties("REPORTDATE") = REPORTDATE
Properties("PARENTREPORTID") = REPORTID
Properties("CUSTOMTEXT") = "Custom Text"
....

In the drill down report, the ParentProperties function is used to retrieve the Properties data from the parent report and assign it to a DIM (if it will be used in the Macro only) or User Field (if it will be used in the Macro or Report Design), for example:

' ********** Region: Initialize **********
Sub Initialize
rptdate = ParentProperties("REPORTDATE")
rptid = ParentProperties("PARENTREPORTID")
customtext = ParentProperties("CUSTOMTEXT")
....

This is used to pass down report dates, report IDs, custom text and more to the drill down report to provide more information to the end user or within macros to return specific information.

The data type for Properties and ParentProperties is stored as a STRING when run from the web application. If a date or number is used in a drill down report macro, it must be formatted before it is used.

For example:

If the Parent Report sets PARENTDATE to the system value REPORTDATE which is a DATE field like this:
Properties("PARENTDATE") = REPORTDATE

The drill down report will call the Parent Property like this:
the_parentdate = ParentProperties("PARENTDATE")

This is a string value requiring the_parentdate to be converted to a date by wrapping it in CDate, like this:
If (TABLE.CREATE_DATE <= CDate(the_parentdate)) then…