interactive dashboard manual

8/6/2019 Interactive Dashboard Manual

1/56

Building Interactive Dashboards and Using Oracle Business IntelligenceAnswers

Purpose

This tutorial shows you how to build, format, and customize Oracle Business Intelligence queries and format andcustomize requests and Interactive Dashboards.

Time to Complete

Approximately 2 hours

Topics

This tutorial covers the following topics:

Overview

Scenario

Connecting to Oracle Business Intelligence Enterprise EditionCreating a Query and a Chart

Working With a Pivot Table

Creating a Narrative View

Creating Column Selectors and View Selectors

Building an Interactive Dashboard

Using Dashboard Prompts and Presentation Variables

Summary

Related information

Overview

This tutorial covers how to use Oracle Business Intelligence Answers to create queries, format views, charts, and adduser interactivity and dynamic content to enhance the user experience. You create queries and work with viewsincluding charts, pivot tables, and narratives. You then create selectors to drive interactivity in your BusinessIntelligence requests, and build a custom Dashboard to contain the requests and views you've created. Finally, youwork with dashboard prompts to filter your dashboard and populate variables.

Back to Topic List

Scenario

The dashboard you build in this tutorial charts sales for the last 12 months by channel, country region, and productcategory and provides trend analysis data as well as narrative highlights of categories that have declined in sales inthe last month.

Back to Topic List

Connecting to Oracle Business Intelligence Enterprise Edition

To connect to Oracle Business Intelligence Enterprise Edition, perform the following steps:

1.In a browser (Internet Explorer or Firefox), navigate to https://bionline.fors.ru/analytics, select English as Language,

enter your User ID and password and click Log In.


2/56

Back to Topic List

Creating a Query and a Chart

In this topic you build and format a business intelligence request using Oracle BI Answers, and create and format achart.

Create a query

Add a filter

Create totals and format results

Create a chart

Back to Topic List

To create a query, perform the following steps:

1. Click the Answers link to navigate to the Answers start page, and select the Sales History subject area by clickingthe SH link in the Subject Areas list.


3/56

In this example, there is only one subject area, but there could be a long list, depending on the metadata that isdefined in the Oracle Business Intelligence repository, which can contain multiple subject areas. Subject areas aresets of related information with a common business purpose.

2. In the left-hand selection pane of the Answers interface, click the Plus icon next to Times to expand it. Expand theCalendar table and click the Calendar Month Desc column to add it to your query criteria, which appears in the rightpane.

The query you are building has one measure and three attributes.

3. Click the Calendar Month Name column to add it to your query. From the Products table, add the Prod Categorycolumn. Finally, add Measures > Sales Facts > Amount Sold (000). Your query should look like this:

4. You can reorder the columns in your query by clicking and dragging them. Drag the Prod Category column in front of

the columns from the Calendar table in your query. Your query criteria should look like this:


4/56

Now drag the column back to its original location.

5. Add a saved filter to the query to limit the data to the last 12 months (based on the SH data, this will cover fromJanuary to December 2001). Under Filters in the left-hand selection pane, expand the SH folder and click the MostRecent 12 Months. The Apply Saved Filter dialog box is displayed.

This filter has been created and saved in a shared folder so that it can be used by other users. The filter uses avariable defined as the value of the maximum month ID, which has been created in the Oracle BI repository metadatato ensure that it is synchronized with the data.

6. Click OK in the Apply Saved Filter dialog box to add the filter to your query.

7. Click the Advanced tab. The Advanced tab can be made available only to specific users. The XML fully defines thequery (including chart formats in case of charts). The SQL defines the content of the query. Note that any query orreporting tool that can issue SQL over ODBC connection, can issue a query to the BI server, just like Answers.Examine the request XML that defines the view and the logical SQL that will be issued for the query.


5/56

