5
Formatting worksheet data

This section explains how to format worksheets, and contains the following topics:

• "About the four types of Discoverer worksheet"

• "About table worksheets"

• "About table worksheets with page detail"

• "About crosstab worksheets"

• "About crosstab worksheets with page detail"

• "About editing worksheets"

• "How to edit worksheets"

• "How to add items to worksheets"

• "How to remove items from worksheets"

• "How to duplicate a worksheet"

• "How to change the format of worksheet data"

• "How to change item headings"

• "How to change the format of item headings"

• "How to change the default worksheet format"

About the four types of Discoverer worksheet

You can display worksheet data in four different styles:

• table - see "About table worksheets"

• table with page detail - see "About table worksheets with page detail"

• crosstab - see "About crosstab worksheets"

• crosstab with page detail - see "About crosstab worksheets with page detail"

About table worksheets

A table worksheet lists data in rows and columns , and is probably the most familiar layout for data. The figure below shows an example table worksheet.

Figure 5-1 A table worksheet

Text description of the illustration ty_tab.gif

The table worksheet shows a list of profit values for departments in cities within the Central region.

About table worksheets with page detail

A table worksheet with page detail is a table worksheet with an additional axis for displaying data for one item value at a time. For example, one year at a time.

The figure below shows an example table worksheet with page detail, where Year is displayed in the Page Items area. The data displayed relates to the year 1998.

Figure 5-2 A table worksheet with page detail

Text description of the illustration ty_tabpd.gif

The worksheet shows the Year item in the Page Items area, which enables you to display data for one year at a time.

About crosstab worksheets

A crosstab worksheet (short for cross-tabulated worksheet) relates two different sets of data and summarizes their interrelationship in terms of a third set of data (see figure below).

Figure 5-3 A crosstab worksheet

Text description of the illustration ty_ct.gif

Key to figure:

1. top axis, containing the Year item

2. left axis, containing the Region item

3. data points, containing profit sum figures

The region and year are displayed as rows and columns on the crosstab. Each row and column intersection shows a data point, which in this case is the profit for a particular region in a particular year.

A note about crosstabs

One of the most powerful features of crosstabs is that you can use them to uncover subtleties in the data that are not readily apparent from a table of data. For example, by relating one group of data to another you might find that:

• the most efficient sales person (i.e. sales calls versus deals closed) for 'Widgets' has the highest volume of 'Gadgets' sales

• the return on investment on a store for the year 2001 might also have provided a good return in the year 2000

About crosstab worksheets with page detail

A crosstab worksheet with page detail is a crosstab worksheet with an additional axis for displaying data for one item value at a time. For example, one department at a time.

The figure below shows an example table worksheet with page detail, where Department is displayed in the Page Items area. The data displayed relates to the Video Sale department.

Figure 5-4 A crosstab worksheet with page detail

Text description of the illustration ct.gif

Key to figure:

1. Page axis, containing the Department item.

2. Top axis, containing the Year item

3. Left axis, containing the Region item.

4. Data points, containing profit figures.

About editing worksheets

Discoverer's powerful layout dialogs enable you to format worksheets so that they are arranged and formatted exactly how you want them. For example, you might want to:

• change the color of rows and columns

• change the background color or title of worksheets

• change the format of text and numbers

• change how data can be filtered by other Discoverer users using parameters

• display new data on the worksheet

• remove data from the worksheet

• rearrange items on the worksheet - for more information, see "Pivoting data"

Note: When you create a new report, a default format is applied (e.g. colors, fonts). To change the default format that is applied to worksheets, see "How to change the default worksheet format".

Hint: If you want to change a worksheet but keep a copy of the original worksheet, use the duplicate worksheet facility. Here, you make an exact copy of a worksheet that you can work on (for more information, see "How to duplicate a worksheet"). Alternatively, save the whole workbook under a different name and work with this copy (for more information, see "How to save workbooks").

How to edit worksheets

You edit a worksheet to change the way that the worksheet looks or behaves. For example, you might want to change the color of worksheet data, or to add calculations, percentages, or totals.

To edit a worksheet:

1. Open the worksheet that you want to edit.

2. Choose Sheet | Edit Sheet to display the "Edit Worksheet dialog".

   
   
   Text description of the illustration ed1.gif

The tabs on the Edit Worksheet dialog are used to edit the properties of the current worksheet (e.g. table layout, format, conditions).

Note: The layout tab is labelled Table Layout on table worksheets, and Crosstab Layout on crosstab worksheets.

