Performing Ad Hoc Analysis Using Oracle's Hyperion Smart View for Office in a Windows Environment

In this tutorial, you learn how to leverage Oracle's Hyperion Smart View for Office (Smart View) to retrieve, analyze, and report against an Oracle's Hyperion Essbase System 9 (Essbase) multidimensional cube and an Oracle Business Intelligence (Oracle BI) Suite Enterprise Edition (EE) Plus relational data source to create robust Enterprise Performance Management (EPM) applications on your desktop.

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Smart View provides a common Microsoft (MS) Office interface for Oracle Essbase, Oracle Hyperion Financial Management, Fusion Edition, Oracle Hyperion Planning, Fusion Edition, Oracle Enterprise Performance Management Workspace, Fusion Edition, Oracle Business Intelligence Suite Enterprise Edition, and Oracle's Hyperion Enterprise data. Using Smart View, you can view, import, manipulate, distribute, and share data in MS Excel, MS Word, and MS PowerPoint.

This lesson provides the following:

Back to Topic List

In this OBE, you learn how to connect to Provider Services and access two data sources, using Data Source Manager within Smart View and manipulate the analytical data inside MS Office tools—specifically, MS Excel, MS Word, and MS PowerPoint. Additionally, you learn how to use cascading, Smart Slices, and Report Designer to slice and dice reports.

The Smart View Data Source Manager is a multifunctional feature that allows you to connect to a centralized server via a URL, where the defined data sources are accessed and showcased through the UI of the Smart View client. In addition to managing data source connections, you use it to create and manage Smart Slices and queries, open data forms, or initiate ad hoc analysis. All connections to the data sources are organized by product area. A central connection list is initially created by an administrator through Provider Services.

Smart View provides the Microsoft Office interface within which you work with the data that is contained in your data source. A data source contains the database for the application or Reporting and Analysis content repository for which you want to access data by using Smart View. How you connect to your Smart View data sources and manage the connections depends on the data source type that you use. Essbase, Planning, and Oracle BI EE Plus data sources are connected through the common provider. Other user-defined connections for individual providers such as Financial Management, Reporting and Analysis, and Hyperion Enterprise data sources are connected directly or through Oracle's Hyperion Shared Services via the Connection Manager, where connections are also managed.

Back to Topic List

The following is a list of software requirements:

Back to Topic List

Before starting this tutorial, you should:

1.

Ensure that the following components are installed and configured properly:

Smart View

Because Smart View is an add-in to MS Office products, you can install only on Windows platforms. To view imported data within MS Office, each user must install Smart View.

How you connect to your Smart View data sources and manage the connections, depends on the data source type that you use. Smart View 11.1.1 has the following compatibility matrix:

Smart View 11.1.1 compatibility with data sources connected through Provider Services 11.1.1:

 

Data Source

Release

Essbase

11.1.1
9.3.1
9.2.x

Oracle BI EE

10.1.3.3.1 and above

 

For all other data sources and additional installation instructions, refer to the Oracle Hyperion documentation library.

Oracle Business Intelligence Enterprise Edition 10.1.3.3.1 and above

Components can be downloaded from here.

 

 

 

 

 

2.

Ensure that you have the proper permissions for installing and configuring Smart View on your company's system

3. Ensure that MS Office is installed (versions listed above in Software Requirements)
4. Ensure that MS Windows is installed (versions listed above in Software Requirements)
5.

Ensure that Java SDK 1.5.0 or later is installed

6.

Ensure that your screen resolution is set to at least 1024x768

7.

Have access to or have installed the default SampleSales schema

You can install the SampleSales schema by following the instructions found in the Installing the Sample Schemas and Establishing a Database Connection OBE tutorial.

The SampleSales schema is located in <installdrive>\OracleBI\server\Sample\ and can be installed as directed in the above-annotated OBE.

8. Identify the server location of your data sources (contact your local administrator) and ensure that the Demo/Basic and Sample/Basic databases provided during installation have been installed
 

Notes:

