Enhanced Drill Through in Dynamic Reports
  • 15 Minutes to read
  • Dark
    Light
  • PDF

Enhanced Drill Through in Dynamic Reports

  • Dark
    Light
  • PDF

Article summary

Traditionally, the Reports section in the Planful application supports two types of drilling capabilities which are as follows:

  • Drill Down 
  • Drill through

Drill Down

Drill down refers to the hierarchical drilling capabilities within a given report. Using this capability, you can expand a line on the report to see data for lower-level members in the main/alternate/attribute hierarchy.

The following are different examples where you can use the drill-down capabilities in a report to view further information in it:

  • To view any Time dimension data - For example, if you expand 2017, you can view data for the 4 quarters associated with 2017. If you expand each quarter you can view data for each month.
  • To view any Variable data - If you expand a variable, such as @CURYR@, you can view data for the 4 quarters associated with the current year variable. You can expand the quarters to view data for each month.
  • Drill down a simple Rule in a Report Set used in a Report. For example, you define a line as follows:
    sum({4010 - Gross Sales , 4020 - Royalties , 4030 - Indirect Sales , 4100 - Intercompany Sales - Mfg})
    You can drill down to view data for 4010, 4020, 4030, and 4100.
    Or, let's say you define a line as follows:
    sum({@CURMTH@ , @CURMTH-1@ , @CURMTH-2@ , @CURMTH-3@ , @CURMTH-4@})
    You can drill down to view data for the current month and the previous 4 months.
Note
A rule represents data elements in the cube similar to rules created in Excel (similar to cells, rows, and columns). Report Sets are built using rules.

Drill Through

You can select any cell with data in it from a Report and select the Drill Through option from the top menu to view transactional data or translational details loaded with Data Load Rules in a drill-through report from single or multiple templates. All Financial dimensions, the corresponding currency, and amounts for each period are displayed in the drill-through report for all modules and templates.

The following are different examples where you can use the drill-through capabilities in a report to view further information in it:

  • You have Net Income on the row and Aug-17 on the column. Click the number and drill to the daily transactional or translations data. This works only if the data is loaded with Data Load Rules.
  • You have Net Income on the row and @CURMTH@ on the column. Click the number and you can drill to the daily transactional or translations data if loaded via Data Load Rules.
  • You have Net Income on the row and sum({@CURMTH@ , @CURMTH-1@ , @CURMTH-2@ , @CURMTH-3@ , @CURMTH-4@}). When you drill through, you can view the transactional or translations data for the current month and the previous 4 months.
  • You have ([Account].&[1068],[Department]. &[101],[Project].&[565]) on the row defined using the Multiple dimension option in Report Sets and MTD on the column. You can drill into the daily transactional or translations data.

Drill-through displays values from the beginning of the year to the current period when YTD or QTD are applied.

Values displayed in the drill-through pop-up window are applicable for all periods, from either the beginning of the current fiscal year or the period selected during report design. For example, if the current month is set to September, drill-through displays the values from January until September if YTD is the Measure applied.

The columns for transactional or translation drill-through data can be sorted in the pop-up window.

Note
Drill through will work if the row and column use simple rule definitions. Suppose multi-dimensional rules are defined using the Advanced Rule page. In that case, the system will not automatically translate the rule to a simple rule definition, and drill through is not opened until you manually convert the advanced rules into simple rules

Enhanced Drill Through

Drill through functionality in Dynamic Reports is available for:

  • Financial Reporting Area

  • Transactions

  • Translations

  • Template lines and sublines

  • Capital details for your budgets and forecasts

  • Workforce Planning data from GL data

  • Data Load Rules data

  • Operational Planning (including Preloaded and Forecast type scenarios)

  • Consolidation data

From a drill-through report, you can view details for a single or multiple templates. All Financial dimensions, the corresponding currency, and amounts for each period are displayed in the drill-through report for all modules and templates. Columns displayed in the drill-through report are dependent on the module and the template accessed by the user. A Measure column is displayed on the Operational Planning, Capital Planning, Workforce Planning, Translations, Consolidation, and GL Data Load tabs. Source dimensions are displayed for Translations and dates are displayed for Transactions. Budget Entity, Template Lines, Sublines, and Notes are displayed in drill through for Planning templates. Empty columns are suppressed.

All values displayed in a drill-through report inherit data format (decimals, thousand separators, scale, etc.) from their parent cell.

