Oracle 12 Finance Training Report Writing using OBIEE Answers. Reference Guide. UK SBS Training Services Team

Oracle 12 – Finance Training Report Writing using OBIEE Answers Reference Guide UK SBS Training Services Team Report Writing using OBIEE Answers ...
Author: Tiffany Sanders
3 downloads 2 Views 3MB Size
Oracle 12 – Finance Training

Report Writing using OBIEE Answers Reference Guide

UK SBS Training Services Team

Report Writing using OBIEE Answers

Version Control Version

Created

Author

Reviewer

Comments

3.1

Sep-2015

M. Houghton

D. Rees

Updated to reflect new SHP training environment

3.2

May 2016

K. Smith

M. Houghton

Updated to reflect amendments to the exercises

OBIEE Answers Reference Guide

Page i Version 3.1

Report Writing using OBIEE Answers

Contents 1

Course Objectives & Agenda .................................................................................................. 1

2

Introduction to OBIEE.............................................................................................................. 1

3

OBIEE Home Page ................................................................................................................... 3 3.1

4

Creating a Folder ..................................................................................................................... 5 4.1

5

Exercise 4 – Conditional Formatting ................................................................................. 20

Applying Filters ...................................................................................................................... 27 8.1

9

Exercise 3 – Formatting Column Properties ...................................................................... 14

Conditional Formatting.......................................................................................................... 20 7.1

8

Exercise 2 – Create an Ad-Hoc Analysis ............................................................................. 7

Formatting Column Properties ............................................................................................. 14 6.1

7

Exercise 1 – Create a Folder .............................................................................................. 5

Create an Ad-Hoc Analysis ..................................................................................................... 7 5.1

6

OBIEE Global Header ......................................................................................................... 4

Exercise 5 – Applying Filters ............................................................................................. 27

Applying Bins ......................................................................................................................... 33 9.1

Exercise 6 – Bins .............................................................................................................. 33

10 Formula Editing...................................................................................................................... 38 10.1

Exercise 7 – Formula Editing ............................................................................................ 38

11 Table Properties ..................................................................................................................... 43 11.1

Exercise 8 – Table Properties ........................................................................................... 43

12 Creating a Chart View ............................................................................................................ 50 12.1

Exercise 9 – Creating a Chart View .................................................................................. 50

13 Creating Pivot Tables ............................................................................................................ 56 13.1

Exercise 10 – Creating Pivot Tables ................................................................................. 56

14 Editing the Compound Layout .............................................................................................. 62 14.1

Exercise 11 – Editing the Compound Layout..................................................................... 62

15 Dashboards ............................................................................................................................ 67 15.1

Dashboard Prompts .......................................................................................................... 68

15.2

Exercise 12 – Step 1 - Setting Dashboard Prompt Column Filters .................................... 69

15.3

Exercise 13 – Step 2 - Creating a Dashboard Prompt ....................................................... 73

OBIEE Answers Reference Guide

Page ii Version 3.1

Report Writing using OBIEE Answers

15.4

Exercise 14 – Creating My Dashboard .............................................................................. 79

15.5

Exercise 15 - Link to an Existing Dashboard Report from My Dashboard ......................... 85

15.6

Exercise 16 – Adding Report Links to your Dashboard ..................................................... 89

16 Appendix A – Icon Definitions .............................................................................................. 91 16.1

Catalog ............................................................................................................................. 91

16.2

Folders Region ................................................................................................................. 92

16.3

Save As ............................................................................................................................ 92

16.4

Criteria Tab ....................................................................................................................... 92

16.4.1

Subject Areas Pane ................................................................................................... 92

16.4.2

Criteria Icons.............................................................................................................. 93

16.4.3

Selected Columns Pane ............................................................................................ 93

16.4.4

Filters Pane ............................................................................................................... 93

16.5

Results Tab....................................................................................................................... 94

16.5.1

Subject Areas Pane ................................................................................................... 94

16.5.2

Catalog Pane ............................................................................................................. 94

16.5.3

Views Pane ................................................................................................................ 94

16.6

Table................................................................................................................................. 95

16.7

Compound Layout Title ..................................................................................................... 95

OBIEE Answers Reference Guide

Page iii Version 3.1

Report Writing using OBIEE Answers

Legend These recognised abbreviations tell you what action to take in the practical exercises. They will not appear as options on your Oracle 12 system, but are our instructions to you: (N) – Navigate (T) – Tab (ST) – Sub Tab Responsibility – YOUR ORGANISATION OBIEE Analysis GL

Action Note: This is to draw your attention to an action you MUST take in order to effectively use the system.

Learning Note: This is to draw your attention to useful additional information that you may find interesting.

Caution Note: This is to give you forewarning about an action that is NOT advisable or may have consequences that could impact on your effective use of the system.

OBIEE Answers Reference Guide

Page iv Version 3.1

Report Writing using OBIEE Answers

1 Course Objectives & Agenda By the end of this course delegates will have an understanding of: 

Creating and saving an ad-hoc report



Formatting Reports



Report Filters



Creating Bins (Summarising data)



Creating Different Report Views



The Dashboards

2 Introduction to OBIEE

Oracle Business Intelligence Enterprise Edition (OBIEE) (sometimes simply referred to as Oracle Business Intelligence) provides a full range of business intelligence capabilities that allow you to: 

Collect up-to-date data from your organisation



Present the data in easy-to-understand formats (such as tables and graphs)



Deliver data in a timely fashion to the employees in your organisation

The data, accessed by OBIEE, is held in a Data Warehouse which is updated every night by the Extract Transform Load (ETL) processes.

OBIEE Answers Reference Guide

Page 1 Version 3.1

Report Writing using OBIEE Answers

In Oracle Business Intelligence Enterprise Edition (OBIEE), you can work with: Analyses — (This area of OBIEE is also known as Answers). 

Analyses are queries against your organisation's data that provide answers to business questions.



Analyses allow you to explore and interact with information by visually presenting data in easy-to-understand formats (such as tables and graphs).