Depending on how Smart View was configured by the administrator, you may or may not be required to enter your username and password when you initially enter the data provider.

Screen captures for this tutorial were taken in a Windows XP Professional environment, using Office 2003; therefore, Start Menu options and toolbars within Office will vary.

This OBE uses Smart View 11.1.1 and Oracle BI EE 10.1.3.4.

 

 

Back to Topic List

If you need additional information, consult the following:

Back to Topic List

Using Connection Manager, you can add, delete, and modify data source connections. These connections are not specific to worksheets and you can establish multiple connections per Excel instance. A connection is a communication line between the user and data source regardless of whether the data source is connected to or disconnected from the Provider Server.

This topic describes how to access Data Source Manager and connect to the Provider Services URL.

1.

After installation, Smart View is automatically enabled for Excel, Word, and PowerPoint. The Hyperion menu appears in the menu bar; in Excel, the Smart View toolbar is displayed by default.

From the Start menu, select All Programs > Microsoft Office > Microsoft Excel 2003. The Office interface for Smart View includes the Hyperion menu, the point of view (POV), and Smart View toolbars. The Hyperion menu is available in Excel, Word, and PowerPoint., but the toolbars are available only in Excel.

If this is your first time accessing Data Source Manager, you must supply the administrator-defined URL to Provider Services. Select Hyperion > Options. The Options dialog box appears.

In the Hyperion Provider Service URL, Default URL text box, enter the URL for Provider Services. The server and port number represent the location where the actual software has been deployed for Provider Services, running on a specific machine. This URL is in the following format:

http://<host>:<port>/aps/SmartView

Example: http://ab-123.hyperion.com:13080/aps/SmartView

The Smart View Data Source Manager panel appears.

Note: If this is not your first time accessing Data Source Manager, simply select Hyperion > Data Source Manager to connect to the default URL.

 

2.

In the Smart View Data Source Manager panel, click Connect to Provider Services. The list of supported Product Areas appears in the docked panel.

The Data Source Manager displays an expandable tree list of available data sources nested as follows:

 

3.

Double-click Oracle Essbase to select the data source type for ad hoc analysis.

 

4.

During any given Smart View session, the first time you access a server, you must provide your user credentials.

Double-click the server name where Demo/Basic and Sample/Basic are installed at your organization.
Note:
In this OBE, the sample databases are installed on ajindal2.hyperion.com.

The Connect to Data Source dialog box appears. Enter your username and password for authentication and click Connect.

The connection is successfully established and a list of applications appears.

Note: The Views () icon, located on the Smart View Data source Manager toolbar, allows you to toggle between the following data sources:

View Type

Data Sources

All

All data sources that you can access

Predefined

A view that is built by the administrator and applicable for all users. This view provides a filtered set of data sources.

Favorite

A list of data sources that you define. You can filter out only those sources that you wish to view. This view is similar to the "favorites list" in a browser.

 

5.

In this step, you select the database against which you want to perform ad hoc query analysis. Double-click Demo.

The Basic cube appears. Right-click Basic and select Ad-hoc Analysis from the shortcut menu.

When you retrieve data into an empty worksheet, Essbase returns the top-level data, also known as the default ad hoc grid. The default grid layout is specific to the database outline. For this example, Year and Market (the first and second dimensions in the Basic outline) are the default row and column dimensions, respectively. The other dimensions are represented on the POV toolbar. You can use the top-level data as a starting point to drill down into levels of detailed data.

The following table provides a description of Smart View components:

Smart View Excel Interface

Data Sources

Hyperion menu

Contains commands that enable you to view, navigate, and analyze Essbase data. For example, the menu functions enable you to connect to a database, drill down into data, change the point of view, and calculate the database.

Smart View toolbar

Provides convenient buttons for accessing Smart View commands without opening the Hyperion menu. You can customize the toolbar so that it contains only the buttons relevant to Essbase. The customizing list displays a toolbar button menu. You can select frequently used toolbar buttons from the list.

POV toolbar