The Request XML defines the whole analysis, including logical SQL and views for the query. The SQL Issued fieldcontains only the logical SQL that will be issued to the Oracle BI Server for processing. Note the saw_Xcolumnaliases, which are added automatically. Editing the logical SQL will change your view definitions.

Back to Topic

To add a filter for the direct channel, perform the following steps:

1. Click the Criteria tabs, using the selection pane, add Channels > Channel Desc to your query criteria.

2. In the criteria pane, click the Filter button on the Channel Desc column to add a filter on that column.

3. In the Create/Edit Filter dialog box, click the All Choices link to display all values for the column.


6/56

You can also limit the list of choices by setting a match criteria using the Match drop-down menu and entering astring, then clicking the All Choices link. The Limited Choices link will limit choices to those that are consistent withany pre-existing filters.

4. Verify that the Operator for the filter is set to "is equal to / is in" and then click the Direct Sales value in the list ofchoices. Direct Sales is added as a value in the filter. Click OK to create the filter.

The filter is added to the existing saved filter you used earlier.

5. Click the Delete button (with and X) on the Channel Desc column to delete it from the criteria. You are filtering onthe channel, but you don't need the column in your query.

Another way to achieve this result is to CTRL-click the column in the selection pane, which opens the Create/EditFilter dialog box with the selected column, but doesn't add the column to the query criteria.

6. Your query criteria should look like this:


7/56

7. Click the Results tab to view the results of your query.


8/56

Other ways to view results are by clicking the view buttons below the tabs in the Criteria pane, or by clicking theDisplay Results button below the columns in your query criteria. By default, the results are displayed in theCompound Layout view, which contains two other views, a Title view and a Table view. As you will see, you candelete these default views and add other views to the Compound Layout. Later, you will add these different types ofviews to a dashboard.

8. Return to the Criteria tab and remove the Channel Desc filter you just created by clicking the X next to the filter in theFilters section.


9/56

Back to Topic

To create totals and format results, perform the following steps:

1. To open the Edit Table view for your results, click the Edit view icon for the Table view in the Compound Layout.

The column controls for each column are displayed with the results. Using the view-level controls, you can also settable-wide formatting properties, import formatting from other queries, and set grand totals for the entire table.

2. Click the Total By icon above the Calendar Month Desc column to add subtotals by month to your results.

The measure is totalled each time the value in Calendar Month Desc changes. In this case, the default aggregationrule (SUM) is applied. The default aggregation rule is set in the Oracle BI repository metadata, but can be overridden

using controls in the Edit Formula dialog box accessed through the Edit Formula icon in the Edit Table view or theCriteria tab.

3. Click the Grand Total button at the view level to add a grand total to your results.


10/56

4. In the Display drop-down menu, select Results Only to eliminate the Header Toolbars.

5.

Scroll down to the bottom of the Results pane and click the All Pages button to navigate to the end of yourresults. Verify that the grand total that you set for the results is present.

6. In the Display drop-down menu, select Header Toolbars with Results to access the Header Toolbars.


11/56

7. Click the Column Properties icon above the Amount Sold (000) column. In the Column Properties dialog box,click the Data Format tab, select the Override Default Data Format option, then select Use 1000's Separatoroption and click OK. This will display a comma separator in the number results for the column.

8. In the Edit Column Format dialog box, click the Save button. If you have permissions as a Web administrator, youcan save the data format as the system-wide default for the column you are working with, or for all columns with thesame data type.

Since you don't want to set this format as the system-wide default, click in the dialog box to close the Save menu.


12/56

9. Click OK and verify that a 1000's separator is displayed in the results for the Amount Sold (000) column.

Back to Topic

To create a chart, perform the following steps:

1. Pick Chart in the View menu. By default charts are displayed as a Vertical Bar graph.

2. Select Line from the Graph drop-down menu and 2D from the Type drop-down menu.


13/56


14/56

custom title to Amount Sold. In the Bottom tab, change the custom title to Calendar Month.

5. Explore some of the other options in the Axis Titles & Labels dialog box. For example, you can toggle whether scale labels adisplayed on the axes and set orientation guidelines for your labels. In addition, you can set overrides for the default data formeasures, similarly to the modification you made to the amount sold column in the steps above. Click OK. Your chart should

this:


15/56

6.Click the General Chart Properties icon. In the General dialog box, select Custom Title, enter Category Sales Last 12as the chart title, and click OK.

7.Click the Additional Charting Options icon. In the Additional Charting Options dialog box, click the Borders & Colors t

set the background color to light gray. Click the color box for Background Color and, in the Color Selector dialog box, select lifrom the pallette and click OK.

.

8. In the Additional Charting Options dialog box, click the Grid Lines tab and set the major grid line color to white and the minocolor to light gray, using the same method as above. After you've set both colors, click OK to apply your changes.


16/56

Your chart should look like this:

9. Sort the legend by sorting the data. You want to sort by month in ascending order and then by sales in the last month in descorder:


17/56

a. Click the Criteria tab and add the Measures > Sales Facts Current Month > Amount Sold (000) Current Month columquery.

b.Click the Order By button in the Calendar Month Desc column. The arrow points up to indicate an ascending sort.

c. Click the Order By button in the Amount Sold (000) Current Month column twice. The arrow points down to indicate a dessort, and the number 2 is displayed to indicate a secondary sort.

10. Click the Results tab to verify that your sort has been applied to the legend in your chart. The legend is sorted to match thethe lines in the last month.

11. Select Compound Layout from the View drop-down menu.


18/56

12. Click the Add View link and select Chart to add the Chart view to the Compound Layout view.

13.Scroll to the bottom of the Compound Layout view and drag the Chart view above the Table view in the Compound Layout. Aline will appear when you have a valid insertion point.


19/56

14.Click the Delete View icon in the Title view to delete it from the Compound Layout.

15.Click the Save Request icon. In the Choose Folder dialog box, click My Folders, then click the Create Folderbutton. In the Create Folder In My Folders dialog box, enter Learn and click OK.

16. In the Save Request dialog box, click the Learn folder. Enter Category Sales as the name and click OK.


20/56

Back to Topic

Back to Topic List

Working with a Pivot TableIn this topic you create a pivot table to examine your results and add calculations and formatting.

Create and set basic layout for a pivot table

Add calculations and formatting to the pivot table

Back to Topic List

To create and set the basic layout for a pivot table, perform the following steps:

1. Pick Pivot Table in the View menu. This opens the Pivot Table Layout page.


21/56

2. Examine the default pivot table that's created and displayed below the layout controls. Measure labels for themeasures in your query appear in blue as columns in the pivot table. Row headings for the dimensional attributes inyour query are displayed in grey as rows by default. The measures are displayed at the intersection of the rows andcolumns.

3. Deselect the Display Results option. This option displays the results of any layout modifications you make as youwork in the Pivot Table layout. To speed performance, you will only view your results periodically.

4. Drag the Amount Sold (000) Current Month column from the Measures area to the Excluded area in your layoutcontrols. When you see a blue line appear around the control, you have a valid insertion point and can drop thecolumn.

5. Drag the Calendar Month Desc column below the Measure Labels in the Columns area in your layout controls.When you see a blue line appear, you have a valid insertion point and can drop the column.


22/56

6. Drag the Calendar Month Name column below the Calendar Month Description in the Columns area in your layoutcontrols. Click the Disply Results link to verify your changes in the pivot table.

Your pivot table should look like this:

Deselect the Display Results link.

Back to Topic

To add calculations and formatting to the pivot table, perform the following steps:

1. You need to add a measure to your pivot table to display the percentage of total monthly sales for each product

category. Duplicate the Amount Sold measure. Click the More Options icon in the Amount Sold (000) measureand select Duplicate Layer.


23/56

2. Click the More Options icon in the duplicated measure and select Format Headings.

3. In the Edit Format dialog box, change the caption to % of Period and click OK.