You can save, organize, and share the results of analyses.

Dashboards — (This area of OBIEE is also known as Interactive Dashboards.) 

Dashboards provide personalised views of corporate management information.



A dashboard consists of one or more pages that contain content, such as analyses and links to websites.



Dashboards allow you to provide end users with access to key management information reports.

OBIEE Answers Reference Guide

Page 2 Version 3.1

Report Writing using OBIEE Answers

3 OBIEE Home Page The Home page provides the starting point for performing tasks in OBIEE. The Home page is divided into sections that allow you to quickly begin a specific task, locate an object, or access technical documentation. It also includes sections (for example, Recent and Favorites) that allow you to quickly access objects that you have recently viewed, created, or updated, and objects that are accessed the most often by the users assigned to the groups to which you belong.

OBIEE Answers Reference Guide

Page 3 Version 3.1

Report Writing using OBIEE Answers

3.1

OBIEE Global Header

The global header provides quick access to commonly used functions and is always available from the screen.

The global header includes the following functions: Function

Purpose Enables you to search the catalog Displays the ‘Catalog page’ in search mode, where you can search for objects in the catalog Displays the following options:  Help Contents — Displays a cascading menu that provides options that link to the tables of contents for OBIEE, BI Publisher, and Marketing.  Documentation — Displays the documentation library for OBIEE. 

About Oracle BI EE — Displays a dialog identifying the OBIEE version and copyright information.

Signs you out of OBIEE Displays the Home page Displays the Catalog page, where you can locate objects in the catalog and perform tasks specific to those objects Displays your favorite objects and any categories that you created to organize your favorite objects Contains links to all dashboards that are stored within the Dashboards sub-folder of the user's folder or any shared folder. Note that dashboards not saved to the Dashboards sub-folders are not displayed from the global header Dashboards list) Displays a list of the objects that you can create. To create an object, select it from the list. The appropriate dialog or editor is displayed for you to create the object Displays the following options:  Open — Displays the ‘Open dialog’ where you can select the object with which you want to work.  Recent objects — Displays a list of the objects that you have recently viewed, created, or updated. You can use this list to select an object with which you want to work. 

OBIEE Answers Reference Guide

Most Popular objects — Displays a list of the objects that are accessed the most often by the users that are assigned to the groups to which you belong. You can use this list to select an object with which you want to work

Page 4 Version 3.1

Report Writing using OBIEE Answers

4 Creating a Folder A folder in OBIEE is similar to a directory in Windows where folders are the ‘containers’ and the files held in the folders are the ‘objects’. OBIEE end users store and select from one or more folders to retrieve information from the database. Folders can be created before objects are created or at the time an object is to be saved. Learning Note: Term

Definition

Object OBIEE generic term for analysis, reports, charts and dashboards

4.1

Exercise 1 – Create a Folder 1. (N) - YOUR ORGANISATION OBIEE Analysis GL 2. Select the 3. Select the

function link from the Global Header

4. Expand

OBIEE Answers Reference Guide

Page 5 Version 3.1

Report Writing using OBIEE Answers

Learning Note: My Folders All of your folders will be displayed. These will be personal to your login and therefore not visible to other users. Folders can be added or deleted as required.

5. Expand the

New drop-down list and select the

icon

6. Enter the following data items: Field

Value

Name Your Name Training

7. Select the

OBIEE Answers Reference Guide

button. Your new folder will now be displayed within the parent folder

Page 6 Version 3.1

Report Writing using OBIEE Answers

5 Create an Ad-Hoc Analysis Analysis and tables are created from Subject Areas held in the data warehouse. A subject area contains folders, measure columns, attribute columns, hierarchical columns, and hierarchy levels that represent information about the areas of your organisation's business or about groups of users with your organisation. Subject areas usually have names that correspond to the types of information that they contain, such as Financials – GL Balance Sheet. When you create an analysis, you first select the subject area with which you want to work. This is known as the primary subject area and is displayed in the Subject Areas pane.

5.1

Exercise 2 – Create an Ad-Hoc Analysis

In this exercise you will create a basic General Ledger Trial Balance Report as a simple introduction to OBIEE Analysis, sometimes referred to as Answers. The report will be saved in the new folder created in exercise 1.

1. Responsibility - YOUR ORGANISATION OBIEE Analysis GL 2. Select the 3. Expand the

4. Select

OBIEE Answers Reference Guide

function drop-down list from the Global Header:

from the displayed list:

Page 7 Version 3.1

Report Writing using OBIEE Answers

5. Select the required subject area –

Learning Note: Analysis are created and maintained from the Criteria tab

6. To add a report column to a report, expand the relevant folder and either double click a data element or drag it to the Selected Columns area of the screen

OBIEE Answers Reference Guide

Page 8 Version 3.1

Report Writing using OBIEE Answers

7. Select the following columns for the General Ledger Trial Balance Report: Subject Area Folder

Data Element

GL Segment1 (Entity)

Segment Code

GL Segment2 (Business Unit) Segment Code GL Segment3 (Cost Centre)

Segment Code

Facts - GL Balance

Opening Amount

Facts - GL Balance

Debit Amount

Facts - GL Balance

Credit Amount

Facts - GL Balance

Closing Amount

8. The following should be displayed in the Selected Areas area of the screen:

Learning Note: Column headings can be changed to reflect the content of the data.

9. Change the column heading for GL Segment1 10. Select the

OBIEE Answers Reference Guide

icon for GL Segment1

Page 9 Version 3.1

Report Writing using OBIEE Answers

11. Select Column Properties from the drop-down list

12. Select the

OBIEE Answers Reference Guide

tab

Page 10 Version 3.1

Report Writing using OBIEE Answers

13. Select the

tick box to over write the column name

14. Change the Column Heading to Entity 15. Ensure that the Value Supression radio button is set to Repeat.

16. Click on the

button

17. Repeat steps 10 to 15 for the GL Segment 2 and 3 columns Column

Data Element

GL Segment2 Business Unit GL Segment3 Cost Centre

OBIEE Answers Reference Guide

Page 11 Version 3.1

Report Writing using OBIEE Answers

18. To view the content of the report select the

tab (note that it may take a few

moments to retrieve the report data):

19. To save the report, click the

20. Click in

OBIEE Answers Reference Guide

icon in the top right corner of the results page:

and then select the folder the report is to be saved in:

Page 12 Version 3.1

Report Writing using OBIEE Answers

21. Name this report as Ex1 and click the

22. Select

OBIEE Answers Reference Guide

button to save the report:

to return to the Home Page

Page 13 Version 3.1

Report Writing using OBIEE Answers

6 Formatting Column Properties After you create and run an analysis, default formatting rules are applied to the results. You can create additional formatting to apply to specific results. Additional formats help you to highlight blocks of related information and call attention to specific data elements. You can also use additional formatting to customise the general appearance of analyses and dashboards. When you build an analysis, you can edit properties for columns to control their appearance and layout. You can also specify formatting to apply only if the contents of the column meet certain conditions. By default, the specifications for a column apply only to the current analysis.

6.1

Exercise 3 – Formatting Column Properties

In this exercise we will look at the various formatting options available to customise the report we created called Ex1. As well as formatting style options, you can also change the format of the data itself.

1. First we need to open our previous report; from the Catalog. Select the

tab from the

Global Header:

2. Select the folder you created earlier and click on the

link for the report we created called

Ex1:

OBIEE Answers Reference Guide

Page 14 Version 3.1

Report Writing using OBIEE Answers

3. The report is automatically displayed in the

4. To edit the report, select the

tab:

tab

5. To begin formatting the column required, select the

icon to the right of the column name,

in this case:

6. Select

OBIEE Answers Reference Guide

from the displayed list of options:

Page 15 Version 3.1

Report Writing using OBIEE Answers

7. The Column Properties window is displayed:

Learning Note: The column properties are initially set to the system default values.

OBIEE Answers Reference Guide

Page 16 Version 3.1

Report Writing using OBIEE Answers

8. This window has 5 tabs as follows: o

Style - This tab mainly deals with column formatting such as colour, font, cell alignment, etc.

o

Column Format - This tab allows you to edit the column headings and the suppression rule.

o

Data Format - This tab allows you to amend data format properties like the number format, how negative values are displayed and the number of decimal places.

o

Conditional Format - Conditional formatting allows you create rule specific formatting options. This is very useful for directing attention to data when certain criteria are met. We will cover this in more detail in a later exercise.

o

Interaction – Use this box to specify what happens when you click either the column heading or a value in the column or hierarchy level. The values that are available depend on the type of column and on column heading versus value.

9. Select the

tab

10. Tick the

box to override the system default values and amend the Data Formatting for the Opening Amount column as shown below:

OBIEE Answers Reference Guide

Page 17 Version 3.1

Report Writing using OBIEE Answers

11. Select the

tab and change the Style formatting for the same column as shown below:

12. Using the Copy icon

at the top of the page, you can copy this Style information into other

columns:

13. Click the

button

14. Click the Column Properties button on the

15. Click the Paste icon

16. Click the OBIEE Answers Reference Guide

column:

to apply the previously defined style information:

button Page 18 Version 3.1

Report Writing using OBIEE Answers

17. Now, feel free to use the style and column formatting options to personalise your report. 18. Click the

tab

19. Your report should now be displayed similar to the below depending on your formatting options:

20. Save the report, click the

icon in the top right corner of the results page:

21. Save the report as Ex2 in your folder

OBIEE Answers Reference Guide

Page 19 Version 3.1

Report Writing using OBIEE Answers

7 Conditional Formatting Use this tab of the Column Properties to add a condition to a column. You specify a condition to affect the formatting of values based on certain criteria. The formatting that you specify for a column on this tab is visible in any view that can display data and its formatting, with the exception of graphs, funnel graphs, and gauges.

7.1

Exercise 4 – Conditional Formatting

Conditional Formatting allows you to direct attention to a specific piece of data when certain criteria are met. In this example we will add Conditional Formatting to the report created called Ex2.

1. First we need to open our previous report. Select the

2. Select the folder you created earlier and click on the

tab from the Global Header:

link for the report we created called

Ex2:

OBIEE Answers Reference Guide

Page 20 Version 3.1

Report Writing using OBIEE Answers

3. The report will open in the

4. To edit the report, select the

tab:

tab:

5. Now we will add some conditional formatting to the Properties

OBIEE Answers Reference Guide

icon and navigate to the

column; click the Column tab:

Page 21 Version 3.1

Report Writing using OBIEE Answers

OBIEE Answers Reference Guide

Page 22 Version 3.1

Report Writing using OBIEE Answers

6. Select the

button to create the first condition:

7. Select Closing Amount and enter the following details: Field

Value

Operator Is less than or equal to Value

0

8. Click the

button. You will now have the option to edit the Style for your condition

9. Set the Font colour as red and the style as Bold

OBIEE Answers Reference Guide

Page 23 Version 3.1

Report Writing using OBIEE Answers

10. Add an image

for your condition:

11. To select an image, click in

12. Select the image icon followed by an image radio button:

OBIEE Answers Reference Guide

Page 24 Version 3.1

Report Writing using OBIEE Answers

13. Select the

button

14. Select the

button

15. Add 2 more conditions to the Closing Amount column as shown below: Condition Operator

Value 1 Value 2 Font Colour Image

2

Is between

0

3

Is greater than or equal to 10000

OBIEE Answers Reference Guide

10000

Bold Yellow Bold Green

Page 25 Version 3.1

Report Writing using OBIEE Answers

16. To view the results of the report, click on the

17. Save the report, click the

button then the

icon in the top right corner of the

tab:

page and save as

Ex3.

OBIEE Answers Reference Guide

Page 26 Version 3.1

Report Writing using OBIEE Answers