Displays dimensions that do not display in columns or rows of a data grid. The POV toolbar identifies a slice of the database for a grid. For default ad hoc grids, all but two database dimensions display in the POV toolbar at the dimension level.

 

Back to Topic List

Now that you have successfully accessed your first data source, you are ready to start using Smart View as a query tool against an Essbase cube. Ad hoc grids are used to analyze data from a data source. An ad hoc query is a request to the database server to search its data for specific information or results. Ad hoc queries enable you to view data for specific dimensions from the data source without writing spreadsheet functions.

Smart View provides menu commands that you can use to create ad hoc analysis reports (ad hoc grids). This table lists several of the common tasks used to create ad hoc grids:

 

Creating a Smart View Query in Excel
Creating a Smart Slice in Excel
Creating a Report Using Report Designer
Using a Smart View Query with PowerPoint
Using a Smart View Query with Word

Creating a Smart View Query in Excel

This subtopic shows you how to manipulate the default ad hoc grid to create a query by using an Essbase data source.

1.

When the default ad hoc grid appears with the top-level data, you can begin your investigation. You can drill down and pivot your data and use all of Excel's robust data manipulation against your grid. You can select the dimensions and members for the rows and columns of the ad hoc grid. When selected, the grid updates with the new dimension members.

A. Double-click B1 (Market) to drill down into the Market dimension.

B. Double-click B1 (East) to drill down into the Eastern region and select New York, Boston, and Chicago.

Hint: To select multiple cells, press and hold down the Ctrl key and click each cell.

 

2.

With the Eastern region cells still selected (B1 through D1), click the Keep Only () icon to keep just the selected cells and their corresponding data. The grid should look like this:

 

3.

You use the Member Selection dialog box to select and change dimension members on your grid. You can filter the list of members and you can view labels or descriptions for dimension members.

A. Select A2 and click the Member Selection () icon. The Member Selection dialog box appears for the member that is currently selected on the grid, namely Year.

B. The Filter drop-drown box allows you to selectively limit the data displayed on your grid. Click the Filter drop-down list and select Children.

C. Click the Select () icon to select the children of Year (the four quarters) and click the Add () icon. The Member Selection dialog box should look like this:

Note: If Year appears in the selection pane, click Year and then click the Remove () icon.

D. Click OK.

E. Click the Refresh () icon to view the data.

 

4.

The POV menu allows you to add dimensions and attributes to the grid.

Note: You can anchor this menu to create a toolbar.

A. Click Product inside the POV menu and drag it to the grid, placing it on top of New_York.

B. Click the Pivot () icon to move it to the row.

C. With A2 still selected, click the Zoom In () icon to drill into Products for the Eastern region.

 

5.

A. Right-click any Product member name that contains data and drag it back to the POV menu. This removes Product from the grid.

B. Select the entire Smart View grid.

C. Click the Chart () icon. The Chart Wizard appears. Develop the chart as follows:

Chart Wizard Step

Chart Wizard Page Name Wizard Detail

1

Chart Type

Chart type: Column
Chart subtype: Clustered Column

2

Chart Source Data

Accept all defaults.

3

Chart Options

Chart title: Regional Sales
X Axis label: Time
Y-axis Label: Dollars

4

Chart Location

As object in: Sheet1

D. Click Finish to add the chart to your grid.

Save this workbook as Book1. This workbook will be used in a subsequent lesson.

 

Back to Topic

Creating a Smart Slice in Excel

This subtopic shows you how to create a Smart Slice. Smart Slices are created by an administrator and stored in the Data Source Manager where they are available to all users with the proper privileges.

A Smart Slice is a reusable perspective of an Essbase, Planning, or Oracle BI EE data source. Available through the Provider Services, it is composed of a single member, a combination of single members, filters, or a combination of single members and filters in any order. These components serve as boundaries to data that you can view and work with in the Smart Slice.

