Return to IS Home Page.
 
...      ...  ...

 

 

Creating Queries

Ownership

The Director of the Business Support Center is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Section Objectives

At the end of this section, you should be able to:

Creating a Query and Viewing Output

WIZARD

RESULTS WINDOW

Choose output type

Select business area

Select folders

Add & Remove Items

Change page axis

Remove items

Move and resize items

Creating and refining an ad hoc query is a cyclic process. The tight integration between the wizard and the results window makes the process of creating a query easy. You use the wizard and the results window to:

Report Templates

Using Discoverer, you can create a number of different styles of output. When you run a query, the way you define your output style determines how the results are displayed.

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.

Page-Detail: A page-detail layout is a table or crosstab report produced for each value of the page item, such as a page per quarter. The values of the page item appear outside the main body of the layout. In the example, Award is the page item, and each Award Number (using the List of Values drop down box) produces a different page. A page-detail layout is sometimes called a master-detail report.

Selecting a Report Template

When you launch Discoverer, the wizard asks what you want to do: create a new workbook (query) or open an existing workbook. If you select to create a new workbook, the wizard asks how you want to display the results. At this point, you select one of the report templates described earlier. This is the first of six steps in creating a workbook. Click Next to advance to the next step in the wizard.

Including Items in a Query

The items you select for inclusion in your query are contained in folders, and folders are contained in business areas. To select the query items, navigate the following hierarchical relationship:

Selecting Items

The wizard contains an item selector—a graphical user interface tool—that provides an easy-to-use method for managing objects such as business areas and folders. The wizard uses the following symbols:

Displaying Children of an Item

To display the children of an item, click the plus icon or double-click the item name. To hide the children of an item, click the minus sign or double-click the item name.

Selecting an Object for Inclusion

You can select objects at any level for inclusion in a query. There are two methods available to move an object from the Available list to the Selected list:

You can also use these methods to move an object from the Selected list back to the Available list.

Customizing the Viewing Output

Customizing the Worksheet Layout

Once you have selected the items to be included in a query, you can order the column and row axis items or enable or disable the page-detail layout.

Ordering Items: You can order top and side axis items by dragging the items along the axis to the desired location.

You can click once on the top axis item, side axis item, or page axis item when the layout is a table or page-detail table to move the item to the desired location.

If you choose a crosstab or page-detail crosstab layout, you double-click the top axis item, side axis item, or page axis item to change the location. When you double-click an item, a dialog box appears alerting you that the item is going to change to a different axis and asking you for confirmation.

Converting a Table Layout to a Crosstab Layout:  You can convert a table layout to a crosstab layout, by selecting Sheet —> Duplicate as Crosstab.

Converting to a Page-Detail Layout: To convert a table or crosstab layout to a page-detail layout, follow these steps:

1. Select the Show Page Items check box.  The Page Items axis is displayed above the selected items.

2. Drag side and top axis items into the Page Items field.

You can also convert a table or crosstab layout to a page-detail layout by selecting View ® Page Items.

Removing the Page Items Axis: To convert a page-detail layout to a table layout, follow these steps:

1. Drag all items in the Page Items filed into the Row item field or the Column item field.

2. Clear the Show Page Items check box.  The Page Items axis is removed.

Hide Duplicate Rows : Use this feature when the query retrieves duplicated rows. The duplicated rows cannot be seen in the results window.

The Results Window

Click the Finish button in the wizard to run the query. The resulting output is displayed in the results window. After viewing the results, you may want to customize or save the output. The end-user component contains many tools and features for customizing and administering your output.

Menu Bar: The menu bar appears across the top of the screen. The options available depend on which action you are currently performing.

Toolbars: Below the menu bar are rows of icons and drop-down lists. As with the menu bar, the icons are active or inactive depending on the action you are currently performing. The icons in the top row match options available in the menu bar and provide faster access to them. The second bar is called the analysis bar, and the third is called the formatting bar. At the bottom of the window is the status bar. You can hide any of these toolbars by selecting its name from the View menu.

Worksheet: The results of the query are displayed in this region.

Customizing the Layout in the Results Window

The following wizard features are available in the results window.

Removing an Item: Select the item you want to remove and then select
Edit—>Delete Item. When an item is deleted, it is removed from the worksheet only. It is not removed from the local cache.

In step 2 of the wizard, you select the item you want to remove from the Selected list, and you drag the item to the Available list.

Adding an Item: You use the wizard to add items to a query.

In step 2 of the wizard, you select the item you want to add from the Available list, and you drag the item to the Selected list.

Summary

In this lesson, you should have learned how to: