Working on Reports in Excel

You can import reports as ad hoc queries and import or insert them as fully formatted reports from Oracle Fusion Cloud Enterprise Performance Management data sources into Microsoft Excel using Oracle Smart View for Office to perform further operations on them.

Import Report as Ad Hoc Grids

You can perform supported ad hoc operations on the grids, such as pivoting and member selection, directly against the data source. The grids can be saved and then used as sources for embedded content in report package doclets.

See Importing Reports as Ad Hoc Queries.

Import or Insert Reports in Excel Workbooks as Fully-Formatted Reports

You can import a single report or insert multiple reports in a workbook. If previewing POV is enabled for reports, you can select the POV while importing or inserting the report. If prompts are included in the report, you specify the prompts during import or insert.

You can also import reports with relational tables in Smart View.

You can do the following with imported and inserted reports:

  • Change the POV and refresh the report data, as needed.
  • Edit the prompts (for imported reports only).
  • Save and refresh the workbook with the latest report data.
  • Distribute the report to others as Excel files.
  • Generate an ad hoc grid from the report, and then perform further ad hoc operations for the purpose of data analysis.

See Importing or Inserting, and Working with Fully Formatted Reports.

Guidelines on Working with Imported and Inserted Reports in Excel

Note the following guidelines and considerations while working with reports in Excel.

  • There will be some differences between reports imported or inserted in the web and reports imported in to Excel, as described in Differences between Reports and Reports Imported in Excel in Designing with Reports, available on the Oracle Help Center, Books tab, for your Cloud EPM business process.

  • Service Administrators: During report design, you can configure reports to use the Member Selection dialog in prompts or the POV by performing these tasks:

    • Clear the Display Suggestions Only option when defining POV dimensions
    • Do not specify a Choice List when defining prompts

    In both cases, users can launch the Member Selection dialog to select the members to which they have access for the POV and prompts.

  • Service Administrators: For reports with multiple data sources:

    • When a dimension name is unique among the data sources, then the Member Selection dialog can be made available for the POV (clear Display Suggestions Only) and prompts (do not specify a Choice List).
    • When the same dimension name occurs in more than one of the multiple data sources, then a suggestions or a choice list must be defined for the POV and prompts.

  • If Print All Selections is enabled during report design, after importing or inserting reports in Excel workbooks, the worksheet names will reflect the report name followed by the first POV dimension that was enabled for Print All Selections, truncating the sheet name as needed to meet Excel’s 31-character limit.

  • Note that a report may contain a number of grids, charts, text objects and images laid out across one or more pages. All such objects are brought in to the Excel workbook upon import. Text boxes in the report are converted to images in the imported Excel sheet. In some cases, you may need to manually resize the image box in Excel to match the report presentation. To resize an image, use Excel's image formatting tool. Right-click the image and select Size and Properties. In Format Picture, set Scale Height and Scale Width to 100%.

Other Ways to Import Reports: Download as Excel and Download as Excel Ad Hoc

You can also use the "Download as Excel" and "Download as Excel Ad Hoc" commands in your web application to import Reports in to Smart View for Excel, as described in Working with Reports in Smart View in Working with Reports.

Note the following considerations while working with reports downloaded using the "Download as Excel Ad Hoc" command:

  • All non-suppressed and visible data rows and columns in a report's ad hoc grid are also imported in Smart View. Hidden rows and columns in the grid are not included in the resulting Smart View ad-hoc grid. Row or column headings that were hidden in the web application view also appear in the respective dimensions, and are not moved to the POV.
  • Only data rows and columns are imported in Smart View. Non-data details are static and remain unchanged even after a grid is refreshed. So to avoid confusion, all non-data details such as text, formula, separator and notes rows and columns are omitted when importing any ad hoc grids present in reports.