You can do anything in a Smart Slice that you can do in Smart View—perform ad hoc analysis, use free form, submit data, and so forth. You can view and work with any data within the restricted set of dimensions, but not with data outside its boundaries. For example, in a Smart Slice that limits sales data to the Western region, you can drill down to data for California or Los Angeles, but you cannot drill across to New York.

The following table provides a list of benefits gained by using Smart Slices:

Note: Smart Slices are particularly useful as the basis for creating queries in Report Designer and will be used in a subsequent subtopic.

1.

Creating Smart Slices involves setting Smart Slice data boundaries and setting Smart Slice
preferences. This first series of steps sets Smart Slice data boundaries.

Open a new workbook. In the Data Source Manager, select the Basic data source.

 

2.

Click the Add () icon.

The Select Alias Table dialog box appears.

Aliases are alternate names for database members. You can perform ad hoc retrievals by using the database member name, which is often a stock number or a product code, or an alias, which can be more descriptive. Each Essbase database to which you connect can contain up to 10 alias tables.

 

3.

From the Select Alias Table, select Default and click OK.

The Smart Slice Designer interface appears.

The Smart Slice Designer interface allows you to create a logical definition for the selected data source. You can select columns and rows, add filters by selecting family lineage and so forth, add POV members, and set specific options (preferences) for your data.

Drag DESCENDANTS([Product]) from the POV and place it directly on DESCENDANTS([Period]).The grid should look like this:

 

4.

A. Select the column header C, DESCENDANTS([Market]) and click Market in the POV to choose only the members that you want to see in the columns of your grid. The Member Selection dialog box appears.

B. Select Descendants from the Filter drop-down list.

C. Select Boston, Seattle, Denver, and Houston from the Members pane and click the Add () icon to move the members to the Selection pane.

D. Select DESCENDANTS([Market]) in the Selection pane and click the Remove () icon. The Selection pane should look like this:

E. Click OK.

 

5.

A. Select the column header B, DESCENDANTS([Period]) and click Period in the POV. The Member Selection dialog box appears.

B. Select Q1, Q2, Q3, and Q4 and click the Add () icon to move the members to the Selection pane. Select DESCENDANTS([Period]) in the Selection pane and click the Remove () icon.

Ensure the Use Descriptions checkbox is selected.

C. Click OK.

 

6.

A. Select the row header A, DESCENDANTS([Product]) and click Product in the POV. The Member Selection dialog box appears.

B. Select Audio and Visual and click the Add () icon to move the members to the Selection pane. Select DESCENDANTS([Product]) in the Selection pane and click the Remove () icon.

Ensure the Use Descriptions checkbox is selected.

C. Click OK. The grid appears with the filtered members.

 

Note: If you want to add a specific Account, or Scenario to your grid, you can select it from the POV by clicking the drop-down list and then the ellipsis (...) to launch the Member Selection dialog box .

 

7.

Next, you change a preference for your data. The preferences that you specify are stored as part of the Smart Slice definition, and they override the global preferences set in the Options dialog box.

Click Options on the POV.

The Preferences dialog box appears.

 

8.

There are two types of user preferences: ad hoc and display. The ad hoc options control how data is retrieved into the spreadsheet and which data is retrieved in ad hoc queries. The display options control how data is displayed in the spreadsheet. Additionally, you can also set cell styles. Each of the Smart View Data Source Providers have a set of default cell styles to identify cell types. You can modify default styles in Smart View and change cell styles by selecting different fonts, background colors, or border colors.

Select #Missing for the missingLabelText val and enter Null. This replaces the literal #Missing with the literal Null when a Null value is encountered in a cell.

Click OK.

 

9.

Click the Done () icon to save your Smart Slice. The Member Selection dialog box appears. Once again, you can change the options, select other members, and so forth.

Click OK and then enter the name TopCities for your Smart Slice.

 

10.

Click the OK () icon to save the Smart Slice and refresh the Data Source Manager. Your Smart Slice appears in the tree associated with the Basic data source.

This Smart Slice is now available for use by the end user.

 

11.