Calculated members, calculations, advanced rules, excel formulas, formula exceptions, rank, and pivot are not supported in drill-through reports.

Note:
This functionality is enabled by default for all new and existing customers.

Topics:

Drill Through to Transactions and Translations

Drill Through to Planning and Capital Template Data

Steps on How to Drill Through to Template Data

Drill Through Reporting on Preloaded and Forecast Scenarios Populated with Seeded Data

Drill Through Reporting on Closed Period (History) Data for Forecast Scenarios

Drill Through Reporting on WFP Data Populated from Workforce Planning

Drill Through Reporting on GL Data Loaded via Data Load Rules

Code/Name/Label Display in Dynamic Report Drill Through

Operational Planning Notes Available in Drill Through Report

How to Disable Drill Through Functionality

Important Info on Drill Through

Note:
Template attributes are not displayed in the drill-through report.

Drill Through to Translations and Transactions

Here is an example of drill-through from a Dynamic Report to Translations and Transactions data.

image (22)(1)

The Date Column format in the Transactions tab of the drill-through report is displayed based on your system settings. The date format is retained when you export a drill through report.

Drill Through to Planning and Capital Template Data

Here is an example of drill through to Planning and Capital template data. Header data is displayed. Click Operational Planning or Capital Planning to display associated data. For Capital Planning, Asset Name and Category data is displayed.

image (21)(1)

Label-code

How to Drill Through to Template Data?

To drill through a Dynamic Report to display template data, complete the following steps:

  1. Vlick Reports and open a Dynamic Report.

  2. Double-click on a cell within the Dynamic Report. Another screen appears. The properties of the cell are displayed in the header portion. Data is displayed for the intersection.

  3. The related data for the cell is displayed. Click the Settings icon to select columns to show or hide. Additionally, you can show/hide columns in Operational and Capital Planning where the data will be aggregated based on segment/dimension combinations.

    image (20)(2)

  4. Optionally, export the data to Excel by selecting Output > Export to Excel. Filter the data and select the double up or down arrows to display header details.

    If you want to view the source template, you can click on the hyperlink available in the Doc Ref column of any record in the report to open the corresponding source document template. The Source template opens in a new browser window. The user security for the budget entity and the navigation access permissions to the template are honored while opening the template.

Drill Through Reporting on Preloaded and Forecast Scenarios Populated with Seeded Data

To populate Preloaded and Forecast scenarios, it is likely that you will seed data from another source or sources. Once the scenarios are seeded (populated) with data, you can use the Scenario dimension and the Preloaded and Forecast scenario dimension members in your Dynamic report.

For Preloaded scenarios, Entity, Doc Ref, Lines, and Sublines do not exist so they will not be seeded or displayed in a Dynamic report.

For Forecast scenarios, Doc Ref is set to Data Seeding. Lines and Sublines are displayed if they are seeded from the source scenario (depending on Scenario Setup), otherwise they are empty. If populated, in the Operational Planning tab (shown below), you can optionally turn off Ref Lines and Sublines. The report will be re-rendered to remove associated rows and columns. To turn off Sublines and Ref Lines, uncheck the boxes under Setup (shown in the image below) and click Apply Changes.

image (20)(2)

When you drill on a Preloaded or Forecast scenario, the seeded data is displayed under the Operational Planning or GL Data Loads tab depending on where the data is input from.

For Preloaded scenarios, if the Seed ETL Direct CC Data option (shown below) is selected when adding the scenario, both LC (local currency) and CC (common currency) data are displayed in the drill-through. If not selected, only LC data with currency conversions (if applied during the data load) is displayed.

Access the Scenario Add page by navigating to Maintenance > Scenario > Scenario Setup > Add.

seed-data

Drill Through Reporting on Closed Period (History) Data for Forecast Scenarios

History or Closed Period data is available for Forecast scenarios only and can be drilled upon in Dynamic Report output. The data can be populated from Actual, Budget, Forecast, or Preload scenarios.

When you drill on a Forecast scenario the history/closed period data is displayed under the Operational Planning tab. Doc Ref is set to Closed Periods Data. Entity, Lines, and Sublines are not applicable for Closed Periods.

Drill Through Reporting on WFP Data Populated from Workforce Planning

Workforce Planning data is available under the tab called Workforce Planning in the Drill Through report (as shown below).

workforce