24/56

Also note the other formatting options available in the dialog box.

4. Click the More Options icon in the duplicated measure and select Show Data As > Percent of > Column.

This setting means that the measure will be displayed as a percentage of the column, which is Month. Note the otheroptions that are available; you can present a measure as a percentage of the total amount for any dimension presentin the pivot table layout, for example a row or a section. You can also set alternate aggregation rules for the measureusing the Aggregation Rule option (the default is Sum).

5. To total the values of the rows in each column, click the Total icon in the Rows layout control and select After.


25/56

Note that you have formatting options available for the total row labels as well as values.

6. To add totals for each column in each row, click the Total icon in the Columns layout control and select After.

7.Click the click the More Options icon in the Calendar Month Desc column and select Hidden.

8. To show both measures together within each calendar month, drag Measure Labels under Calendar Month Name inthe Columns layout control.

9. Click the Display Results link. Your pivot table should look like this:


26/56

Note that there are section and page levels of the pivot table that we have not used in this example. Effective use of

these would require additional attributes. Experiment, if you want, by temporarily dragging Prod Category to sectionand then page.

10. Save the request by clicking the Save Request icon.

Back to Topic

Back to Topic List

Creating a Narrative ViewIn this topic you build and format a business intelligence request using Oracle BI Answers, and create and format aNarrative View. The Narrative view allows you to add text to appear with the results to provide information such ascontext, explanatory text, or extended descriptions.

Create a query

Add a filter

Create a Narrative view

Back to Topic List

To create a query, perform the following steps:

1. Click Answers to navigate to the Answers start page, and select the Sales History subject area by clicking the SHlink in the Subject Areas list.

You want a query to detail what happened in the most recent month, pointing out all product categories where sales


27/56

are less than the previous month.

2. In the left-hand selection pane of the Answers interface, expand the Products table and click the Prod Categorycolumn to add it to your query criteria.

3. Add Measures > Month Ago Facts> % Chg Amount Sold MAgo to your query . Your query should look l ike this:

4. Add a saved filter to the query to limit the data to the current calendar month. Under Filters in the left-hand selectionpane, expand the SH folder and click the Current Calendar Month filter. Click OK.

This filter has been created and saved in a shared folder so that it can be used by other users. The filter uses amaximum month variable defined in the Oracle BI repository metadata to ensure that it is synchronized with the data.

Back to Topic

To add a filter for the percent change in the amount sold, perform the following steps:

1. In the criteria pane, click the Filter button on the % Chg Amount Sold MAgo column to add a filter on that column.


28/56

2. In the Create/Edit Filter dialog box, select the is less than operator, and enter 0 as the value, then click OK.

The filter is added to the existing saved filter you used earlier. Your filters should look like this:

3. Click the Display Results button to view the results of your query.

Your results should look like this:

Back to Topic


29/56

To create a narrative view, perform the following steps:

1. Select Narrative from the View drop-down menu.

2. In the Narrative view workspace, enter the following: @1 declined @2% this month compared with last month inthe Narrative field.

The narrative is a combination of text and query column values. In this example, @1 refers to the first column in thequery, Prod Category, and @2 refers to the second column, % Chg Amount Sold MAgo. Note that you can controlthe number of row values returned in the Narrative view by setting the Rows to display value. By default, all queriedrows are displayed.

3. To highlight the column values in the narrative, select @1 in the narrative and click the Bold button. Also add boldtags to @2%.


30/56


4. Select No Results from the View drop-down menu. You need to create a No Results view to appear when there areno results for the query.

5. In the No Results view workspace, enter No categories declined this month! as the headline. Enter All categorieshad positive sales results compared with the previous month as text.


31/56


6. Click the Save Request icon. In the Choose Folder dialog box,select the Learn folder and save the request asNarrative and No Results Views.

Back to Topic

Back to Topic List


32/56

Creating Column Selectors and View Selectors

