8
Lesson 7: Customizing items

Lesson 7: Customizing items

It will probably take you about 20 minutes to complete this lesson.

In this lesson you will customize items in the business area.

As the Discoverer manager, your concern is not only what Discoverer Plus, Discoverer Desktop and Discoverer Viewer display to end users, but that data is presented in a way that makes the information more readable, and therefore easier to analyze. In this lesson you will learn how to hide items from end users, make changes to the axis structure, create lists of values and alternative sorts for them. You will also learn how to create derived items and aggregated calculations that are useful for queries and reports.

This lesson consists of the following exercises:

• "Exercise 1: Hiding items in the business area"

• "Exercise 2: Setting the display axis and display sequence for items"

• "Exercise 3: Creating a list of values"

• "Exercise 4: Viewing the list of values item class"

• "Exercise 5: Creating an alternative sort"

• "Exercise 6: Viewing the alternative sort item class"

• "Exercise 7: Creating a new calculated item"

• "Examples of other calculated items"

Exercise 1: Hiding items in the business area

In this exercise you will hide items in the business area so that end users do not see them.

End users might not need to see all the items in a business area (e.g. primary and foreign keys). Also, sensitive information such as pay scale, time in service, and some items used in calculations might be inappropriate to display to end users. The items listed in the following table are essential to the business area as they are used in join conditions, but they are of no interest to the business user.

Hiding items does not delete them, they remain in the business area, but are not visible to the end user. However, deleted items are removed from the business area.

In general it is a good idea to hide all items that end users do not need to query. This reduces the length of the list of items presented to the end user, making it easier to find what they are looking for.

In the New Video Stores business area, typical items in a folder that would be hidden are shown in the following table:

Folder	Item
Product Information	Product Key
Time Information	Time Key
Store Information	Store Key
Sales Details	Time Key
Sales Details	Product Key
Sales Details	Store Key

To hide the key items from end users:

1. Select all of the following items by holding down the Ctrl key and clicking each item in turn:
   • Product Information.Product Key

   • Time Information.Time Key

   • Store Information.Store Key

   • Sales Details.Time Key

   • Sales Details.Product Key

   • Sales Details.Store Key

2. Right click one of the selected items and choose Properties from the popup menu to display the Item Properties dialog

Figure 8-1 Item Properties dialog

Text description of the illustration custipr.gif

The Item Properties dialog displays the properties that each of the selected items have in common.

3. Set the Visible to user field to No.

4. Click OK.

Note: Discoverer now displays items that are hidden from the user in grayed text.

In this exercise you hid items in the business area so that end users do not see them.

Exercise 2: Setting the display axis and display sequence for items

In this exercise you will set the default axis position of an item and learn how you can rearrange the display axes in Discoverer Plus.

A crosstab worksheet in Discoverer Plus can display data along three axes named top, side, and page (for more information, see the figure below). In Discoverer Administrator, you can specify the default axis for each item. Specifying a default axis does not prevent the end user from switching an item from one axis to another during data analysis.

To set the default position of an item to the side axis and rearrange the display axes:

1. Display the Workarea: Data tab and click the Store Information.Region item.

2. Choose Edit | Properties to display the Item Properties dialog for the Store Information.Region item.

3. Click the Default position field and choose Side from the drop down list.

Figure 8-2 Item Properties dialog

Text description of the illustration itmprsi.gif

4. Click OK to set the default position of the Store Information.Region item to the side axis.

In Discoverer Plus, the end user can rearrange the axes to override the settings you create in Discoverer Administrator. The figure below shows the Workbook Wizard, Step 3, where the end user can rearrange the axes.

Figure 8-3 Workbook Wizard - rearranging display axes in Discoverer Plus

Text description of the illustration usedax.gif

Figure 8-4 Sample workbook showing how Discoverer Plus displays the axes.

Text description of the illustration usedax2.gif

The above figures show how Discoverer Plus displays axes items in a Worksheet after they have been rearranged in the Worksheet Wizard.

Key to the above figures:

1. Page axis

2. Top axis