3. Make changes to the worksheet as required.

   Hint: Click the torch icon ( ) above the Available box to display the "Find dialog (in Item Navigator)", where you search the EUL for items that you want to add to the worksheet.

4. Click OK to save changes and return to the worksheet.

   Discoverer updates the worksheet as you specified.

How to add items to worksheets

You add items to a worksheet when you want to analyze new areas of data. For example, you might want to add a Year item so that you can look at trends over time.

To add an item to a worksheet:

1. Open the worksheet that you want to edit.

2. Choose Sheet | Edit Sheet to display the "Edit Worksheet dialog".

3. Display the "Edit Worksheet dialog: Select Items tab".

   Hint: Click the plus (+) sign next to folders and items to see items within them.

4. Move items that you want to add to worksheets from the Available list to the Selected list (for more information about selecting items, see Notes below).

   Hint: Click the torch icon ( ) above the Available box to display the "Find dialog (in Item Navigator)", where you search the EUL for items that you want to add to the worksheet.

5. Click OK to save the changes you have made and close the dialog.

   Discoverer updates the worksheet as you specified.

Notes:

• You can select more than one item by pressing the Ctrl key and clicking another item.

• To move items from the Available list to the Selected list, do one of the following:
  • use the right arrow button to move selected items to the Selected list

  • use the cursor to drag and drop selected items from the Available list to the Selected list

• From the "Edit Worksheet dialog: Select Items tab", you can also specify sheet format settings and query governor settings. Click Options to display the "Options dialog: Sheet Format tabs" and "Options dialog: Query Governor tab".

How to remove items from worksheets

You remove an item from a worksheet when you no longer need to analyze the item. For example, you might want to remove a year item to change the format of a printed report.

To remove items from worksheets:

1. Open the worksheet that you want to edit.

2. Choose Sheet | Edit Sheet to display the "Edit Worksheet dialog".

3. Display the "Edit Worksheet dialog: Select Items tab".

4. To remove items from worksheets, move items from the Selected list to the Available list.

5. Click OK to save changes and close the dialog.

Notes:

• You can select more than one item by pressing the Ctrl key and clicking another item.

• You can also delete items from a worksheet using the "Edit Worksheet dialog: Table Layout tab" or the "Edit Worksheet dialog: Crosstab layout tab".

How to duplicate a worksheet

You duplicate a worksheet when you want to quickly create a copy of a worksheet based on the style of an existing worksheet. You can then edit the new worksheet as required.

Duplicating a worksheet also enables you to work on a copy of a worksheet, leaving the original worksheet intact. For example, you might want to work on a temporary worksheet that you discard later.

To duplicate a worksheet:

1. Open the worksheet that you want to duplicate.

2. Choose one of the following options, depending on whether you want the new worksheet to be a table worksheet or a crosstab worksheet:
   • choose Sheet | Duplicate as a Table to duplicate the current worksheet using the Duplicate as Table dialog

   • choose Sheet | Duplicate as a Crosstab to duplicate the current worksheet using the Duplicate as Crosstab dialog

   Discoverer displays the Duplicate as Table dialog or Duplicate as Crosstab dialog, which enables you to change the default layout of the new worksheet.

3. (optional) Use the tabs on the Duplicate as Table dialog or Duplicate as Crosstab dialog to change the default settings for items on the duplicated worksheet.

   For example, you might use the Select Items tab to add items to the duplicated worksheet, or you might use the Format tab to change the default display style of worksheet items.

4. Click OK to save the new worksheet using default worksheet settings and close the dialog.

   The new worksheet is now displayed ready for you to analyze. You might want to change the default name of the new worksheet (for more information, see "How to rename workbooks and worksheets").

   Hint: Use the tabs at the bottom of worksheets to navigate between worksheets.

How to change the format of worksheet data

You change the format of worksheet data when you want to change how existing worksheet data is displayed. For example, you might want to:

• change a numeric value to a currency format

• modify the number of decimal places displayed

• change the background color of headings and totals.

Hint: You can also change the item heading (for more information, see "How to change item headings") and change the format of item headings (for more information, see "How to change the format of item headings").

To change the format of worksheet data:

1. Open the worksheet that you want to edit.

2. Choose Sheet | Format to display the "Edit Worksheet dialog: Format tab".

   
   
   Text description of the illustration ed5.gif

   The "Edit Worksheet dialog: Format tab" displays a list of items currently displayed on the worksheet.

