A Report Authoring Tips

This section provides tips and guidelines for creating effective and timely reports.

Topics

Common Report Authoring Tips

Improve all of your Oracle Fusion Data Intelligence reports with these recommendations and answers to frequently asked questions regarding reporting. The information isn’t exhaustive and is updated regularly with additional information and authoring tips.

Tips for Filters

When applying filters to reports, follow these guidelines:

  • Use dashboard filters instead of workbook or canvas filters for user interactions in analyses.
  • Use workbook filters for hidden and non-interaction filters.
  • To improve the performance of prompts, set the Limit by Values to None in System Settings so that it's applied to all workbooks by default. You can change this setting in specific workbook filters if needed, however the default value of None should remain.
  • When applying a filter on an attribute, use a Code column (when available) instead of a Name or Description column.
  • Always apply the necessary filters first before you start building visualizations to ensure optimal queries are created when you add the metrics required in the visualization.

Tips for Brushing

Disable brushing to improve report performance. See Update Canvas Properties.

Tips for Working with Currency

To display the currency format for currency amounts, set the number format in each visualization as a custom currency. Then in the subject area folder, select the currency column. See Set Currency Symbols for Visualizations.

Tips for Reporting on Attributes Across Multiple Dimensions

When reporting on attributes against multiple dimensions, always use a measure in the report. In subject areas with more than one fact, the measure sets the correct context in the query, builds an accurate navigational path, and returns the expected results. If you don’t want the measure to show in the report, hide it. See Hide or Delete a Column.

Tips for Presentation Hierarchies

To improve performance, use presentation columns instead of presentation hierarchies. See Work with Presentation Tables and Columns.

Report Authoring Tips for Oracle Fusion SCM Analytics

Improve your Oracle Fusion SCM Analytics and Fusion PROC Analytics reports with these recommendations and answers to frequently asked questions regarding reporting. The information isn’t exhaustive and is updated regularly with additional information and authoring tips.

Tips for Querying on the Common Fiscal Calendar Dimension

Select a Fiscal Calendar to analyze Purchase Order, Spend, Requisition, and Receipt to avoid aggregating data across different Fiscal Calendars.

Tips for reporting on the SCM — Manufacturing Work Orders Subject Area

The Work Order Subject Area is at different levels: Work Order and Operations.
  • The granularity of the subject area is at the Operations level.
  • The Metrics folder has measures at Work Order and Operations.
When qerying on this subject area, use as a Time filter for better performance.

Tips for Querying on Balancing Segment, Natural Segment, Cost Center, and GL Segment 1 - 10 in relevant Supply Chain and Procurement Subject Areas

When reporting on qualified or non-qualified segments, use the segment code attributes instead of the name or description to improve performance.

Tips for Querying on SCM — Inventory Valuations

Use Cost Organization, Cost Book, Valuation Unit, and Valuation structure filters to ensure better performance when using a fiscal calendar filter.

Tips for Querying on SCM — Item Cost

Use Cost Organization, Cost Book, Valuation Unit, and Valuation structure filters to improve performance when using a fiscal calendar filter.

Tips for Querying Across Subject Areas

When joining two subject areas in a report, use at least one attribute from a common dimension.

This Bus Matrix shows the Conforming Dimension for SCM subject areas. Review the spreadsheet before creating a cross subject area analysis.

Recommended Filters

Items with an asterisk (*) are required.

Subject Area Presentation Table (Recommended Filter)
Enterprise Contracts - Contracts
  • *Contract Business Unit.Contract Business Unit
  • Name
  • *Time.Year/Quarter/Month
SCM - Cogs and Gross Margin
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Cost Organization Book Details. Cost Organization
  • *Cost Organization Book Details. Cost Book
  • *Business Unit.Business Unit
  • *Inventory Organization.Inventory Organization
  • Cost Organization Book Details.Cost Book Effective Start Date
  • Cost Organization Book Details.Cost Book Effective End Date
SCM - Cost Accounting
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • Legal Entity. Legal Entity Name
  • *Cost Organization Book Details. Cost Book, Cost Organization
SCM - Draft Sales Orders
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Sales Business Unit.Sales Business Unit
  • *Inventory Organization.Inventory Organization
  • *Unit of Measure.UOM
SCM - Draft Sales Orders Holds
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Sales Business Unit.Sales Business Unit
  • *Inventory Organization.Inventory Organization