Workforce Planning data can be loaded for Budget and Forecast scenarios. GL segment combination, Home Entity, Position Entity, Employee, Employee Type, Position, Compensation Item, Currency, Month, and Amount are displayed in the Workforce Planning Drill Through report.

Note:
Compensation Items security is always honored when displaying data, ensuring that sensitive information, such as employee salaries, is only visible to users with appropriate access. This means that if an employee is tied to a restricted compensation item, only users with access to that item will be able to see the details, while others will not have visibility of that information. As a result, if you notice discrepancies when comparing the drill-through amounts, it could be because a certain row or rows are hidden due to compensation item security and may be one of the reasons why the amounts don’t fully match.

If you want to view the workforce planning source template, you can click on the hyperlink available in the Home Entity column of any record in the report to open the corresponding workforce planning source document template. The source template opens in a new browser window. The user security for the budget entity and the navigation access permissions to the template are honored while opening the template.

The ability to turn on/off Doc Ref is disabled for the Workforce Planning Drill Through report.

Drill Through Reporting on GL Data Loaded via Data Load Rules

GL Data can be loaded for all Scenario types (Actual, Budget, Forecast, and Preloaded) via Data Load Rules. GL Data loaded using Data Load Rules is displayed under a tab called GL Data Loads in the Drill Through report as shown below.

GLData

GL segment combination, Doc Ref, Currency, Month, and Amount columns are displayed in the GL Data Loads tab on the Drill Through report. The Data Load Rule Label is displayed under the column Doc Ref.

You can turn on/off the Doc Ref. If turned off, data is aggregated for a given segment and currency combination across all Data Load Rules. Click the Settings icon to turn Doc Ref on or off. Select or deselect the checkbox and click Apply Changes. See the example report below.

GLData1

Note:
GL data is displayed in the Drill Through report in addition to Translations and Transactions data.

Drill Through Reporting on Consolidation Data

Drill through to source information on Consolidation data. Data imported from a general ledger or adjustments made using journals or the transformations of data by the Consolidation process can be drilled into using Dynamic Report Drill Through.

When you drill through in a Dynamic Report, you will see a tab called Consolidation as shown below.

conso

As shown in the image above, the default columns displayed for Consolidation include the Chart of Accounts segment combination (in this example; Account, Company, Intercompany, Department, Product, Project), Measure, Reporting, Type, Doc Ref, Line, Currency, Month and Amount.

Cells in the Type column populate from the following; Standard Journals, Dynamic Journals, Non-Controlling Interest Calculations, Reclassifications, Eliminations, Consolidation Process, and MSOB (Multiple Set of Books) Calculations.

Cells in the Doc Ref column populate with labels that correspond with the Type column. For example in the image below, Doc Ref represents the name of a Standard Journal, which is why the Type is Standard Journal.

conso1

The Standard Journals in this case are set up against the US Co 1 company as shown below in the Member Selector screen of the Consolidation Control Panel.

Screenshot (216)

Similarly, if the Type is Elimination, then Doc Ref references the Elimination entry for the account and company.

Cells in the Line column populate for Standard Journals, Dynamic Journals, and Non-Controlling Interest Types. The cells in this column will be empty for all other Types. If two Lines with the same Line Name/Description are posted from a single Journal for a given segment combination, their values are aggregated in the drill-through report.

Turn ON/OFF is enabled for Type and Doc Ref. If turned OFF, data is aggregated for unique segment and currency combinations across all journals and the Consolidation Process.

Reporting Dimension Filters on the Consolidation Tab

You can filter Drill Through data based on Reporting dimensions for the Consolidation tab.
 
For example, If the Reporting dimension is mapped in the report and you filter data based on the Adjustments member, then data related only to the Adjustments member is displayed in the Drill Through report as shown in the image below.

report(1)

The Drill Through report is shown in the image below.

report1

Note:
The Reporting dimension filter is only applicable to the Consolidation tab in the Drill Through report. Also, the filters are visible when you export the Drill Through report output to Excel.

Code/Name/Label Display in Dynamic Report Drill Through

Display drilled through Dynamic Report output by member code, name, or label (code+name). When a Drill Through report is exported to Excel, the code, name, and label configuration are retained across all the tabs. You cannot save the code, name, or label configuration as it is for viewing and export purposes only. Once you navigate away from the Drill Through screen, the default setting is reapplied.

Note:
Label is the default selection.

Label

In the example below, code is selected on the Operational Planning tab.

Label-code