8 Applying Filters You use filters to limit the results that are displayed when an analysis is run. Together with the columns that you select for an analysis, filters determine what the results contain. Based on the filters and selection steps, only those results that match the criteria are shown.

8.1

Exercise 5 – Applying Filters

Filters can be applied to a report to limit results to match certain criteria, combining filters will further constrain your report results. In this exercise filters will be applied to GL Segment 1 (Entity), GL Segment 2 (Business Unit) and GL Segment 3 (Cost Centre).

1. Open the report saved as Ex3 and click

link, followed by the

tab

2. First we need to apply a filter against the GL Segment Code 1 (Entity) column; click the properties icon and then select

from the drop-down list:

3. Enter the following filter details: Field

Value

Operator Is equal to / is in Value

1000

OBIEE Answers Reference Guide

Page 27 Version 3.1

Report Writing using OBIEE Answers

4. Click the

button

5. The filter is now showing in the Filters section of the Criteria page:

6. Repeat the process by adding the following filters to GL Segment 2 (Business Unit) and GL Segment 3 (Cost Centre) GL Segment

Field

Value

2 – Business Unit Operator Is equal to / is in Value 3 – Cost Centre

Operator Is equal to / is in Value

OBIEE Answers Reference Guide

10000

102048, 104061,104800

Page 28 Version 3.1

Report Writing using OBIEE Answers

7. Display the results by selecting the

tab to confirm that the filtesr have been

applied correctly:

8. Next we will add another filter, but this time filter against data that is not displayed in the report

9. In this example we will filter to show only the results from a specific time period 10. Return to the

tab

11. In the Filters section of the screen, click on the New Filter

icon on the top right hand

side:

OBIEE Answers Reference Guide

Page 29 Version 3.1

Report Writing using OBIEE Answers

12. Select the More Columns option:

13. Expand the

folder

14. Select the Fiscal Quarter option, then click the

OBIEE Answers Reference Guide

button

Page 30 Version 3.1

Report Writing using OBIEE Answers

15. The New Filter screen is displayed:

16. Add a new filter so that only results for the third quarter of 2014 are shown by clicking on the Search icon

17. The Select Values screen is displayed, enter 2014 in the search field and click the button:

18. Select 2014 Q 3 and move it to the Selected area of the screen using the arrow key then click on the

OBIEE Answers Reference Guide

button

Page 31 Version 3.1

Report Writing using OBIEE Answers

19. The proposed filter is displayed:

20. Click the

button

21. The filter is now showing in the Filters section of the Criteria page:

22. Click on the

tab to view the details of the report

23. Save the report, click the

OBIEE Answers Reference Guide

icon in the top right corner of the results page and save as Ex4

Page 32 Version 3.1

Report Writing using OBIEE Answers

9 Applying Bins Bins enable you to combine values for the column into sets (summaries). For example, summarize the column values for selected Cost Centre’s.

9.1

Exercise 6 – Bins

Bins are useful for grouping together relative sets of data. In this example we will take a report from an earlier exercise and group the Entity (GL Segment 1) values.

1. Select the 2. Select the

tab from the Global Header option for the report saved as Ex3 and click on the

tab

3. Apply Filters to Entity (1000) and BU (10000)

4. The option to create Bins are held in the Edit Formula window accessed from the column properties

5. Click the

icon button for the Segment Code 3 column:

6. In the Edit Column Formula window, click the

OBIEE Answers Reference Guide

tab.

Page 33 Version 3.1

Report Writing using OBIEE Answers

OBIEE Answers Reference Guide

Page 34 Version 3.1

Report Writing using OBIEE Answers

7. Click the Add Bin

8. Click the Value search

button in the bottom left hand corner

icon

9. Complete the Bin by adding both 102048 and 104061 Cost Centres as shown below:

10. Click the

OBIEE Answers Reference Guide

button

Page 35 Version 3.1

Report Writing using OBIEE Answers

11. Click the button 12. Name the Bin “HE & GO Science”

13. Click the

button

14. The Bin details are displayed:

15. Select ‘Create a bin for all other values’ and name it ‘All other cost centres’.

OBIEE Answers Reference Guide

Page 36 Version 3.1

Report Writing using OBIEE Answers

16. Click the

button.

17. Display the report details by selecting the

tab:

18. Note that the Cost Centre column now has active drilldowns (hyperlinked) for both HE & GO Science and All other cost centres.

19. You will be able to select either hyperlink to drill down and view the details contained within each.

20. Save the report, click the

icon in the top right corner of the

page and save as

Ex5

OBIEE Answers Reference Guide

Page 37 Version 3.1

Report Writing using OBIEE Answers

10 Formula Editing A column formula specifies what the column values represent. You can edit the formula to add functions and conditional expressions. This editing enables you to present analysis results in a variety of ways. The Edit Formula option also allows the column heading to be renamed.

10.1 Exercise 7 – Formula Editing The Edit Formula button allows you to manually write a formula for a given column, such as ranking or percentiles. In this example we will add a new column to the report and modify the formula to create a percentage column using the Edit Formula tool.

1. Select the 2. Select the

tab from the Global Header option for the report saved as Ex4 and click on the

3. Expand the

tab

folder in the Subject Areas region of the screen

4. Add the Closing Entered Amount fact to the report. 5. Select the column properties

icon next to Closing Entered Amount, followed by

Column Properties.

6. Select the Data Format tab, tick Override Default Data Format, and change to Treat Numbers as Currency & Currency Symbol to £ English – United Kingdom, as shown below: OBIEE Answers Reference Guide

Page 38 Version 3.1

Report Writing using OBIEE Answers

7. Click the

button.

8. Then, from the column properties

icon

next to Closing Entered Amount, select

Learning Note: The edit formula tool can be used to manually write formula statements. OBIEE also provides a series of pre-defined commands that can be used to make report writing easier. For more advanced help on writing report formulae, click the right corner of the edit formula window.

link in the top

9. First rename the Column Header to “Verify Closing Balance” by ticking the Custom Headings box and overtyping the text:

OBIEE Answers Reference Guide

Page 39 Version 3.1

Report Writing using OBIEE Answers

10. Then delete the existing Column Formula "Facts - GL Balance"."Closing Entered Amount" Action Note: When creating a new formula ALWAYS place the cursor at the end of the formula before selecting the next component. Failure to do so will result in the formula being deleted and replaced by the next selected formula element.

11. Enter the following new formula in the Column Formula region of the screen: Opening Balance + (Debit Amount – Credit Amount)

a. Click the

button and select the column - "Opening Amount"

b. Place the cursor at the end of the formula c. Select the plus

button

d. Place the cursor at the end of the formula e. Select the Open Bracket button OBIEE Answers Reference Guide

Page 40 Version 3.1

Report Writing using OBIEE Answers

f.

Place the cursor at the end of the formula

g. Click the

button and select the column - "Debit Amount"

h. Place the cursor at the end of the formula i.

Select the close bracket

j.

Place the cursor at the end of the formula

k. Click the l.

button

button and select the column - "Credit Amount"

Select the Close Bracket button

m. Place the cursor at the end of the formula

12. Click the 13. Select the

OBIEE Answers Reference Guide

button tab to display the report:

Page 41 Version 3.1

Report Writing using OBIEE Answers

14. Save the report, click the

OBIEE Answers Reference Guide

icon in the top right corner of the results page and save as Ex6

Page 42 Version 3.1

Report Writing using OBIEE Answers

11 Table Properties Table Properties allow you to add column totals, change the number of lines displayed per page, alternate the background colours of the report lines and hide duplicate cell values.

11.1 Exercise 8 – Table Properties When editing the Table view, you can amend properties such as sorting defaults, column totals, page display options and table formatting controls. In this example we will take the report created in Exercise 2 and edit some of the more useful Table properties.

1. Select the

tab from the Global Header

2. Open report Ex 2 and click the

link

3. The Results screen is displayed:

OBIEE Answers Reference Guide

Page 43 Version 3.1

Report Writing using OBIEE Answers

4. To edit the Table properties, ensure table is selected in the Views region of the screen 5. Select the

OBIEE Answers Reference Guide

edit icon

Page 44 Version 3.1

Report Writing using OBIEE Answers

6. The table Layout properties screen is displayed below the report

7. First we will create a total row for each Business Unit in the second column. Click the

icon

for the GL Segment 2 column (Business Unit) and select After from the drop-down list

8. A total row is displayed after each Business Unit Value in the results table:

OBIEE Answers Reference Guide

Page 45 Version 3.1

Report Writing using OBIEE Answers

OBIEE Answers Reference Guide

Page 46 Version 3.1

Report Writing using OBIEE Answers

Caution Note: At the time of writing, there is a known issue with OBIEE Answers when adding both Section totals and Grand totals will cause an error. This is due to the size of the data values. An incident has been raised with Oracle to resolve. For this example each totalling process will be shown separately.

9. To add a Grand Total select the

icon for

and select After

from the drop-down list

OBIEE Answers Reference Guide

Page 47 Version 3.1

Report Writing using OBIEE Answers

10. To change the number of lines displayed per page, alternate the background colours of the report lines and hide duplicate cell values, select the Table View Properties

icon

11. Using the Table Properties window, set the following: i.

Data Viewing - Content paging to position the report scrolling icons ( and the number of lines per page

ii.

Paging Controls - Top to position the report page scrolling icons at the top of the report

iii.

Rows per page – 10 to display ten lines per report page

iv.

Row Styling – Select the tick box to display the report lines in alternating colours. The default is green but this can be changed by selecting the Enable alternate styling icon

OBIEE Answers Reference Guide

)

Page 48 Version 3.1

Report Writing using OBIEE Answers

12. Click the

button

13. Your report should be displayed as follows:

14. Edit any further table format properties as you wish 15. Select the

OBIEE Answers Reference Guide

button and save

the report as Ex7

Page 49 Version 3.1

Report Writing using OBIEE Answers

12 Creating a Chart View Using the Charts View you can display your reports as various chart and graph types, and then customise elements such as the title, legend, axis properties, data labels, and many others. Charts are created in a different Views (Graph, Pivot Table etc.).

12.1 Exercise 9 – Creating a Chart View In this exercise the report we created called Ex4 will be converted to a horizontal bar chart. The bar chart will then be rotated into a vertical position

1. From the

tab at the Global Header

1. Open the and select the 2. Click the

OBIEE Answers Reference Guide

link for report Ex4

New View drop-down list, and select Graph > Bar > Horizontal

Page 50 Version 3.1

Report Writing using OBIEE Answers

3. The chart will be displayed below the report

4. Click on the Edit

OBIEE Answers Reference Guide

icon in the Graph view

Page 51 Version 3.1

Report Writing using OBIEE Answers

5. You can Edit the displayed properties of the graph using the toolbar:

6. In our chart we have decided we would like to see the columns in a vertical format 7. Click on the

drop-down list from the tool bar and select

from the

displayed choices

OBIEE Answers Reference Guide

Page 52 Version 3.1

Report Writing using OBIEE Answers

8. The chart view has now been changed as below

9. Click on the

Graph Properties button to access further editing options such as Style and

Titles

OBIEE Answers Reference Guide

Page 53 Version 3.1

Report Writing using OBIEE Answers

10. Add a background colour (to the graph and plot area) and a border 11. Select the

tab

12. Change the title of the graph by selecting the

13. Select the OBIEE Answers Reference Guide

tab

button Page 54 Version 3.1

Report Writing using OBIEE Answers

14. Click the

button

15. Once finished save the chart view as Ex8

OBIEE Answers Reference Guide

Page 55 Version 3.1

Report Writing using OBIEE Answers

13 Creating Pivot Tables The pivot table is an interactive view that allows you to rotate and re-position your rows and columns to obtain different perspectives of your data.

