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: