Using Oracle Business Intelligence Office Add-Ins with Oracle Business Intelligence Enterprise Edition (10.1.3.4) on Windows

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:

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.

Back to Topic List

 

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.

Back to Topic List

 

The following is a list of software requirements:

1..
2.
3.

Ensure that you have one of the following client operating systems:

Windows XP

Windows Vista

4.

Ensure that you have one of the following versions of Microsoft Office:

Microsoft Office 2003

Microsoft Office 2007

Note: This tutorial uses Microsoft Office 2007.
However, if you are using MS Office 2003, you can still follow the instructions in this tutorial, but the screens will differ. Also, in this case, the presentations or workbooks will be saved in the Office 2003 format.

 

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.

Back to Topic List

 

1. Before starting this tutorial, you should create a PowerPoint presentation named Sample_BIOffice.pptx with one slide that is a "Title slide."
2.

Back to Topic List

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.
Note: Microsoft .Net Framework 2.0 is required for the BI Office client. If it is not installed on your computer, you are prompted to exit and install Microsoft .Net Framework 2.0 from the Microsoft Web site.

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.
If you have MS Office 2003, the look and feel in Excel 2003 is slightly different, though all the BI Office menus and options remain the same. A few screens from Excel 2003 are displayed here for your observation. From the Excel Menu bar, select Oracle BI. From the Oracle BI menu, select Preferences.

 

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.

.

 

Back to Topic List

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:

 

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

Back to Topic

Back to Topic List

Using BI Office Add-In with Excel

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 to expand the Shared and Paint Demo folders.

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 () to expand Sales Summaries.

Click the plus sign () to expand Brand Analysis.

 

5.

Click the plus sign () to expand the Sales by Brand for Current Year Trends request. (Observe the listed views available for this request.)

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:

Table view

Enables you to select levels before insertion into Excel, allowing you to limit the data. You can add Excel formatting to the inserted view. Use this view to see data in the grouped format defined in the Answers request.

Pivot table view

Offers the features of the table view and maintains features defined in Answers (such as page item lists and group sectioning)

Chart view

Inserts charts as defined in Answers

List view (Insert as List)

Available for all table views. This inserts the entire data set for the original request. Use this view when you want to perform filtering, pivoting, charting, and other manipulations in Excel. (In the previous version of BI Office, this was available as flat data view).

Funnel View Displays results as a three-dimensional chart that represents target and actual values by using volume, level, and color. It is useful for depicting target values that decline over time, such as a sales pipeline.
Gauge View Shows results as gauges, such as dial, bar, and bulb-style gauges. (Funnel and gauge are two new views, which are introduced in BI Office 10.1.3.4.0.)
Compound view Provides an assembly of different views on a dashboard. This view must be composed of supported view types (those listed above) to be fully inserted. If the compound view includes unsupported views, only those from this list of views will be pasted into Excel or PowerPoint.

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 () are added to each column heading. These pivot icons allow you to sort the data, or filter unwanted data (as shown in the screenshots below).

 

 

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 (found at the bottom of the workbook, beside the sheet names as shown in the screenshot).

 

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 on the chart (highlighted in the screenshot).

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 () in the column area, select the Year check box.

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 to add a new worksheet. Then click the Paste icon from the BI Office toolbar to add the pie chart to the new worksheet.

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 icon.)

Click the plus sign () to expand Sales Summaries (if it is not already open). Then expand Regional Analysis.

Click the plus sign () to expand the Colors by Region request and select Pivot Table1.

 

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 (found at the upper-left corner of the Excel window) and
select Save As > Excel Work Book to save the workbook. Save the workbook as OBI_Workbook.xslx.

Note: Leave this Excel workbook open to use in the following topics.

Back to Topic

Using BI Office with PowerPoint

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.
In the first slide, which is the title slide, add a name for the presentation (for example, Brand and Units Analysis) and the presenter's name.

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
Top 5 Products request. Right-click Table and select Insert.

 

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.
First, insert another blank slide in the presentation.

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 () icon to view your slide.

 

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.

 