You cannot have one tab display code while the other displays a label, for example. All tabs will display based on the latest selection made on any of the tabs.

The table below provides information on the Drill Through tab name and the related columns to which the code, name, and label selection are applied.

Shared Mode Data in Enhanced Drill Through

For information on how Shared Mode works, please click here.

Now, when Shared Mode is enabled in your application, GL data is displayed under the Operational Planning tab when you’ve drilled through from a Dynamic Report, irrespective of whether the data is posted from the Budget Control Panel or Data Load Rules.

However, when data is loaded using multiple sources within Shared Mode, the data is overridden for a given segment combination resulting is the display of the latest details. This means that the history data is not maintained and Planning templates will be updated with the latest data only after running the Simulation Engine or opening and saving each template.

In applications where Shared Mode is enabled, Entity and Doc Ref/Template columns display as Shared Mode in the Operational Planning tab for the applicable Template Types and Scenarios.

Lines and sublines are displayed if data is posted from the Budget Control Panel. However, the data displayed for each period is pulled from the most recently updated source. If data is uploaded only via Data Load Rules, it is displayed under the Operational Planning tab, but, lines and sublines will be empty and hidden from display.

Operational Planning Notes Available in Drill Through Report

A Notes column is available on the Operational Planning tab in the Dynamic Report Drill Through report. This column displays a Notes icon for a line in Drill Through when the line has a note available in the source Planning template. You can double-click the icon to view the content.

For example, the image below displays a note for a line in the Planning template. When you use the data from the Planning template the note is retained in the Drill Through report.

Notes

The Drill Through report displays the note that is available in the source Planning template.

Notes2

Important Information:
The Notes column is not displayed in the grid if the Ref Line checkbox is not selected.

Ref Line

When you export the Drill Through Dynamic Report, 1024 characters are displayed in the notes.

Drill Through displays up to 250 notes.

Save Settings and Reset Settings in the Drill Through Report

The following options are available in the Drill Through report toolbar:

  • Save Settings

    You can save the following settings applied in the Drill Through report:

    • Code, Name, Label display

    • Turn ON/OFF

    • SHOW/HIDE COLUMNS

    • Order and width of the columns

The settings are retained when you access the Drill Through report from Dynamic Reports and Dashboards.

Note:
You cannot save Sort and Filter modifications in the Drill Through report.
  • Reset Settings

You can reset the settings to default by using this option.

Save and Reset

How to Disable Drill-Through Functionality for a Specific Dynamic Report

You can enable or disable Drill Through functionality for specific Dynamic Reports. To do so: open a Dynamic Report, select the More drop-down menu, and click Disable Drill Through. You can enable Drill Through at any time by opening the More menu and selecting Enable Drill Through.

Note:
By default, Drill Through is enabled.

Important Details:

  • Drill Through is enabled for the Financial reporting area only.

  • Drill Through works with dimensions, report sets, attributes, attribute hierarchies, and alternate hierarchies.

  • For QTD and YTD measures, drill-through data is displayed from the beginning of the period to the current period.

  • Data honors the formatting from the source cell and the user locale.

  • All transaction amounts are displayed in Common Currency irrespective of the reporting dimension applied on the report/source cell. Transaction data exists only for Actual Scenarios.

  • Operational and Capital Planning data is displayed in Local Currency irrespective of the reporting dimension applied on the report/source cell. Operational Planning data exists only for Budget/Forecast scenarios.

  • In the Operational Planning tab, when you turn off Sublines or Lines, the report is re-rendered, which removes the associated rows and columns. If both lines and sublines are turned off, data is aggregated at the Doc Ref (Template) level. If Doc Ref is turned off, data is aggregated for the unique segment and currency combination.

  • In the Capital Planning tab, when you turn off Asset, the report is re-rendered, which removes the rows and columns associated, and the data is aggregated for Asset Category. If Asset and Category are turned off, data is aggregated at the Doc Ref (Template) level. If Doc Ref is turned off, data is aggregated for the unique segment and currency combination.

  • When you export a drill-through report, all sources are exported to Excel. Each source on the drill-through report becomes a tab in the Excel workbook.

  • Metadata is shown in exported Drill Through reports. Metadata includes the data shown in the Drill Through report, the dimensions, and the date and time of report generation.

  • The Drill Through report allows a maximum of 30K rows per source.

Note
Your content goes here You can disable the Drill Through functionality for a specific report. See  How to Disable Drill Through Functionality.

Was this article helpful?