10
Items and Item Classes

This chapter consists of the following sections:

• 10.1 Introduction

• 10.2 Editing Item Properties

• 10.4 Creating an Item Class

• 10.5 Editing an Item Class

• 10.6 Adding Items to an Item Class

• 10.7 Viewing the Items that use an Item Class

• 10.8 Removing Items from an Item Class

• 10.9 Viewing a List of Values

• 10.10 Deleting Items & Item Classes

10.1 Introduction

This section describes the concepts of Items and Item Classes.

10.1.1 Items

An Item, is a representation of a database table's column, in the EUL. By presenting columns as Items, Discoverer enables the administrator to make formatting changes, name changes and other similar changes enabling the user to clearly read the data. Items are stored in folders and can be created, deleted, and moved among different folders.

10.1.2 Item Classes

An item class is a group of items that share similar attributes. For example, an item called Product may contain a description of products and be part of the Product folder. A similar item, Product, may also be required in the Sales Revenue folder. For both items to share common attributes, such as a list of values, you create one item class which defines the values, and apply it to both items. Thus, you only have to define the attributes once. Without the item class, it would be necessary to define the attributes individually for each folder.

The administrator creates an item class to enable the following features:

• lists of values

• alternative sorts

• drill-to-detail links (hyperdrills)

These features help users build queries more quickly and easily. An item class lets the administrator define properties for the same item once, and then assign the item class to other items that share similar properties.

There is no specific link between the three features listed above, but they are all implemented using the item class mechanism. The item class can be created to support these features individually, or in combination. The only exception is that an alternative sort must relate to a list of values.

10.1.2.1 Lists of Values

A list of values is the item's set of unique values. The values the item class references correspond to those found in a database column. If the database contained the following items and values:

Table 10-1 Database Items and Values

Item	Value
Widgets	4
Bolts	28
Fan Belts	34
Gaskets	90
Brackets	90

The item class would produce a list of five distinct values: widgets, bolts, fan belts, gaskets, brackets.

Lists of values are used by end users to refer to values in the database and to apply conditions and parameter values.

Lists of values are often generated automatically when the business area is first created (in Load Wizard: Step 4). The Item Class Wizard provides a way to extend a list of values to other items. For information on creating a list of values, see Section 10.4, "Creating an Item Class."

10.1.2.2 Alternative Sorts

Items are normally sorted in ascending or descending order, according to ASCII sort values. However, end-users may require some data elements to be sorted in an alternative order. For example, a series of sales regions would be sorted alphabetically by default, such as East, North, South, and West. But the end user may need them sorted in this order: North, South, East, and West.

To create an alternative sort order, you must link together two items. One item defines the sort order and the other defines the list of values to be sorted. Figure 10-2 shows an alternative sort order: North=1, South=2, East=3 and West=4.

10.1.2.2.1 Alternative methods

An alternative method is to use SQL to create a new table that has two columns defined - the item name and the sort value. You would then need to populate the columns with values. Table 10-2 shows an example.

Table 10-2 Alternate Sort Table

Region	Sort Value
North	1
South	2
East	3
West	4

You can also use SQL*Plus to create a new table defining the alternative sort order. Then, using the Administration Edition, associate a list of values with the alternative sort column from either the alternative sort table or the existing database table--depending on the method you use to create the alternative sort order. The values (Region in Figure 10-2) are then sorted according to the alternative sort column - (Sort Value).

Before creating the alternative sort, keep these rules in mind.

• The item to be assigned the alternative sort order must be contained within the same folder as the item providing the list of values for the item class.

• An item class containing an alternate sort sequence must also contain a list of values.

For information about creating an Alternative Sort Item Class, see Section 10.4, "Creating an Item Class."

10.1.2.3 Drill to Detail

Drill to detail, or hyperdrills, enable users to drill to detail information using the relationships between data, rather than through hierarchical levels. A related item might be an item that was selected from the current source folder but is not currently in the query and not part of an existing item or date hierarchy structure. A drill-to-detail link allows the user to jump directly to related items, without having to drill through hierarchical levels. Although related items can be grouped in a hierarchy, if the Items are in different Folders, there must be Joins between them for the group to be valid.