Back to Topic

Back to Topic List

Working with Compound Views and More BI Office Features in Excel and PowerPoint

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
Adding Cascading Prompts to the Request, Copying and Pasting Compound Views in PowerPoint
Editing Prompts and Levels for BI Views and Refreshing Data in PowerPoint
Modifying Chart Views in PowerPoint
Securing BI Data in PowerPoint

 

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.
a.) Click the Criteria tab to see the columns and other criteria of the request. (Note that the Paint subject area is opened in the left pane.)

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:

  • Enter Units by Brand as the name of the chart, and select the Force standard shape and equal stage widths check box to display the funnel view properly.
  • Ensure that Brand is selected as the stage, Units as the actual value, and Year Ago Units as the target value from the respective drop-down lists.
  • Enter 50 in the Minimum Threshold field, and 80 in the Maximum Threshold field.
  • Accept defaults for the other options. Click OK. (Observe the highlighted options in the following screenshot.)

 

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 (found on the upper-right corner) to save the request.
Enter Finish Sales Trend Targets- With Gauge and Funnel Views as the name of the request and save it with this name.

 

Back to Topic

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:

  • Enter BrandPrompt as the caption.
  • Select Brand from the Filter on Column drop-down list and is equal to / is in as the operator.
  • In the How should the user choose a value or values? section, select the Browse through choices and/or type in directly option.
  • In the What values should be shown to the user? section, ensure that All Values is selected.
  • Select Allow user to skip prompt.
  • Click OK. (Observe the highlighted portions in the screenshot.)

 

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:

  • Enter FinishPrompt as the caption.
  • Select Finish from the Filter on Column drop-down list and is equal to / is in as the operator.
  • In the How should the user choose a value or values? section, select the Browse through choices and/or type in directly option.
  • In the What values should be shown to the user? section, select Limited Values.
  • Select Allow user to constrain choices. (This option combined with the Limited Values option ensures that only the corresponding values for Finish are shown for a particular product brand.)
  • Select Allow user to skip prompt.
  • Click OK. (Observe the highlighted portions in the screenshot.)

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.
Click the Copy link (found at the bottom of the view) to copy this compound view.

 

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.
Select Oracle BI > Preferences. Click the General tab and deselect the Show list of unsupported BI Views after paste from BI Clipboard option. Click OK.

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.
Move these views to different slides for better clarity. Use the simple copy-paste (not BI paste) in PowerPoint to segregate the views, so that the table view is on one slide, chart view is on another, and gauge and funnel views are on a different slide. Also add appropriate titles to the slides.

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.

 

Back to Topic

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.)
Click OK.

 

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.)
Click Select beside FinishPrompt to select the values.

 

 

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:

 

Back to Topic

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.

 

Back to Topic

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.
For example, the table view after securing the presentation looks like this:

 

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.)

Back to Topic

Back to Topic List

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:

  • Edit the chart as appropriate. (Change the chart type to Pyramid.)
  • Edit the prompts to include all the finish values for only the McCloskey brand in the table. (Select table view and select the Edit Prompts and Levels option from the Oracle BI menu.)
  • Refresh the sheet. (Click Refresh from the Oracle BI menu.)

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. )

Back to Topic

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).


The Dollars column appears on the sheet with the conditional formats applied:


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.

Back to Topic

Securing BI Data 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).

Back to Topic

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.
Also, note that you can insert views from a dashboard directly into PowerPoint as well. If you have time, try inserting some views from dashboards directly into the PowerPoint slide and manipulate these views.

Back to Topic

Back to Topic List

 

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.)

Back to Topic List

In this lesson, you should have learned how to:

If you need additional information, refer to the following:

 

Back to Topic List

Place the cursor over this icon to hide all screenshots.