13.1 Exercise 10 – Creating Pivot Tables In this example we will create a pivot table that displays the results for the Business Units for Enity 4260, not as rows but as columns. Row and Column totals will also be added.

1. Select the

tab from the Global Header

2. Open report Ex 1 and click the

link

3. Firstly, add a Filter for Entity 4260 4. Select the

tab

5. Add a filter for Entity 4260 in GL Segment1

OBIEE Answers Reference Guide

Page 56 Version 3.1

Report Writing using OBIEE Answers

6. Select the

7. Click the New View

OBIEE Answers Reference Guide

tab

drop-down list and select Pivot Table

Page 57 Version 3.1

Report Writing using OBIEE Answers

8. Two views will be displayed – Table and Pivot Table

9. By default the results will be displayed as a basic Pivot Table, by dragging the column headings to different positions in the Pivot Table you can tailor the report view

OBIEE Answers Reference Guide

Page 58 Version 3.1

Report Writing using OBIEE Answers

10. Select the edit

icon in the Pivot Table view

11. With the mouse, drag the GL Segment 2 (Business Unit) row into the Columns area of the screen

12. The amended Pivot Table will be displayed

OBIEE Answers Reference Guide

Page 59 Version 3.1

Report Writing using OBIEE Answers

13. To add Row totals (Grand Total) select the

icon and the Before option from the displayed

drop-down list

14. The Pivot Table will be redisplayed

Caution Note: At the time of writing, there is a known issue with OBIEE Answers when adding both Row and Column totals resulting in an error. This is due to the size of the data values. An incident has been raised with Oracle to resolve. For this example each totalling process will be shown separately.

15. To add Column totals select the

icon and the Before option from the displayed drop-

down list

OBIEE Answers Reference Guide

Page 60 Version 3.1

Report Writing using OBIEE Answers

16. The Pivot Table will be resplayed

17. Select the

button

18. Save the Pivot Table view as Ex9

OBIEE Answers Reference Guide

Page 61 Version 3.1

Report Writing using OBIEE Answers

14 Editing the Compound Layout The compound view is how the report will look when viewed as part of a dashboard or directly from answers. Each individual view within the Compound Layout can be edited to change:

1. The Title of the Compound Layout 2. The Table Properties (See section 11) 3. The Chart Properties (See section 12) 14.1 Exercise 11 – Editing the Compound Layout In this example we will modify the Compound Layout called Ex8 and add titles to the Compound Layout.

1. Select the

tab from the Global Header

2. Open report Ex 8 and click the

link

3. The Compound Layout will be automatically displayed in the

OBIEE Answers Reference Guide

tab

Page 62 Version 3.1

Report Writing using OBIEE Answers

4. You can edit and format each view in the compound layout 5. To add titles to the Compound Layout select the

OBIEE Answers Reference Guide

icon in the Title section of the Layout

Page 63 Version 3.1

Report Writing using OBIEE Answers

6. The following screen is displayed

7. Enter the following data. As each field is populated the information is displayed in the lower half of the screen Field

Contents

Title

Basic Balance Sheet (Use the

Display Name

icon to amend the font)

Saved By default the name the compound layout was saved as will be displayed. To hide the name deselect the check box

Subtitle

Report and corresponding Chart (Use the

Started Time

Display date and time (Use the

8. Select the

OBIEE Answers Reference Guide

icon to amend the font)

icon to amend the font)

button

Page 64 Version 3.1

Report Writing using OBIEE Answers

OBIEE Answers Reference Guide

Page 65 Version 3.1

Report Writing using OBIEE Answers

9. We will now remove the Table section leaving just the graph within the compound view. 10. Click into the table properties within the Table section

11. Select the cross symbol

to delete the table section.

12. Save the report as Ex10

OBIEE Answers Reference Guide

Page 66 Version 3.1

Report Writing using OBIEE Answers

15 Dashboards Dashboards provide personalised views of financial and external information. A dashboard consists of one or more pages containing columns and sections. Pages can display anything that you can access or open with a web browser, including the following:

1. The results of analysis a. The result of an analysis is the output returned from the Oracle BI Server that matches the analysis criteria. It can be shown in various views, such as a i. Table ii. Graph b. Users can examine and analyze results, print as PDF or HTML, save as PDF, or export them to a spreadsheet.

2. Images 3. Text 4. Views of folders in the Oracle BI Presentation Catalog 5. Links to web sites 6. Links to documents 7. Embedded content (such as web pages or documents) 8. Oracle BI Publisher reports

OBIEE Answers Reference Guide

Page 67 Version 3.1

Report Writing using OBIEE Answers

15.1 Dashboard Prompts A prompt that is created at the dashboard level is called a dashboard prompt because the prompt is created outside of a specific dashboard and is stored in the catalog as an object, which can then be added to any dashboard or dashboard page that contains the columns that are specified in the prompt. Dashboard prompts allow the end user to specify the data values (parameters) that determine the content of all of the analyses and scorecard objects contained on the dashboard. Dashboard prompts are reusable, because you can create one prompt and use it many times. When the prompt object is updated and saved, those updates are immediately displayed in all dashboards where the prompt is used. A dashboard prompt is a specific kind of filter that, when created, saved, and applied to a dashboard or dashboard pages, can filter all or some of the analyses and scorecard objects that are embedded in a dashboard or analyses and scorecard objects that are embedded on the same dashboard page. A dashboard prompt is interactive and is always displayed on the dashboard page so that the user can prompt for different values without having to re-run the dashboard. Users can create and save dashboard prompts to either a private folder or to a shared folder. Creating Dashboard Prompts is a two step process: 

Step 1 - Set the option for the columns that are to act as Prompt paramaters in the Dashboard page to ‘is prompted’



Step 2 - Create and save a Dashboard Prompt file

OBIEE Answers Reference Guide

Page 68 Version 3.1

Report Writing using OBIEE Answers

15.2 Exercise 12 – Step 1 - Setting Dashboard Prompt Column Filters In this example we will modify the Compound Layout from Ex1 changing the column headings and set the filters for Segments 1, 2 and 3 to ‘is prompted’.

1. From the

tab at the top of the screen select the

link for report Ex1

2. The Compound Layout will be automatically displayed in the

OBIEE Answers Reference Guide

tab

Page 69 Version 3.1

Report Writing using OBIEE Answers

3. Select the

tab

4. Amend Business Unit and Cost Centre to ‘is prompted’. 5. To set the column filter to ‘is prompted’ select the name for Business Unit and select the

6. From the

OBIEE Answers Reference Guide

options icon to the right of the column

option

drop-down list select is prompted

Page 70 Version 3.1

Report Writing using OBIEE Answers

7. Select the

button

8. Repeat the above process for the Cost Centre column (Steps 5 to 7)

OBIEE Answers Reference Guide

Page 71 Version 3.1

Report Writing using OBIEE Answers

9. Select the

tab

10. Save the report, click the

icon in the top right corner of the

page and save as

Ex11

11. If you have a graph in your compound layout, you will need to click on the

icon in the Title section of the graph and move the graph prompts into the Excluded section, as shown below.

OBIEE Answers Reference Guide

Page 72 Version 3.1

Report Writing using OBIEE Answers

15.3 Exercise 13 – Step 2 - Creating a Dashboard Prompt In this example we will create a Dashboard Prompt to apply to reports created from the Financials – GL Balance Sheet Subject Area to be placed on a dashboard.

1. Select the

2. Select

from the Global Header

from the displayed drop-down list. A list of the Subject Areas will be

displayed:

OBIEE Answers Reference Guide

Page 73 Version 3.1

Report Writing using OBIEE Answers

OBIEE Answers Reference Guide

Page 74 Version 3.1

Report Writing using OBIEE Answers

3. Select the Subject Area the Dashboard Prompt is to be created from. In this example select the displayed:

4. To add a Prompt select the

subject area. The Dashboard Prompt screen will be

icon drop-down list

5. Select Column Prompt… from the displayed list 6. Expand the GL Segment 2 folder

OBIEE Answers Reference Guide

Page 75 Version 3.1

Report Writing using OBIEE Answers

7. Select

followed by the

button to display the New Prompt detail screen

8. Overwrite the contents of the Label field with Business Unit. This will be the name displayed for the prompt:

OBIEE Answers Reference Guide

Page 76 Version 3.1

Report Writing using OBIEE Answers

Field

Description

Label

The name displayed against the prompt

Description

Description of the prompt

Operator

What data is returned on the report is based on the operator value selected Choice List default – displays a list of the possible data items for the prompt from a drop-down list. Allows the selection of multiple prompt values Check Boxes – automatically displays the complete list of possible data items for the prompt with together with check boxes for selection (No drop-down list). Allows the selection of multiple prompt values

User Input Radio Buttons - automatically displays the complete list of possible data items for the prompt with together with radio buttons for selection (No drop-down list). Allows the selection of multiple prompt values List Box - automatically displays the complete list of possible data items for the prompt. (No drop-down list). Single prompt selection only

9. Select the

button. The new Prompt will be displayed in the top half of the screen. The lower half of the screen displays how the Prompt will be displayed in the Dashboard:

OBIEE Answers Reference Guide

Page 77 Version 3.1

Report Writing using OBIEE Answers

10. Add another prompt for GL Segments 3 with a Label of Cost Centre by repeating steps 4 through 9

11. By default the prompts will be displayed in a column format in the lower half of the screen. To change the grouping of how the prompts will be displayed select the check box(es) in the New Column if the prompts are to be displayed in different columns

12. Save the Dashboard Prompt, click the

icon in the top right corner of the page and save as

Ex12 - Dashboard Prompt Learning Note: Dashboard prompts can be used on multiple reports that have been created form the same Subject area(s).

OBIEE Answers Reference Guide

Page 78 Version 3.1

Report Writing using OBIEE Answers

15.4 Exercise 14 – Creating My Dashboard “My Dashboard” is a personal space within OBIEE where you can place objects such as reports, URLs, text, images and prompts. The dashboard can be arranged across multiple sections, columns or even pages depending on how much content you want and how you want it to be arranged. In this exercise we will cover the basics of arranging a dashboard page.

1. From the

tab on the Global Header select My Dashboard

2. Your personal Dashboard will be displayed which should be blank as the Dashboard has not been created

3. Select

OBIEE Answers Reference Guide

to create your dashboard:

Page 79 Version 3.1

Report Writing using OBIEE Answers

4. In the Catalog section of the screen expand

to locate the Reports, Tables

and Charts to be populated on the dashboard:

5. Locate the Ex4 report you saved in your own folder and drag it into the blank region on your dashboard page:

Learning Note: OBIEE has automatically created Page 1 of the dashboard, Column 1 and Section 1, containing the report.

OBIEE Answers Reference Guide

Page 80 Version 3.1

Report Writing using OBIEE Answers

6. To create another column select

from the Dashboard Objects region of the screen and drag it to the desired location. For example to the right of column 1

Learning Note: Where the new column will be located is determined by the position of a blue location line that is displayed while the new column is being dragged.

7. Select Ex12 - Dashboard Prompt and drag it into column 2

8. To create another section select

from the Dashboard Objects region of the screen and drag it to the desired location. For example below section 1 in column 1

OBIEE Answers Reference Guide

Page 81 Version 3.1

Report Writing using OBIEE Answers

9. Select and drag Ex6 into the section 3 in Column 1 below Ex4 in a new section 10. Select and drag Ex11 into Column 2 and drop it below Ex12- Dashboard Prompt in Section 2

Learning Note: The properties of the columns, sections or items on the dashboard can be modified by selecting the

icon in the relevant region that the cursor has been placed.

An active Column, Section or Item is highlighted by a yellow boarder.

11. As a demonstration the first Section in Column 1 will be given a name.

12. Ensure the cursor is in the first section, click on the

OBIEE Answers Reference Guide

icon and select Rename