3. In the list box on the left, select the items that you want to format.

   You can select more than item by pressing the Ctrl key and clicking another item.

   Hint: The Example box displays the heading format of the current item.

4. Click Format Data to display the "Format Data dialog: Format tab".

   For example, you might want to change the font size, color, and alignment of numbers.

5. Use the "Format Data dialog: Format tab" to specify:
   • the font, point size, and font style of data

   • the color of text

   • the horizontal and vertical alignment of text

   • whether to wrap words in each cell

   Hint: The Example field shows the effect of the changes that you make.

   
   
   Text description of the illustration ed5a.gif

6. Depending on the type of data in the item you are formatting, other tabs appear on the "Format Data dialog", which you use as follows:
   • use the "Format Data dialog: Number tab" to format numbers (e.g. add or remove decimal places, show or hide a currency symbol for your country, create a custom number format).

   • use the "Format Data dialog: Text tab" to format text (e.g. to change the text style to UPPERCASE, lowercase, or Capitalized).

   • use the "Format Data dialog: Date tab" to format dates (e.g. to change the date format for a Year item from YY to YYYY).

   Hint: On each tab, the Example field shows the affect of the changes on your items.

7. When you have finished formatting the worksheet data, click OK to save changes and return to the worksheet.

   Discoverer updates the worksheet as you specified.

How to change item headings

You change item headings when you want to change the column or row headings that are displayed on worksheets. For example, you might want to change 'Year' to 'Financial Year' so that 'Financial Year' is displayed on reports.

To change an item heading:

1. Open the worksheet that you want to edit.

2. Choose Sheet | Format to display the "Edit Worksheet dialog: Format tab".

3. In the list box on the left, select the item for which you want to change the heading.

   Hint: The Example box displays the heading format of the current item.

4. Click Edit Heading to display the "Edit Heading dialog".

   
   
   Text description of the illustration edhd.gif

5. Use the Heading field to enter a new heading name or edit the existing heading name.

6. Click OK to save changes and close the dialog.

7. Click OK to close the "Edit Worksheet dialog: Format tab" and return to the worksheet.

   Discoverer updates the worksheet as you specified.

How to change the format of item headings

You change the format of item headings when you want to display different row or column headings on a worksheet. For example, you might want to change a column heading to use a larger font or different color.

To change the format of an item heading:

1. Open the worksheet that you want to edit.

2. Choose Sheet | Format... to display the "Edit Worksheet dialog: Format tab".

   
   
   Text description of the illustration ed5.gif

3. In the list box on the left, select the items that you want to format.

   You can select more than item by pressing the Ctrl key and clicking another item.

   Hint: The Example box displays the heading format of the current item.

4. Click the Format Heading button to display the "Format heading dialog".
   
   
   Text description of the illustration formhead.gif

5. Use the "Format heading dialog" to specify:
   • the font, point size, and font style of heading text

   • the color of heading text

   • the horizontal and vertical alignment of heading text

   • whether to wrap heading text in each cell

   Hint: The Example field shows the effect of the changes that you make.

6. When you have finished formatting the worksheet headings, click OK to save changes and return to the "Edit Worksheet dialog: Format tab".

7. Click OK to close the "Edit Worksheet dialog: Format tab" and return to the worksheet.

   Discoverer updates the worksheet as you specified.

How to change the default worksheet format

You change the default worksheet format when you want to change how new worksheets are formatted. For example, you might want to display all new worksheet items in blue text with a yellow background.

Note: Changing the default worksheet format does not affect the format of existing worksheet items - only items that you add to the worksheet have the default format.

To change the default worksheet format:

1. Choose Tools | Options to display the Options dialog.

2. Display the "Options dialog: Default Formats tab".

3. Select an item in the Default Formats list.

4. Click Change to display the format dialog for that item.

   For example, the "Data Format dialog (Default format)", the "Heading Format dialog (Default format)", or the "Total Format dialog (Default format)".

5. Use the Format dialogs to specify:
   • the font, point size, and font style of heading text

   • the color of text

   • the horizontal and vertical alignment of text

   • whether to wrap text in each cell

   Hint: The Example field shows the effect of the changes that you make.

6. When you have made your changes, click OK to save changes and return to the "Options dialog: Default Formats tab".

7. Click OK to close the "Options dialog: Default Formats tab" and return to the worksheet.

   The default formats that you specified are used for new worksheets that you create. For example, if you set the default data format style to a blue font with a yellow background, any new items that you add to worksheets will have this format.