Discoverer Plus - Queries - Creating a Query

...

Distribution

ODS Specialist

ODS Specialist-Limited

ODS Specialist-Restricted

Ownership

The ISDS System Usability Director is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Discoverer Plus
Enter connection details > Workbook Wizard > Next
Workbook Wizard Step 1of 5

When you begin to create a query, the Workbook Wizard guides you through the process. You will progress through Workbook Wizard Steps 1 – 5 to create your query.

It is very important that you use the [Next] button to go to each window in the Wizard.

Ddo not select [Finish] at least through Step 3.

This screen provides choices for the display in the worksheet. You can leave the defaults as selected, or make changes as you wish. You can always change your mind later; however, you will not see this initial Step 1 screen in this particular format once you have finished your query.

1. Select [Title]. This is the default.

2. Select [Table]

Table output has columns of information with headings. Each column is also called a column item.
Crosstab: Crosstab output looks like a bus or train timetable, with values running across the top, down the left edge, and in the intersection of rows and columns. These values are usually summarized to show a total or average. A crosstab is sometimes called a matrix report.

3. Select [Page-Items].

Page Items can be used with both table and crosstab reports. Page items allow you to subdivide a large amount of data into more manageable chunks. The values of the page item appear outside the main body of the layout. We recommend you keep Page Items checked; even if you don’t use it initially, it makes it easy to rearrange the report later.

4. Uncheck [Graph].

By default, Graph is checked. We suggest you uncheck this box. If you change your mind later, you can always view the graph; this is not your only chance to do so.

5. Select [Text]. This is the default.

This is handy for documenting changes you make as you develop your query. You can turn this feature on and off at any time, so it is always available to you.

Workbook Wizard – Step 2

This is what you see when you begin to create a query.

The highest level of information is the business area. It is represented by the icon that looks like a filing cabinet. In the screenshot above the business area is AP_Payables.

6. Select the Business Area that contains the data you need.

The example below shows the drop-down list of business areas.

Within each business area are folders.

7. Select the plus sign beside the folder that contains the information that interests you.

Within the folders are items that you will select to create your query. (The items become the column headings in your query).

Selecting Items for Inclusion

8. Select the item(s) you want to include in your query.

To open a folder, double click on the folder name or click on the + sign to the left of the folder to view the items in that folder.

Creating Conditions

After you have selected the items to be included in your query, the next step is to create at least one or more conditions that will limit the data to your specific needs. If you do not create at least one condition, you will either receive much more data than you wanted, or you will have created a report that runs forever and ever.

Before you select an item, click on the + sign to the left of the item to view choices. Some items contain defined Lists of Values (LOV)- such as the example below where the individual Trainer names are choices.

9. Select the actual Trainer’s name.

Selecting the specific name is an easy way to create a condition if a List of Values has been defined.

10. Either Drag the item to the Selected panel or click the Right arrow in the central panel to add the item.

The screen shot shown below shows

Selected Items and their folders

[New] button allows you to create Conditions, Calculations, Percentages and Totals.

Conditions –limit your data retrieval. Be sure to create a condition (or conditions) that will limit the data to your specific requirements. In the example above the conditions set are the trainers’ names.
we do not recommend that you create calculations, Percentages and Totals here – rather create them after you have created and run the query

11. Click [Next].

The Workbook Wizard – Step 3 of 5: Table Layout appears.

Workbook Wizard – Step 3 of 5

Layout

You can move items from the top Heading axis to the Page axis (and reverse).

You can change the order of the columns using the drag and drop technique.

You can also select [Show Page Item] and [Hide Duplicate Rows]

12. Click and drag the column heading to the position you want.

Look for the bold vertical line before releasing the item.

Properties:

13. Select [Properties]

14. Select [Table Format].

15. Select [Show column headings].

16. Select [Show row Numbers].

There is no way to count the number of rows returned, so this is a way that you can scroll down to view the number of rows returned in your query.

17. Select [Show vertical gridlines] and [Show horizontal gridlines].

Properties can be edited after you run your query by clicking [Edit/Worksheet Properties]

Workbook Wizard – Step 4 of 5

Sort

You have the opportunity to create sorts while you are creating your query. However, it is usually preferred that sorts be created after the results have been viewed, because it “Adds Overhead.” - sorting slows down the query and uses memory.

Workbook Wizard – Step 5 or 5

Parameters

This screen is available for creating parameters, but we recommend that you do not create parameters until you have run your query and viewed the results.

Click the [Finish] button to run the query. The resulting output is displayed in the results window.

Effective: 06/29/10

Revision: 1

Oracle Tutor can be licensed to modify and add to this content, documenting all of your business process documentation in the Oracle Tutor format.