Enhanced Drill Through in Dynamic Reports

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.

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.

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.

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

Drill Through to Translations and Transactions

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

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.

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.

   

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.

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.

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 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.

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.

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.

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.

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.

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.

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.

The Drill Through report is shown in the image below.

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.

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

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.

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

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.

• Reset Settings

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

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.

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.