Open a new workbook. Right-click your Smart Slice and select Ad-hoc Analysis. Your query appears and contains precisely the members specified in the Smart Slice.

Next, you use the Smart Slice to create a report.

Back to Topic

Creating a Report Using Report Designer

This subtopic shows you how to create a report by using your Smart Slice and Report Designer. Report Designer is a Smart View feature in which you can design and manage a variety of reports that are based on Smart Slices. Report Designer can be used with Essbase, Planning, and Oracle BI EE data sources.

You design reports based on Smart Slices from the Report Designer interface, which appears when you insert a Smart Slice query or subquery from the Data Source Manager. Reports can then be displayed in an Excel spreadsheet, Word document, or PowerPoint slide. You can display as many reports from as many data sources as space permits on one sheet.

A report can be displayed as follows:

An entire report is associated with an Excel workbook, a Word document, or a PowerPoint presentation. One report page is associated with an Excel worksheet, a Word page, or a PowerPoint slide. For PowerPoint presentations, Smart View recommends one report type per slide.

Note: Contents of charts and tables are visible only in presentation mode.

1.

Ad hoc and Report Designer objects cannot be added to the same worksheet. Open a new workbook.

With the Smart Slice still selected, click the drop-down list for the Insert Into Report () icon and select Query. The Report Designer pane appears with a query named exactly the same as your Smart Slice.
Note:
You can also select Subquery to create a report based on a subset of your data.

 

2.

A. In the Report Designer pane, click the drop-down list for the Insert () icon and select Chart. The chart is added to your workbook.

B. Click the Refresh () icon to view your data. The refreshed workbook appears.

Hint: You can use the Expand All () icon to see each object within the Report Designer pane.

 

3.

You can create separate reports for any or all of the members of one dimension in a report based on a Smart Slice query in Report Designer or an ad hoc grid, and cascade these reports separately across the worksheets of an Excel workbook. For reports created in Report Designer, you can also cascade reports across slides in a PowerPoint presentation (worksheets or slides are created as needed to accommodate all reports).

A. Cascading reports allow you to showcase your data on separate worksheets within the workbook. Click the Cascade Report Across the Workbook () icon. The Member Selection dialog box appears. Select the following detail:

Selection

Value

Dimension

Accounts

Members

Profit, Margin, and COGS

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK. A new workbook is created with three separate worksheets to reflect each of the member selections: Profit, Margin, and COGS. Additionally, the Report Designer pane details your changes.

Back to Topic

Using a Smart View Query with PowerPoint

This subtopic shows you how to use Report Controls to manage members within a PowerPoint slide.

1.

Queries inserted in Report Designer cannot be modified. However, you can control the POV of a query in any of its reports that display on a sheet by using a slider. The slider displays a selected set of dimension members from a query; when you drag the slider marker to a member, its data appears in all reports associated with the query on the sheet. Sliders can contain dimensions from more than one query in Report Designer if the dimensions have the same boundaries.

A. Open PowerPoint. In Normal View, from the Slides tab, right-click and select Slide Layout.

B. From the Content Layouts pane, select the Blank layout.

The new layout appears. Close the Slide Layout pane.

 

2.

Select Hyperion > Data Source Manager > Connect to Provider Services to establish a connection and navigate to the TopCities Smart Slice.

Select the TopCities Smart Slice. Right-click and select Insert Query Into Report.

The Report Designer pane appears.

 

3.

A. In Report Designer, click the drop-down list for the Insert () icon and select Function Grid. The grid is added to your slide.

B. Select Hyperion > Refresh. The Function Grid appears with the cells refreshed.

You can add formatting, include a title, reposition the object, and so forth, using PowerPoint's capabilities.

Note: If you select a cell, detail about that member appears in a callout.

 

4.

A. Once again within the Report Designer pane, click the drop-down list for the Insert () icon and select Chart. The chart is added to your slide.

B. Select Hyperion > Refresh. The Chart appears.

 

5.

