No results found

Your search did not match any results.

Oracle Smart View for Office

Oracle Smart View for Office

Analyze, Update and Report
Oracle Smart View for Office (Smart View) provides a common Microsoft Office interface designed specifically for Oracle's Enterprise Performance Management (EPM) and Business Intelligence (BI). Using Smart View, you can view, import, manipulate, distribute and share data in Microsoft Excel, Word and PowerPoint interfaces. It is a comprehensive tool for accessing and integrating EPM and BI content from Microsoft Office products.

Learn about Oracle's broad selection of application solutions.

Ad hoc or Free-form Analysis for EPM and BI Data in Office

This is for users requiring an environment to have a conversation with the data. Typically they use the Excel environment to interactively investigate the data contained in the source(s). They may start with templates that begin the process (such as East > Cola > Sales) or a blank sheet where they begin shaping and altering the grids of data as they use the exposed functionality. Typically users go after the data from Oracle EPM on-premises sources such as Essbase, Planning or Financial Management using clicks and drag-and-drop. Other data sources supported for ad hoc analysis includes Oracle Business Intelligence Enterprise Edition, and Oracle EPM Cloud sources such as Oracle Planning and Budgeting Cloud.

Predefined Form Interaction

EPM application users that execute predefined input/reporting forms find Smart View a convenient way of completing tasks within the Office environment. These users would be planners, consolidators and the like that have a desire to work in the Excel environment either for consistent experience compared to the web application or to tie other spreadsheet-based models into their process. For example, Pepsi Bottling uses Smart View for Planning in order to incorporate data still housed in spreadsheet and workbook-based models.

Report Design

Reporting is another dimension of Smart View usage which leverages the capabilities of EPM data retrieves. Once the data is available within Office we can create reports as needed based on a combination of data sources. For example Planning and Financial Management data could be used to compare actual to budget. Reports could be made more complex by providing the ability to compare multiple scenarios and different periods. The power of Office can be used to create reports in the Office environment and these reports can be refreshed periodically as needed.

Pre-Created Content Access

Another area of use is for importing pre-created content—for example, importing charts or grids from Oracle Hyperion Financial Reporting and Oracle Business Intelligence Enterprise Edition to PowerPoint, Word, or Excel. The imported content can be refreshed from the Office environment.

Smart View Key capabilities

  • Ability to integrate EPM and BI data directly from the data source into Microsoft Word, Microsoft PowerPoint, and Microsoft Outlook
  • Full ad hoc and free-form analysis capabilities for Oracle Hyperion Financial Management, Oracle Essbase, Oracle Hyperion Planning and Oracle Business Intelligence Enterprise Edition
  • Full ad hoc and free-form analysis capabilities for Oracle EPM Cloud data sources, including Oracle Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, and Oracle Tax Reporting Cloud
  • Data access from Relational or multidimensional sources
  • An alternative, Office-based interface for leveraging EPM on-premises and cloud product data entry forms
  • Ability to create data perspectives for end users called Smart Slices
  • Ability to dynamically refresh EPM and BI data across Office documents with a single click
  • Ability to perform selective, multi-page import of Oracle Hyperion Financial Reporting documents.
  • Point of View (POV) search for efficient, context-based searching
  • Support for highly formatted grids in Microsoft Excel
  • Report creation in Excel, Word and PowerPoint using EPM and BI data
  • Comprehensive VBA toolkit for developing custom Office-based analytic interfaces

Oracle by Example - Smart View for Office

The Oracle by Example (OBE) series provides step-by-step instructions on how to perform a variety of tasks. The Oracle by Example series reduces the time spent investigating what steps are required to perform a task. Because the step-by-step solutions are built for practical real world situations, not only is knowledge gained through valuable hands-on experience, but also the solutions presented may then be used as the foundation for production implementation, dramatically reducing time to deployment.

What's New

Smart View for Office 11.1.2.1.102
Smart View for Office 11.1.2

Creating Free-Form Reports in Smart View by Using Essbase Data Sources

Purpose

This tutorial covers creating a free-from grid in Smart View by using an Essbase data source.

Time to Complete

Approximately 30 minutes.

Overview

In this tutorial, you use free-form reporting to analyze the sales results for diet drinks. You also add Excel formulas and formatting, and save the report as a template to analyze a different line of products in the future.

Software and Hardware Requirements

The following is a list of software requirements:

  • Oracle® Essbase 11.1.1
  • Oracle® Hyperion Smart View for Office 11.1.1
  • Analytic Provider Services 11.1.1
  • Microsoft Office 2007

Note: You can create free-form reports in Smart View using Microsoft Office 2003. However, Microsoft Office 2003 has the Hyperion menu instead of the Hyperion ribbon.