3. Side axis

In this exercise you set the default axis position of an item and learned how to rearrange the display axes in Discoverer Plus.

Exercise 3: Creating a list of values

In this exercise you will create a list of values that displays each department name in the New Video Stores business area.

You create lists of values by using an item class. Most of the item classes used in the tutorial were generated automatically when you loaded the EUL in "Lesson 2: Connecting to Discoverer Administrator, using the Load Wizard and the Workarea". However, you can create new item classes that contain lists of the unique data values represented by an item.

To create a list of values:

1. Display the Workarea: Item classes tab.

2. Click the New Video Stores business area and choose Insert | Item Class to display the Item Class Wizard: Step 1 dialog.

3. Select the List of values check box.

4. Clear the Alternative sort check box.

5. Clear the Drill to detail check box.

Figure 8-5 Item Class Wizard: Step 1 dialog

Text description of the illustration icwiz1.gif

6. Click Next to display the Item Class Wizard: Step 2 dialog.

Figure 8-6 Item Class Wizard:Step 2 dialog

Text description of the illustration icwiz2.gif

7. Select the Product Information.Department item.

   This is the source that Discoverer uses to create a list of values for the new item class.

8. Click Next to display the Item Class Wizard: Step 3 dialog.

Figure 8-7 Item Class Wizard: Step 3 dialog

Text description of the illustration icwiz3.gif

9. Click Next to display the Item Class Wizard: Step 4 dialog.

Figure 8-8 Item Class Wizard: Step 4 dialog

Text description of the illustration icwiz4a.gif

10. Select the Sort values and remove duplicates check box.

11. Select the Show values in "Select items" page of Worksheet Wizard check box.

12. Clear the Require user to always search for values check box.

13. Select the Cache list of values for each connection check box.

14. Click Next to display the Item Class Wizard: Step 5 dialog.

15. Type Departments in the Item class name field.

16. Type Video Store Sales and Rentals in the Item class description field.

Figure 8-9 Item Class Wizard: Step 5 dialog

Text description of the illustration icwiz4.gif

17. Click Finish for Discoverer Administrator to create a new item class named Departments.

In this exercise you created a list of values that displays each department name in the New Video Stores business area.

Exercise 4: Viewing the list of values item class

In this exercise you will view the list of values (LOV) item class that you created in the previous exercise.

To view the list of values item class created in the previous exercise:

1. Display the Workarea: Item classes tab and click the + symbol next to the New Video Stores business area to display all item classes in this business area.

Figure 8-10 Workarea: Item Classes tab

Text description of the illustration wkaic1.gif

2. Scroll down the item classes until you see the Departments item class.

3. Click the + symbol next to the Departments item class that you just created.

4. Click the + symbol next to List of values to display the values for this item class (e.g. Departments).

   Note: To build a list of values, Discoverer Administrator reads every row of the relevant table from the database. If the table has a large number of rows, it can take a long time to build the list of values. Discoverer Administrator warns you of this and asks you if you want to continue.

5. Click Yes.

6. Click the + symbol next to Items using this Item class to display the items that use this item class.

Figure 8-11 Workarea: Item Classes tab

Text description of the illustration wkaic2.gif

In this exercise you viewed the list of values (LOV) item class that you created in the previous exercise.

What the Discoverer Plus user sees - selecting from a list of values in Discoverer Plus

The Discoverer Plus user sees the list of values created in Discoverer Administrator, and can use this list of values to apply a condition.

Figure 8-12 Workbook Wizard - selecting item values from a list of values in Discoverer Plus

Text description of the illustration pluslv.gif

When you create a worksheet, the end user creates a condition by selecting the specific item values from the list of values in the Available list and moving them to the Selected list as shown above.

Exercise 5: Creating an alternative sort

In this exercise you will create an alternative sort that enables end users to sort list of value items in a non-standard fashion.

A standard sort might be alphabetical, numerical or chronological, whereas a non-standard sort is arbitrary such as North=1, South=2, East=3, and West=4, or the days of the week beginning with Monday. In Discoverer an alternative sort orders a list of values by using a sequence provided by another item (does not apply to ODBC databases).

