This chapter explains how to use the various items on the screen while you're working with Discoverer Desktop.
The topics covered in this chapter include:
Tool Bar - Buttons help you use Discoverer Desktop's features quickly, for example, opening a new workbook, editing a worksheet, drilling data, or sorting data.
Clicking a button on these bars runs that button's function. The functions controlled by these buttons are also available from the menus. Note that button availability depends on the type of selected item. For example, if you select a text item, the Average button is not available because it doesn't make sense to find the average of text.
Also notice that when you put the cursor on a button, it enlarges and displays a small tool tip that tells you what the button is for. Text on the status bar shows a slightly expanded version of the explanation.
Typical axis items are Sales Regions, Cities, Year, Company Department, Product Name, Salesperson Name, and so on. Sales Region, for example, usually has only a few values associated with it: North, South, Central, West.
Axis items represent data that you can pivot on a crosstab worksheet or that can be column headings on a table. Another way of thinking about axis items is that they are the items that would appear on the axes of a graph. When creating a new worksheet, you identify the data that become axis items.
Data Points - The data points of a table or crosstab are the data in the “body” of the worksheet. Data points are the data that you want to use for analysis purposes or to see listed on a table. On a crosstab worksheet, the data is almost always numerical, such as monthly profits or sales amounts by product. Table worksheets, on the other hand, are better for listing text and numeric information. Mailing lists of customers or product part number lists are typical table worksheets.
Worksheet Tabs - Click to open or view the various worksheets in the workbook. If you've already opened the worksheet, it appears right away. If you haven't opened the worksheet yet, Discoverer Desktop gets it from the business area and then displays it. You can rename these tabs.
Table and Page-Detail Table
Crosstab and Page-Detail Crosstab
The most familiar layout for data, a table, lists data in rows and columns. Typical data for tables includes lists, such as a mailing list of customers sorted by zip code or customer name, lists of income or profit from various departments, lists of products sorted by part number or part name, and so on.
Here is a sample of a table layout on the workbook window. As you can see, it is essentially a listing of data.
A table layout with page details is a table with multiple pages of data, where each page shows various portions of the data in detail. You set the criteria for displaying portions of data in order to see exactly what you want on each page. Usually you use this type of layout to study data details in a specific, recurring way. For example, in the Video Store data you may always want to see monthly profits per region. In that case, each page would show one region's profits each month.
A crosstab, short for cross-tabulation, relates two different sets of data and summarizes their interrelationship in terms of a third set of data. For example, a typical crosstab for a chain of retail business stores might show the monthly total sales by products by store. In other words, there are three sets of original data: each store name, product type, and total sales amounts. The store name and product type are axes of the crosstab as rows and columns. Each row and column intersection shows the data points, in this case the total sales for a store and product type.
Every crosstab has at least three dimensions of data—rows, columns, and data points. But in Discoverer Desktop, crosstabs can show the interrelationships between many dimensions of data on the various axes. A crosstab layout has three axes: side axis, top axis and page axis. Because each axis can hold several data items, a crosstab can display many dimensions of data.
For example, the following figure is a sample crosstab that shows five dimensions of data: regions and city names, year, department (video rentals and video sales) and the total profit. In this example, the data points (that is, the intersections, or cells on the crosstab) are the sums for total profit. The next page of data would show the same type of data, except for the next department, which is Video Sale.
A page detail crosstab layout is a crosstab with multiple pages of data, so you can group the data on separate pages. You set the criteria for displaying portions of data in order to see exactly what you want on each page, (see Figure 2-5).
One of the most powerful features of crosstabs is that they can uncover subtleties in the data that are not readily apparent from a table of data or from the raw data itself. By relating one group of data to another, for example, you might find that your best salesperson in terms of volume isn't the best in terms of efficiency (sales calls versus deals closed). Or that the return on investment at one store may be higher in a percentage sense than at another store that posts a higher profit.
A word of caution—used incorrectly, crosstabs can show relationships between two sets of numbers when, in fact, there is no meaningful correlation between them at all. For example, it is a well-known observation that the rise and fall of stock prices on the New York Stock Exchange seems to correlate with the length of women's hemlines—miniskirts during booms, maxiskirts during recessions. The correlation seems to exist, but no one knows why. The same can be true with crosstab numbers; a relationship seems to exist but it may not be valid. The point is simply this: As with the women's skirts, you have to know what's being covered before the crosstab relationships make sense.
By selecting sections on a worksheet you can assign various features to the sections. For example, to boldface the data in a column, you select any cell that column first.
Note:Selecting on a worksheet is similar to selecting on a spreadsheet. If you are familiar with Microsoft Excel or a similar worksheet application, the steps described in this section will also be familiar.
To select a row, click the row number or row marker (Figure 2-8). You can also click the cells at the beginning or the end of the row and drag the pointer across the rest of the cells in the row.
To select a column, click the column heading on a table (Figure 2-9) or the column marker on a crosstab (Figure 2-10). You can also click on the cells at the top or bottom of the row and drag the pointer down over the rest of the cells in the column.
Highlighting indicates all the cells in the column are selected. As in a selected row, the first selected cell on the table or crosstab is outlined instead of highlighted.
On a table, click the box at the top of the row numbers (Figure 2-11).
On a crosstab, click the small box at the top corner of the rows and columns (Figure 2-12).
To select all the items at one level on an axis, click an axis item marker. These markers are either horizontal or vertical depending on the axis where the item is located. See Section 2.3.1, "About the Axis Item Markers" for more about item markers.
An axis item marker pertains to one level of data on the axis. For example, Region is at a higher level than Cities (because the Regions contain the Cities). When you click an axis item marker it selects all the items at that level.
Tables and crosstabs can have several axis item markers. Items on the side axis have their axis markers at the top of the axis. Items on the top axis or page axis have their markers to the left of the item. The number of markers on a crosstab indicates how many levels of items in the axes (which is another way of saying how many “dimensions” of data the crosstabs displays).
Note:The axis markers, row markers, column markers, and other similar elements on the screen are only for your work on the screen. Those elements do not appear when you print a table or crosstab report. Use the Print Preview command to see what a table or crosstab will look like when printed.
When you put the pointer on an axis item, it changes to an arrow, indicating the marker is selected. On a crosstab, a small note shows you the name of the data item for the selected marker. Typically you use an axis marker to pivot data from one axis to another. See Section 4.1, "Pivoting Data" for details about pivoting.
When the page axis contains data, the table or crosstab displays one page of data at a time. A page displays all of the data for a particular data item, such as the profits from Video Rentals for 1996.
To see another page of data on a table or crosstab:
Click the down arrow next to the data you want to see.
Select the page that you want to see next from the drop-down menu.
In the following figure, the page axis has two data items—Department and Calendar Year. The Calendar Year data covers 1998, 1999, and 2000; the Department data has two departments—Video Sale and Video Rental. Therefore, in combination, the crosstab has a total of six pages of data:
1. 1998 Video Sale 2. 1999 Video Sale 3. 2000 Video Sale 4. 1998 Video Rental 5. 1999 Video Rental 6. 2000 Video Rental
Part of a Database Administrator's responsibility when designing workbooks is to format each worksheet. Text fonts, background colors, column names, and so forth are all part of the default format set up by the Database Administrator. However, you can reformat a worksheet. The following sections describe how to do this.
On a table you can move columns from left to right. You can also move columns from the body of a table to the page axis. This is known as pivoting (see Section 4.1, "Pivoting Data" for more information).
To move a table column to the left or right:
Select the column heading of the column to move.
Drag the column in the direction you want to move it.
Release the mouse button when you reach the new position.
Hint: To make the selected column the first or last one in the table, drag the pointer to the leftmost or rightmost column. See Figure 2-20 for an example.
Dragging the column edges is relatively imprecise, but is quick and usually sufficient for working on the screen. If you want to resize columns precisely (for example, for printing purposes or to create a report), use the Format menu.
To resize a column by dragging its edge:
Put the pointer on the right-hand edge of the column header. The pointer changes to a resize pointer.
Hold down the mouse button and drag to the right or left.
When decreasing the size of a column that contains text data, click the Text Wrap button. This ensures that text too long to fit on one line in the new column width, will be displayed with multiple lines so all text remains visible. See Section 220.127.116.11, "Reformatting a Column Heading on a Table" for the steps to make a column wrap its text.
To resize columns with the Format menu:
Select the column(s) to resize.
The Column Width dialog box appears.
Enter a number for the width of the column. The number sets the width of the column in characters.
Hint: You can also resize selected columns using the Format | Columns | Auto Size command. The columns automatically resize to fit their data and column headings. Double-clicking on the right edge of a column header also auto-sizes the column.
The steps to reformat column headings are slightly different for tables and crosstabs as explained in the following sections.
You use the Item Properties dialog box to edit the text of column headings.
To edit column header text:
Select the column header, page item, or item handle.
Choose Edit | Item Properties.
To use the right mouse button pop up menu, double-click on a column header or click on the column header, page item, or item handle in a crosstab. Then choose Item Properties from the menu.
The Item Properties dialog box appears.
Enter a new heading and click OK.
To reformat column headings on a table:
Select the column heading. To change the formats of several columns at the same time, Shift-click or Ctrl-click to select them.
The Format Heading dialog box appears.
Click the tabs across the top of the dialog box and select the options for reformatting the heading(s).
Note:The tabs you see in this dialog box may differ, depending on the type of data in the column. For example, a column for text will have a tab named Text, while a column for dates will have a tab named Date so you can select specific formatting for the specific data type.
Font—Select the font, style, size, and color for the column heading text. In the example above, the text is Arial, Bold, 11 point and yellow. Click a color on the color palette to set the text to that color.
Alignment—Select the options to align the text in the column heading in both the horizontal and vertical directions. The example above shows text that is both left and top aligned. The horizontal alignment option Wrap Text means a column heading automatically extends to the next line, in case the text is too long to fit on a single line.
Click Apply to apply the new formats so you can see how they look.
When formatting a column heading on a crosstab, all of the headings at the same level will also be reformatted because a column heading represents the same data. For example, Year:1995 and Year:1996 both represent the data item, Year. You cannot reformat a single column heading on a crosstab if there are other columns at the same level.
To reformat a column heading on a crosstab:
Select a column heading. All column headings at that level receive the formatting.
Choose Format | Headings and now format the headings the same way you do for a table (see Section 18.104.22.168, "Reformatting a Column Heading on a Table").
Note: Unlike tables, you can use the formatting buttons on the formatting bar to reformat column heading on crosstabs. For example, to change the format of the Year columns on the sample crosstab, select either Year column, click a formatting button, and the both columns are reformatted. Figure 2-1 shows the formatting bar.
When reformatting data in a table or crosstab, you can use the workbook menu or the formatting buttons on the formatting bar. Figure 2-1 shows the formatting bar.
Select a column heading to reformat the data in that column. You can also Shift-click to reformat the data in multiple columns.
Select a column heading or any cell in the column. You can also Shift-click to reformat the data in multiple columns.
To reformat all of the data in a crosstab at the same time, select the entire crosstab. Page items will not be formatted unless you select them.
Choose Format | Data and select options from the Format Data dialog box.
To add graphic bars to numbers:
Select a column heading with numeric data, or any cell in the column.
A special editing feature lets you insert text codes that automatically add certain types of data to the title. These text codes are particularly helpful when printing a worksheet. For more information, see Section 22.214.171.124, "Adding Text Variables to Titles".
Discoverer Desktop also has a special function so you can put a bitmap image in the title. For example, your company logo as the background for the worksheet title is useful when printing copies of worksheets for external distribution. For more information, see Section 126.96.36.199, "Adding Bitmaps to Titles".
To reformat a worksheet title:
The Edit Title dialog box appears:
Select the text options such as the font, size, and alignment to format the title.
Note: As you select options, the sample in the dialog box changes to show the effects of your choices.
Click OK when the formatting is complete.
You can also edit formats of a title by choosing Format | Sheet | Title and selecting options from the Format Title dialog box. Those options are the same as shown in Figure 2-23.
Click in the title where you want to add text, or select a portion of the title to change it.
Figure 2-28 shows a section of text that has been selected:
Type the new text.
Text Codes contain Workbook information such as the current date and time, the name of the workbook where the worksheet is located, or the number of pages. Once inserted, they are automatically updated when Workbook details change.
To add text code to the title of the worksheet:
Click in the title where you want to add a text variable.
To add it on the next line under the title, put the pointer at the end of the title and press Return.
Click the Insert button.
The Insert menu shows the text codes that you can insert in the title:
Select the text code from the menu.
Figure 2-30 shows a text code example.
To add a bitmap to the title:
The Edit Title dialog box appears:
Click the Browse button.
An Open dialog box appears.
Find the bitmap file that you want to use and click Open.
The bitmap appears with the title. For example, in Figure 2-32, the file Disco.bmp has been defined as the bitmap graphic.
When the bitmap is properly displayed in the title, click OK. The bitmap is added to the title on the worksheet.
Note:Bitmaps are saved in the database along with the Workbook. If Discoverer Desktop cannot find the original bitmap file, it uses the bitmap stored in the database.
A worksheet's name appears in the worksheet tabs at the lower left of the Workbook windows. See Figure 2-1, "The Workbook Window" as an example.
To rename a worksheet:
Clicking the right mouse button when the pointer is on various features of the window produces a pop-up menu with context-sensitive commands specific to that feature. This shortcut helps you choose commands quickly without having to use the menus.
Figure 2-33 shows samples of right-click menus. There are many others for the other features.
To resize a window:
Put the pointer on an edge of the window.
The pointer becomes a horizontal or vertical arrow. In Figure 2-34 below, the pointer is horizontal arrow, used to change the width of the dialog box.
Drag the pointer to adjust the width of the dialog box.
When the pointer is on the side edge, drag it to the left or right. When the pointer is on the top or bottom edge, drag it up or down.
The window will be the selected size from now on.