Finally, add a slider by using Report Designer. As mentioned in step 1, the slider displays a selected set of dimension members from a query. When you drag the slider marker to a member, the data appears in all reports associated with the query on the sheet.

A. Click the drop-down list for the Insert () icon and select Slider. The Member Selection dialog box appears. Select the following detail:

Selection

Value

Dimension

Accounts

Member

Profit, Margin, COGS, and Total_Expenses

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK. A slider is added.

As you move the slider, the member changes and the associated data in the graph and grid are updated to reflect the new member values.

Back to Topic

Using a Smart View Query with Word

This subtopic shows you how to copy a Smart View query into Word from Excel and how to dynamically exchange the data in the table within Word by using POV Manager.

To present data, Word or PowerPoint is often preferable to Excel. Smart View enables you to copy data points from Excel and paste them into Word or PowerPoint, creating linked views (dynamic data) between applications. You can copy and paste data points from:

The data points retain their original Excel-based query information, thus enabling users to perform data analysis. Word and PowerPoint can contain data points from multiple data sources, such as Essbase, Financial Management, and Hyperion Enterprise within one document.

Notes:

 

1.

This step focuses on copying a grid from Excel into Word. Open Book1 created in the first lesson.

Select the grid as in step 5 B , Creating a Smart View Query in Excel.

 

2.

A. From the Hyperion menu in Excel, select Copy Data Points.

B. Open Word and, from the Hyperion menu within Word, select Paste Data Points.

When prompted to connect to a data source, enter your username and password for authentication.

Your blank document appears with the requested data points. Your Word document should look like this:

 

3.

From the Hyperion menu, select Refresh. Your Word document now contains dynamically linked data.

You can use Word's powerful formatting capability to create a more visually appealing report.

 

4.

After pasting Excel data points into Word or PowerPoint, you can use the POV Manager to
change the POV, enabling you to view a different slice of data. From the Hyperion menu, select POV Manager.

The POV Manager dialog box appears.

 

5.

A. Click the connection to view the data source connection information and active POV members.

The POV Manager refreshes with connection and member detail.

B. Double-click the connection to view the Member Selection list.

 

6.

Ensure that Accounts is selected in the Dimension drop-down list. Select Descendants from the Filter drop-down list.

A. Select Cost_of_Goods_Sold in the Members pane.

B. Click the Add () icon. The Member Selection pane should look like this:

C. Click OK. The POV Manager appears.

D. Click Close.

 

7.

From the Hyperion menu, select Refresh to see the cost of goods sold for each quarter for cities in the Eastern region.

Close Word.

 

Back to Topic List

This topic shows you how to use a Smart Slice in Smart View to create queries by using a Oracle BI EE data source. Smart View takes advantage of the unlimited scalability of Oracle BI EE relational data sources. By integrating the relational data source through Provider Services, you are able to leverage the conceptual power of Smart View to deliver relevant information easily and in a timely fashion to a variety of channels, enabling broader decision-making across pervasive audiences.

Creating a Smart Slice and Smart View Query in Excel
Creating a Subquery in Excel Using a Smart Slice
Creating a Cascading Report in PowerPoint from a Smart Slice
Creating a Subquery in Word Using a Smart Slice

Creating a Smart Slice and Smart View Query in Excel

This subtopic shows you how to create a Smart Slice for a Oracle BI EE data source that you use to create a query in Excel.

1.

A. In the Smart View Data Source Manager panel, double-click Oracle BI Server to select the data source type. The list of servers appears.

Note: If prompted to connect to a data source, enter your username and password for authentication.

B. Double-click OBIEE and then double-click the Sample Sales Reduced application.

C. Select the Sample Sales Reduced database.

D. Click the Add () icon.

The Smart Slice Designer appears with the default ad hoc grid and top-level data.

 

2.

In this step, you begin to create a Customer query. Choose the appropriate time frame.

