A Report Authoring Tips
This section provides tips and guidelines for creating effective and timely reports.
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.
- Set the Limit by Values to None for all filters in custom workbooks or dashboards to improve performance of prompts.
- 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 ERP Analytics
Improve your Oracle Fusion ERP 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 to Improve the Performance of Reports
- For qualified or non-qualified segments, use the segment code instead of the name or description.
- For Name and Description of qualified and non-qualified
segments:
- Use List or is equal to/is
- Use filter type List or is equal to/is in for Balancing Segment, Natural Segment, Cost Center, and GL Segment 1 - 10
- For other filter types (e.g. begins with, IS LIKE) use Code.
- For all reports, use code attributes instead of the name or description when possible.
- For qualified or non-qualified segments, use Tree Code and Version instead of Tree Filter in release 23.R4.P3 and 24.R1. Use Tree Filter in release 24.R1.P3 or later. Tree Code and Version is deprecated.
Tips to Improve the Performance of Presentation Hierarchy Reports
The application is designed to support presentation hierarchies from level 22-31. Expose the attribute columns if all levels are needed and avoid presentation hierarchies in reports.
Recommended Filters
Items with an asterisk (*) are required.
| Subject Area | Presentation Columns |
|---|---|
| AP Aging |
|
| AP Expense |
|
| AP Holds |
|
| AP Invoices |
|
| AP Liabilities |
|
| AP Payments |
|
| GL Balance Sheet |
|
| GL Budget |
|
| GL Detail Transactions |
|
| GL Account Analysis |
|
| Configurable Account Analysis |
|
| AR Transactions |
|
| AR Receipts and Applications |
|
| AR Credit Memo Applications |
|
| AR Adjustments |
|
| AR Aging |
|
| AR Revenue |
|
| Asset Analysis |
|
| Asset Transactions |
|
| EXM Employee Expenses |
|
| GL Profitability |
|
Report Authoring Tips for Project Analytics
Improve your Oracle Fusion Data Intelligence PPM 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 to Improve the Performance of xTD Metrics Queries
Select a Fiscal Period, Fiscal Quarter, or Fiscal Year along with a Project Business Unit to analyze xTD metrics for a specific subject area. To improve performance on cross-subject area analyses involving xTD metrics, apply a filter on the workbook.
Tips to Improve the Performance of PPM – Project Costs Reports
To improve report performance in PPM - Project Costs, follow these guidelines:
- Apply a filter on Ledger and Distribution accounting periods when analyzing accounting information that includes details about unaccounted transactions.
- When analyzing accounting information, use Code instead of Name or Description for qualified and non-qualified segments.
Tips to Improve the Performance of PPM – Project Revenue Reports
To improve report performance in PPM - Project Revenue, follow these guidelines:
- Apply a filter on Ledger and Distribution accounting periods when analyzing accounting information that includes details about unaccounted transactions.
- When analyzing accounting information, use Code instead of Name or Description for qualified and non-qualified segments.
Tips to Improve the Performance of PPM – Grants Award Funding Reports
To improve report performance in PPM - Grants Award Funding, follow these guidelines:
- Apply a filter on Award Business Unit for analyzing Award Funding information.
- When analyzing project level funding information or other Project metrics like Cost, Budget Amount etc., use the Project Business Unit filter.
Recommended Filters
Items with an asterisk (*) are required.
| Subject Area | Presentation Table (Recommended Filter) |
|---|---|
| PPM - Grants Award Funding |
|
| PPM – Project Assets |
|
| PPM – Project Budgets |
|
| PPM – Project Costs |
|
| PPM – Project Forecasts |
|
| PPM – Project Funding |
|
| PPM – Project Invoices |
|
| PPM - Project Labor Distribution Costs |
|
| PPM - Project Labor Schedules |
|
| PPM – Project Revenue |
|
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 |
|
| Procurement - Accrual Balances at Receipt |
|
| Procurement - Agreements |
|
| Procurement - Purchase Orders |
|
| Procurement - Receipt Accounting |
|
| Procurement - Receipts |
|
| Procurement - Requisitions |
|
| Procurement - Spend |
|
Cross-Subject Area Report Authoring Tips
Oracle Fusion Data Intelligence 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 Analysis and best practices for building cross-subject area analysis, giving you the confidence to explore the full potential of the platform.
- Using conformed dimensions only
- Using conformed and non-conformed dimensions
- Combining more than subject area using union operators
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 ERP 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 AP Holds subject area has hold folders that contain information which is only specific to the Holds subject area and isn’t relevant to other Accounts Payable 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 at the Common 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 Accounting Calendar 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 bus matrix for common (Conforming Dimensions).
See appendix-report-authoring-tips-erp.html#GUID-AC96F315-4385-49F8-9EB2-7ECFCB15AB64__GUID-A743EDCB-18E8-4BFD-97CC-2B6B38F09408 for the list of conforming dimensions for Oracle Fusion Data Intelligence.
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:
- 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 filter sallow 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.
- Use Action Links. It’s all supposed to be one to break up the report with conforming and non- conforming dimensions into two separate reports. Add the reports to separate canvas 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 SQLs 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 Matrixthat shows the Conforming Dimension for Subject Area Financial Subject areas. Review the spreadsheet before creating a Cross Subject Areas analysis.