There may be existing Joins between Folders in a drill-to-detail item class, but those Joins are not necessary for the hyperdrill to work. What is necessary is that the items are the same data type. For information about creating a hyperdrill, see Section 10.4, "Creating an Item Class."

10.2 Editing Item Properties

Item properties are accessible through Item Properties dialog boxes. This section shows you how to enhance the user's view of the data by editing item properties. Figure 10-1 shows an example Item Properties dialog box.

Figure 10-1 Item Properties Dialog Box with the General Tab Selected

10.2.1 Editing the Properties of a Single Item

This section describes how to edit an item's properties.

1. Open the item's Properties dialog box.

   There are four ways to do this:

   • Double-click
     Double-click the item on the Data page.

   • Popup Menu
     Right-click the item on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the item on the Data page and click the Properties tool bar icon ()

   • Menu
     Click the item on the Data page and choose Edit | Properties.

2. Make your changes as required.

   For more information on the fields on this dialog box, click Help.

3. Click OK.

10.2.2 Editing the Properties of Multiple Items

The following steps show you how to set common properties for more than one item at a time:

1. Select all of the items whose properties you want to edit.
   (Ctrl-clicking enables you to select more than one Item.)

2. Display the Item Properties dialog box.

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected Items on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the Properties tool bar icon ()

   • Menu
     Choose Edit | Properties.

   All properties that are common to each of the selected Items are displayed. If the data for a field is not common to each of the selected Items, the field is blank.

3. Make your changes as required.
   Any changes you make here will be applied to all of the selected Items.

   For more information on the fields on this dialog box, click Help.

4. Click OK.

10.3 Item Content Types

In the Item Properties dialog, the Content Type setting defines the Item's data type. The Content Type field has two possible settings, (see also Chapter 4.10.4, "Modifying an Item's Content Type"):

• None - the Item's data is stored in the EUL database, and is displayed by Discoverer.

• <File format> - the Item's data is not stored in the EUL database, but in an external location or format. For example, HTML, text, Microsoft Excel spreadsheet, CLOB, NCLOB, and BFILE format.
  The data is not displayed directly by Discoverer, but by an external application. For example, the Content Type DOC is displayed by Microsoft Word; the Content Type HTML is displayed by your current browser, (see Table 10-3 below).

NOTE: For more information about Oracle Content Types, refer to the section Datatypes in the Oracle 8i SQL Reference, Release 2 (8.1.6), Part Number A76989-01.

Table 10-3 Common Discoverer Content Types

Content Type Setting	Description
.AVI	Display this Item in a Media Player
DOC	Display this Item in MS Word
HTML	Display this Item in a Web Browser
SCM	Display this Item in Lotus Screencam
XLS	Display this Item in a MS Excel
BFILE	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
BLOB	A binary large object. Maximum size is 4 gigabytes.
CLOB	A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.
NCLOB	A character large object containing multibyte characters. Both fixed-width and variable-width character sets are supported, both using the NCHAR database character set. Maximum size is 4 gigabytes. Stores national character set data.

10.4 Creating an Item Class

An item class can define alternative sorts, provide hyperdrills, and / or reference a list of values in the database.

This section describes how to create an Item Class using the Item Class Wizard. It consists of the following topics:

• 10.4.1 Starting the Item Class Wizard

• 10.4.2 Choose the Item Class Attributes

• 10.4.3 Select the Item that Generates the List of Values

• 10.4.4 Select the Item that Contains the Alternative Sort Sequence

• 10.4.5 Select the Items that Use this Item Class

You may not need to follow every step. The choices you make in the wizard determine the exact path you need to take.

10.4.1 Starting the Item Class Wizard

1. Click the Item Classes tab in the work area.

2. Open the Item Class Wizard (see Figure 10-2).

   There are three ways of doing this:

   • Popup Menu
     Right-click anywhere on the Item Classes page and choose New Item Class... on the popup menu.

   • Toolbar Icon
     Click the New Item Class tool bar icon ().

   • Menu
     Choose Insert | Item Class....

Figure 10-2 The Item Class Wizard

10.4.2 Choose the Item Class Attributes

This page of the wizard enables you to specify the attributes for your new Item Class.

For more information about these attributes, see Section 10.1.2, "Item Classes.".

1. Decide which attributes are needed by your new Item Class.

   Note, selecting Alternative sort automatically selects List of values.