SCM - Inventory Aging
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization. Inventory Organization Name
  • Unit of Measure. Item Primary UOM
  • Business Unit. Business Unit
SCM - Inventory Balances
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization. Inventory Organization Name
  • *Unit of Measure. Item Primary UOM
  • Inbound Details. Document Type
SCM - Inventory Receipts
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization. Inventory Organization
  • *Unit of Measure. Item Primary UOM
SCM - Inventory Transactions
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization. Inventory Organization Name
  • *Unit of Measure. Item Primary UOM
  • Item Category.Category
  • Subinventory
SCM - Inventory Turns
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization. Inventory Organization Name
  • *Unit of Measure. Item Primary UOM
  • *Business Unit.Business Unit Name
SCM - Inventory Valuations
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Cost Organization Book Details. Cost Organization
  • *Cost Organization Book Details. Cost Book
  • *Cost Valuation Unit. Valuation Unit
  • Cost Valuation Unit. Valuation Structure Type
  • Valuation Details.Cost Method
SCM - Item Cost
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Cost Organization Book Details. Cost Organization
  • *Cost Organization Book Details. Cost Book
  • *Cost Valuation Unit. Valuation Unit Cost Valuation Unit. Valuation Structure Type Inventory Item. Item
  • Cost As-of Date. Cost As-of Fiscal Date/Cost As-of Fiscal Year/ Cost As-of Fiscal Quarter/ Cost As-of Fiscal Period
SCM - Maintenance Materials
  • *Time
  • *Manufacturing Organization
SCM - Maintenance Operation Transactions
  • *Time
  • *Manufacturing Organization
SCM - Maintenance Resources
  • *Time
  • *Manufacturing Organization
SCM - Maintenance Work Orders
  • *Time
  • *Manufacturing Organization
