How do I create a report that has drill-down levels so that I can have summary information at the top level but then view specific records at a more detailed level?

Environment:

Analytics, Rollups and Slices

Resolution:

Rollups and Slices

You can use rollups or slices to collapse records based on a common value in your report. This, in effect, creates a drill-down level based on the field used to configure the slice or rollup.

For example, if you have a report that lists all the unassigned incidents in your site, you can create a rollup based on the staff group or even the staff account.  When the rollup is configured, all the incidents assigned to the staff groups or staff accounts are grouped together in the report based. When you expand the rollup, you can view the individual records assigned to the specific staff group or staff account.

Similarly, slices allow you to list the values for the slice you create and if you display the slices in a tree format, you can include group counts so that the slices and group counts display to the left of the overall report. When you click on a specific slice only the records associated with that record display.

Rollups and slices can either be configured as part of the saved report so that when the report is run, the rollup or slice is generated as part of the report or they can be added to a report after the report is generated.

Rollups and slices are added to a report either in edit mode or after the report has been run. 

• From the Home tab, select Rollup or Slice and select Choose Columns to define the grouping.

For more information on slices and rollups, refer to Answer ID 2511: Using Slicing and Rollups with Reports.

Adding Drill-down Levels to a Report 

When configuring a report, you can include drill-down levels so that staff can view summary information from the top level of the report and then click on a row of data or link within that row to view more detailed records and information. Drill-down levels are configured as part of the report and cannot be added or removed after the report is generated.

The top level of the report typically includes some kind of grouping of records. Most frequently, the Count(expr) aggregate function is used with at least one field at the top level. That way, the count of the number of supporting records displays in the top level. Then, the user can drill down to see the specific records reported in the count number.

For example, you can create a report that lists your active customers along with a count of their support incidents and a count of sales opportunities associated to them.  Then, you can create a drill-down so that when staff click on the count of incidents, they view the incidents assigned to the customer. Similarly, you can create a drill-down level so that when staff click on the count of sales opportunities, they can see the specific opportunities for that customer.

To add a drill-down level to a report, you must first determine what information to display at the top level and then determine what information to display in the drill-down levels.  Then, you can add the additional levels and define the output columns for each additional level.

To configure a report with a drill-down level, use the steps below. For more information on creating a single-level report, including adding report filters and output columns, refer to Answer ID 2509: Creating a Basic Custom Report.

1. From the toolbar, select New > Report or open a report for editing.
    
2. In the report outline, define the filters used for the report.
    
3. Drag and drop fields from the Data Dictionary section to the Data Area. 
    
4. To add a function (such as Count) to the output column, right click on the output column, select Edit Definition and edit the Expression field to be Count(table.field_name).  Then, click OK.
    
5. In the Report Outline area, under the Output Levels heading, right click on the report level and select Add Drilldown.
    
6. Add the name for the level and a brief description.
    
7. In the Drilldown Settings area, set the Filter field to the field or fields used to group and filter the data into subgroups. For example, if you want to drill down to see incidents assigned to a specific status, the filter is incidents.status.
    
8. For the Link field, indicate which field should be used to access the drill down level.  Or select Entire Row and the user can double click anywhere on the row to drill down.
    
9. Click OK.
    
10. In the new report level, drag and drop output columns into the Data Area.
     
11. Add additional drill down levels as appropriate.
     
12. Save your report. 

After saving the report, run your report and verify that the links work as you expect and that the data displays correctly.