In this exercise you will create an alternative sort for the Days of the Week item in the Time Information folder. To achieve this you will create a new item class that defines an alternative sort and you will apply it to the Days of the Week item. The alternative sort that you will use is called Alternate Sort Days, this is the custom folder you created previously (for more information, see "Lesson 5: Working with custom folders").

Note: When creating or editing an 'alternative sort' item class, a one-to-one relationship must exist between the column (item) used for the list of values and the column (item) used for the sort order. The two items must be in the same folder for the alternative sort order to work.

To create an alternative sort:

1. Select Insert | Item Class to display the Item Class Wizard: Step 1 dialog.

2. Select the Alternative Sort check box.

Figure 8-13 Item Class Wizard: Step 1 dialog

Text description of the illustration altsc1.gif

3. Click Next to display the Item Class Wizard: Step 2 dialog.

Figure 8-14 Item Class Wizard: Step 2 dialog

Text description of the illustration altsc2.gif

4. Select Day of the Week from the Alternate Sort Days custom folder.

   This is the item that will generate the list of values.

5. Click Next to display the Item Class Wizard: Step 3 dialog.

Figure 8-15 Item Class Wizard: Step 3 dialog

Text description of the illustration altsc3.gif

6. Select the Alternate Sort item.

   This is the item that contains the alternative sort sequence.

7. Click Next to display the Item Class Wizard: Step 4 dialog.

Figure 8-16 Item Class Wizard: Step 4 dialog

Text description of the illustration altsc4.gif

The Item Class Wizard: Step 4 dialog enables you to select one or more items that will use the alternative sort sequence. In this lesson you will choose an item from the Time Information folder.

8. Click the + symbol next to the Time Information folder in the New Video Stores business area to display the folder items.

9. Select the Day of the Week item from the Available Items list and move it into the Selected Items list.

10. Click Next to display the Item Class Wizard: Step 5 dialog.

Figure 8-17 Item Class Wizard: Step 5 dialog

Text description of the illustration altsc5.gif

11. Select the Sort values and remove duplicates check box.

12. Select the Show values in "Select items" page of Worksheet Wizard check box.

13. Clear the Require user to always search for values check box.

14. Select the Cache list of values for each connection check box.

15. Click Next to display the Item Class Wizard: Step 6 dialog.

Figure 8-18 Item Class Wizard: Step 6

Text description of the illustration altsc6.gif

16. Type Days of the Week - Alternative Sort into the Item class name field.

17. Click Finish for Discoverer Administrator to create a new item class called Days of the Week - Alternative Sort that is used by the Days of the Week item in the Time Information folder.

In this exercise you created an alternative sort that enables end users to sort list of value items in a non-standard fashion.

Exercise 6: Viewing the alternative sort item class

In this exercise you will view the alternative sort you created in the previous exercise.

To display the newly created alternative sort in the Days of the Week item:

1. Display the Workarea: Data tab and click the Time Information folder.

2. Click the + symbol next to the Day of the Week item to display the list of values based on the alternative sort that you just created.

Figure 8-19 Workarea: Data tab

Text description of the illustration altsclv.gif

You can now see the days of the week can displayed in the sequence that is defined in the custom folder Alternate Sort Days.

Note: The reason you create a local list of values in the EUL is to improve performance in Discoverer Plus and Discoverer Viewer. Discoverer retrieves the values from the EUL and therefore does not need to read every row of the relevant table from the database. For more information, see Chapter 6, "Exercise 1: Creating a custom folder".

In this exercise you viewed the alternative sort you created in the previous exercise.

Exercise 7: Creating a new calculated item

In this exercise you will create a new calculated item of profit as a percentage of sales.

Calculated items are often a critical element of a report. Typical business calculations include values such as:

• profit margins

• average revenues per month

• expected sales

• percent of profit by product type

You can improve the usefulness of a business area, by creating calculations that an end user is likely to require. Discoverer Administrator stores these calculations in the EUL for use in Discoverer Plus and Discoverer Viewer.