2. Click Next.

   This opens the next page of the Item Class Wizard.

3. The next step depends on your selections on the first page of the wizard:
   • If you ticked either List of values or Alternative sort, go to Section 10.4.3, "Select the Item that Generates the List of Values."

   • If you didn't tick either of these check boxes, go to Section 10.4.5, "Select the Items that Use this Item Class."

10.4.3 Select the Item that Generates the List of Values

This page of the wizard (see Figure 10-3) enables you to select the item that contains the list of values you want for your new Item Class.

Figure 10-3 Selecting the Item that Generates the List of Values

1. Select the Business Area, that contains the item that you want to use to generate the list of values.

2. Select the item that you want to use to generate the list of values.

   Discoverer uses a SELECT DISTINCT query to retrieve a List of Values. If you select an item in a folder with a large number of rows compared to the number of distinct values, then the query can be inefficient. It is much better to select an item from a small "dimension" table attached to the FACT table rather than using the FACT table itself. If such a table does not exist, it might be worth creating the table to speed up the List of Values process.

Create Custom Folder for list for values

Alternatively if you have a small number of values, use a custom folder to create a local List of Values within the End User Layer. For example, if you want a list of values for North, South, East, and West, create a custom folder called Region_lov and type in the following SQL statements:

SELECT `NORTH' REGION FROM sys.dual
UNION
SELECT `SOUTH' REGION FROM sys.dual
UNION
SELECT `EAST' REGION FROM sys.dual
UNION
SELECT `WEST' REGION FROM sys.dual

This query creates one item called Region, that can now be used as a List of Values to optimize performance.

For more information about custom folders, see Section 6.5, "Creating Custom Folders."

• Click Next.

  This opens the next page of the Item Class Wizard.

• The next step depends on your selections on the first page of the wizard:
  • If you ticked Alternative sort, go to Section 10.4.4, "Select the Item that Contains the Alternative Sort Sequence."

  • Otherwise go to Section 10.4.5, "Select the Items that Use this Item Class."

10.4.4 Select the Item that Contains the Alternative Sort Sequence

This page of the wizard (see Figure 10-4), enables you to select the item that contains the alternative sort sequence for your new Item Class.

Modify Custom Folder to add an Alternative Sort

An alternative method of creating an alternative sort for a list of values (if you have a small number of values to sort) is to use a custom folder.

You can modify the Region_lov custom folder (described in previous section) to include an Alternative Sort sequence.

For example, if you want the list of values sorted in the following order: West, North, South, East you need to edit the SQL in the Region_lov custom folder so that it appears as follows:

SELECT `NORTH' REGION,
4 ALTERNATIVE_SORT
FROM sys.dual,
UNION
SELECT `SOUTH' REGION,
2 ALTERNATIVE_SORT
FROM sys.dual
UNION
SELECT `EAST' REGION,
3 ALTERNATIVE_SORT
FROM sys.dual
UNION
SELECT `WEST' REGION,
1 ALTERNATIVE_SORT
FROM sys.dual

This query creates a single item called Region that can be used as a List of Values and an alternative sort sequence.

Figure 10-4 Selecting the Alternative Sort Sequence

1. Select the item that contains the alternative sort sequence.

   This item must:

   • already exist in the database and

   • be in the same folder as the item that generates the list of values.

   When you select an item, the item's description is displayed at the bottom of the wizard.

2. Click Next.

   This opens the next page of the Item Class Wizard.

3. Go to Section 10.4.5, "Select the Items that Use this Item Class.".

10.4.5 Select the Items that Use this Item Class

This page of the wizard (see Figure 10-5) enables you to select the items that use your new item class.

If you selected Drill to detail on the first page of the Item Class wizard, end users will be able to "Drill to detail" between any of the items that you select on this page.

Figure 10-5 Selecting the Items that Use this Item Class

1. Move the items that use this item class, from the Available items list to the Selected items list.

2. Click Next.

   This opens the next page of the Item Class Wizard.

3. Go to Section 10.4.6, "Specify the Name and Description for the Item Class."

10.4.6 Specify the Name and Description for the Item Class

This page of the wizard (see Figure 10-6) enables you to specify a name and description for your new Item Class.

Figure 10-6 Naming and Describing the Item Class

1. Specify a name for your new Item Class.

