Integrating Oracle BI EE with Oracle Hyperion Smart View for Office

Overview

    Purpose

    This tutorial covers the connectivity available between Oracle Hyperion Smart View for Office 11.1.2.3 and Oracle Business Intelligence Enterprise Edition (Oracle BI EE) 11.1.1.7. Smart View provides a Microsoft Office interface for the content in the Oracle BI catalog.

    Time to Complete

    Approximately 1 hour

    Introduction

    With Smart View, you can connect to Oracle BI EE servers and use the content created in Oracle BI EE dashboards and analyses in Microsoft Office. You can insert Oracle BI EE tables, pivot tables, and graphs into Excel, Word, and PowerPoint as refreshable, editable objects. You can also copy Oracle BI EE dashboard content and paste it into Smart View client documents. In addition to working with this content, you can create content in the form of simple views with the View Designer, which you can then publish to the Oracle BI catalog. Smart View offers you a cohesive and consistent experience across all Oracle BI EE content providers.

    Hardware and Software Requirements

    The following is a list of hardware and software requirements:

    Prerequisites

    Before starting this tutorial, you should:

    • Have access to or have installed the Oracle BI EE 11.1.1.7 Sample Application V305 VirtualBox image (SampleAppv305OTNGA) with networking enabled.
    • Have started the SampleAppv305OTNGA VirtualBox image.
    • Have started the services necessary for "Standard OBI Demo," as described in the SampleApp LaunchPad web page (located on the desktop of the SampleAppv305OTNGA VirtualBox image).
    • Have access to or have installed Microsoft Office on a Windows machine that can connect to the SampleAppv305OTNGA VirtualBox image. Note: You may need to add the VirtualBox image's IP address and name (obieesample.us.oracle.com) to the hosts file on your Windows machine.