For more information about calculations, click Help.

How to create a new calculated item:

1. Display the Workarea: Data tab and click the Sales Details folder.

   Note: You will create the new item in the Sales Details folder.

2. Choose Insert | Item to display the New Item dialog.

3. Click the + symbol next to the Sales Details folder to display the items.

Figure 8-20 New Item dialog

Text description of the illustration calc1.gif

4. Type Percent Profit in the Name field.

5. Type the following text in the Calculation field:

   SUM(Sales Details.Profit)/SUM(Sales Details.Sales)

   Hint: You can type the calculation directly or you can use the following ways to minimize the amount of typing you need to do:

   • To add items, select the items from the list on the left and click the Paste >> button to paste them directly into the calculation.

   • To add an operator, click the operator buttons below the Calculation field.

   • To display a list of database functions, click the Functions radio button.

   Note: Calculations must follow the Oracle calculation standard syntax. For a full description of the Oracle calculation syntax, see the Oracle9i SQL Language Reference Manual.

Figure 8-21 New Item dialog

Text description of the illustration calc.gif

Key to the New Item dialog:

1. Calculation panel

2. Operator buttons

6. Click OK to save the new calculation in the business area.

In this exercise you created a new calculated item of profit as a percentage of sales.

What the Discoverer Plus user sees - calculations in Discoverer Plus

The Discoverer Plus user sees the calculated items created in Discoverer Administrato as shown in the following figures.

Figure 8-22 Workbook Wizard - displaying the Percent Profit item in Discoverer Plus

Text description of the illustration plusp.gif

The above figure shows the calculated item Percent Profit, created in Discoverer Administrator, along with all the other items in the Sales Details folder.

The end user might use the Workbook Wizard to create a table style worksheet in Discoverer Plus using the New Video Stores business area by selecting the following items:

• Profit SUM, Sales SUM and Percent Profit from the Sales Details folder

• City from the Store Information folder

Discoverer will display the Percent Profit item (a calculated item, not a database column) with all the other items in the Sales Details folder.

When the end user runs the query, Discoverer displays Percent Profit in a Workbook.

Figure 8-23 Discoverer workbook displaying data for the Percent Profit calculated item

Text description of the illustration plusp2.gif

The above figure shows how including the Percent Profit calculated item in a Workbook causes Discoverer Plus to perform the calculation on each row and display the results in a column.

To control the display format of numbers for the Percent Profit item, the number format was set to the first option in the `Percent' category list in Discoverer Plus using the menu option Sheet | Format | Format Data | Number.

Examples of other calculated items

Look at the following examples of how to create some other kinds of calculated items:

Address record calculated item:

For example: Sidney Sloan, 21 Great Jones Street Apt. 2B, New York City, New York 10012

• Name: Address

• Calculation: Name || ',' || Street Line 1 || Street Line 2 || ',' || City || ',' || State || Zip Code

Personnel record calculated item:

For example: Business Analyst in Accounting Department, $50,000

• Name: Function

• Calculation: Job Title || 'in' || Department || ',' || Salary

Annual compensation based on monthly salary and commission (NVL permits a zero value) calculated item:

• Name: Compensation

• Calculation: Salary*12+NVL(Commission, 0)

Hint: NVL is a database function that can replace NULL with an alternative value, such as 0.

Aggregated calculated items

For example:

• Name: Total Compensation

  Calculation: SUM(Salary + NVL(Commission, 0))

• Name: % Commission

  Calculation: SUM(Commission)/SUM(Salary)

• Name: Avg. Units per Customer

  Calculation: SUM(unit_sales)/SUM(customer_count)

For more detailed information about creating calculations, click Help.

Lesson summary

In this lesson you customized items in the business area and completed the following exercises:

• Hiding items in the business area

• Setting the display axis for items and display sequence

• Creating lists of values

• Viewing the list of values item class

• Creating an alternative sort

• Viewing the alternative sort item class

• Creating a new calculated item

In the next lesson you will create a complex folder that is made up of items from other folders in a business area.