2. (Optional) Specify a description for your new Item Class.

3. Click Finish.

10.5 Editing an Item Class

This section describes how to edit an existing Item Class.

1. Open the Edit Item Class dialog box.

   There are two ways of doing this:

   • Popup Menu
     Right-click on the Item Class that you want to edit (on the Item Classes page) and choose Edit Item Class... on the popup menu.

   • Menu
     Click on the Item Class that you want to edit (on the Item Classes page) and choose Edit | Edit....

   The Edit Item Class dialog box consists of four pages. These pages resemble the pages in the Item Class Wizard and enable you to edit the settings you specified when you created the Item Class.

2. Click the List of Values tab to change the List of Values used in the selected Item Class (Figure 10-7).

Figure 10-7 Edit List of Values Tab

• Click the Alternative Sort tab to change the alternative sort sequence assigned to the List of Values for the selected Item Class (Figure 10-8).

Figure 10-8 Edit Alternative Sort

• Click the Select Items tab to add or remove the items that use the selected Item Class (Figure 10-9).

Figure 10-9 Edit Items that Use the Item Class

3. If you want to enable "Drill to detail" between the Items that belong to this Item Class, tick Use these items in drill to detail (otherwise, clear it).

• Click the General tab to change the name and description of the selected Item Class (Figure 10-10).

Figure 10-10 Edit Item Class Name/Description

4. Click OK.

10.6 Adding Items to an Item Class

This section describes how to add an Item to an Item Class.

There are three ways of doing this:

• Drag & Drop

• Edit Item Class dialog box

• Item Properties dialog box

Using Drag & Drop

1. Display a second work area (choose Window | New Window).

2. Select the Data page in one work area.

3. Select the Item classes page in the other work area.

4. Select the Item(s) that you want to add to an Item Class (on the Data page of the work area).

5. Drag the Items from the Data page to the Item Class (on the Item classes page of the work area).

6. Close one of the work area windows.

Using the Edit Item Class Dialog Box

1. Display the Edit Item Class dialog box for the Item Class that you want to add items to.

   There are two ways of doing this:

   • Popup Menu
     Right-click on the Item Class that you want to edit (on the Item Classes page) and choose Edit Item Class... on the popup menu.

   • Menu
     Click on the Item Class that you want to edit (on the Item Classes page) and choose Edit | Edit....

2. Click the Select Items tab.

3. Move the items, that you want to add to this item class, from the Available items list to the Selected items list.

   There are three ways of moving items from one list to the other:

   • Drag & Drop
     Drag one or more items from one list to the other.

   • Include / Exclude buttons
     Select one or more items in a list and then click either the Include (right arrow) or Exclude (left arrow) button.

   • Double-click
     Double-click an item to move it from one list to the other.

   To select more than one item at once, hold down Ctrl while you click on the items.

   The Available items drop-down list enables you to select items from any open business area.

4. Click OK.

For more information, see Section 10.5, "Editing an Item Class."

Using the Item Properties Dialog Box

1. Select the Item(s) that you want to add to an Item Class (on the Data page of the work area).

2. Display the Item Properties dialog box.

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected folders on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the Properties tool bar icon ()

   • Menu
     Choose Edit | Properties.

3. Specify the Item Class that you want the Item(s) to belong to using the Item class field.

4. Click OK.

For more information, see Section 10.2, "Editing Item Properties."

10.7 Viewing the Items that use an Item Class

This section describes how to view the Items that belong to a specific Item Class.

1. On the Item classes page, expand the Item Class that you are interested in.

   This reveals two objects under the Item Class.

   • List of values

   • Items using this item class (with drill to detail)

2. Expand the Items using this item class (with drill to detail) object.

   This displays the a list of Items that belong to this Item Class.

10.8 Removing Items from an Item Class

This section describes how to remove items from an Item Class.

There are two ways to do this:

There are three ways of doing this:

• Popup Menu

• Edit Item Class dialog box

• Item Properties dialog box

Using a Popup Menu

1. Click the Item class tab on the work area.

2. Expand the Item Class that you want to remove Items from.

3. Expand the Items using this item class (with drill to detail) object.

4. Select the Items that you want to remove from the Item Class.

   To select more than one item at once, hold down Ctrl while you click on the items.

