7. Building Worksheets and Workbooks
7.1.2 Selecting the
Data
Your company's database
administrator creates worksheets and workbooks based on your requirements for specific data
analysis and reporting. However, while working with Discoverer Desktop, you may
want to create additional worksheets and workbooks.
For example, you may want
to create a special worksheet as a scheduled report that gets printed each
month as part of your business reporting requirements. Or, you may want to
consolidate project-specific information in a separate workbook that you share
with other team members.
If you have the
appropriate access rights (usually granted by the
database administrator), you can use the steps explained in this chapter to
create worksheets and workbooks.
|
Note: You use the same basic process to build workbooks and
worksheets. In fact, to build a new workbook, you create the initial
worksheet for the new workbook. Thus, the steps described in this section are
for both processes—building a new workbook and building a new worksheet.
|
Topics in this chapter
include:
Required Steps
- Select
the type of display for the new worksheet or workbook.
- Select
the data that belongs on the worksheet or in the workbook.
- Arrange
the data on a table or crosstab layout.
- Sort
the data (for tables only).
- Select
conditions to apply to the data.
- Select
calculations to apply to the data.
To start the process to
build a new workbook or worksheet, choose either of the following:
The New Sheet dialog box appears (Figure 7-1).
Note: The sample dialog boxes in the rest of this section are for
creating a new worksheet. The dialog boxes for creating a new workbook are the
same, except the dialog box titles are ÒWorkbook WizardÓ instead of ÒNew Sheet.Ó
The next dialog box is for selecting the data that you want on the
new worksheet.
7.1.2 Selecting the
Data
This dialog box lists the
data in the business area that you can use to build the new worksheet. To
include data on the new worksheet, you move it from the Available column to the
Selected column.
Figure 7-2 Step 2: Select the Data
Business area - the file cabinet
icon displays a business area created by
the database administrator; to select another business area for the new
worksheet, click the drop-down arrow and choose from the list of business
areas.
Find button - click the torch icon to find a specific item or folder by
name in the current business area, or all business areas.
1.
Select the business area from
the drop-down menu at the top of the Available list.
The items may have plus signs next to them too indicating you
can select values for those items as well. For example, the City item contains
the names of the cities in the database. You can select a specific city to add
to the worksheet.
The following figure shows that the Video Analysis folder is
expanded to show its items (such as Department and Region), and the Region item
is expanded to show the list of values that correspond to Region (Central,
East, West) and the ways to aggregate the data (Count, Max, Min).
- Key
to Figure 7-3:
a. Click a + sign to expand the item and see its values.
b. drop-down button.
c. Browse button.
Axis
Item - for example Region, corresponds to a column on a table or a level on a crosstab; axis items remain constant and have relatively few
values, such as the names of Departments in your company, or the names of your
Sales Regions. The values of an axis item are shown as a list of values.
Numeric Item - for example Profit,
represents numeric data; corresponds to the data in the body of a crosstab.
The values of numeric items can change as you
analyze the data, such as summing profits for cities compared to regions.
Numeric items also behave as Axis items on tables.
Aggregations - for example SUM, the mathematical functions to aggregate the data; for text items such as
Region, the typical aggregations are Count, Max, and Min. That is, you can
count the number of text items, or find the highest or lowest (where A might
be the highest and Z the lowest).
For numeric data, the
typical aggregations are Sum, Count, Max, Min, Average, and Detail. For
example, you can find the Sum or Average of the numeric data with the
aggregation. The aggregation in boldface type is the default.
The database administrator sets which aggregation is the default.
Condition - a filter for finding specific data. The database
administrator defines conditions for the folder, not for individual items.
Clicking the Options button
displays a dialog box for selecting options for the new worksheet or workbook.
See Section 8.5, "Setting Options" for
details.
Add specific data to your
worksheet as follows:
-
Figure 7-4 Selecting Items for your Worksheet
Description of "Figure 7-4 Selecting Items for your Worksheet"
Key to Figure 7-4:
a. Click an Item to select the Item.
b. The Add button.
c. The Selected list, containing Items that will be displayed on your Worksheet. - The Add button becomes active.
- Click the Add button to add the item(s) to the new worksheet. You can also drag the selected items from the Available list to the Selected box, (see Figure 7-4).The Selected list shows the data for the new worksheet:You can select data at various levels in the Available list. For example, selecting a folder and moving it to the Selected list, moves all the data within the folder to the Selected list. Similarly, adding an item adds all the values in it to the worksheet. For example, adding the City item adds all the names of the cities.Adding a numeric item automatically adds its default aggregation functions. All values are automatically added too. Adding an axis item does not automatically add aggregate functions.
- At this point, you can click Finish to create a new worksheet or workbook. However, clicking Next shows the next (optional) dialog box for adding other features to the new worksheet or workbook.Note: to change selections on the previous dialog boxes, click the Back button.
- Click Next to see the dialog box for step 3 of the process.
Note:If you add an Aggregated Data Point and a Detail Data Point to the same Worksheet, Discoverer Desktop displays a warning that you may get unexpected results. Click OK to continue.
7.1.3 Arranging the Layout
In this step, you move the items on the worksheet to display them in the proper columns, rows, and page axes. It is the same basic process as on the worksheet itself; that is, drag the item to a new position on the worksheet. See Section 4.1, "Pivoting Data" for a description of moving items to the page axis on a worksheet.
The layout that appears on the dialog box depends on the selection—table or crosstab—in step 1 of the new worksheet/new workbook process. The following figure shows a tabular layout.
Show Page Items—If an item is selected as a Page Item (as Department is in the example above), this option is not available. If no item is selected as a Page Item, the option becomes available. Removing the checkmark hides the Page Item section of the layout. Click the option again to see the Page Item section.
To rearrange the data on the table:
- Select the item to move.
- Drag it to its new position. The following figure shows moving Year to the Page Axis.
Key to Figure 7-6:
a. Drag an item to the Page Items area to create a Page Item. The Show Page Items check box must be selected first. You can also move Items to the left and right to change the display layout.Rearranging items on this layout does not preclude you from rearranging them directly on the worksheet.
7.1.4 Selecting Conditions for the Data
Conditions defined for the items added to the Selected list appear in the next dialog box. You can select the conditions as part of the new worksheet or workbook, or create new conditions.
- Click the boxes in front of the conditions that you want to add to the new worksheet or workbook.In the figure above, the first two conditions are selected.
- To create a new condition, click the New button.
- To edit a condition, select it in the list and click the Edit button.
See Section 5.5, "Finding Data that Meets Conditions" for a description of creating and editing conditions. See Section 8.4, "Creating Advanced Conditions" for a description of creating advanced conditions. - Click Next or click Finish if you're finished selecting conditions for the data items and creating the new worksheet or workbook.
7.1.5 Sorting the Data in a New Worksheet or Workbook
This step only applies to tables. If you are creating a new worksheet or workbook as a crosstab, the sorting dialog box does not appear.
- Click the Add button.The items on the new table appear in the drop-down list.
- Select an item on which you want to sort the table from the pull-down list. The selected is added to the dialog box.
- Set the Direction, Group, Line, and Spaces options as required. See Section 4.3.1, "Sorting Data on Tables" for a complete description of the sorting options.
7.1.6 Selecting Calculations for the Data
Calculations defined for the items added to the Selected list appear in the final dialog box. You can select the calculations as part of the new worksheet or workbook, or create new calculations.
- Click the boxes in front of the conditions for the new worksheet or workbook.In the figure above, all the calculations are selected.
- To create a new calculation click the New button.
- To edit a calculation, select it in the list and click the Edit button.
See Section 8.3, "Creating Calculations" for a complete description of creating and editing calculations.
7.2 Editing a Worksheet
The dialog boxes for editing a worksheet offer the same selections and features that you use to create a worksheet or workbook.
To edit a worksheet:
- Display the worksheet that you want to edit.
The tabs across the top of the dialog box are for editing the various features of the worksheet. Clicking on a tab displays the options for the feature. When the Select Items tab is selected, the items shown in the Selected list are the items currently in use on the worksheet. Items not grayed out in the Available list can be added to the worksheet.
The figure above is for editing a tabular worksheet. A similar dialog box appears for crosstab worksheets, except Table Layout tab becomes Crosstab Layout and the dialog box does not include the Sort tab. To sort crosstab data, choose Tools | Sort.
7.2.1 Adding and Deleting Items on a Worksheet
The first tab on the Edit Sheet dialog box is for adding or deleting items on a worksheet. For example, if the original item on the worksheet is Region, but does not include City names, you can add a column for the cities within the regions.
Note:
Adding a new item to a worksheet adds a column to the table or crosstab, but you can see the same item as part of drilling into data. For example, if you included Cities as part of the original worksheet, drilling down into the Region data displays the cities under the regions. |
To add an item to the current worksheet:
- Click the plus (+) sign next to folders and items to see their contents.
- Select the item in the Available list.
- Click the Add button or drag the item to the Selected list.
To delete an item from the current worksheet:
- Select the item in the Selected list.
- Click the Remove button (the button under the Add button).
7.2.2 Editing the Layout
Use the Layout tab (Figure 7-13) to arrange the items on the worksheet. Drag an item to its new position on the worksheet just as you do on the worksheet itself. ClickHide Duplicate Rows to hide rows that contain the same data.
To remove the Page Items box from the top of the worksheet, drag all items from that box to the report body, then click Shows Page Items to remove the checkmark.
7.2.3 Editing the Conditions
Use the Conditions tab to edit the conditions on the worksheet. Select the conditions for the worksheet. Click to remove the checkmarks next to the conditions that you donot want as part of the worksheet. SeeSection 5.5, "Finding Data that Meets Conditions" for a complete description of conditions.
7.2.4 Editing the Sorting
Use the Sorting tab to change the conditions for sorting on a table. The sort information shows the current sorting applied to the worksheet. See Section 4.3.1, "Sorting Data on Tables" for a complete description of the sorting options for tables.
Note: The Sorting tab is only available for tabular worksheets.
7.2.5 Editing Calculations
Use the Calculation tab (Figure 7-16) to edit the calculations applied to the worksheet. The selected calculations are active for the worksheet. See Section 8.3, "Creating Calculations" for a complete description of the calculations.
No comments:
Post a Comment