Version 2021

Setting drilling options for a report

You can select drilling options for a specific report, to specify how to drill on that report. These options apply when a user right-clicks a report component and then selects the direction to drill and the destination object. If a user drills using the Drill dialog box instead, he can override the default settings for keeping the parent, thresholds, across-level subtotals, and page-by.

The drilling options described below override the properties set for a drill path (using the Drill Map Editor).

You can:

  • Disable drilling for the report. By default, drilling is enabled. For instructions, see Disabling drilling for a report.

  • Allow users to drill anywhere on the report or to drill down only:

  • Drill anywhere: Users can view data associated with the object they drill from, no matter which direction in the attribute's hierarchy they drill. For example:

    •  A user can drill down from an attribute to the child attribute data, for example, drilling from Year data down to Month data.

    • A user can drill up from an attribute to the attribute's parent attribute data, for example, drilling from Item data up to Product data.

    • A user can drill across to other, related attributes, for example, drilling from Employee data across to Region data.

  • Drill down only: Users can only view data associated with objects lower in the hierarchy than the attribute on which they are drilling. For example:

    • Drilling down from the Month attribute or one of its elements, users can only drill to Day.

    • Drilling down from the Category attribute or one of its elements, users can only drill to Item.

  • Select whether to keep the parent while drilling. Keeping the parent displays the level immediately above for the attribute on which you are drilling.

    The Keep parent while drilling setting can be set in either Report Data Options or the Drill Map Editor. Setting this option within Report Data Options allows the report designer to specify whether the parent is kept or removed whenever a user drills on the report. Set Keep parent while drilling to Default to allow the drill path to determine whether or not the parent is kept.

  • Select whether to keep thresholds while drilling.

    The Keep thresholds while drilling setting can be set in either Report Data Options or the Drill Map Editor. Setting this option within Report Data Options allows the report designer to specify whether thresholds are kept or removed whenever a user drills on the report. Set Keep thresholds while drilling to Default to allow the drill path to determine whether or not thresholds are displayed.

  • Select whether to inherit the across-level subtotals from the parent, which determines whether these subtotals are displayed in the drilled-to report. Across-level subtotals are hidden only when all of the following are true:

    • Inherit across-level subtotal from parent is set to No.

    • Keep parent while drilling is set to No.

    • The user is drilling from the object that is the level of the subtotal.

  • Specify the filtering options for page-by elements: Any page by field and/or Any other part of the report can be added to the filter during drilling.

  • If you select Any page-by field, choose one of the following:

    • Apply to current page by field: The current page-by element is added to the filter when drilling. For example, if the page-by is set to State and you drill from Store to Employee when this option is selected, the page-by remains as State.

    • Apply to all page by fields: The page-by element is added to the filter when you drill from all the page-by fields of the report.

  • Select one of the following Drilling filter options, which allow you to select what elements are included in the filter when drilling:

    • Include in the filter only elements of attributes positioned to the left of or above the selected attribute element(s): When a user drills on a report, only the elements to the left of and above the selection are included in the SQL for the new report. This shortens the SQL required to create the report. This can be important for reports that generate large amounts of SQL so that the SQL does not exceed the maximum allowable length.

    • For example, a report contains the Year and Quarter attributes. If you select a year and drill on Quarter, the report filter contains the year you selected.

    • Include in the filter all highlighted attribute elements: When a user drills on a report, all relevant elements are included in the SQL for the new report. This generates more SQL and has no effect on report data. In certain reports it may affect processing time.

    • In the example above, if you select this drilling filter option, and then select a year and drill on Quarter, the report filter contains all quarters in the selected year.

  • This advanced setting affects only the SQL used to create the report, and not the report results.

  • Select the drill attributes, which will appear on the destination reports when drilling, and their respective join types.

When you execute a report, data is often retrieved that has come from more than one table in your data source. The final results on any report are greatly dependent on the organization and structure of your data source's tables and how data is stored within the tables.