5. Right-click one of the selected Items and choose Delete Item from Item Class on the popup menu.

Discoverer Administration Edition displays the Confirm Delete dialog box. This works in the same way as described in Section 10.10, "Deleting Items & Item Classes."

Using the Edit Item Class Dialog Box

1. Display the Edit Item Class dialog box for the Item Class that you want to remove items from.

   There are two ways of doing this:

   • Popup Menu
     Right-click on the Item Class that you want to edit (on the Item Classes page) and choose Edit Item Class... on the popup menu.

   • Menu
     Click on the Item Class that you want to edit (on the Item Classes page) and choose Edit | Edit....

2. Click the Select Items tab.

3. Move the items, that you want to add to this item class, from the Selected items list to the Available items list.

   There are three ways of moving items from one list to the other:

   • Drag & Drop
     Drag one or more items from one list to the other.

   • Include / Exclude buttons
     Select one or more items in a list and then click either the Include (right arrow) or Exclude (left arrow) button.

   • Double-click
     Double-click an item to move it from one list to the other.

   To select more than one item at once, hold down Ctrl while you click on the items.

4. Click OK.

For more information, see Section 10.5, "Editing an Item Class."

Using the Item Properties dialog box

1. Select the Item(s) that you want to remove from an Item Class (on the Data page of the work area).

2. Display the Item Properties dialog box.

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected folders on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the Properties tool bar icon ()

   • Menu
     Choose Edit | Properties.

3. Specify the None in the Item class field.

4. Click OK.

For more information, see Section 10.2, "Editing Item Properties."

10.9 Viewing a List of Values

This section describes how to view the List of Values associated with an Item.

There are two places where you can view a List of Values:

• Data page (shows a List of Values for specific items).

• Item classes page (shows a List of Values for specific Item Classes)

10.9.1 Viewing the List of Values for a Specific Item

1. Expand the Item whose List of Values you want to view.

You may be warned that retrieving the List of Values may take a long time. When you retrieve a List of Values, Discoverer submits a SELECT DISTINCT query to the database (which selects the distinct set of values for the item). If there is a large number of values in the database, retrieving the list can take some time. The End User Layer has a record of the length of time it takes to retrieve the values. If this length of time is greater than 15 seconds, Discoverer will display a warning. This limit can be changed in Discoverer Plus under Tools | Options | Query Governor.

TIP: If the Item you want to display a List of Values for exists in more than one Folder, choose the Item in the Folder that has the least number of rows. This will return the List of Values in the shortest amount of time.

10.9.2 Viewing the List of Values for an Item Class

To view the List of Values associated with an Item Class:

1. On the Item classes page, expand the Item Class that you want to display a List of Values for.

   This reveals two objects under the Item Class.

   • List of values

   • Items using this item class (with drill to detail)

2. Expand the List of values object.

   You may be warned that retrieving the List of Values may take a long time.

   This displays the List of Values for the Item Class (see Figure 10-11).

Figure 10-11 Item Classes Tab with List of Values

When you retrieve a List of Values, Discoverer submits a SELECT DISTINCT query to the database (which selects the distinct set of values for the item). If there is a large number of values in the database, retrieving the list can take some time. The End User Layer has a record of the length of time it takes to retrieve the values. If this length of time is greater than 15 seconds, Discoverer will display a warning.

10.10 Deleting Items & Item Classes

This section describes how to delete Items and Item Classes.

1. Select the Item(s) or Item Class(es) that you want to delete.
   • Items are listed on the Data page of the work area.

   • Item Classes are listed on the Item classes page of the work area.

   To select more than one item at once, hold down Ctrl while you click on the Items or Item Classes.

2. Delete the Item(s) or Item Class(es):

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected Item(s) or Item Class(es) and choose Delete Item or Delete Item Class on the popup menu.

   • Menu
     Choose Edit | Delete.

   • Keyboard
     Press Delete.

   This opens the Confirm Delete dialog box.

3. Click Impact.
   This displays the Impact dialog box that shows any other objects that may be affected by the deletion (Figure 10-12). The Impact dialog box helps you to make the right choice.

Figure 10-12 The Impact Dialog Box

4. When you have finished reviewing the impact this action will have, click OK.

5. If you still want to delete the selected Item(s) or Item Class(es), click Yes.