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