A. Drag DESCENDANTS([Time].[[Year]) from the POV to B2.

The dimension is added to the grid.

B. Right-click and drag B2, DESCENDANTS([Time].[[Day Date]) from the grid to the POV. DESCENDANTS([Time].[[Day Date]) is removed from the grid.

 

3.

Next, choose the Revenue detail that you wish to see in the report.

A. Click B1, DESCENDANTS([MEASURES].[[ALL]). The Member Selection dialog box appears.

Select the Use Descriptions check box.

B. In the Members panel, select the following and add the members to the Selection pane:

[Facts Revenue].[Year To Date Revenue]

[Facts Revenue].[Revenue]

[Facts Revenue].[Year ago YTD Revenue]

[Facts Other].[Inventory Total]

Remove DESCENDANTS([MEASURES].[[ALL]) from the Selection pane. The Member Selection dialog box should look like this:

C. Click OK. The grid appears.

 

4.

A. Click the Done () icon to save your Smart Slice. The Member Selection dialog box appears.

B. Click OK.

C. Name the Smart Slice Customer Detail and click the OK () icon to save the Smart Slice and refresh the Data Source Manager. Your Smart Slice appears in the tree associated with the Sample Sales Reduced data source.

This Smart Slice is now available for use by the end user.

 

5.

A. Right-click Customer Detail and select Insert Query Into Report. The Report Designer pane appears.

B. Click the Insert () icon and select Function Grid. The grid is added to your workbook.

C. Select Hyperion > Refresh to view your data. The refreshed workbook appears.

Using Excel formatting, you can resize the columns, set text wrap on, and so forth to create a more appealing report. This is a sample of what the formatted report might look like:

Hint: To change #Missing to NULL, select Hyperion > Options. On the Display tab, in the #NoData/Missing Label text box, enter NULL. This is a global user preference.

 

 

Back to Topic

Creating a Subquery in Excel Using a Smart Slice

This subtopic shows you how to create a subquery in Excel.

1.

Select the Customer Detail Smart Slice that you just created. Right-click and select Insert Subquery Into Report. Your ad hoc query contains only those members from within your Smart Slice.

Subqueries allow you to create a report based on a subset of your data.

 

2.

In the POV, select [Time].[[Year]

The Member Selection dialog box appears.

 

3.

A. In the Member Selection dialog box, make the following selections:

Selection

Value

Dimension

[Time].[Year] - Year

Member

[Time].[Year].[2007] - 2007; [Time].[Year].[2008] - 2008

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK. A subset of the Time dimension is added.

 

4.

A. In the POV, select [MEASURES].[ALL].

The Member Selection dialog box appears.

B. In the Member Selection dialog box, make the following selections:

Selection

Value

Dimension

[MEASURES].[ALL] - All Measures

Member

[Facts Revenue].[Year to Date Revenue] - Year to Date Revenue;
[Facts Revenue].[Revenue] - Revenue

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

C. Click OK. A subset of the Measures dimension is added.

 

5.

Click the Done () icon to save your subquery. The Member Selection dialog box appears.

Click OK. In the Enter New Name text box, enter Customer Revenue and click the OK () icon to save the subquery. The subquery appears in the Report Designer pane.

 

6.

A. Select A6—this specifies the placement of the object to be added. In the Report Designer pane, click the drop-down list for the Insert () icon and select Chart. The chart is added to your workbook.

B. Select Hyperion > Refresh to view your data. The refreshed workbook appears and features only those members selected to participate in this subquery.

 

7.

A. In the Report Designer pane, click the drop-down list for the Insert () icon and select Slider. The slider is always placed in the first cell within Excel.

The Member Selection dialog box appears.

In the Member Selection dialog box, make the following selections:

Selection

Value

Dimension

[Time].[Quarter] - Quarter

Member

[Time].[Quarter].[2007 Q1] - 2007 Q1;
[Time].[Quarter].[2007 Q2] - 2007 Q2;
[Time].[Quarter].[2007 Q3] - 2007 Q3;
[Time].[Quarter].[2007 Q4] - 2007 Q4;
[Time].[Quarter].[2008 Q1] - 2008 Q1;
[Time].[Quarter].[2008 Q2] - 2008 Q2;
[Time].[Quarter].[2008 Q3] - 2008 Q3;
[Time].[Quarter].[2008 Q4] - 2008 Q4

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK. The slider is added.

As exhibited in the previous topic, when you move the control on the slider, the member data in the chart automatically updates.

C. In the Report Designer pane, click the Expand All () icon to view the objects added to the subquery.

 

Back to Topic

Creating a Cascading Report in PowerPoint from a Smart Slice

This subtopic shows you how to add a chart to a PowerPoint slide and then cascade the members to a new presentation across multiple slides.

1.

A. Open PowerPoint. In Normal View, from the Slides tab, right-click and select Slide Layout.

B. From the Content Layouts pane, select the Title Only layout.

The new layout appears. Close the Slide Layout pane.

C. Click the placeholder for the title on the slide and enter Customer Qtr Revenue for 2008.

 

2.

A. Select Hyperion > Data Source Manager > Connect to Provider Services to establish a connection and navigate to the Customer Detail Smart Slice.

B. Select the Customer Detail Smart Slice.

C. In the Smart View Data Source Manager pane, click the drop-down list for the Insert Into Report () icon and select Query. The Report Designer pane appears.

 

3.

A. In Report Designer, click the drop-down list for the Insert () icon and select Chart. The chart is added to your slide.

B. Select Hyperion > Refresh. The chart refreshes.

 

4.

Click the Cascade Report Across the Workbook () icon. The Member Selection dialog box appears. Select the following detail:

Selection

Value

Dimension

[Time].[Quarter] - Quarter

Member

[Time].[Quarter].[2008 Q1] - 2008 Q1;
[Time].[Quarter].[2008 Q2] - 2008 Q2;
[Time].[Quarter].[2008 Q3] - 2008 Q3;
[Time].[Quarter].[2008 Q4] - 2008 Q4

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK. A new presentation is created with four separate slides (one for each quarter) to reflect each of the member selections.

Additionally, the Report Designer pane details your changes.

Back to Topic

Creating a Subquery in Word Using a Smart Slice

This subtopic shows you how to create a subquery in Word by using a Smart Slice.

1.

Open Word.

A. Select Hyperion > Data Source Manager > Connect to Provider Services to establish a connection and navigate to the Customer Detail Smart Slice.

B. Select the Customer Detail Smart Slice.

C. In the Smart View Data Source Manager pane, click the drop-down list for the Insert Into Report () icon and select Subquery. Hyperion automatically opens Excel, enabling you to create a new subquery.

 

2.

A. In the POV, select [Time].[[Year].

The Member Selection dialog box appears. Make the following selections:

Selection

Value

Dimension

[Time].[Year] - Year

Member

[Time].[Year].[2007] - 2007; [Time].[Year].[2008] - 2008

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK.

 

3.

A. In the POV, select [MEASURES].[ALL].

The Member Selection dialog box appears. Make the following selections:

Selection

Value

Dimension

[MEASURES].[ALL] - All Measures

Member

[Facts Revenue].[Year to Date Revenue] - Year to Date Revenue;
[Facts Revenue].[Revenue] - Revenue;
[Facts Revenue].[Year ago YTD Revenue] - Year ago YTD Revenue

Use Descriptions

Select the check box.

The Member Selection dialog box should look like this:

B. Click OK.

 

4.

Click the Done () icon to save your subquery. The Member Selection dialog box appears.

Click OK. Word reappears.

In the Enter New Name text box, enter Customer Revenue for 2007 and 2008 and click the OK () icon to save the subquery. The subquery appears in the Report Designer pane.

 

5.

A. In the Report Designer pane, click the drop-down list for the Insert () icon and select Function Grid. The grid is added to your workbook.

B. Select Hyperion > Refresh to view your data. The refreshed document appears.

You can now format the report to suit your requirements.

 

Back to Topic List

In this tutorial, you should have learned how to:

Place the cursor over this icon to hide all screenshots.