Page 82 Version 3.1

Report Writing using OBIEE Answers

13. Enter Basic GL Balance Sheet as the section name and select the

14. Select

15. Close

button

from the toolbar to review the contents of the Dashboard

the Preview screen

16. Select the

icon to save My Dashboard

Learning Note: Select the

17. Select the

icon to save the Dashboard under a different name.

icon to refresh and display the Dashboard data

Action Note: The Dashboard MUST be saved before selecting the the Dashboard will be lost. OBIEE Answers Reference Guide

icon. If not the details of

Page 83 Version 3.1

Report Writing using OBIEE Answers

OBIEE Answers Reference Guide

Page 84 Version 3.1

Report Writing using OBIEE Answers

15.5 Exercise 15 - Link to an Existing Dashboard Report from My Dashboard OBIEE allows users to Link to and modify existing dashboard reports on their personal dashboard. In this exercise we will cover how to link to an existing centrally created dashboard report from ‘My Dashboard’.

1. From the Dashboard Editor, select the Link or Image Dashboard Object and drag it into Column 1 as a separate section below Ex 6:

2. To link to an existing dashboard click on Properties

OBIEE Answers Reference Guide

within the Link or Image 1 section:

Page 85 Version 3.1

Report Writing using OBIEE Answers

3. The Link or Image Properties screen is displayed.

4. Select the

button to locate the Shared Dashboard or Analysis to be linkled to your

Dashboard

5. All the available dashboards are displayed, select the required option, for example Quarterly Balance Sheet

OBIEE Answers Reference Guide

Page 86 Version 3.1

Report Writing using OBIEE Answers

6. Select the

button

Learning Note: The Caption can be modified if required.

Learning Note: The destination can either be: URL – Web address link to external information Request or Dashboard – Link to exisiting Dashboards/Requests within OBIEE

7. You can specifiy whether the link options in the same window as your dashboard or a whether a new window should open

8. After entering all the required properties click on 9. The Dashboard Editor screen is displayed, click on

OBIEE Answers Reference Guide

and then

Page 87 Version 3.1

Report Writing using OBIEE Answers

10. The My Dashboard screen is displayed containing the new link to the existing Quarterly Balance Sheet report:

11. To open the Quarterly Balance Sheet report click on the Quarterly Balance Sheet link

OBIEE Answers Reference Guide

Page 88 Version 3.1

Report Writing using OBIEE Answers

15.6 Exercise 16 – Adding Report Links to your Dashboard In this example we will add links to your reports to enable you to download, print or export them.

1. From the Dashboard Editor, click on Properties

for the Basic GL balance Sheet report

within the first section:

2. Select Report Links

OBIEE Answers Reference Guide

Page 89 Version 3.1

Report Writing using OBIEE Answers

3. Select the

OBIEE Answers Reference Guide

radio button

Page 90 Version 3.1

Report Writing using OBIEE Answers

4. The following options are displayed: Report Link Option Description Analyze/Edit Export Refresh Print Add to Briefing Book

Takes you back into Answers to amend the report To Excel, PowerPoint, PDF Refreshes the data Allows you to print the report Not enabled in our version on OBIEE but will allow you to download reports offline and share with other users.

5. Tick the options Edit, Export, Refresh and Print options and click

6. Click on

and then

7. The links are now displayed at the bottom of the Ex 4 report:

8. Repeat this process for all the reports on your Dashboard 9. Click on

and then

16 Appendix A – Icon Definitions 16.1 Catalog Icon

Name

Function

New

Create a new analysis, filter, dashboard prompt

OBIEE Answers Reference Guide

Page 91 Version 3.1

Report Writing using OBIEE Answers

Refresh

Refresh the contents of the catalog

Up

Move up to the next level in the folder hierarchy

Show / Hide folders pane Search

Search for a file(s) in the catalog

Change List View Type

Change the format of how the folder contents are displayed

Create a new folder

Only active at the My Folders level via the

icon

16.2 Folders Region Icon Name

Function

Show Folder Tree Show Quick Folders

Display all folders in hierarchical directories Access Use this button to list, excluding their hierarchy, the following folders:

1. My Folders 2. Shared folders 3. My (object) folder

16.3 Save As Icon

Name

Function

Up

Move up to the next level in the folder hierarchy

Create a new folder Change List View Type Delete the Title section from the Layout

16.4 Criteria Tab 16.4.1 Subject Areas Pane Icon Name Add/Remove Subject Areas Refresh display

OBIEE Answers Reference Guide

Page 92 Version 3.1

Report Writing using OBIEE Answers

Remove View from Compound Layout

16.4.2 Criteria Icons Icon Name Show/Hide Filters pane Show/Hide Selection Steps pane Edit Analysis Properties Save Save As Help

16.4.3 Selected Columns Pane Icon Name Remove all columns Apply the following to the selected column Sort Edit Column Formula Column Properties Add a Filter to the column Delete the column

16.4.4 Filters Pane Icon Name Create a filter Remove all filters

OBIEE Answers Reference Guide

Page 93 Version 3.1

Report Writing using OBIEE Answers

16.5 Results Tab 16.5.1 Subject Areas Pane Icon Name Add/Remove Subject Areas Refresh display Remove View from Compound Layout

16.5.2 Catalog Pane Icon Name Refresh display

16.5.3 Views Pane Icon

Name Create a new view Edit the view Duplicate the view Delete the view Rename the view

OBIEE Answers Reference Guide

Page 94 Version 3.1

Report Writing using OBIEE Answers

16.6 Table Icon Name

Function

Format Container

Modify cell alignment, background colour and borders

Edit View

Format the Table Column properties

Remove Layout

View

from

Compound Delete the Table from the Compound Layout

16.7 Compound Layout Title Icon Name

Function

Format Container

Modify cell alignment, background colour and borders

Edit View

Modify the name of the Title

Remove Layout

OBIEE Answers Reference Guide

View

from

Compound Delete the Title section from the Compound Layout

Page 95 Version 3.1