Refer to the Oracle® Enterprise Performance Management System Installation Start Here Guide release 11.1.1.2 for hardware requirements.

Prerequisites

Before starting this tutorial, you should:

  • Have data loaded into the Essbase Sample Basic data source.
  • Have started Analytic Provider Services
  • Know your username and /password to connect to the Sample Basic data source in Smart View.

Note: You can create free-form reports for any data source that Smart View supports. This tutorial uses Essbase as a data source.

Free-Form Reporting Overview

Free-form reporting enables you to extract information from a connected data source by typing member names into the grid. Free-form reporting is very efficient if you are familiar with the database outline. You can also use the following Smart View and Excel options with free-form reporting:

  • Select additional members by using the POV or Member Selection dialog-box
  • Use ad hoc retrieval options, such as zoom in and out; pivot, etc.
  • Use VBA functions to customize and automate worksheets
  • Format member and data cells
  • Create a template that can be shared with other users

Note: Refer to the Oracle ® Hyperion Smart View for Office, Fusion Edition 11.1.1 User Guide for information on the free-form reporting guidelines.

Creating a Free-Form Grid

In the following example, you create a free-form grid to analyze diet drinks sales.

  • Open Excel and connect to the Sample Basic database.
  • Associate Sheet 1 with the Sample Basic database.
  • In Data Source Manager, right-click Sample Basic and select Change Alias Table.
  • Select Default and click OK.
  • .Create the following layout by typing the members directly into the grid. Note the underscore in cells B10 and B11. These cells are label cells.
  • On the Hyperion ribbon, click Refresh.
    The grid is refreshed with the data. Note: if you see numbers for the product name instead of the aliases, click Refresh again.
  • Select the Market dimension members to view the data for different markets:
    On the POV toolbar, click Market, and select the ellipsis. Under Members, select East, West, South, and Central. Click the right arrow button to add these members to the selection list and click OK.
  • Save the workbook without closing it.

Adding Excel Formulas

Smart View enables you to use Excel formulas to calculate data values that are not stored in the database. In the example below, you add Excel formulas to calculate the difference between actual and target sales, and actual and target COGS.

  • In cell C10, type =C4-C7 and press Enter.
  • In cell C11, type = C5-C8 and press Enter.
  • Repeat steps 1 and 2 to calculate budget variances for Diet Root Beer, Diet Cream, and Diet Drinks columns.
  • Save the workbook without closing it.

Applying Excel Formatting to the Grid

You can apply Excel formatting to your grid. For example, you can add background color and borders, add a currency symbol, change the number of decimal places, and apply conditional formatting. In this topic, you format the grid to make it look like the example below:

  • Apply the following formatting to the headings:
    • Actual, Budget, and Actual vs. Budget - bold
    • Sales, COGS, and product names - dark blue color, bold
  • Merge cells 1A-F2 and add Diet Drinks Analysis as the title.
  • Add background color and borders:
    • Add borders below rows 6 and 9.
    • Add a pale orange fill color to cells A4-A11.
    • Add a pale green fill color to cells C4 - F11.
  • Apply the following formatting to the numbers:
    • Currency symbol: $
    • Number of decimal places: 2
    • Negative numbers sample: -$1,234.10
    • Conditional formatting: cells lesser than -$1.00 should be formatted with red text.
  • On the POV toolbar, click Market, select Central from the drop-down list, and click Refresh. The data for the Central region is displayed.
  • Rename Sheet1 to Diet Drinks, and save the workbook without closing it.

Reusing the Grid as a Template

After creating a report that contains your specified formatting and formulas, you can use it as a template to create other reports. In the following example, you use the Diet Drinks worksheet as a template to analyze the Cream Soda sales.

  • Create a copy of the Diet Drinks worksheet and rename it to Cream Soda.
  • Associate the Cream Soda worksheet with the Sample Basic connection.
  • .Select cell C3, and click Member Selection on the Hyperion ribbon.
  • In the Dimension Name Resolution dialog-box, from the Dimensions drop-down list, select Product and click OK. Note: leave the Vertical Orientation option unchecked.
    The Member Selection dialog-box is displayed.
  • Expand Cream Soda. Add Dark Cream, Vanilla Cream, Diet Cream, and Cream Soda to the Selection list (in that order), and click OK.
  • Add Cream Soda Analysis as the title, and change the Dark Cream column heading font to dark blue. You grid should look like the following sample:

Summary

In this tutorial, you have learned how to:

  • Create a free-form grid by typing members into the worksheet
  • Add Excel formulas to calculate data that's not stored in the database
  • Add Excel formatting to your grid
  • Reuse your highly formatted free-form grid as a template

Resources

In this tutorial, you have learned how to: