In the Web browser, enter the URL for Discoverer Plus. This will display the Connect to OracleBI Discoverer screen. The URL looks like the one given below:

http://<hostname>:<port>/discoverer/plus

Enter the login credentials for the bi_user in the Connect Directly section. Enter the username and password, and the database connect string as shown. Enter the EUL name in uppercase (BI_USER), and click Go. This will connect you to Discoverer Plus.

Note: Alternately, you can choose from the list of predefined connections. You can also create a private connection by using the login credentials for the database that you want to connect to. Also, note that when you have a stand-alone BI installation, which is not associated with the infrastructure database of the Oracle Application Server, you will see only the Connect Directly option, not the connections. To associate the BI installation with the infrastructure database, contact the middle-tier administrator.

When you connect to Discoverer Plus, the first step of the Workbook Wizard is displayed. Select the Open an Existing Workbook option, and click Browse.

This will display the list of all workbooks.

The Name Contains field can be used to filter the list of workbooks. Enter Sales in the Name Contains field to display only those workbooks, whose name contains Sales.

Note: You can select to View all workbooks or scheduled workbooks, by selecting the appropriate options from the View drop-down list. Also, you can select the list of workbooks displayed as a Workbook Tree or User Tree as shown in the images below. Observe the improved interface.

Expand the sign next to the workbook Sales &Profits by Time & Geography to see all the worksheets, and select the worksheet Sales Vs Profits By Year, and click Open. This will open the worksheet in Discoverer Plus.

The worksheet is shown below. Observe the new look and feel.

Select Edit > Add Worksheet to invoke the new Worksheet Wizard. Deselect the Title, Text Area, and Graph options. Select Table as the worksheet layout, and click Next.

In step 2 of the wizard, you select items to be included in the worksheet. Observe the new Search icon, which enables you to search for required folders. Click the Find icon.

This opens a Find dialog box. Select Contains as the Search by criterion, and enter Profits in the Search For field. Click Go. This displays two folders Profits and Profits Analysis. Select the Profits Analysis folder. Click OK.

You can see that Profits Analysis folder is selected in the step 2 of the wizard. Select the Sales Revenue, Profit, Year, and Region items by pressing and holding the Ctrl key. Click to move these items to the Selected list, and click Next.

You can choose to display or hide the folders in the Selected list by clicking the Views icon.

Also, note that you can create Conditions and Calculations from the respective pages while creating the worksheet. These can be defined later while you edit the worksheet too.

Step 3 of the Workbook Wizard is displayed. Retain the default options and click Finish.
Note: The next two steps of defining sorts and parameters are skipped here, because this is a simple worksheet. These can be defined later.

This is how the worksheet you created looks like:

Note that the steps in creating a new workbook by using the Workbook Wizard are similar to these steps.

Select the Edit > Rename Worksheet menu option. This displays the new Worksheet Properties dialog box. Enter the name of the worksheet as Regional Sales and Profits By Year-New and add a Description (optional). Click OK.

Click Save icon to save the workbook. Enter Sales&Profits By Time &Geography -XX as the name of the workbook, where XX are your initials, so that you do not overwrite the sample workbook. Click Save.

Select the Edit > Worksheet menu option. Open the Worksheet Layout page, select the option to display Page Items, and click OK.

Note: Observe the other options in this page to display graph, title, and text in the worksheet.

Now the Worksheet will look like this with an option to display the page items.

To arrange the items in this worksheet better, pivot the Year item to the page axis by dragging this item to the page axis. Alternately, you can select the appropriate option to pivot this item from the shortcut menu as shown in the image below.

Similarly, move the Region item to place it before the Sales Revenue SUM item. After pivoting Year to the page axis, and placing Region before Sales Revenue SUM, this is how the worksheet will look:

You can see that the worksheet items are arranged better now, and also that pivoting can be done interactively.

Add suitable data formats to the items in the worksheet. For example, you can apply currency formats to Sales Revenue SUM and Profit SUM items.

Note: You can apply the appropriate currency format by selecting the Format Data option from the shortcut menu. In the Format Data Dialog box, select appropriate currency options as shown below.

After applying these formats, the worksheet will look like this:

Open the Worksheet Properties dialog box. This can be done from the shortcut menu which appears when you click the worksheet name.

Alternately, this dialog box can be opened by selecting Edit > Rename Worksheet.

Go to the Table Format tab, and click the Gridline Color drop-down list and select a color for the gridlines in the worksheet. Click OK.

Note: Observe the enhanced interface and other options in this page to display gridlines, column headings, row numbers, and NULL values.

Now the worksheet looks like this: (Observe the change in the gridline color.)

To add Prod Category item, you can right-click the item and select Add to Worksheet from the shortcut menu. You can also do this by selecting the required item to be added, and by clicking the
Add to Worksheet icon (highlighted in the image below). Alternately, you can drag the item to be added from the Available Items pane, and drop it on the worksheet.

The worksheet with the newly added item is shown below. By default new items are added at the end. However, in the worksheet below, note that Prod Category is placed (pivoted) before Sales Revenue SUM.

Note: Similarly , you can also remove the items from the worksheet interactively.

Before proceeding with this exercise, interchange the positions of Region and Year items. (That is, move Region to the page axis, and Year to top axis). The worksheet now looks like this:

Select the Edit > Worksheet Layout menu option.
Note: You can alternately select Edit > Worksheet and open the Worksheet Layout page.

Select the option to add a graph to the worksheet, and select the option to display the graph below the worksheet. Click OK.

The worksheet will now look like the image below. Edit the graph type and subtypes to improve the visualization. Right-click the graph in the worksheet, and select the Edit Graph option from the shortcut menu.
Note: Observe that a simple Bar graph is added to the worksheet. However, the graph type and subtypes added may differ based on the last graph added in the workbook.

You can also edit a graph by clicking the Edit Graph icon on the toolbar.

Open the Type tab. Select Bar as the graph type, Split Dual-Y Bar as the graph subtype, and click OK.

The graph in the worksheet will now appear like this:

Click the Edit Graph icon on the toolbar, and go to the Styletab. From the Style drop-down list, select Regatta as the option and also select the option to display 3D Effect in the graph.

A sample of the graph style that you have selected is reflected in the page. Click OK.

After applying this graph style, this is how the worksheet looks like:

To add Fitline options, click the Edit Graph icon and open the Plot Area tab. Select Linear as the Fitline option for Profit SUM and Sales Revenue SUM. Click OK.

Note:This option can be found in the table under the section Select options for the series displayed in your graph.

The graph in the worksheet now shows the Fitline option added. Note that though sales revenues were raising, profits do not show this trend.

Note: You can also change the series colors from the Plot Area tab, as shown in the screen below:

The graph in the worksheet now looks like this:

In the graph, click the legend for the year 2001 to view the quarterly information.

The worksheet and the graph now display quarterly information. Change the Fitline option for both Sales Revenue SUM and Profit SUM series to Exponential. You can see that the Profits and Sales Revenues have started increasing exponentially in the year 2001.

You can click the icon for New Calculation (highlighted in the image). Alternately, open the Calculations page, right-click the My Calculations node, and select New Calculation option.

Enter My Profit Margin as the name for the calculation, and add a formula to calculate profit margin (Profit SUM/ Sales Revenue SUM) in the Calculation field. Click OK.

The Calculation will be added automatically to the worksheet as shown below. You can also see this calculation added to the My Calculations node and also in the Selected Items pane.

Note that the graph type used here is Line with a subtype Dual-Y Stacked Line.

Change the data format for My Profit Margin to represent it as a percentage, without decimals.

Now the worksheet looks like this:

Note: To save the changes that you are making to the worksheet, you can save the workbook. Also, if you want to save the changes as a separate worksheet in the same workbook, then you can duplicate the worksheet, and give it an appropriate name before saving the workbook. For example, you can name the above worksheet as Regional Profit Margins By Year. Duplicating a worksheet can be done from the Edit > Duplicate Worksheet menu option as shown below:

The image below displays a tabular worksheet, with Region pivoted to the top axis. This worksheet displays sales, profits, and profit margins for all the regions and years. To add a group sort on year (or in general, any sort on an item), you need just a single click. Select the item Year and click the group sort icon on the toolbar. You can also do the same from the shortcut menu of the item.
(Note that the worksheet layout is edited not to display page items and graph; and also font sizes for the items are changed.)

The worksheet looks like this after applying a group sort on Year:

To apply line breaks or page breaks right-click Year and select Format Data option.

Observe the tabs, and open the Breaks tab. Select to insert a line break at each change of value, select the line width as 3, and select a color for the line break as shown in the image below. Click OK.

Now the worksheet looks like this:

Note: You can select from custom colors, or edit the colors to add your own combinations of colors. Similarly, you can define page breaks too. Also, note that page breaks can be applied only to tabular worksheets and not crosstabs.

Similarly, add sorts on Sales Revenue SUM and Profit SUM to display these items in descending order.
Hint: Use the sort icon on the toolbar. Also, you can define sorts from Tools > Sort menu option.

Now the worksheet looks as shown in the image below. Observe that Americas and Europe Regions are doing well in terms of the sales revenues and profits. You can also see that profit margins declined in the year 2000, and started rising again in the year 2001.

To define a stoplight format on Profit SUM, select this item and click the New Stoplight Format icon on the toolbar.

The New Stoplight Format dialog box appears. Deselect the check box to automatically generate the name, and enter Stoplight for Profit SUM as the name of the format. Enter 250000 in the Unacceptable field, and 1300000 in the Desirable field. Click OK.

Note: You can also edit the default colors of red, green, and yellow to have your own combinations of colors to represent unacceptable, desirable, and other values.

Now the worksheet with stoplights applied on Profit SUM looks like this:

Now to define a conditional format on Sales Revenue SUM, select this item, and click the New Conditional Format icon on the toolbar. The New Conditional Format dialog box is displayed. Deselect the check box to automatically generate the name, and enter Conditional Format on Sales Revenue as the name. Select BETWEEN as the condition, and enter 5000000 and 17000000 in the two fields to define the range on values. To define the color formats, click Format.

Select colors as shown for the Text and the Background from the color palettes, select 14 as the Size of the font, and click the icon to select bold as the style. Click OK.

After applying both conditional and stoplight formats, the worksheet will appear as shown in the image below. Note that Americas and Europe regions are top contributors for the sales revenues and profits in all the years.

Note: You can edit the stoplight or conditional formats from the Format > Conditional Formats menu option, which will open the Conditional Formats dialog box.

Rename the sheet as Regions Contributing to Sales & Profits at a Glance and save the workbook.

Select the menu option Edit > Worksheet Layout, and select the options to add a Text Area and a Title to the worksheet as shown in the image below. Click OK.

Double-click the Title (found at the top of the worksheet), select Text and Background colors from the drop-down list. Select 16 as the font size, and select Bold as the style. Insert Sheet Name as title, and click OK.

Now the worksheet name is displayed as title.

Similarly, double-click the Text Area (found at the bottom of the worksheet), and from the Insert drop-down list select Conditional Formats to insert conditional format metatokens. Click OK. This helps in understanding the conditional and stoplight formats defined in the worksheet.

The worksheet now looks like this.

Note: You can further format the worksheet by adding formats on text, headings, and so on according to your requirements.

Open the worksheet in which you have added My Profit Margin calculation (that is, Regional Sales and Profits By Year-New in this case, or open the appropriate worksheet if you have duplicated the worksheet and saved it with a different name).

Increase the font sizes of the items in the worksheet, and also edit the worksheet layout not to display the graph. The worksheet now looks the image below. Right-click the drill icon for Year. It is highlighted in the image below.

Select Drill as the option. This opens the Drill dialog box. In the Where you want to Drill to drop-down list, select Drill to a Related Item.

From the list of items, select Product Category to drill to this item. Click OK.

Note: Add a group sort on Year. Also, sort Sales Revenue SUM and Profit SUM in descending order.

After drilling to product category form Year, now the worksheet looks as shown in the image below. Note that the worksheet also reflects the sorts that you have added in the previous step. Save the workbook to save the changes in Regional Sales and Profits By Year-New worksheet.

You can see that sales revenues and profits are high for the products in the category Peripherals and Accessories category in all the years. But you can also see that the profit margins in the 'Photo' category are high. Probably, as a BI Analyst, you can advise the company to implement marketing strategies, that will rise sales revenues and profits for the products in the Photo category, as the profit margins are high for these products. If you observe the data for other regions, you can see that similar trends were seen in other regions too.

In the worksheet, right-click the profit margin value of 35% for the Photo category in the year 1998. Select Drill as the option.

Select to display the component rows in a new worksheet as a Table, and click OK.

This will create a new worksheet with all the component rows that contributed to the 35% profit margin in the year 1998 for the Photo category. The worksheet displays all the detail items in the business area. A portion of the worksheet is shown below.

Note: You can now browse through the worksheet data using the scroll bars, without losing the sight of the column or row headers. This is especially helpful when you are working with large tables or crosstabs. Browse through the worksheet that you created in this exercise to see the advantage of this enhancement.

Add a new crosstab worksheet to the workbook with Product Category and Product Subcategory as the page items, Year and Channel Desc as the row items, and Sales Revenue SUM, Profit SUM, My Profit Margin as the data points. The following screenshots will help you create this worksheet:

In the Worksheet Properties page, rename this worksheet as Channel Profitability by Product Categories. Also from the Crosstab Format page of the Worksheet Properties page, select the options to show item labels and heading gridlines. Take the help of the screenshots below to do this.

Note: Also, apply the appropriate currency , and percentage formats for the items Sales Revenue SUM, Profit SUM, and My profit Margin.

The crosstab that you have created will look like the image below. Click the New Parameter icon on the toolbar.

New Parameter screen is displayed. Enter Product Category Parameter as the name of the parameter, select Prod Category item from the drop-down list to base the parameter on this item. To specify default value for this parameter, click the search icon to search for the values. Observe the highlighted portions in the following image:

Select Peripherals and Accessories as the default value in the Select Values dialog, and click OK.

This brings you back to the New Parameter screen. Click OK.

The worksheet now looks like the image below. Note that the default value Peripherals and Accessories is displayed in the worksheet.

Click the New Parameter icon from the toolbar again to create another parameter. Enter Product Subcategory Parameter as the name, select Prod Subcategory from the drop-down list, to base this parameter on this item. Also, select the option Filter the list of values based on the selected conditions, and select appropriate condition to display the list of values based on the product category as shown in the image. Click OK.

The Edit Parameter Values dialog box will open. Click the search icon to select values for product subcategory. From the Select Values dialog box, select Monitor, and Printer Supplies as the subcategories. Click OK.

This will take you back to the Edit Parameter Values dialog box. Click OK. Note that now the worksheet will show the data for the selected product category and subcategory values. Save the workbook.

Open the Regional Sales and Profits By Year-New worksheet. Right-click the Prod Category item, and select the Manage Links option.

The Manage Links dialog box is displayed. Click New Link.

In the New Link dialog box, enter the name of the link as My Link to Worksheet, select appropriate options as shown below, to link to the Channel Profitability By Product Categories worksheet from the workbook that you saved with your initials (Sales&Profits By Time &Geography -XX). To pass parameter values from the link, click Parameters.

Note that you have created the Channel Profitability By Product Categories worksheet in the previous exercise with cascading parameters for product category and subcategory. Also, note that you can create a link to a worksheet in another workbook, by clicking Browse and selecting an appropriate workbook.

This displays the Parameters dialog box. Select Prod Category from the drop-down list for the Product Category Parameter, so that the product category value will be passed as the value. Click OK.

Note: This will take you back through the same screens New Link, and then Manage Links. Click OK in each of these steps.

Note that in the worksheet, drill icons are displayed beside every value under Product Category item to indicate that you can drill to links from any of the item values. Click the drill icon beside Photo as shown, and click My Link to Worksheet, to drill to the Channel Profitability by Product Categories worksheet.

You can see that the worksheet prompts for parameter values, and Photo is already passed on as a product category value. Click the search icon for product subcategory, and select Camcorders, Camera Media as the values. Click OK.

This will take you back to Edit Parameter values dialog. Click OK. Save the workbook. The worksheet will display the data for the selected values as shown below:

Note: You can see that Camcorders are selling well in Direct and Partners channels, compared to the Internet channel. Similarly, you can analyze the profitability of the distribution channels for other products as well, to come up with different marketing strategies for different channels. You can also analyze what type of products are selling well in each channel.

You can create another link to drill to an URL (static link) as shown in the image below:

Open the Regional Profit Margins worksheet. To print this worksheet, select the File > Print menu option. You can alternately click the Print icon on the toolbar.

This displays the Print dialog box with various options. Select the options to print the current worksheet including graph, with all the combinations of page items. Click Page Setup.

Note: Observe the other options to print the entire workbook, to resolve print issues, and so on.

Observe the pages available in the Page Setup dialog box. In the Worksheet page, select the Orientation as Portrait, and also select the option Print All Page Item Combinations.

Open the Crosstab page, observe the options available, and accept default values for the options on this page. (You can opt to change these values too as per your requirement.)

Open the Graph page, and select the options to fit the graph to the page as shown in the image.

Open the Margins page and change the margin settings as per your requirement. You can select to define the margins in units of pixels, inches, or centimeters.

Open the Header/Footer page and insert the worksheet name in the center as the Header. Also, select to display a line as shown in the image as border below the Header. For the Footer, insert page number at the center, date on the left, and time to the right. Also, select to display a line as border above the footer. Click Preview to preview the worksheet print settings.

Observe the highlighted options in the image.

Zoom the worksheet to 100% to see the worksheet properly. Traverse through the pages to check the worksheet print settings. Click Print to print the worksheet.

You can also click Page Setup to go back and change the print settings.

To export the worksheet Regional Profit Margins, select the File > Export menu option. From the Export Wizard Step1, select the options to export the current worksheet including graph. (Select both Graph and Crosstab to be exported.) Click Next.

In step 2 of the Export Wizard, select Portable Document Format (*.pdf) from the drop-down list of the export formats as shown. Specify a destination (path) and a name for the PDF file to be saved using Browse button as shown in the images below, and click Next.

In step 3 of the Export Wizard, specify appropriate size and settings for the Graph as shown. Click Next.

In the next step, select to carry on the export in Supervised mode, and click Finish.

This shows the Export Log. Observe the log and click OK to open the first exported sheet.

This opens the first page of the PDF file in Acrobat Reader as shown in the image:

To export the worksheet Regional Profit Margins to Web query format, select File > Export. In step 1 of the Export Wizard, deselect the option to export graph, and select only crosstab to export. Click Next.

Note: Graph cannot be exported to Web query (.iqy) format.

In step 2 of the Export Wizard, select Web Query for Microsoft Excel 2000+ (*.iqy) from the drop-down list of the export formats as shown. Specify a destination (path) and a name for the web query file to be saved using Browse button. Also, uncheck the option to prompt for Connection Information, and select the option to prompt for Page Item Values, and Click OK.

Note: Every time you run this Web query, you are prompted only for password and not the entire connection information. Also, you are prompted for the page item values.

In step 3 of the Export Wizard, select the page items for which the users will be prompted while running the Web query. Select Year, and click Next.

In the next step, select to carry on the export in Supervised mode, and click Finish.

This shows the Export Log. Observe the log and click OK to open the first exported sheet.

This opens Microsoft Excel, and you are prompted for a password. Enter the password. When prompted for the page item Year, enter 2001. The Excel worksheet displays data for the year 2001.

Select File > Schedule. This will open the Schedule Wizard.

Schedule Wizard-Step1 is displayed. Enter Sales &Profits by Time& Geography-XX-Scheduled (XX are your initials) as the name of the workbook to be scheduled, and also enter a description (optional). You can select a few or all the worksheets in the workbook to be scheduled. Click Select All to select all the worksheets in the workbook. Alternately, you can select the check boxes beside the names of the worksheets to select only a few worksheets. Click Next.

Schedule Wizard- Step2 is displayed. As some of the worksheets contain parameters, the wizard will prompt for these parameter values. Select the values for Region and Country parameters as shown, and click Next.

Similarly, in Schedule Wizard- Step3, select the values for Product Category and Product Subcategory parameters as shown in the image, and click Next.

Note: These steps prompting for parameter values will be skipped if none of the selected sheets contain parameters.

Schedule Wizard -Step4 is displayed. Select a date from the calendar, and also select an appropriate time for the workbook to be scheduled. Observe the other options in the screen and accept default values, and click Finish.

After the report is ready, open the scheduled workbook to see the results.

Note: After the scheduled time, the results of the query will be available in the database, and the report will be ready. You can check the status of the report from the Tools > Manage Schedules menu option. Also, note that you can share the workbook from this menu option, without having to open the scheduled workbook.

Select File > Share menu option to share this scheduled workbook.

The Share Workbook dialog box is displayed. Enter BI as the search string to search for usernames or roles that contain BI. Click Go.

Observe the clock icon beside the workbook name (highlighted in the image), which indicates that it is a scheduled workbook that you are sharing.

From the list of users, select OBE4BI, to share the results of scheduled workbook for this user. Click OK.

Note: The user OBE4BI may not exist in the database that you are using, select any of the existing users in your database, to share the results of this scheduled workbook.

Now connect directly to Discoverer Plus as the user OBE4BI, supplying the credentials of the OBE4BI user, but mention BI_USER as the EUL.

Note that you have mentioned BI_USER as the EUL name while connecting, because the scheduled workbook is present in BI_USER EUL.

Open the scheduled workbook which is shared for this user by BI_USER.