In this topic you build Column and View Selectors and experiment with their use in requests and dashboards. Columnselectors allow users to select from a group of columns, substituting columns in their queries for comparativeanalysis. View Selectors allow users to quickly navigate between different views of their queries, for example viewingdifferent charts of the same data or quickly navigating to a pivot table to do trend analysis.

Create a Column Selector

Create a View Selector

Back to Topic List

To create a Column Selector, perform the following steps:

1. Click the Answers link and open the first query you saved. In the selection pane, click Shared Folders > Learn >Category Sales.

2. Click the Modify button.

3. Click the Results tab and select Column Selector from the View drop-down menu.


33/56

4. Select the Include Selector option in Column 3, currently Prod Category.

5. In the selection pane, click the following columns to make them available in the Column Selector:Customers.Country Region, Customers.Country, and Channels.Channel Desc.


34/56

Your Column Selector in the results pane should look like this:

6. Select Compound Layout from the View drop-down menu.

7. Click the Add View link and select Column Selector to add the Column Selector view to the Compound Layout view.

Notice that views that you have built for the active request appear in the View and Add View drop-down menus withgreen circle icons next to them.


35/56

8. Scroll to the bottom of the Compound Layout and drag the Column Selector view above both Table and Chart views.

9. In the Column Selector, select Country.

10. Your Compound Layout view should look like this:

Note that the third column in the table now reflects values for the Country column, as does the chart. Switch thecolumn back to Channel Desc. Save the request.


36/56

Back to Topic

To create a View Selector, perform the following steps:

1.Duplicate the existing chart:

a.Using the Views menu, navigate to the Chart view.

b.Click the Menu icon and select Duplicate View.

You should now have a new view, Chart:2:

c. In the Type drop-down menu, select 3D.


37/56


38/56

4. Select Chart in the Views Included field, click the Rename button, and, in the Rename dialog box, rename it 2D Chart. Click

5. Rename Chart:2 as 3D Chart and Pivot Table as Trend Data. Use the Move buttons to arrange the views in the list as follows

Chart, 3D Chart, Trend Data.

7.Use the View drop-down menu to navigate to the Compound Layout view. Delete the Chart view from the Compound Layout.


39/56

8.Add the View Selector view using the Add View button and drag it above the Table view.


40/56

9. Select Trend Data from the View Selector.

Your chart should now change to the Pivot Table view:

Back to Topic

Back to Topic List

Building an Interactive Dashboard

In this topic you build a new shared Interactive Dashboard and add content you've saved in previous steps.

To build an Interactive Dashboard, perform the following steps:

1. Click the Dashboards link to navigate to the dashboard.

Your new dashboard is empty and should look like this:


41/56

2. Click the Page Options button in the upper right corner and select Edit Dashboard to open the Dashboard Editor.

3.In the Dashboard Editor, you can drag saved content from your Presentation Catalog directly into the dashboard.Sections are are automatically created in the layout to contain the requests and other objects you add to the dashboard.In the left-hand selection panel, expand the Learn folder you've saved your work in.

Drag the Category Sales request onto the layout workspace. The layout area is highlighted in blue to indicate that youhave a valid insertion point for the object.

Your dashboard layout should look like this:


42/56

Notice that a section was automatically added to contain the request. You could also have dragged a Section objectfrom the Dashboard Objects pallette to create the section before dragging content into the section. Sections andcolumns are containers you can use to control the layout of your dashboards.

4. Drag the Narrative and No Results request into Section 1 of the dashboard layout, below the Category Sales request.

5. In the Narrative and No Results object, select Properties > Show View > Narrative 1 to ensure that when thedashboard is presented, the request will be in the Narrative view.

You can select any other view; note that the list is limited to views which you've created or worked with for the request.The Compound Layout is the default view. The No Results view is always displayed if there are no results for the


43/56

request at runtime.

6. Click Save to save your changes and display the dashboard.

Your dashboard should look like this:

Scroll to the bottom of the dashboard to verify that the narrative view appears correctly for the Narrative and No Resultsembedded request. It should look like this:

Note that the Column Selector only applies to the request that contains it. If you select a column in the control, it will


44/56

only apply to the table and chart, not the narrative request in the same dashboard.

Back to Topic List

Using Dashboard Prompts and Presentation Variables

In this topic you build a dashboard prompt to filter your dashboard for a specific country region. Then you use aPresentation variable to dynamically update a request title in your dashboard that includes the filtered name of thecountry region. Dashboard prompts are used both to allow users to filter results of embedded requests in adashboard as well as to populate and update Presentation variables, which are variables that can be defined inOracle BI Answers.

Create a dashboard prompt

Use a presentation variable

Back to Topic List

To create a dashboard prompt that filters on Country Region, perform the following steps:1. Click the Answers link. In the selection pane, click the New Dashboard Prompt icon and, in the drop-down Subject Area

select SH.

2. In the selection pane, click Customers.Country Region to add it to the prompt.

The prompt should look like this:

This prompt will filter the Country Region column in any requests which contain it in a dashboard with which it is associated.you can control several aspects of the prompt. You can select the type of control users will use to enter their selections in theand you can determine what values will be presented in a drop-down list or multi-select control, limiting the available valuesthrough SQL or by constraining the values based on the results of another dashboard prompt.

3. Leave the default selections for the prompt and select Dashboard from the Scope drop-down menu. This means the filter wilapplied to all pages in any dashboard it is associated with.


45/56

4. Click the Save icon and save the prompt in the Learn folder as Prompt for Country Region.

You have created the prompt, but for it to take effect on any embedded requests in a dashboard, the requests must contain fithe column being prompted.

5. Click the Open icon, and in the Open dialog box, select the Category Sales request in the Learn folder and click OK.


46/56

6. In the selection pane, CTRL + click the Customers.Country Region column to add a filter on it. In the Create/Edit Filter dialselect is prompted as the operator for the filter and click OK.

Your filters should look like this:


47/56

7. Save the Category Sales Request and, using the same steps, add a filter to the Narrative and No Results Views request andWhen complete, the filters on the request should look like this:

8. Click the Dashboards link and navigate to the My Dashboard dashboard, then click Page Options > Edit Dashboard to opDashboard Editor.

9. From the left-hand selection panel, expand the Learn folder and drag Prompt for Country Region above the Category Salein Section 1 of the dashboard. The area will be highlighted in blue when you have found a valid insertion point.

Your dashboard should look like this:


48/56


49/56

b. Click the Results tab and select Filters in the View menu.


c. Using the View menu, navigate to the Compound Layout. Click the Add View link and select Filters.


50/56

d.Drag the Filters view to the top of the Compound Layout and save the request.

e. Navigate back to the CountryManagers2 dashboard, select Americas in the Country Region dashboard prompt, and clic

f. Verify that the dashboard prompt has added a filter to the requests on the dashboard for Country Region is equal to Amer


51/56

Back to Topic

To use a Presentation variable to populate a title with the name of the selected country region, perform the

following steps:1. Navigate to Answers, open the Category Sales request, and click the Modify button.


52/56

2.

Click the Compound Layout View button, add a Title view to the Compound Layout view using the Add Viewlink, and drag the new Title view to the top of the layout.

Your Compound Layout should look like this:


53/56


54/56

6. In the Set Variable field of the prompt, select Presentation.

Note that you could also reference a Request Variable. Request Variables are defined as SessionVariables in theOracle BI metadata and are instantiated when the user's session begins. Their values for any request can beupdated by dashboard prompts.

7. In the Set Variable text field, enter CountryRegion.

By entering the name of the variable here, you are creating the variable as well as setting it to be populated by theuser selection in the Prompt for Country Region.

8. Save the dashboard prompt.


55/56


56/56

interactive dashboard manual

Documents