SCM - Manufacturing Materials
  • *Time
  • Manufacturing Organization (Don't select No Value from list)
SCM - Manufacturing Operation Transactions
  • *Time
  • Manufacturing Organization (Don't select No Value from list)
SCM - Manufacturing Resources
  • *Time
  • Manufacturing Organization (Don't select No Value from list)
SCM - Manufacturing Work Orders
  • *Time
  • Manufacturing Organization (Don't select No Value from list)

Note:

You should have only a few values in the mandatory filters to improve performance. Removing all the values or selecting all may cause performance issues. Don't turn off mandatory filters in seeded workbooks.
SCM - Picking and Shipping
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • * Business Unit. Business Unit
  • *Inventory Organization. Inventory Organization
  • Order Details. Sales Order
  • Catalog.Catalog
  • Item Category.Category
SCM - Receipt Accounting
  • *Time.Fiscal Calendar Name
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period Legal Entity.Legal Entity Name
  • Source Organization. Source Organization Name
  • *Business Unit.Business Unit
SCM - Sales Orders
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Sales Business Unit.Sales Business Unit
  • *Inventory Organization.Inventory Organization
  • *Unit of Measure.UOM
SCM - Sales Orders Holds
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Sales Business Unit.Sales Business Unit
  • *Inventory Organization.Inventory Organization
  • Hold Active
  • Hold Source Entity
SCM - Transfer Orders
  • Destination Organization .Destination Organization Name
  • Source Organization. Source Organization Name
  • Inventory Item. Item
  • Transfer Order Details. Transfer Order
  • *Time. Fiscal Calendar Name/Fiscal Year/Fiscal Quarter/Fiscal Period
SCM - Work Order Costing
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Legal Entity. Legal Entity Name
  • *Cost Organization Book Details. Cost Book, Cost Organization
Sustainability - Activities
  • *Time.Fiscal Calendar/Fiscal Year/Fiscal Quarter
  • Legal Entity.Legal Entity Name
  • Activity Details.Activity Type
  • Activity Details.Scope
WMS – Inbound
  • *Time.Year/Quarter/Month
  • *Company.Company Name
  • *Unit of Measure.UOM Name
  • *Facility.Facility Name
WMS - Inventory Onhand
  • *Time.Year/Quarter/Month
  • *Company.Company Name
  • *Unit of Measure.UOM Name
  • *Facility.Facility Name

Report Authoring Tips for Procurement Analytics

Improve your Oracle Fusion Data Intelligence PROC reports with these recommendations and answers to frequently asked questions regarding reporting. The information isn’t exhaustive and is updated regularly with additional information and authoring tips.

Tips for Querying on Spend Classification Categories in Procurement - Purchase Orders, Procurement - Requisitions, or Procurement - Spend

Select only one taxonomy dimension to analyze the fact metrics in one report to avoid joins across large data volume fact tables.

Recommended Filters

Items with an asterisk (*) are required.

Subject Area Presentation Table (Recommended Filter)
Procurement - Accrual Balances at Period End
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization: Inventory Organization
  • *Business Unit. Business Unit
Procurement - Accrual Balances at Receipt
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Inventory Organization: Inventory Organization
  • *Business Unit. Business Unit
Procurement - Agreement Change Orders
  • *Time.Year/Quarter/Month
  • *Agreement Procurement Business Unit.Agreement Procurement Business Unit Name
Procurement - Agreements
  • *Agreement Procurement Business Unit.Agreement Procurement Business Unit Name
  • Document Details.Agreement Document Type
  • Supplier.Supplier Name
  • Purchasing Category.Purchasing Category
  • *Time.Year/Quarter/Month
  • Buyer.Buyer
Procurement - Purchase Order Change Orders
  • *Time.Fiscal Calendar Name (Single Selection)
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Procurement Business Unit. Procurement Business Unit Name
Procurement - Purchase Orders
  • Requisition Business Unit.PO Header Requisition Business Unit Name
  • *Time.Fiscal Calendar Name (Single Selection)
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Procurement Business Unit. Procurement Business Unit Name
  • Buyer.Buyer
  • Purchasing Category.Purchasing Category
  • Unit of Measure.UOM (if quantity metrics are selected in the report) (single selection)
Procurement - Receipt Accounting
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • Legal Entity. Legal Entity Name
  • Inventory Organization: Inventory Organization
  • *Business Unit. Business Unit
Procurement - Receipts
  • *Procurement Business Unit. Procurement Business Unit Name
  • Supplier. Supplier Name
  • *Time. Fiscal Calendar Name
  • *Time. Fiscal Year/Fiscal Quarter/Fiscal Period
  • Buyer.Buyer
Procurement - Requisitions
  • *Requisition Business Unit. Requisition Business Unit Name
  • Supplier.Supplier Name
  • Purchasing Category.Purchasing Category
  • *Time.Fiscal Calendar Name (Single Selection)
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period
  • Buyer.Buyer
  • Purchasing Category.Purchasing Category
  • Unit of Measure.UOM (if quantity metrics is selected in the report) (Single Selection)
Procurement - Sourcing - Negotiations
  • *Time.Fiscal Calendar Name (Single Selection)
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Procurement Business Unit. Procurement Business Unit Name
  • Purchasing Category.Purchasing Category
Procurement - Sourcing - Responses and Awards
  • *Time.Fiscal Calendar Name (Single Selection)
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period
  • *Procurement Business Unit. Procurement Business Unit Name
  • Purchasing Category.Purchasing Category
Procurement - Spend
  • *Payables Invoicing Business Unit.Payables Invoicing Business Unit Name
  • Supplier.Supplier Name
  • *Ledger.Ledger Type Name
  • *Time.Fiscal Calendar Name (Single Selection)
  • *Time.Fiscal Year/Fiscal Quarter/Fiscal Period
  • Buyer.Buyer
  • Purchasing Category.Purchasing Category
  • Unit of Measure.UOM (if quantity metrics is selected in the report) (Single Selection)

Cross-Subject Area Report Authoring Tips

Oracle Fusion Data Intelligence isn’t limited to one area of information. It allows you to combine data from more than one subject area, opening up a world of possibilities for analysis. Such queries, referred to as cross-subject area analysis, are a testament to the comprehensive nature of the platform. The following section discusses different types of cross-subject area analyses and best practices for building cross-subject area analysis, giving you the confidence to explore the full potential of the platform.

There are three broad categories for building cross-subject area analyses:

Conformed, or common, dimensions have the same meaning and value across different fact tables or subject areas, meaning, they are common dimensions across all dimensions. For example, Ledger is a conforming dimensions across all the Fusion SCM Analytics subject areas.

Non-conformed, or non-common, dimensions are dimensions that aren’t attached to all the fact tables or subject areas. For example, the SCM - Manufacturing Work Orders subject area has Work Order Supplier Operations folders that contain information which is only specific to the SCM - Manufacturing Work Orders and isn’t relevant to other subject areas.

Cross-Subject Area Analysis Using Conforming Dimensions

You can create a visualization from multiple subject areas using facts and confirming dimensions from all the subject areas. There are clear advantages to building a visualization that only uses conforming dimensions from across subject areas. You can use any metric from any subject area in your report and join on conforming dimensions. This allows you to include metrics from multiple subject areas in a single visualization.

Always follow the best practices mentioned in Common Report Authoring Tips.

General Guidelines

  • If all the required metrics and attributes for the report are available in a single subject area and fact, use that single subject area only and don’t create a cross-subject area query.
  • When you want to bring the data from more than one subject area, you must choose metrics from all the subject areas in the analysis.
  • Start with the necessary filters before you start building visualizations to ensure you use the best performing queries when you add the necessary metrics required in the visualization.
  • Always start by selecting all the columns in one subject area, including the facts and dimensions, and then add the facts from the second subject area.
  • Always start by adding the Fiscal Calendar Name and Time Dimensions filters first. Restrict the data for one period, and then build on to the report by adding facts and columns one-by-one from one or more subject areas.
  • When joining two subject areas in a report, use at least one attribute from a common dimension. Refer to the bus matrix for common (conforming) dimensions.

See Bus Matrix for the list of conforming dimensions for Fusion SCM Analytics.

Cross-Subject Area Analysis Using Conforming Dimensions and Non-Conforming Dimensions

You need to study the subject areas you’re using when you create cross-subject area analyses using common (conforming) and non-common (non-conforming) dimensions in a single report. Each subject area has a fact and each fact has a transactional grain, so you need to review and understand the transactional grain of each subject area you use. See Subject Areas.

After reviewing the transactional grain of the subject areas, follow these guidelines to create your report.

  • First, analyze the structure of the subject areas and the type of report that you are planning to create.
  • Start by creating separate reports for the subject areas that you want to combine by adding the necessary metrics and the dimensions in the necessary reports.
  • Add more filters to reduce the data scope to understand and analyze the transaction grain of both, or all, the reports.
  • After analyzing, you can start by choosing one report and start adding non-conforming dimensions one by one from the other reports.
  • Review the logical and physical queries at each step. Understand how to construct a logical query and how to join two logical queries on common dimension attributes. See Expression Editor Reference.

Challenges with Conforming and Non-Conforming Dimensions

Creating reports with conforming and non-conforming dimension can cause two types of issues:

  • Report errors
  • Unexpected results

To work around these issues, perform these steps:

  1. Add expression filters in the report to force a specific join path. Oracle Analytics supports many types of filters to focus on the most interesting data in visualizations, canvases, and workbooks. Expression filters allow you to create complex filters using SQL expressions. For example, you can create an expression filter in to join a non-confirming attribute from one subject area to the non-conforming attribute of another subject area. See Filter Types.
  2. Use action links. You need to break up the report with conforming and non-conforming dimensions into two separate reports. Add the reports to separate canvases in the same workbook or create separate workbooks and use a data action to link them together. A data action link passes context values as parameters to other workbooks or visualizations. You can use the data action to drill from one subject area to another. This creates an interactive way to review the content of the reports without having to join them together. Data actions are often required to move from one report to another, especially when you can’t join both reports. See Use Data Actions.

Combining Subject Areas Using Union Operators

You can create an analysis by combining data from one or more subject areas using union operators. To combine the data from one or more subject areas using union operators, you create datasets from local subject areas stored in your Oracle Analytics instance. See Create a Dataset from a Local Subject Area.

To create the datasets from local subject areas, you can drag and drop the subject areas and select the columns. Or you can copy the logical SQL from an existing report and create local subject areas based on the local SQL. Use this option to create the logical SQL queries using union operators.

General Guidelines

  • Start by creating separate reports for the subject areas that you want to combine by adding the necessary metrics and the dimensions in the necessary reports.
  • Analyze the local SQL statements in the logs of individual reports and use them to create a dataset.
  • Always have the necessary filters before you finalize the logical SQL statements to ensure the queries are optimized when you build the dataset and add the metrics required in the visualization.
  • To simplify troubleshooting, add an additional field to identify which part of the local SQL the data is coming from.
  • For optimized performance, limit the amount of the data that is brought in to the dataset.

Bus Matrix

This Bus Matrix shows the conforming dimension for Fusion SCM Analytics subject areas. Review the spreadsheet before creating a cross-subject area analysis.