When data is pulled from two or more tables in your data source, the way that data is combined, or joined, into a single set of data is called a join. The order in which the data is joined from the different tables can affect the outcome of the data calculation, just as the order of operations in any arithmetic expression can affect the result.

For business attribute data, the most common types of joins are inner joins and outer joins. An inner join includes in the data calculation only the relevant data that is common to all the tables being joined, while an outer join includes all of the relevant data in all of the tables being joined.

You can affect how data is calculated on a drilled-to report by setting the type of join for attributes that may appear on the drilled-to report. For any attribute, you can set the join type as inner or outer. For details and examples showing how attribute joins affect report results, see the Advanced Reporting Help.

For examples of all these drilling options, except the drill attributes and join types, see the Basic Reporting Help.

To set drilling options

The options set for drilling in a report play an important role in the latitude for data manipulation that the report has to offer. The procedure that follows describes how to set drilling options for a report. These options override the properties set for a drill path.

  1. Open the report for which to set drilling options. (For steps to run a report, see Running a report.)

  2. Select Report Data Options from the Data menu. The Report Data Options dialog box opens.

  3. Under Categories, expand General, and then select Drilling. General – Drilling appears on the right side of the editor.

  4. Enable or restrict the drilling directions available to the user:

    • To enable drilling in every direction, select Drill anywhere.

    • To enable drilling only from parent level to child level for any attribute on the report, select Drill down only.

  5. Select whether the parent attribute level is to remain displayed while drilling down, using the Keep the parent while drillingoptions. If you select Default, the parent display becomes a function of the attribute being drilled.

  6. Select whether thresholds are still displayed when drilling, using the Keep thresholds while drillingoptions. If you select Default, thresholds are displayed according to the drill path definition.

  7. Select whether subtotals are still displayed when drilling, using the Inherit across-level subtotals from parent option. If you select Default, across-level subtotals are displayed according to the drill path definition. If you select No, across-level subtotals are still displayed on the drilled-to report, unless all of the following are true:

    • Keep parent while drilling is set to No.

    • The user drills from the object that is the level of the subtotal.

  8. For reports which have page-by functionality, select whether to use the page-by fields or any other part of the report in the report filter.

    • To add the page-by element to the filter only when a user drills from a page-by field, select Any page-by field, then select one of the following options:

    • To add the current page-by element to the filter when drilling, select Apply to current page by field. For example, if the page-by is set to State and you drill from Store to Employee when this option is selected, the page-by remains as State.

    • To add the page-by element to the filter when a user drills from all the page-by fields of the report, select Apply to all page by fields.

    • To add the page-by element to the filter when a user drills from anywhere except a page-by field, select Any other part of the report.

  9. Select one of the following Drilling filter options, which allow you to select what elements are included in the filter when drilling:

    • Include in the filter only elements of attributes positioned to the left of or above the selected attribute element(s): When a user drills on a report, only the elements to the left of and above the selection are included in the SQL for the new report. This shortens the SQL required to create the report. This can be important for reports that generate large amounts of SQL so that the SQL does not exceed the maximum allowable length.

    • For example, a report contains the Year and Quarter attributes. If you select a year and drill on Quarter, the report filter contains the year you selected.

    • Include in the filter all highlighted attribute elements: When a user drills on a report, all relevant elements are included in the SQL for the new report. This generates more SQL and has no effect on report data. In certain reports it may affect processing time.

    • In the example above, if you select this drilling filter option, and then select a year and drill on Quarter, the report filter contains all quarters in the selected year.

    This advanced setting affects only the SQL used to create the report, and not the report results.

  10. To specify the join types for attributes that appear when drilling:

    • Click Drill Attributes Join Type. The Drill Attributes Join Type dialog box opens.

    • Select an attribute in the Available attributes list and click > to add it to the Selected attributes list.

    • Click the Join Type of the attribute in the Selected attributes list and select an Inner or Outer join.

    • Click OK when you have specified all the drill attributes and their join types.

    • For details and examples showing how attribute joins affect report results, see the Advanced Reporting Help.

  11. Click OK when you are done. Your changes are saved.

Related topics