Leagan Gaeilge   Text Version   Display: Zoom (-) (+)   View ( Normal High contrast ) Printer Friendly  A to Z  Sitemap

•   Important Links
• Staff eMail
• StudentMail
• Blackboard
• Print Credit
• Service Desk Ticketing System

information solutions and services, Quercus, Oracle BI Discoverer

   

OracleBI Discoverer 10g (Version (10.1.2.2)

Oracle Discoverer can be accessed from Staff/Online Services page on the NUI Galway website and selecting the Reporting (Discoverer) link.  This page includes the following information:

1. Discover Reports Student Records System: this is a link to the Oracle Discoverer software that is based on the Student Records System (Quercus Plus).  This is the link that we are interested in.
   
   
2. Discover Reports HR/Payroll/Finance Systems (Please note that the database name has changed to core): this is a link to the Discoverer software that is based on the Core and Agresso software packages used by the Accounts Office and HR. We will NOT be using this.  The instructions in this manual do NOT apply to this software
   
   
3. How to Create a Connection in OracleBI Discoverer Plus v10: This contains instructions for the creation of a connection which is the account details necessary to log into the Discoverer software to access the Student System.

What is Oracle Discoverer?

Oracle Discoverer is a reporting tool that sits on top of a database that has been designed in Oracle.  Examples of Oracle databases at NUI, Galway would include Quercus Plus (Student Records), Core (Payroll) and Agresso (Accounts).  Note that the providers of software (Quercus Plus, Core, Agresso) design the software with Oracle database in the background.

What kind of reports can we get with Oracle Discoverer?

Oracle Discoverer allows us to get the following types of reports:

• Lists of Data e.g. names and addresses of students registered on a course
• Summaries of Data e.g. counts of numbers of students registered on all courses
• Graphs

The reports are presented in formats which can be printed in their own right, but which also can be exported in electronic format.  Often Oracle Discoverer reports are exported as Microsoft Excel file which is then put to other use such as:

• Microsoft Word Mail Merge with Microsoft Excel names and addresses of students registered on a course
• Circulation of Microsoft Excel counts of students registered on all courses

 
Connecting to Oracle Discoverer

Click on the link for Discoverer Reports Student Records System and you will be presented with the following screen:

To use Oracle Discoverer we have to create a Connection.  In the above there are no connections currently set up.  We will be creating the connection for Admissions.  You may ALREADY have the Admissions connection set up.

To create the Admissions connection, press the Create Connection button. The following page will appear:

Complete the detail as outlined above. Note that you have to obtain the password field.

Useful Tip: Specify the Locale as English do not leave it as Locale set in users browser as this will mean that each time you log into Discoverer you have to specify the language you wish to use.

Once you have entered all the required information press the Apply button and then you will see the following:

Notice the new connection for Admissions has been created.  It is only necessary to create a connection ONCE.  The connection will be remembered on your PC.

Lets use the link we have just created. Click on the Admissions connection and you will see the following box:

N.B. If you experience any issues with connections please email  servicedesknuigalway.ie or call extension 5777 to solve the issue.

Note that the details of your connection have been remembered and you any NOT expected to re-enter them EXCEPT for the password which you MUST enter each time you log in.  This is necessary for security reasons.  Enter the password and press Connect.  At this point one of TWO things will happen:

1. You will download the Oracle Discoverer Client Software and it will be automatically installed on your PC.  This applies if you have never used Oracle Discoverer before.  Once the software has installed the software will open and you will see the screen on the next page
2. You will open the software and see the screen as outlined on the next page.

 

At this point it is worth looking at some of the conventions used in Oracle Discoverer and also look at how Oracle Discoverer has been implemented at NUI, Galway.

• Workbook: A work book is a collection of Sheets
• Worksheet: A Worksheet is a report in Oracle Discoverer. As there can be several Worksheets in a Workbook there can be several reports in a Workbook.
• Conditions: the criteria that form the basis of the report
• Parameter: when the user is asked to specify the Condition when they open the report for the first time
• Table: This is a format which is applied to a Worksheet and is a straight forward view of the data
• Crosstab: This is a format which is applied to a Worksheet which is complex with Calculations on Data e.g. Counts of Students, Totals for money.

To open an existing workbook click on the Browse button. Remember to use only those workbooks create by the user QUERCUS. Any workbooks owned by another users e.g. ADMISS_REPORT are not updated centrally by QUERCUS and therefore may not contain the correct data.  This happens when you exit the report and say yes to saving it instead of no or cancel.

Full details of the Oracle Discoverer reports available are on a Microsoft Excel file which is circulated separately and maintained by Academic Records.

You can click on the first letter of the word e.g. R and it will bring you to the first occurrence of the letter R as in Records – so to get to the workbook you need then scroll down to the workbook QUERCUS.Records – Class List with Home Address – Registered.  If we double click on the report name OR click on the + sign in the  to the left of the report name we will see the Worksheets within the Workbook.  This is presented to us as follows:

It is also possible to search for a Workbook based on the name. For example to search for workbooks containing the word class, we would do the following:

Once we select a Worksheet we can then press the Open button and the Workbook will open on the selected Worksheet.

The view you choose depends on the options ticked under View which will display available items pane and selected items pane or none if they are not selected.

When we open our Workbook we are presented with the following:

There are several worksheets within this workbook including All Students and 1st years.  The worksheet 1GU1 is currently selected.  To select the All Students worksheet, simply click on the sheet title. The contents of the Worksheets within each Workbook are also explained in the Spreadsheet from Academic Records.

There are a couple of other features from the above workbook that merit attention:

The Standard Toolbar which is made up as follows:
	Create New Workbook
	Open an Existing Workbook
	Save Workbook
	Print Worksheet
	Print Preview
	Add Worksheet (Duplicate as Table, Duplicate as Crosstab)
	Edit Worksheet
	Edit Table
	Table Layout
	Edit Graph
	Refresh
	Sort Low to High
	Sort High to Low
	Group Sort
	New Total (Sum, Count, Average, Maximum, Minimum)
	New Percentage (Percentage)
	New Calculation (Add, Subtract, Multiply, Divide)
	New Condition (Equals, Not Equals, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals)
	Export to HTML
	Export to Excel
The Formatting Toolbar which is made up as follows:
	Font Name, Font Size
	Bold, Italic, Underline
	Left, Centre, Right, Start, Word Wrap
	Number Format (Number, Currency, Percentage)
	Decimal Right, Decimal Left
	Background Colour, Font Colour, New Conditional Format, New Spotlight Format

The other components are explained further on in this document.

Ensure that under Tools/Options

Query Governor – the tick is not applied to Limit retrieved Rows to 10000

Advanced – the tick is applied to Disable Fan-Trap Detection

Sheet – Show NULL values as – set this to blank and not NULL.

Using Oracle Discoverer at NUI, Galway

You will have noticed at this stage that there are two options available when you use Oracle Discoverer:

1. To Create New Workbooks from Scratch
2. To Open an Existing Workbook

Creating New Workbooks

Unfortunately, due to the complexity in the structure of the database underlining the Quercus Plus system for Student Records, it is too complex to create workbooks from scratch.  This has to be done by MIS.  However a significant body of workbooks have been developed to meet most user needs and these are documented and explained in the Microsoft Excel spreadsheet prepared by Academic Records.  When a report is required it should be determined whether the report exists already.  If not then a request needs to go to MIS for same.  This practice is currently under review and it is hoped that in the future it may be possible to create workbooks from scratch.

Opening Existing Workbooks

This is what we are going to focus on.  It should be noted that many workbooks have been set up flexibly in that:

• There may be parameters set up where you have to specify criteria when you select the worksheet e.g. specify the academic year
• Page Items may be used which allow you to select values e.g. select the Course Instance code

You might feel that some of the reports would benefit from modification.

Looking at the workbook QUERCUS.Records – Class List with Home Address – Registered again and click on the All Students sheet.  The first thing we notice is that we are asked to enter an Academic Year.  This means that a parameter was set up to ask the user to specify the academic year.  Enter the academic year you require and click on OK.

The report that we now see consists of records for 2006. The parameter has limited the selection to 2006.  Also note that Academic Year was a Required Field to be specified as a parameter.

The Page Items on the sheet allow us to filter the data further.  When the sheet first opens we see records for 2GA3.  The other Page Items give us further information on the other filters in operation:

Faculty:	Adult & Continuing Education
Course Stream:	Bachelor of Arts / Diploma in Arts (P/T) - Galway
Instance Code:	2GA3
Stream Description:	GA3Bachelor of Arts (Evening Degree)
Course Year:	2

Click on the black down arrow beside Instance Code and you will see a list of the available Instance Codes within the specific faculty:

Note the dot beside 2GA3 indicates it is currently selected.  Also notice that it is possible to select the value <All> from ANY of the Page Items.

Useful Tip: when working with Multiple Page Item use the following approach:

• Where values are already selected in the worksheet, change ALL values to ALL
• Starting from the Left and Working to the Right choose the values that you require
• If you are not sure about a particular value ask someone or see what the results look like if you leave that value at all
• Remember you must check ALL the filters

The above sheet allows you to select a list of addresses for groups of students based on the Page Items.  How do we get 1BA1?

So far we have come across Parameters and filtering based on Page Items Lets look at how to edit a workbook in existence.

Editing Existing Worksheets

Before proceeding any further, it is important to note that you CANNOT save any changes that you make to the Workbooks/Worksheets you are working with.  The purpose of making changes is to allow you make minor alterations to a report to meet a once off requirement. If such requirements are frequent/regular, the relevant record should be modified by MIS on a permanent basis.

The following points should be noted

• If you edit and EXISTING workbook you should NOT save your changes.
  Usually the workbook will be write protected and the save button on the Discoverer toolbar will be shaded out
  
• You could consider exporting the results of a report to Microsoft Excel and fine tuning your data further in Microsoft Excel.

You will notice that if the workbook has been saved by the user logged in then it is prefixed with the owner of that workbook and you can sort by clicking on the Owner tab to sort ascending or descending by Owner. As you will see there are a number of workbooks saved by the user ADMISS_REPORT but as these are not maintained they should not be used. You need to use the equivalent name owned by QUERCUS.

There are three means of editing a worksheet:

1. Adding/Removing Items, Conditions and Calculations with the Available Items Pane and the Selected Items Pane
2. Changing the formats of information using the Formatting Toolbar
3. Use the Edit Worksheet button to change Table Layout and to Add/Remove Parameters

Available Items Pane 

	Add to Worksheet
	Remove from Worksheet
	New Total
	New Percentage
	New Calculation
	New Condition
	Edit
	Show
	Delete

 

Items Tab

The Items tab in the Available Items Pane indicates ALL of the available information in an underlying database that can be included in a Discoverer Report.  Usually this is typically straightforward but due to the complexity of how Oracle Discoverer is mapped onto the Quercus database, adding items can be difficult.  You can see from the example above, the complexity in how Oracle Discoverer has been set up on Quercus. You need to know the Business Area that is used for the report first in order to add extra fields into the report which is listed in the spreadsheet mentioned above that is maintained by records under the heading BA.

Items are added to the report by selecting the item on the Items Window and pressing the Add to Worksheet button ().  Once the correct Business Area has been selected items are available as they appear in black. As items are added they appear in the Selected Items Pane.  The added item also appears in the Available Items Pane with a tick () beside it.

Conditions Tab (Also  on toolbar)

This is where the conditions are specified so that Oracle Discoverer can tell Quercus Plus what records it wishes to see on the reports.  A condition can be set up with an explicit value e.g. where Academic Year is 2006 or a condition can be set up in a flexible way where the user can specify a value each time e.g. where the Academic Year is the value entered by the user.  This value provided by the user is called a parameter.  In the Available Items Pane click on the Conditions tab

We can see from the above that there are several conditions set up.  However, only ones TICKED () apply to the CURRENT worksheet.  In the above example, the condition Instance Code IN (’3NG1’,’4NG1’) is not active.

To make the condition Instance Code IN (’3NG1’,’4NG1’) active select the condition and click on the Add to Worksheet button ().  The Worksheet will update and a tick () will appear to the left of the condition.

Working With Conditions

Select the Instance Code IN (’3NG1’,’4NG1’) and click the Edit Button ().  You will see the following:

Note the Formula Box operates as follows:

Component	Description
Selected Item	Any Item on the Quercus System
Condition	Including
	• =" • > • < • <> • IN • LIKE
Value	Which you can • Specify • Choose from a List of Other Selected Items • Choose from Available Values

Have a go at changing the condition.  Some examples:

Course Instance = 1BA1
Course Instance IN (1BA1, 2BA1, 3BA1)
Course Instance LIKE º%
Date Of Birth IS NULL
Socio Economic Group IS NULL

Calculations tab (Also on toolbar)

This allows us to perform Calculations on two or more columns (also  on toolbar) within a worksheet.  In addition it is also possible to put in Percentages (also  on toolbar) and Totals (also  on toolbar) for inclusion on the Worksheet.

Formatting Toolbar

Select the appropriate items from the Worksheet and change the formats as you would in Microsoft Word or Microsoft Excel.  If you need to use a different font you need to highlight the columns you need to change.  To choose more than one column press down the shift key while at the same time clicking on the columns you want to re-format. 

To choose a font type, a font size, bold, italics, underline so choose for example Tahoma, 10 with bold, italics and underline. 

You will see the columns change format.

You can also choose to align with center, left, or right 

 
So choosing center you get
 

Edit Worksheet (Table Layout, Parameters)

Lets take our sample workbook QUERCUS.Records – Class List with Home Address – Registered and again we are on the ALL Students Tab where we were prompted for the Academic Year for which we entered 2007.

If we want to run this Worksheet again and be prompted for the Academic Year, we do not have to close and open the Worksheet again. Simply press the Refresh button ().

Lets click on the Edit Worksheet button.  When we do this we will see the Edit Worksheet Screen which has several tabs within it. We will go through each of these tabs in turn.  You should note that the Oracle Discoverer toolbar has several buttons that can be used to activate the Edit Worksheet Screen BUT some can select a different tab in the first instance.  These buttons are also indicated below.  Choose which method of navigation works for you.

Select Items  (also operated by using the Available Items Pane and Selected Items Pane)

This is where the items that appear on the report are selected from Quercus database.  In the screen below you can see, on the left, that there are several components available including information on the Person and the Course Instance.

On the right you can see that Address detail has been selected to be included in the report including Address Lines 1-6.

Table Layout (Also  on toolbar)

This is where it is determined how the information selected will appear in the worksheet. i.e. which column comes first, second etc. This is also where Page Items are specified.  In the example below, Instance Code will not appear as a worksheet column but will be a Page Item allowing the user to select the Instance Code within the worksheet.  N.B. This tab does not determine how the information will be sorted.

To filter the data all you need to do is to drag the field name you want from the report body up to page items. For example if you were looking for a specific surname for the student click on Surname and hold down the left mouse button and then drag to the page items area. You can then click on the right arrow and choose the surname.

You can drag as many page items as you need but just be careful not to clutter the page items as it becomes difficult to find what you need. In these cases as you will see extra worksheets have been used to filter for specific requests as in this workbook where there is a sheet for ’3NG1’ and ’4NG1’.

Sort (Also  and  on toolbar)

This is where the sort order for the spreadsheet is determined by indicating the Columns that form the basis for sorting.  For each component you specify the Order or Direction and the Sort Type. 

In the example above the sort is by Surname then by First Name

Parameters

This is where we can specify a parameter for our worksheet.  A parameter is where we ask the user to specify criteria for the purposes of the report. In the example below we can see that the report requires us to specify the academic year. 

If we select Academic Year and press the Edit button, we can see how the parameter is set up. We will see the following:

The default value that is set will always appear unless it is changed by the owner Quercus. The other option is to leave it blank so that the user can choose the year but as there are new users beginning to use the system if it is populated then it serves as a hint to what they should be putting in there. Otherwise leaving it blank is prone to error with what should be entered.

Exporting to Microsoft Excel

One of the useful features of Oracle Discoverer is that we can export our reports into Microsoft Excel.  This allows us to share the contents of the reports with staff that do not have access to Oracle Discoverer.  Exporting to Microsoft Excel is also useful for extracting student details for the purposes of a personalised mail shot.  Microsoft Word allows a mail merge to be done with a Microsoft Excel spreadsheet as the data source.  As well as merging to send a personalised letter it is also possible to merge to send a personalised email.

Once you have your report on screen, choose Export … from the File menu.

Useful Tip:  avoid the temptation to use the Export to Excel button  in the Oracle Discoverer toolbar.  This does NOT give you the option to specify the directory to which you wish to save your file.

You should see the following screen:

We will just be exporting the Current Worksheet so select this radio button and click on Next.   We will then see the following screen:

You can see from the above that you have the option to specify the Export Format which should be set to Microsoft Excel Workbook.  You also have the option to specify where you want to Save the file.  By default Oracle Discoverer does NOT save files in the My Documents folder.  Oracle Discoverer also assigns the name of the workbook to the name of the Excel File.  In general, the default location and filename is NOT correct.  To correct this click on the Browse button and you will see the screen below:

Double click on the My Documents folder to see the contents of you’re my Documents Folder like the example below.  This is where we want to save our file.  We can further decide to save the file in a folder if we wish and there is a Create Folder button for us to use.

Before we go any further we should type in a more appropriate filename than “Records – Class List with Home Address – Registered”.  Remember we asked before how we use the workbook to get 1BA1? Well if we did get our report we could assign the filename 1BA1 Class list.  It is always a good to include the date in the filename, being the date the report was exported from Excel.  This way each time a report is extracted you will have a unique filename and won’t overwrite a report previously extracted.

Once we are happy with the location and filename we can press the Save button and you will see that the filename and location are now correct. We can click on Next and we are presented with a request for any parameters as follows:

What is happening on the above screen is that Oracle Discoverer recognises that there is a parameter set up on the worksheet and some time may have elapsed since you opened the worksheet and ran the report, so you are being asked to enter the parameter again. Obviously, this only applies if parameters are set.  The next screen looks as follows:

Always opt for supervised as this allows you see if there are any problems encountered while exporting the results.  Press the Finish button and you will see the results of the export as a log.  This should look as follows:

Opening Files in Microsoft Excel

Having exported Oracle Discoverer Reports to Microsoft Excel, you can then Open up Microsoft Excel and Open the file from the location in which it is stored.  You should see the following message on screen:

If you don’t see the above box you need to follow the instructions in the Grey Box to amend you Microsoft Excel Macro Security Settings

This is a deliberate security feature within Microsoft Excel aimed at preventing viruses embedded in what are known as Macros.  In Microsoft Excel, a macro is a very simple program that runs Microsoft Excel commands.  Often macros are used to run a series of commands for repetitive tasks.  For example if a spreadsheet was produced regularly and was formatted in the same way each time, a macro could be used to format the spreadsheet quickly. When you export a Microsoft Excel file from Oracle Discoverer, the Excel File contains a spreadsheet with the data and a once off macro which formats the data so that it appears in Microsoft Excel similar to how it appears in Oracle Discoverer. Due to security concerns, Microsoft Excel allows you to specify the levels of security that you would like to apply.  This is setup in Microsoft Excel in Tools – Macro – Security and looks as follows: Security Level should be set at Medium NOT at Low, High or Very High

Again when we opened we see the following:

As we know the Macro in the Excel file is legitimate we can press the Enable Macros button. The Microsoft Excel Spreadsheet will open.  If you watch closely what you will see is the data opening up in the first instances and then the formatting being applied to the data so that you are presented with the final spreadsheet as on the next page.  The application of the formatting was the macro in operation.

Useful Tip: If you have several reports you wish to export to Microsoft Excel it’s a good idea to open them up one at a time, save the formatted spreadsheet and close the file and Microsoft Excel BEFORE opening up the next file.

You can see all of the formatting is in place. Save your Microsoft Excel File with the formatting applied and the Macro will be removed so you won’t get the Macro Warning again.

Useful Tip:  You can do a Mail Merge in Microsoft Word with Microsoft Excel File as a data source.  You can merge to produce personalised emails or personalised letters.  However you should delete any rows BEFORE the column headers if your spreadsheet is to work as a data source e.g. in the above example delete rows 1-4

    

• Disclaimer
• Privacy
• Copyright
• Accessibility