This tutorial covers how to install and configure Oracle Business Intelligence Office (BI Office) Add-Ins for Microsoft Office, and configure
connections for the Add-Ins. This tutorial guides you to work with the enhanced BI Office Add-Ins for Excel and PowerPoint. You learn to insert and work with different report views from Oracle Business Intelligence Answers (BI Answers)
into Microsoft Excel (Excel) and PowerPoint.
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.
This release of Oracle BI Enterprise Edition (OBI EE) 10.1.3.4.0 provides improved versions of Oracle BI Add-Ins for Microsoft Office applications, Excel and PowerPoint. Using these Add-Ins, you can easily insert, deploy, and analyze business intelligence reports in Excel and PowerPoint. You can insert Oracle BI EE objects such as table views and chart views as native MS Office objects. This ensures that a single version of the truth is available to users across an enterprise. Some of the highly enhanced new features of the current version include adding gauge and funnel views, enhanced prompts and level selections, and so on. (A few key features of BI Office are listed below.)
With BI Office you can:
![]() |
Insert and manage BI tables and pivot tables into Excel as refreshable objects |
![]() |
Insert and manage BI tables into PowerPoint as refreshable objects |
![]() |
Insert BI charts, and change the chart type, colors, and other chart properties in Excel and PowerPoint (All changes are preserved during refreshes of the chart.) |
![]() |
Apply Excel-specific formatting to BI data: data formats, fonts, and conditional formats that are retained when you refresh your BI data |
![]() |
Copy and paste, by a single click, BI requests from Answers and Oracle Business Intelligence Interactive Dashboards (Interactive Dashboards) and still be able to refresh the data |
![]() |
Insert compound BI views in Excel or PowerPoint by using the BI copy-and-paste functionality |
![]() |
Insert funnel and gauge views in Excel and PowerPoint |
![]() |
Edit prompts and levels in a view |
![]() |
Secure and refresh your BI data in BI Office documents |
In this tutorial, you learn how to install and configure BI Office 10.1.3.4. BI Office includes the Oracle BI Office Server (BI Office Server) and the Oracle BI Add-In for Microsoft Office client, which in turn includes the BI Office Excel and PowerPoint Add-Ins. Also, you learn to use the enhanced BI Office Add-Ins for Excel and PowerPoint.
In this tutorial, you use the Paint.rpd repository and the Paint Demo presentation catalog provided with the product. To perform the steps listed in this tutorial, you should have the required software as listed in the Software Requirements section (below). Also, you should have met the prerequisites as listed in the Prerequisites section.
The following is a list of software requirements:
1.. | Oracle BI Enterprise Edition 10.1.3.4.0 should be installed. | ||||
2. | Ensure that the default repository, Paint.rpd, is available to manage the prebuilt report views and create new report views. | ||||
3. | Ensure that you have one of the following client operating systems:
|
||||
4. | Ensure that you have one of the following versions of Microsoft Office:
|
||||
5. | Microsoft .Net Framework 2.0 is required for the BI Office client.
|
||||
Note: To import reports from the Oracle Business Intelligence Publisher (BI Publisher) repository into Excel, you must use the BI Publisher Analyzer for Excel. Both these Add-Ins—BI Office Add-In for Excel and BI Publisher Analyzer for Excel—can coexist. For more information about BI Publisher Analyzer for Excel, see the Oracle Business Intelligence Publisher User's Guide. |
1. | Before starting this tutorial, you should create a PowerPoint presentation named Sample_BIOffice.pptx with one slide that is a "Title slide." |
2. | For this tutorial, Presentation Services, BI Office, and the BI Office Server are located on the same machine. The BI Office Server is deployed locally to an Oracle Application Server Containers for J2EE ( OC4J) container. |
This topic describes the process of downloading and running the installation wizard for BI Office.
1. | For this installation, you must log in to Answers from Interactive Dashboards. From the Start menu, select All Programs > Oracle Business Intelligence > Presentation Services. Enter Administrator as the username and password to log in. Paint Dashboard appears. Click the More Products link and then select Download Oracle BI for Microsoft Office. A dialog box prompts you to save or run OracleBIOffice.exe. Save the file to your local directory.
|
||||||||
2. |
Close the Excel or PowerPoint applications on your system, if open. Navigate to the saved location and double-click OracleBIOffice.exe. InstallShield Wizard appears.
When the Welcome page appears, click Next.
|
||||||||
3. | Enter your username and company name. Select who will be using the application. Click Next.
|
||||||||
4. | Select the installation type. Typical performs an installation for both Excel and PowerPoint Add-Ins to C:\Program Files\Oracle\BIOffice. Custom enables you to select the install directory and choose the components to install. Select Typical as the installation type, and click Next.
|
||||||||
5. | Review the setup information and then click Next.
|
||||||||
6. | The software installs.
|
||||||||
7. | When the InstallShield Wizard completes, click Finish.
|
||||||||
8. | When Oracle BI EE 10.1.3.4. was installed on your computer, the BI Office Server was installed automatically. The installer performs other configuration tasks such as the following:
The following image depicts a sample bioffice.xml file, which contains critical information used by the client to connect to Presentation Services. (Observe the highlighted line in the file, which defines the URL for the Presentation Services.) You create the connection in the BI Office client in the next step.
Note: If any of the above configuration tasks were not performed due to some reason, you can look up the documentation available here to perform these installation and configuration tasks manually.
|
||||||||
9. | Configure the BI Office client to associate the BI Office Add-Ins with Presentation Services: To add connection information to the client, you begin by opening either Excel or PowerPoint. To open Excel, select Start > All Programs > Microsoft Office > Microsoft Excel 2007.
In the Excel window that opens, select Oracle BI > Preferences from the Microsoft Office 2007 Ribbon.
Note: MS Office 2007 has an entirely new UI, the most notable of which is the tabbed tool bar, Office Ribbon (see the screenshot above). The Office Ribbon replaces the traditional MS Office menus and toolbars.
|
||||||||
10. | When the Preferences dialog box opens, ensure that the Connections tab is selected, and then click New.
Enter the following information in the Connection Detail dialog box:
Click Test Connection.
Note: If you have given a different name for the application than the default value of bioffice, enter the name of the application accordingly while creating the connection. You can also edit the connection details after it is created, select the connection from Oracle BI > Preferences, and then click Edit. Also, if your environment is secure sockets layer (SSL) enabled, refer to the Oracle Business Intelligence New Features Guide for additional configuration requirements.
|
||||||||
11. | If the connection is established successfully between the Excel Add-In and the server, a success message is displayed. You can also click Show Details to see the connection details. Click OK.
Click OK again to close the Connection Detail dialog box. Note: Connections are common to Excel and PowerPoint—that is, the connection you created in Excel is available in PowerPoint too. However, in this instance, you have to exit Excel once, so that the connection information is reflected in PowerPoint. . |
Now that you have successfully installed the BI Office client and configured the connections, you are ready to start using the BI Office Add-Ins. In this topic, you are guided to use the features in the BI Office Add-Ins that enable you to analyze and deploy Business Intelligence reports easily in Microsoft Office applications, Excel and PowerPoint.
Some key features provided by BI Office are listed here:
![]() |
You can copy views from Answers and Interactive
Dashboard pages and paste them into Excel and PowerPoint documents by
using the Paste feature of BI Office Add-Ins. Copied views are pasted
as Office documents (Excel or PowerPoint tables and charts). This functionality
also extends to copying compound views from Answers and Dashboards. Compound
views copied from Answers or Dashboards are pasted into Microsoft Office
documents as native Microsoft Office tables and charts. |
![]() |
For every request, the flat data view provides the entire data set for the original request in a simple tabular format. This view is more suitable for use when users want to obtain only the data and then use the Excel functionality to perform further analysis on that data. |
![]() |
The table view displays results in a tabular format. Users can navigate through the results, add totals, customize headings, and change the formula or aggregation rule for a column. |
![]() |
The pivot table view, available in the BI Office Excel
Add-In, presents data from BI Requests with page items as well as a section-based
layout if these sections were defined in the BI pivot table view for the
request in Answers. |
![]() |
A BI Presentation Catalog browser is provided in Excel and PowerPoint to browse requests (both user created
and shared). |
![]() |
Support is provided for prompts defined in the BI request. |
![]() |
If a view selected for insertion has multiple levels
from one or more dimensions, you can choose to limit the data for each
dimension by deselecting the levels from a level-selection dialog box. You can also edit the prompts and levels after the view is inserted . |
![]() |
You can insert table and chart views into PowerPoint presentations as PowerPoint tables and charts (subject to the limitations of PowerPoint). |
![]() |
You can insert chart views as images in Excel spreadsheets
and PowerPoint presentations that can be refreshed. |
![]() |
You can insert chart views as high-quality Flash objects in PowerPoint presentations that can be refreshed. |
![]() |
For BI chart views inserted as native Excel or PowerPoint charts, you can change the chart type and apply other formatting changes by using Excel and PowerPoint charting capabilities. These changes are preserved during refreshes. |
![]() |
You can define Excel-specific conditional formats to
data from BI views. These conditional formats can be preserved during
data refreshes. |
![]() |
You can add gauge and funnel views in Excel and PowerPoint for better analysis of the reports. |
![]() |
You can secure a slide or worksheet, or you can secure the entire presentation or workbook. When you secure a particular object in Excel or Powerpoint, all BI views on that object—that is, slide, worksheet, presentation, or workbook are secured. Users must authenticate themselves before they can refresh and view this secured BI data. Secure communication is handled through SSL. |
![]() |
Using BI Office Add-In with Excel | |
![]() |
Using BI Office Add-In with PowerPoint | |
![]() |
Working with Compound Views and More BI Office Features in Excel and PowerPoint |
In this topic, you work with basic views such as table, chart, and pivot table. This topic shows you how to log in to BI Office in Excel, locate a table and insert the data as a list (flat data) into Excel (so that you can manipulate the data by using Excel capabilities), copy a chart from the Presentation Catalog, switch between Excel and Answers to modify the chart, and copy and paste this chart into Excel. You also insert the pivot table view into Excel.
1. | To use BI Office Add-Ins, you must log in. Select Start > All Programs> Microsoft Office > Microsoft Excel 2007 to launch Excel. From the Oracle BI menu on the Ribbon, select Login. (Observe the Oracle BI–specific options added in the toolbar. Note that these menu options are added in Excel during the installation of BI Office Add-Ins.)
| |||||||||||||||||||||
2. | The Login dialog box appears. Ensure that the connection you created before (localhost) is selected from the Connection drop-down box. Enter Administrator in both the User ID and Password fields, accept the default for the remaining fields, and click Login.
You are now connected to Presentation Services.
|
|||||||||||||||||||||
3. | The Presentation Catalog appears in Oracle B I Task Pane on the right.
To view catalog details, click the plus sign
Also note that the Presentation Catalog can be hidden or displayed by clicking the Oracle BI Catalog icon on the toolbar.
|
|||||||||||||||||||||
4. | Click the plus sign (
Click the plus sign (
|
|||||||||||||||||||||
5. | Click the plus sign (
The following table identifies the types of request views that are supported, and can be inserted (or copied and pasted) into Excel from the Presentation Catalog:
Right-click Table and
select Insert as List from the shortcut menu. The Edit Prompts and Levels dialog box appears. Accept the default options and click Insert.
Note: The Edit View option launches Answers in your browser, which in turn enables you to modify the request. The entire data set for the original request appears in the worksheet area. (Note that the worksheet is named tableView). All Excel capabilities are available for use with this data.
|
|||||||||||||||||||||
6. | Click any cell in the data area. Pivot icons
(
|
|||||||||||||||||||||
7. | In this step, you create a new worksheet and
insert the chart associated with the list (flat data) view that you just inserted. Click the Insert Worksheet icon
|
|||||||||||||||||||||
8. |
In the Presentation Catalog that is already open in the right pane,
right-click Chart under the Sales by Brand for Current Year Trends request, and select Insert
from the shortcut menu.
Note: The "Insert as Image" option enables you to add the chart as a static image to Excel. The 3D Line chart from the request appears in the newly added worksheet.
|
|||||||||||||||||||||
9. | Double-click the chart to view data associated with the chart values.
|
|||||||||||||||||||||
10. | In this step, you edit the chart request in BI Answers. In the Presentation Catalog pane, right-click Chart and select Edit View from the shortcut menu.
The Login dialog box for the Presentation Services appears in the browser. Enter Administrator in both the User ID and Password fields and click Log In.
|
|||||||||||||||||||||
11. | The chart view request opens in Answers. Click
the Edit View icon
The Edit window appears.
|
|||||||||||||||||||||
12. | In this step, you change the chart type. From the toolbar directly above the chart, select Pie from the Graph drop-down list.
The 3D line chart becomes a 3D pie chart .
|
|||||||||||||||||||||
13. | In the chart pane on the left, under the
Legends icon (
Click Redraw. The year is added to the legend. Click OK.
|
|||||||||||||||||||||
14. | The request page appears again with your changes. Click the Copy link found on the lower-left corner of the request page to copy this chart view to the clipboard. Return to Excel.
Note: The Copy link is also used to copy compound views of a request from dashboards into both Excel and PowerPoint. You work with compound views in Interactive Dashboards in the next topic.
|
|||||||||||||||||||||
15. | In Excel, click the Insert Worksheet icon
A message window may be displayed showing the unsupported views (such as the title view) in the request, which cannot be pasted in the worksheet. Click Show Details to see the details of these views (After seeing the details, if you want to close the details, then click Hide Details). Click OK.
The chart view appears on the newly added Excel worksheet.
|
|||||||||||||||||||||
16. | In this step, you create
a new worksheet and insert a pivot table view. Insert a new Excel sheet into the workbook. (Click the Insert Worksheet Click the plus sign (
Click the plus sign (
|
|||||||||||||||||||||
17. | Right-click Pivot Table1 and select Insert from the shortcut menu.
When the Edit Prompts and Levels dialog box appears, click Insert.
The pivot table view is added to a new worksheet labeled pivotView.
| |||||||||||||||||||||
18. |
You can manipulate this pivot table as you would manipulate any pivot table in Excel. To view the Markets detail for a different color, select a new color, Black, from the drop-down list and click Refresh from the BI Office menu.
The pivot table looks like the following screenshot, after refreshing:
Similarly, select Burgundy from the Color drop-down list and refresh the worksheet. The pivot table view refreshes with the appropriate data for the color burgundy.
|
|||||||||||||||||||||
19. | In Excel, click the Office button
Note: Leave this Excel workbook open to use in the following topics. |
You can also insert various request views from the Presentation Catalog into PowerPoint ( as in Excel). This topic shows you how to work with BI Office PowerPoint Add-In. You learn to insert table and chart views from Oracle BI Catalog, insert a chart as a static image, and insert a chart as a Flash object into PowerPoint.
1. | Select Start > All Programs > Microsoft Office > Microsoft PowerPoint 2007 to launch PowerPoint. Open the PowerPoint presentation Sample_BIOffice.pptx that you created as a prerequisite. Now you add a chart from the Presentation Catalog. (First, you create a blank slide.) From the menu, click the New Slide icon. (A new slide is inserted.)
Also, select Blank as the layout option from the Layout drop-down list .
|
2. | Click Oracle BI in the MS Office Ribbon. Click Login to connect to Presentation Services through PowerPoint. Enter Administrator in both the User ID and Password fields, accept the defaults for the remaining fields, and click Login.
Note: You should have created a connection in PowerPoint, like you have in Excel in the topic titled "Installing and Configuring Oracle BI Office." If you have not created a connection, select Preferences from the Oracle BI menu and create a new connection by using the steps 9–11 of the first topic. The Presentation Catalog appears in the right pane.
Also, note that the Oracle BI menu options are similar to the ones in Excel.
|
3. | Expand the Paint Demo node under Shared Folders. Navigate to Sales Summaries > Brand Analysis folder, and select the Sales by Brand for Current Year Trend request.
Right-click Chart and select Insert as Image.
A processing message appears.
|
4. | When processing completes, a static image of the chart is inserted. Click the image to view the sizing handles.
Drag the sizing handle to enlarge the image. The enlarged image of the chart looks like this:
Note that the Picture toolbar opens when you select the image, indicating that this is a static image.
|
5. | In this step, you insert a table from the Presentation Catalog. Insert another blank slide in the presentation. (Click the New Slide option and select Blank as the layout.)
In the Presentation Catalog, expand Top & Bottom Performers under the Paint Demo node, and expand the
|
6. | When the Edit Prompts and Levels dialog box appears, accept the default values, and click Insert.
The table for the Top 5 Products request is inserted in the PowerPoint slide.
Save the changes.
|
7. | In this step, you learn to insert a chart view
as a Flash object. From the Sales Summaries > Brand Analysis folder, expand Color Ranking by Units Sold.
|
8. | Right-click Chart and select Insert as Flash.
A processing message appears.
|
9. | A Shockwave Flash object appears in the slide.
|
10. | Click to select the Flash object. With your cursor positioned on the lower-right handle of the object, drag the cursor to the lower-right corner of the slide as indicated in the following image:
The slide refreshes and the Flash object appears.
|
11. | Click the Slide Show (
|
12. | Right-click the chart to display the shortcut menu and select Zoom In.
|
13. | The chart zooms in and reappears.
Note: No degradation of the chart occurs. Embedding BI Charts as Flash objects allows you to maintain better quality and avoid the pixelation that is inherent with PNG and JPG images. Save the changes to the presentation. Leave the presentation open.
|
In the previous topics, you should have learned to work with the basic features of BI Office Add-Ins for Excel and Power Point, such as inserting table view, table view as a list (flat data), and inserting chart as a BI Object, a static image, and also as a Flash object.
In this topic, you work with compound views and more BI Office features. First, you modify a request to create a compound view consisting of table, chart, funnel, and gauge views in Answers. You add cascading prompts to the request. Cascading prompts are defined when the values of one column are dependent on the other. (For example, if you have region and country columns in your request, you can define the prompts on these columns as cascading prompts, so that when you select a region, only the corresponding country values are retrieved in the prompts.)
After creating the required views and prompts in the request, you insert these views into PowerPoint and Excel. You work with many enhanced or new features of the product such as inserting compound views with gauge and funnel views, editing prompts and levels, securing BI data in Excel and PowerPoint, refreshing the data in the Excel sheet, preserving conditional formats in Excel, and so on. You also learn how to insert views from Interactive Dashboards.
![]() |
Working with Compound Views and More BI Office Features in PowerPoint | |
![]() |
Working with Compound Views and More BI Office Features in Excel | |
![]() |
Working with Compound Views from Interactive Dashboards |
Working with Compound Views and More BI Office Features in PowerPoint
In this topic, you work with compound views and enhanced features of BI Office Add-In for PowerPoint.
Note: The steps listed in the following subtopics are continuous, so do not close or log out from any application unless the steps instruct you to do so.
Modifying a Request to Add Gauge and Funnel Views
1. | Go to the PowerPoint window, where the Sample_BIOffice.pptx presentation is open. Ensure that the Presentation Catalog is open in PowerPoint. (If not logged in, log in from the Oracle BI menu on the Office Ribbon, and click Oracle BI Catalog to display the Presentation Catalog.)
Insert a blank slide with a title. (Select the slide layout as Title Only in PowerPoint.)
|
2. | In the Presentation Catalog, expand Paint Demo > Sales Summaries > Brand Analysis and locate the Finish Sales Trend Targets request .
Note that this request has table and chart views defined in it. You modify this request to add gauge and funnel views and prompts.
|
3. | In this step, you edit the request in Answers. Right-click any of the chart or table views for the request, and click Edit View. Log in to Presentation Services by entering Administrator as the username and password.
The request view is opened in Answers, so that you can modify it :
|
4. | In this step, you modify this request to add the required columns.
b.) Expand the Products node and click Brand to add this column to the request. The Brand column is added to the request and is displayed on the Criteria tabbed page.
c.) Drag Brand and drop it to before the Finish column. Your screen should look like this:
|
5. | In this step, you modify the request to display the table view, and add gauge and funnel views in the compound view. a.) Click the Results tab. On the Results tabbed page, click Add View and select Table from the list to display the table view in the compound layout.
b.) Click the Add View link again, and this time, select Gauge to add the gauge view to the request. The gauge view appears showing the gauge set for the request. Accept the default options. Click OK.
c.)This takes you back to the Compound Layout view. Click Add View and select Funnel Chart to add the funnel view to the request.
d.) The Funnel Chart view appears showing the funnel chart options. Enter or select the following options for the funnel view:
|
6. | Note that the request compound layout now has a chart, a table with the added columns, and gauge and funnel views added to it. Scroll down to see the various views.
|
7. | Click the Save Request icon
|
Adding Cascading Prompts to the Request, Copying and Pasting Compound Views in PowerPoint
In this topic, you define cascading prompts on the Brand and Finish columns for the request you modified above. (Note that the finish for a product is dependent on the brand, so that only the corresponding values for a brand should be reflected in the Finish prompt.). Then you copy any paste the compound views in PowerPoint.
1. | Click the Prompts tab. Click Create Prompt and select Column Filter Prompt from the list.
|
2. | In Column Filter Prompt Properties that appears, select the following options to define a prompt on the Brand column:
|
3. | Now you create a prompt on the Finish column, so that the Brand and Finish prompts are defined as cascading prompts. Click Create Prompt, select Column Filter Prompt from the list, and select the following options to define the prompt on the Finish column:
Do not forget to save every change to the request.
|
4. | On the Prompts tabbed page, click Test Prompts to see what the results look like with prompts:
BrandPrompt appears. Note that all the values for the brand are listed. Click Enterprise to select this value. This value is added to the list on the left. Also, select Magicolor and McCloskey values for the brand, and click Next Prompt. (See the screenshots below):
|
5. | The next page displays the values for FinishPrompt. Click Skip Prompt to add all the available values for Finish.
Note: You selected the option to allow the user to skip prompts when creating BrandPrompt and FinishPrompt. Selecting the Skip Prompt option selects all the available values for that prompt. |
6. | The compound view appears showing the results for all the views that you added. (The preview shows the results in the way they would appear on a dashboard). Scroll down to see the gauge and funnel views.
|
7. | Switch to the PowerPoint window, which has Sample_BIOffice.pptx open. Select the new blank slide that you have inserted before, from the Oracle BI menu. Click Paste.
|
8. | The message alerting that unsupported views will not be pasted may be displayed. You can see the details. Click OK.
Note: You can switch off this message by setting the preferences. |
9. | All views in the request are pasted in the slide. (These may not fit well in one slide.) Move around the views and resize them. The slides with different views:
Note: Observe that the table view is inserted as a native Office table, the chart view is inserted as a native Office chart object, and funnel and gauge views are pasted as images (with image handlers), and all these objects are refreshable.
|
Editing Prompts and Levels for BI Views and Refreshing Data in PowerPoint
1. | Now go to the slide with the table view. Select the table view, and from Oracle BI menu, select Edit Prompts and Levels.
|
2. | In the Edit Prompts and Levels dialog box that appears, click Select beside BrandPrompt to change the brand values.
|
3.. | The Select Prompt Values dialog box appears. Note that previously defined prompt values are already listed in the Selected list. In this dialog box, exclude McCloskey from the Selected values list for brand. (See the screenshot below.)
|
4. | This takes you back to the Edit Prompts and Levels dialog box. Deselect the Skip option for FinishPrompt. (Previously, you chose to skip this prompt in the view.)
|
5. | Note that Gloss is the only finish available for Enterprise and Magicolor brands (cascading prompts). Select this value and click OK.
This takes you back to the Edit Prompts and Levels dialog box. Click OK again.
|
6. | Click Refresh from the Oracle BI menu to refresh this table view. The data refreshes and appears in the slide.
Note: You can also select the Refresh All option from the BI menu to refresh all the BI data in the presentation.
|
7. | Also, you can edit the prompts and levels for each of the BI views inserted. If you have time, go to the slide where you have funnel and gauge views (in the example, the slide titled Units by Brand and Finish), and edit the prompt values for the gauge and funnel charts to see different views: A few such views are shown here:
|
Modifying Chart Views in PowerPoint
1. | Go to the slide in which you have copied and pasted the chart view from the compound view (in the example, the slide titled Units by Finish). Right-click the chart and select Chart Object > Edit from the shortcut menu.
|
2. | The chart appears in Edit mode along with the data sheet. Right-click the chart and select Chart Type.
. |
3. | Select an appropriate chart type and subtype. Click OK.
|
4. | The following chart appears:
Note that you can modify the chart object in PowerPoint to change the legends, fonts, data series colors, and so on.
|
5. | Click Refresh All from the Oracle BI menu in PowerPoint to refresh all the refreshable objects in the presentation.
The refresh processing dialog box appears, which shows the list of BI objects refreshed.
|
Securing BI Data in PowerPoint
In this subtopic, you secure the BI data in the presentation. When you secure a slide or a presentation, the data is secured or hidden, though the metadata is retained. Only when an authentic user (who is logged in) refreshes the data, the data is displayed in the slide or presentation.
1. | To secure the data in the current slide (containing funnel and gauge views), from the Oracle BI menu, select Secure Slide.
The funnel and gauge views in the slide are secured.
|
2. | You can also secure the entire presentation. When you select Secure Presentation, the data in all the BI objects is secured.
|
3. | Save the changes to the presentation. After the data is secured, you can refresh the data to view the data in the presentation. (You should be logged in to refresh the data after it is secured.) |
Working with Compound Views and More BI Office Features in Excel
In this subtopic, you work with compound views and enhanced features of BI Office Add-In for Excel.
![]() |
Copying and Pasting Compound Views in Excel | |
![]() |
Applying and Preserving Conditional Formats in Excel | |
![]() |
Securing BI Data in Excel |
Copying and Pasting the Compound View in Excel
1. | Go to the Excel window that has OBI_Workook.xslx open. (Ensure that you are logged in and the Presentation Catalog is open.) Add a new worksheet.
|
2. | Now go the Answers/Dashboards preview window that has the same compound view with prompts that you have created in the Finish Sales Trend Targets- With Gauge and Funnel Views request. (If you are not logged in, you are prompted to log in to Presentation Services. Log in as Administrator.)
|
3. | Click the Copy link found at the bottom of the view. (Note that you have selected McCloskey for BrandPrompt, and Flat and Gloss as the values for FinishPrompt. The same are reflected in the compound view.)
|
4. | From the Oracle BI menu in Excel, click Paste to paste the various views in the compound view. |
5. | A message alerting that unsupported views will not be pasted may appear. See the details and click OK.
You can turn off this message in Excel too. Select Oracle BI > Preferences and deselect the Show list of unsupported BI views after paste from BI Clipboard option.
|
6. | Note that various views are pasted into the Excel sheet. Adjust the views to see all the views.
Note that you can edit the prompt levels for the views and edit chart objects in Excel, just as you did in PowerPoint. Perform the following:
The Excel sheet should look like this:
|
7. | Copy the gauge and funnel views into a separate (new) worksheet. (If you have time, you can also edit the prompts for gauge and funnel views separately. ) |
Applying and Preserving Conditional Formats in Excel
In this subtopic, you add conditional formats on the Excel sheet, and refresh the worksheet to preserve the conditional formats.
1. . | In Excel, click Home. Select the Dollars column in the table and select Conditional Formatting from the drop-down list. Select Color Scales and select Green - Yellow - Red Color Scale. (See the screenshot below).
|
2. | If you refresh the sheet now, the conditional formats will not be retained. To retain the conditional formats, select Oracle BI > Preferences. On the General tabbed page, select the Preserve Conditional Formatting check box. |
3. | From the Oracle BI menu, select the Refresh option. Observe that the conditional formats in Excel are retained on the refreshed sheet. |
4. | You can also select the Refresh All option. This refreshes all the BI objects in the workbook. Save the changes to the workbook. Note: You can also modify the table view and format the cells in Excel. |
In this subtopic, you secure the BI data in Excel. You can secure the data either in a worksheet or workbook:
1. | . From the Oracle BI menu, select the Secure Workbook option.
|
2. | The secure process dialog box is displayed : |
3. | Note that the data in all the worksheets is secured. (The worksheet that you modified in the previous steps is shown here as secured.)
You can refresh all the worksheets to see the data (if you have logged in as an authentic user). |
Working with Compound Views from Interactive Dashboards
You can also insert compound views from Interactive Dashboards directly into PowerPoint and Excel. (These views can also be edited in the same manner as the Answers views). In this subtopic, you insert a compound view from the dashboards into an Excel sheet.
1.. | Insert a new sheet in the Excel workbook. In the Presentation Catalog, navigate to Paint Demo > Sales Summaries > Regional Analysis, and locate the Regional Revenue request. Right-click and select Edit View.
|
2. | Log in to the Presentation Services as Administrator. The request opens in Answers. Click Dashboards to open the Paint Dashboard.
|
3. | Click the Regional Analysis tab, where the Regional Revenue request is published. (You can scroll down to see the Regional Revenue request on the dashboard.)
Note that the Copy link is not displayed for the request. Select Edit Dashboard from the Page Options drop-down list (found on the upper-right corner).
|
4. | On the dashboard layout page, scroll down to see the Regional Revenue request, click Properties, and select Report Links.
Ensure that Copy is selected in the Report Links. (Select other links to display on your dashboard as desired.) Click OK.
Click Save to save the changes to the dashboard.
|
5. | On the Regional Analysis dashboard page, scroll down to see the Regional Revenue request, and click the Copy link.
|
6. | Switch to Excel, where your workbook is open. Select Paste from the Oracle BI menu to copy this view from the dashboard in an Excel sheet. (It looks like the screenshot below).
|
7. | Now go back to the dashboard to see the Regional Revenue request, and click one of the bars for the central region.
This opens the compound view. Click the Copy link (highlighted) to copy the view.
Switch to Excel again and click Paste from the Oracle BI menu. (Paste it in a new worksheet.) You can see that the various views from the dashboard's compound view are pasted on the Excel sheet. Save the workbook. Note: You can manipulate these views as you have done with the other views from Answers. |
The current version of Oracle BI Office supports many server-side features such as NT Lan Manager (NTLM) support, data compression, and so on. These changes are reflected in the BI Office Server configuration file (bioffice.xml). This topic briefly covers some such changes made to the bioffice.xml file to support some interesting features:
The default bioffice.xml file is displayed in the screenshot below, with the new properties highlighted in red:
|
|
![]() |
NTLM Support: The first highlighted portion in the screenshot above refers to NTLM support. BI Office connections running on Windows clients that use an Active Directory such as the Light Weight Directory Access (LDAP) server for Windows authentication can now log in without requiring the user to enter an Oracle BI EE login username and password. This is applicable only if the OBI EE authentication is also taking place against the same Active Directory server. |
![]() |
Data Compression: The settings or properties in the other highlighted section of the configuration file refer to data compression. You can specify whether to compress the data on the server before sending to client. You can also set the properties to specify the minimum number of bytes to trigger compression to optimize performance. (On the client side, compressed data is detected and decompressed automatically.) |
In this lesson, you should have learned how to:
![]() |
Install and configure the BI Office client | |
![]() |
Log in to BI Office and insert the following elements from Answers into Excel and/or PowerPoint: dynamic flat data, table, pivot table, chart, and so on | |
![]() |
Work with compound views and more advanced features such as editing prompts, inserting funnel and gauge views, and so on | |
![]() |
Securing and refreshing data in workbooks, worksheets, slides, or presentations in Excel and PowerPoint | |
![]() |
Preserving conditional formats in Excel | |
![]() |
Inserting views from Interactive Dashboards |
If you need additional information, refer to the following:
![]() |
Oracle Business Intelligence Enterprise Edition Documentation |
![]() |
Additional OBEs on BI on the OTN Web site. |
Place
the cursor over this icon to hide all screenshots.