Setting Up Smart View

    To set up Smart View for Oracle BI EE, you first install Smart View, then you verify the Oracle BI EE extension is enabled. Next, you create a connection to the Oracle BI EE server, and finally you view the Oracle BI EE catalog in Smart View.

    Installing Smart View

      On your Windows machine, open a web browser and enter the URL for the instance of Oracle BI EE that is running on the VirtualBox image (for example, http://localhost:9704/analytics).

      The Oracle Business Intelligence Sign In page is displayed.

      Enter prodney for the User ID and Admin123 for the password, and then click Sign In.

      The OBI EE 11.1.1.7.0 Samples Application Index page is displayed by default.

      Click Home to display the default Oracle Business Intelligence home page.

      In the left navigation pane, under Get Started, select Download BI Desktop Tools > Smart View for MS Office.

      The SmartView.exe file download is displayed.

      Download the executable file to your hard drive. Then, in Windows Explorer, browse to the location where you downloaded the Smart View executable and double-click SmartView.exe to start the Smart View installer.

      Click Next to display destination folder options.

      If necessary, change the destination folder, and then click Next. At the final page, click Install to begin the installation.

      The Smart View client is installed.

    Verifying the Oracle BI EE Extension

      In Microsoft Excel, on the Smart View ribbon, click Options.

      The Smart View Options dialog box is displayed. Click Extensions.

      The Oracle BI EE extension is enabled by default. You can disable it by clicking Disable.

      Click Cancel to close the Options dialog box.

    Creating a Connection to Oracle BI EE in Smart View

      In Microsoft Excel, on the Smart View ribbon, click Panel.

      The Smart View Panel is displayed. Click Private Connections.

      The Private Connections list is displayed. Click Create new connection.

      In the list of connection provider types, click Oracle BI EE.

      The "Add Connection - URL" dialog box is displayed. Enter the connection URL for the VirtualBox image as http://obieesample.us.oracle.com:9704/analytics/jbips and then click Finish.

      The Connect to Data Source Dialog Box is displayed. Enter the following connection information, and then click Connect.

      User Name: weblogic
      Password: Admin123


      The Save as Private Connection dialog box is displayed. In the Name text box, enter OracleBISample, and then click OK.

      The OracleBISample connection is displayed in the Private Connections list.

    Viewing the Oracle BI EE Catalog in Smart View

      After you create a connection to Oracle BI EE, the Oracle BI EE Presentation Catalog, also referred to as the catalog or catalog tree, is displayed in the Smart View Panel. The catalog tree displays a root node called "Catalog Root" and, by default, two folders called "My Folders" and "Shared Folders." The items you can see in the catalog are governed by your Oracle BI EE permissions and security settings.

      In the Smart View panel, in the Private Connections list, select the OracleBISample connection, and then click Connect.

      The Connect to Data Source dialog box is displayed. Enter the following information, and then click Connect:

      User Name: weblogic
      Password: Admin123

      Expand OracleBISample > Catalog Root to display My Folders and Shared Folders.

      You can view the properties of any folder to which you have access, including the name of the folder creator and the creation date. Right-click Shared Folders, and select View Folder Properties.

      The contents of Shared Folders can be viewed by all logged-on users with BI Consumer privileges. In the Folder Properties dialog box, notice that the Owner is listed as "System Account."

      Expand Shared Folders > 3. Analysis and Dashboards > Smart View. This is where the demonstration material for Smart View in the Oracle BI EE sample application is located.

      The folders in the catalog tree contain a list of analyses. You can view the properties of an analysis, including the name of the analysis creator, the creation date, and modification date. Under the Smart View folder, right-click History Overview, and select View Analysis Properties.

      Notice that the analysis owner is the user weblogic, and the analysis was created on 4/26/2013.

      Click Close to close the Analysis Properties dialog box.

      If you have BI Consumer privileges, you can edit an analysis in BI Answers. Under the Smart View folder, right-click History Overview, and select Edit Analysis in Answers.

      A browser window opens to BI Answers with the analysis open in edit mode. Note: if you are not already logged on to Oracle Business Intelligence, you must enter your login credentials before the analysis is displayed. If you are prompted for login credentials, enter the following information to see the analysis:

      User ID: prodney
      Password: Admin123

Inserting Oracle BI EE Views into Smart View Clients

    In BI Answers, analyses are composed of individual views; dashboards are composed of analyses and individual views. You can insert the following Oracle BI EE view types into Smart View clients (Excel, PowerPoint, and Word) from the catalog:

    • Table views: You can insert tables either using the native BI Answers format, or, in Excel, as an Excel table. Using the Excel table format enables you to perform Excel tasks such as sorting and filtering.
    • Pivot table views: You can insert pivot tables either using the native BI Answers grouping and format, or, in Excel, as an Excel pivot table. Using the Excel pivot table format enables you to perform Excel tasks such as pivoting, aggregating, drilling, sorting, and filtering.
    • Graph views: You can insert graphs either as an image, or as a Microsoft Office chart object. Inserting graphs as Microsoft Office chart objects in any of the Smart View clients enables you to edit and refresh the objects.
    • Gauge views and funnel graph views: In BI Answers, gauge views and funnel graph views are separate object types from graph views. You can insert these view types in to Smart View clients as images.
    • Title views: In BI Answers, title views are typically composed of title text, subtitle text, the analysis name, the date and time inserted, an optional logo, and an optional URL. You can insert title views into Smart View clients as a collection of text boxes and images grouped together as a single Office object.
    • Compound views: You can insert compound views, which comprise two or more views, into Smart View clients. Each type of supported view is inserted with its own default format.

    Inserting Tables

      First, see the default results when you insert a table. In a new Excel worksheet, select cell A1.

      In the Smart View panel, under OracleBISample > Catalog Root > 3. Analysis and Dashboards > Smart View, expand the Monthly Revenue Trend analysis node to display the views it comprises.

      Under Monthly Revenue Trend, right click Table 1, and select Insert.

      The view is inserted into the Excel worksheet. Notice the following things:

      • Grouped data (such as 2010) is displayed in merged cells.
      • The Oracle BI EE tab is displayed in the Office ribbon. This tab contains buttons that enable you to interact with the Oracle BI EE content in Smart View. The buttons are covered later in this tutorial.
      • The Document Contents pane is displayed at the bottom of the Smart View panel. This pane displays your open workbooks and any worksheets that have Oracle BI EE content.

      Next, see what happens when you insert the same table as an Excel table. In the Document Contents pane, right-click Table 1 and notice the available options (Properties, Refresh View, Delete View, and Mask). Select Delete View.

      The message "Selected view(s) will be deleted permanently. Are you sure?" is displayed. Click Yes.

      The table is removed from the worksheet and the Document Contents pane is closed.

      In the Smart View panel, under Shared Folders > 3. Analysis and Dashboards > Smart View > Monthly Revenue Trend, right click Table 1, and select Insert As Excel Table.

      The view is inserted into the Excel worksheet as an Excel table. Notice that the column headers display filter drop-downs.

      Filter the report to view only data from 2012:

      1. In Column A, click the down arrow to display the filter drop-down list for Year.
      2. Deselect (Select All).
      3. Select 2012.

      Click OK to display the filtered data.

      Although the instructions provided in the preceding example were for Excel, you can insert table views into Word and PowerPoint using the same procedure. However, the Excel table format is supported only in Excel.

    Inserting Pivot Tables

      First, see the default resutls when you insert a pivot table. In Excel select Sheet 2, and then select cell A1.

      In the Smart View panel, under OracleBISample > Catalog Root > 3. Analysis and Dashboards > Smart View, expand the Pivot Table CF analysis node to display the views it comprises.

      Under Pivot Table CF, right click Pivot Table 1, and select Insert.

      The pivot table is inserted into the worksheet. The color formatting displayed in Excel comes from the analysis formatting in Oracle BI EE.

      Next, see what happens when you insert the same pivot table view as an Excel pivot table. In the Document Contents pane, under Sheet2, right-click Pivot Table 1 and select Delete View. At the confirmation message, click Yes.

      The pivot table is removed from the worksheet.

      In the Smart View Panel, under Shared Folders > 3. Analysis and Dashboards > Smart View > Pivot Table CF, right click Pivot Table 1, and select Insert as Excel Pivot.

      The pivot table is inserted into the worksheet as an Excel pivot table object. Notice that in addition to the Smart View panel, the PivotTable Field List panel is now displayed. You can select this option when you want to perform further analysis on the data, such as aggregating, pivoting, drilling, sorting, and filtering.

      Although the instructions provided in the preceding example were for Excel, you can insert pivot table views into Word and PowerPoint using the same procedure. However, the Excel pivot table format is supported only in Excel.

    Inserting Graphs

      You can insert graphs either as editable Microsoft Office chart objects, or as refreshable images. In your Excel workbook, select a blank worksheet, and then select cell A1.

      In the Smart View panel, under OracleBISample > Catalog Root > 3. Analysis and Dashboards > Smart View, expand the Examples of Standard Visualizations analysis node to display the views it comprises.

      Note: The analysis "Examples of Standard Visualizations" contains examples of the various views you can import from Oracle BI EE to Smart View. Notice that the view icons for the graphs indicate what type of graph will be displayed.

      Under Examples of Standard Visualizations, right-click HorizBar, and select Insert.

      The horizontal bar chart is inserted into the worksheet as a Microsoft Office chart object.

      You can edit the chart using Microsoft Office tools. Right-click the chart object, and select Change Chart Type.

      In the Change Chart Type dialog box, select the flat pie chart icon, and then click OK.

      The chart display changes to a pie chart.

      You can further edit the chart's appearance and title. Click on the chart title to edit the text, then replace Horizontal Bar Chart with Revenue by Month.

      You can also insert the original graph as a refreshable image. Select cell A16.

      In the Smart View Panel, under Shared Folders > 3. Analysis and Dashboards > Smart View > Examples of Standard Visualizations, right-click HorizBar and select Insert as Image.

      The chart is inserted as an image underneath the pie chart. Although you cannot change the appearance of the image, the data in the image is refreshed when you refresh the view.

      Although the instructions provided in the preceding example were for Excel, you can insert graph views into Word and PowerPoint using the same procedures. Note: Time line series, pareto, waterfall, gauge, and funnel graph views can only be inserted as images; all other graph types can be inserted as chart objects or images.

    Inserting Compound Views

      When an analysis contains a compound view (that is, an overview comprising multiple views), you can insert all of the supported views together by inserting the compound view.

      In a new blank worksheet, select cell A1.

      In the Smart View panel, under OracleBISample > Catalog Root > 3. Analysis and Dashboards > Smart View, expand the History Overview analysis node to display the views it comprises.

      Under History Overview, right-click Compound View 1, and select Insert All Views.

      A dialog box is diaplyed with insert options for multiple objects. Select All objects on one sheet / slide.

      Note: This option is displayed only in Excel and PowerPoint. In Word documents, objects are placed adjacent to and below other objects on a page until the page is filled, and then continues filling as many pages as required to hold all the objects.

      The title, pivot table, and chart views are inserted into the worksheet. The default formatting comes from Oracle BI EE.

    Inserting Dashboards

      Dashboards are displayed in the Presentation Catalog as folders. A dashboard folder can be expanded to show dashboard pages; a dashboard page can be expanded to show the list of analyses included in that page. You can insert dashboard pages into Excel, PowerPoint, and Word.

      In a new blank worksheet, select cell A1.

      In the Smart View panel, under OracleBISample > Catalog Root > 3. Analysis and Dashboards > Smart View > Dashboards > Smart View, right-click the Flight dashboard page, and select Insert All Views.

      At the prompt, select All objects on one sheet / slide.

      The views from the dashboard page are displayed in the Excel worksheet. Notice that three chart views and three pivot table views were inserted. You can scroll to the top of the worksheet to view the entire dashboard.

Working with Views in Smart View Clients

    Prompts enable you to specify criteria that determines the content of the analysis that you are inserting. Smart view supports both analysis-level prompts and page-level prompts. Additionally, you can mask data in Smart View, which requires users to refresh and provide authentication before viewing the information. The following subtopics describe how to work with prompts, mask data, and refresh views.

    Working with Prompts

      Prompts in Smart View are the equivalent of prompts in BI Answers; they affect all views in an analysis. For example, in an analysis, a prompt can be set on Year. This prompt affects all views in the analysis. If prompts are defined on an analysis in BI Answers, then when you insert a table, pivot table, or graph view from the analysis into Smart View, you are prompted to select the data to display.

      In a new blank worksheet, select cell A1.

      In the Smart View panel, under OracleBISample > Catalog Root > 3. Analysis and Dashboards > Smart View, expand the P&L by Time - SV analysis node to display the views it comprises. This analysis contains prompts for Scenario and Country.

      Under "P&L by Time - SV," right-click Pivot Table 1, and select Insert.

      The prompt selector is displayed. Select values for Scenarios and Country:

      1. In the Scenarios row, click the Browse (ellipsis) button to display the Value Selector dialog box.
      2. Select Actual, and then click the Add (right arrow) button. Click OK to close the Value Selector dialog box.
      3. In the Country row, click the Browse (ellipsis) button to open the Value Selector dialog box for Country.
      4. Select North America Corporate, and then click the Add (right arrow) button. Click OK to close the Value Selector dialog box.
      5. Click Show Filters Summary to display the filter that will be used for the query.
      6. Click OK to apply the prompts and insert the pivot table.

      The pivot table is displayed in Smart View. Notice that the prompt values are not displayed in the client.

      You can edit prompt values to view different data. On the Oracle BI EE ribbon, click Edit Prompts.

      The Prompt Selector dialog box is displayed. Edit the prompt selections as follows:

      • Scenarios: Plan
      • Country: EMEA Corporate

      Click OK. The pivot table is refreshed and reflects the new selections.

      Note: Page prompts apply to a single view. There is an example of updating page prompts in the topic "Creating and Publishing Views Using the View Designer."

    Setting Refresh Preferences and Refreshing Views

      Refresh preferences are available on a per-view basis. The refresh preferences that you set for each view within a workbook, slide, or page are saved along with the Office document. The following refresh preferences are available:

      • Refresh Data: Refreshes only the data points in the selected view or analysis.
      • Replace View: Replaces the entire view, including any formatting changes set in BI Answers. If you select this option, your prompt and graph selections and custom formatting you created in Smart View are lost upon refresh if the view definition has changed in Oracle BI EE. Note: Oracle BI EE title views are not included in refresh actions, so all customizations to a title view in Smart View are retained when you refresh the document.
      • Do not refresh: Refreshing is not allowed.

      You can refresh the BI data in your inserted views You have the option of refreshing selected views or refreshing all views, depending on the Office document type, as follows:

      Excel:

      • Individual views
      • Individual worksheets
      • Entire workbook

      PowerPoint:

      • Individual views
      • Individual slides

      Word:

      • Individual views
      • Entire document

      You can refresh views from the Document Contents pane, the Oracle BI EE ribbon, or the Smart View ribbon. The refresh action is limited according to the refresh preference set for a selected view.

      Select the worksheet on which you previously inserted the views from the Flight dashboard page.

      Note: Your sheet number may differ from the example.

      In the Document Contents pane, click the Refresh Contents button (right arrow) to refresh the content of the pane.

      The view "Pivot Table 3" is the first view displayed on the worksheet. View the default refresh preferences for the pivot table: Under the sheet name, right-click Pivot Table 3, and select Properties.

      The view properties are displayed. Notice that the default refresh preference is "Refresh Data."

      Click Cancel to close the View Properties dialog box..

      Make formatting changes to the pivot table:

      1. Select cells F6:F23 and B24:F24.
      2. Change the color formatting on the selected cells.

      In the Document Contents pane, under the sheet name, right-click Pivot Table 3, and select Refresh View.

      The data in the pivot table is refreshed. Notice that your formatting changes are retained.

      Change the refresh preferences for the pivot table:

      1. In the Document Contents pane, under the sheet name, right-click Pivot Table 3, and select Properties.
      2. In the Refresh Options drop-down list, select Replace View, then click OK to close the View Properties dialog box.

      On the Oracle BI EE ribbon, click Refresh to refresh all views in the worksheet. Note: Because the worksheet includes multiple views, this action takes longer than refreshing just the pivot table.

      The pivot table view is replaced from Oracle BI EE. Notice that your formatting changes have been replaced by the formatting stored in Oracle BI EE.

      Optional: You can refresh the entire workbook by using the Refresh button on the Smart View ribbon. Depending on how many views are included in your workbook, this operation may take some time. On the Smart View ribbon, select Refresh > Refresh all Worksheets.

      All worksheets in the workbook are refreshed.

    Masking Data in Views

      You can mask data in Oracle BI EE views displayed in Smart View clients. Masking data ensures that users who view Office documents with Oracle BI EE views must be authenticated, and can only view data to which they have been granted access. You have the option of masking data in:

      • Selected views
      • All views on sheet or a slide
      • All views in an Office document

      In Microsoft Word, on the Office ribbon, click the Smart View tab and then click Panel to display the Smart View panel.

      In the Smart View panel, click Private Connections.

      In the Private Connections drop-down list, select obieesample.us.oracle.com.

      Expand the OracleBISample node.

      The Connect to Data Source dialog box is displayed. Enter the following user authentication, and then click Connect.

      • User Name: weblogic
      • Password: Admin123

      In the Oracle BI EE catalog, under Shared Folders > 3. Analysis and Dashboards > Smart View > History Overview, right-click Compound View 1 and select Insert All Views.

      The pivot table, title, and chart are inserted into the Word document. Notice that the table is inserted as a formattable Word table, the title is inserted as a text box, and the chart is inserted as an editable Microsoft Office chart object. You can reposition the objects on the page as required.

      You want to share this document with other users. To ensure that they can see only the data to which they have been granted permissions, you must mask the views in the document. You can mask individual views, or all views in a document.

      First, try masking only the data for the pivot table. In the Smart View panel, in the Document Contents pane, right-click Pivot Table 1 and select Mask.

      The pivot table is masked; all data in the table is replaced by the words "need refresh." Notice that the chart is not masked.

      On the Oracle BI EE ribbon, click Refresh to display the masked data.

      Data is displayed in the pivot table again. Notice that you were not prompted to log in again, because you are already connected to Oracle BI EE.

      Now try masking the entire document. On the Oracle BI EE ribbon, click Mask Data.

      Note: Word documents can only be masked in their entirety; you cannot mask individual pages in Word. In Excel, the Mask Data button provides the following options:

      • Mask Data: Mask all data on the active worksheet
      • Mask Workbook Data: Mask all data in the current workbook

      In PowerPoint, the Mask Data button provides the following options:

      • Mask Data: Mask all data on the active worksheet
      • Mask Presentation Data: Mask all data in the current presentation

      Notice that data is masked for both views in the document.

      You can save the document with masked data and share it with other users. To view the data you must first refresh the document. If you are not already connected to Oracle BI EE, you are prompted to connect when you refresh.

      Optional: Refresh the document to view the data again, and then use Word formatting tools to customize the table and the title text box. The following graphic is an example of a formatted version of the document.

      Because the default refresh option for inserted views is Refresh Data, any custom formatting you apply in Word is preserved when you refresh the document.

Copying and Pasting Oracle BI EE Objects

    Smart View enables you to copy Oracle BI EE views within and between Smart View clients, with some limitations. Additionally, when you design analyses in Oracle BI EE, you can enable copying directly from the web interface to Smart View clients.

    Copying and Pasting Oracle BI EE Objects Between Office Applications

      You can copy Oracle BI EE objects within and between Office applications as follows:

      • You can copy graphs within and between any Office application.
      • You can copy tables and pivot tables within and between Word and PowerPoint.
      • You cannot copy tables and pivot tables within Excel, from Excel to another Office application, or from another Office application to Excel.

      First, copy a pivot table from Word to PowerPoint. In the Word document you created in the preceding topic, select the pivot table.

      On the Oracle BI EE ribbon, click Copy.

      Open a PowerPoint presentation and select a blank slide. On the Smart View ribbon, click Paste.

      The pivot table is inserted into the PowerPoint presentation. You can reposition the table as needed. Notice that if you added custom formatting to the table in Word, the formatting is preserved when you copy the table to PowerPoint.

      You can optionally apply custom formatting to the table in PowerPoint, as shown in the following example. Again, because the default refresh option is Refresh Data, your custom formatting in PowerPoint is preserved when you refresh the view.

      Next, copy a chart from Word to Excel. Return to the Word document and select the revenue chart.

      On the Oracle BI EE ribbon, click Copy.

      Open an Excel workbook and select a blank worksheet. On the Smart View ribbon, click Paste.

      The chart is pasted into the Excel worksheet.

    Copying and Pasting Views from Oracle BI to Smart View

      You can copy and paste views from BI Answers to Smart View clients. When you refresh pasted views, the data is refreshed, but the analysis definition is not, even if its definition has been modified in BI Answers.

      Log on to BI Answers. Open a browser window and enter the URL for the Oracle Business Intelligence Sign In page (http://obieesample.us.oracle.com:9704/analytics). Enter the user ID prodney and the password Admin123, and click Sign In.

      By default, the OBI EE 11.1.1.7.0 Samples Application Index is displayed. Under "3. Analysis and Dashboards," expand 3.40 Dashboards Design, and then click Classic Prompts.

      The Prompts dashboard page is displayed. Notice that the two analyses in the center of the page (Attribute Prompts and Un-Prompted Control Report) include a Copy link at the bottom of the view. The analysis must be enabled for copying for this link to be displayed.

      Under the Un-Prompted Control Report, click Copy.

      Open Excel, and select a blank worksheet. On the Smart View or Oracle BI EE ribbon, click Paste.

      The view is pasted into the Excel worksheet.

Creating and Publishing Views Using the View Designer

    Using the View Designer in Smart View, you can create a new ad hoc view based on an Oracle BI EE subject area. These views can be published to the catalog and edited in BI Answers. Note: The View Designer is available only in Microsoft Excel.

    When you create a view, you must first define the view type, display style, and layout. Next, you define the filter expressions for the view. Finally, you publish the view to the BI Catalog.

    In Excel, select a blank worksheet. In the Smart View panel, right-click the Catalog Root node in the catalog tree, and then select Create New View.

    The View Designer is displayed. Notice the following:

    • Oracle BI EE subject areas are listed on the left.
    • The View Type drop-down list enables you to select a view type.
    • The Design Layout tab is where you define the view layout.
    • The Define Filters tab is where you define filters for the view.

    Create a pivot table view. In the View Type drop-down list, select Pivot Table.

    Note: The following view types are available in the View Designer:

    • Table
    • Pivot Table
    • Line Graph
    • Bar Graph
    • Column Graph
    • Area Graph
    • Pie Chart
    • Scatter Graph
    • Stacked Column Graph

    The options in the Display Style drop-down list depend on the selected view type. The display options available in the View Designer correlate to the display options available when you insert existing views (for example, you can display pivot tables either as pivot tables or Excel pivot tables, and you can display charts either as Office chart objects or images). In the Display Style drop-down list, select Pivot Table.

    You create the view by dragging columns from the subject area tree and dropping them into the layout area. Note: The layout area varies depending on the type of view you are creating. In the Subject Area tree, expand A - Sample Sales > Products, and drag P4 Brand to the Row layout area.

    Complete the view layout as follows:

    1. Drag T05 Per Name Year (located under A - Sample Sales > Time) to the Column area.
    2. Drag 1 - Revenue (located under A - Sample Sales > Facts > Base Facts) to the Measures area.
    3. Drag D4 Company (located under A - Sample Sales > Offices) to the Page area.

    Add grand totals to the rows and columns:

    1. In the Row area, select Row Grand Total.
    2. In the Column area, select Column Grand Total.

    You want to limit the view to data between 2010 and 2012. In the Column area, right-click the tile for T05 Per Name Year, and select Add to Filter.

    The Define Filters tab is displayed, with the T05 Per Name Year tile. Define a filter for the column:

    1. In the drop-down list next to T05 Per Name Year, select is in.
    2. Click the Browse (ellipsis) button to open the Value Selector dialog box.
    3. Select 2010, 2011, and 2012.
    4. Click the Add (right arrow) button, and then click OK to close the Value Selector dialog box.

    Notice that the value selection is displayed in the Filter Summary.

    Note: You can drag columns from the subject area tree directly to the Define Filters tab. Additional columns are added to the Filter Summary as an AND condition; you can change them to OR conditions if necessary.

    Click OK to add the view to the Excel worksheet. Note: After closing the View Designer, you cannot edit the view definition in Excel. However, you can publish the view to BI Answers and edit it there.

    The pivot table is displayed in Excel. Notice that the page selections for Company are not displayed in the Excel worksheet.

    Change the page value:

    1. In the Oracle BI EE ribbon, click Edit Page Prompts.
    2. The Page Selector is displayed, with the current company (Genmind Corp) selected. Select Stockplus Inc., and then click OK.

    Notice that the pivot table has been refreshed with data for Stockplus Inc.

    You can publish the pivot table to the BI catalog, and edit it in BI Answers:

    1. On the Oracle BI EE ribbon, click Publish View.
    2. The Save Analysis dialog box is displayed. Double-click My Folders.
    3. In the Analysis Name text box, enter PivotExample, and then click Save.
    4. At the success message, click OK.

    Edit the pivot table in BI Answers:

    1. In the Smart View panel, under OracleBISample > Catalog Root > My Folders, right-click PivotExample and select Edit Analysis in Answers.
    2. The Oracle Business Intelligence Sign In page is displayed. Sign in as weblogic/Admin123.
    3. The pivot table is displayed in edit mode in BI Answers. Click the Criteria tab to update the pivot table definition.
    4. Under Filters, next to the year filter, click the Edit Filter (pencil) icon.
    5. The Edit Filter dialog box is displayed. Remove 2010 data from the pivot table: Next to the Value text box, click the drop-down arrow to display the list of values.
    6. Deselect 2010, and then click OK to update the filter definition.
    7. The year 2010 is removed from the filter definition. Click the Save Analysis icon to save your changes.

    View the edited pivot table in Smart View: In Excel, select a blank worksheet. In the Smart View panel, under OracleBISample > Catalog Root > My Folders > PivotExample, right-click Pivot Table 1, and select Insert.

    The pivot table is displayed in Excel. Notice that the 2010 data has been removed from the table.

Summary

    In this tutorial, you have learned how to:

    • Set up Smart View
    • Insert Oracle BI EE views into Smart View clients
    • Work with views in Smart View clients
    • Copy and paste Oracle BI EE objects into Smart View clients
    • Create and publish views using the View Designer

    Resources

    The following resources are available online for more information relating to Smart View, Oracle BI EE, and the Oracle BI EE Sample Application v305:

    Credits

    • Lead Curriculum Developer: Lisa Alexander
    • Other Contributors: Shubho Bhattacharya

To navigate this Oracle by Example tutorial, note the following:

Hide Header Buttons:
Click the title to hide the buttons in the header. To show the buttons again, click the title again.
Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.