Working on Reports in Excel

Import Fully Formatted Reports in Excel

You can import a fully formatted report in an Excel workbook. Importing is useful when you want to import a single report in a workbook, that is one report per workbook. Each subsequent report you select for import will be imported to a new workbook.

If prompts are included in the report, you specify the prompts during import. If previewing POV is enabled for reports, you can select the POV while importing the report.

Reports containing relational tables can be imported in Smart View 24.200 and later using Narrative Reporting extension 24.10 and later.

You can do the following with imported reports:

Change the POV and refresh the report data, as needed.
Edit the prompts.
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 Reports in Excel.

For other ways of importing reports in Excel, see More Ways to Import Reports: Download as Excel and Download as Excel Ad Hoc.

Insert Fully Formatted Reports in Excel

You can insert multiple reports in an Excel workbook. Inserting is useful when you want to insert multiple reports in one workbook or same report multiple times in one workbook. Each subsequent report you select for insert is added to the same workbook.

You can insert the same report multiple times in the same workbook by changing the report's prompt and POV combinations. This is useful when you want to present the same report for differing POV selections, such as time periods, scenarios, entities, or products in the same workbook. Each report gets inserted on a separate sheet within the workbook.

For example, you want to present the expense information from a report for Manufacturing, Marketing, and Sales Departments for FY24. While inserting the expense report, select Manufacturing as the Entity in the Select POV dialog. Once the report is inserted, you select the same report again from the Smart View Home Panel and click Insert Formatted Report. This time, in the Select POV dialog, select Marketing as the Entity. This second report gets inserted in a new sheet within the same workbook. Similarly, repeat the same process to insert the same report a third time by selecting Sales as the Entity in the Select POV dialog. This way, you can get the same expense report with information about three departments on different sheets within the same workbook.

If prompts are included in the report, you specify the prompts during inserting. If previewing POV is enabled for reports, you can select the POV while inserting the report.

You cannot edit prompts in an inserted report. The Edit Prompts action in the Smart View ribbon is disabled. Instead, insert the report again using the Insert Formatted Report option and select the prompts you require from the Select Prompts dialog box while inserting the report.

You can do the following with inserted reports:

Change the POV and refresh the report data, as needed.
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 Reports in Excel.

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.

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%.
Sometimes, performing the Analyze operation on a second or a subsequent report sheet may fail. As a workaround, it recommended to perform the Analyze operation on the first sheet.

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