Objects with Prompts: Interactive Objects

---

Business Objects refers to objects that contain prompts as interactive objects. Each time a user accesses an interactive object, BusinessObjects prompts the user for additional information that you, as the designer, build into the object. Prompts can be useful but also annoying if the user always wants the same values. For example, if a user always wants current year data, it can be aggravating if the object prompts the user for the year each time the user refreshes the query. In such a case, the user is better off placing a fixed condition in a report. Objects with prompts should be reserved for items in which some sort of condition is required either to limit the number of rows returned or to guarantee correct results.

In Chapter 8, Figure 8-2, you looked at the risk of constructing a query that involved a single point in time (account balance or ending inventory) and a period of time (debits and credits or movements in and out). One way to ensure users select one point in time for inventory or account balances is to prompt users to enter an individual date whenever they select month-end inventory, as shown in Figure 10-2. (It would be wrong to put the prompt on the Day object, because it would prevent users from analyzing debits and credits for more than one day.)

Figure 10-2: Create interactive objects with @Prompt

Notice in Figure 10-2 that the object or TABLE.COLUMN in the SELECT portion of the SQL can be different than the table.column in the WHERE clause. Because these two columns can be different, the format types also may be different. The Balance object uses the Type=Number, yet the @Prompt uses D to indicate the prompt answer will be in date format. Prompts are added in the WHERE clause of a new or existing object. Under Functions, double-click the @Prompt function to insert the syntax in the SQL statement, as shown here.

The @Prompt function uses the following syntax:

@Prompt('Message','type','object or list of values',MONO/MULTI,FREE/CONSTRAINED)

where

1. Message is the prompt you want users to see when they run a query that contains this object.

2. Type is the object type or field format for the condition column. BusinessObjects uses this to determine if the values require quotes or not. The base object and the WHERE clause column may be two different types. In this example, Balance is numeric and Day is date. Acceptable values are

   • A for alphanumeric

   • N for number

   • D for date

3. The 'object or list of values' parameter can either be the individual objects whose list of values you want to use or a list of values you enter manually in the prompt. When entering a predetermined list of values, you must enclose the values in single quotes, separated by commas, and the complete list must be enclosed by brackets. For example, if I wanted to restrict users to the most recent two ending balances: {'09/30/02','10/31/02'}.

4. MONO/MULTI If users can select only one value, use MONO. If users can enter more than one value, use MULTI. Note that the SQL operator must correspond to this setting. If users can enter more than one value, use the IN operator.

5. FREE/CONSTRAINED If users must select a value from the list of values, use CONSTRAINED. If users can either select from a list of values or enter their own value, use FREE.

   Caution 

   The @Prompt function is very particular about commas, quotation marks, and matching object types. Be sure to Parse the object.

When a user constructs a query that uses the Balance object, BusinessObjects will always prompt them to Select the Day for the Ending Balance. The following screen illustrates how prompts can become user unfriendly. In the following query, the user wants to see daily balances and correctly includes the Day object as a result object:

However, the Balance object also now includes the prompt. This generates the following SQL:

SELECT   Daily_Balance.Date,   Daily_Balance.Balance FROM   Daily_Balance WHERE ( ( Daily_Balance.Date ) = @Prompt('Select the Day for the Ending Balance','D','Time\Day',MONO,FREE)  )   AND  (   Daily_Balance.Date  BETWEEN  {d '2002-09-01'} AND {d '2002-09-30'}   )

First, from the user's viewpoint it seems nonsensical that BusinessObjects will prompt for information that the user has already included in the conditions. Second, users will only ever be able to retrieve balances for one day at a time when it is a valid business question for them to review daily balances over a period of time. In this respect, it is important in training and object descriptions to emphasize how these objects work and when to use them. As you build interactive objects, follow these guidelines:

• Use interactive objects only when the absence of a prompt could lead to inaccurate information.

• If the prompts are for user friendliness or automation, have two objects, one without a prompt for unrestricted information and one with the prompt. Differentiate between these two objects with clear names-for example, Balance and Balance-Date Required.

• Provide usage information in the object description or in training.

All Values

If you do not want the prompt to be mandatory to limit the number of rows, try the following technique, suggested by Walter Muellner of Mercury Business Solutions in Austria:

'ALL'  IN @Prompt('Enter City or ALL','A', 'Customer\City',multi,)  OR City.city IN @Prompt('Enter City or ALL','A', 'Customer\City',multi,)

This is a very creative approach to balancing user friendliness with prompting. When users enter ALL, the first part of the condition SQL gets used ('ALL' IN 'ALL'). Because the condition statement uses an OR clause, the second condition is not evaluated. Conversely, if users enter city names (or anything other than ALL), then the first part of the SQL is not true so it does not get used, while the second condition does. Of course, if ALL is a possible data value in the City column, this poses a problem. The alternative is to use a symbol such as * or %-however, the risk is that users mix up true uses of these symbols (wildcards in certain databases). Finally, in order for this to work, the prompt in both condition statements must be exactly the same or users will be prompted twice.

Reusing Interactive Objects with @Variable

BusinessObjects allows you to reuse the prompt as a variable that you can then use in other objects. The variable can be one that you create with @Prompt, or it can be a system variable. BusinessObjects provides the following system variables:

• BOUSER The BusinessObjects user ID

• BOPASS The BusinessObjects password

As an example, let's assume that a hierarchical PRODUCT table contains both products and employees responsible for those products. So that users automatically see information for their products, the My Product object could contain the following WHERE clause:

PRODUCT.PRODUCT_OWNER = @Variable('BOUSER')

Reusing Interactive Objects with @Where

As mentioned previously, @Where allows you to reuse a WHERE clause in multiple objects. There are two benefits to using @Where: the first is decreased maintenance. The second is decreased user prompting. If a query contains multiple occurrences of the exact same prompt, BusinessObjects will prompt the user only one time. For example, let's assume that users must filter Customer information by City to ensure only a limited amount of data is returned. The dimension object City contains a prompt. A designer could add @Where (Customer\City) to Customer Name, Customer Id, Customer Age Group. Even if a query contains all four of these objects in a query, the user is prompted only once to Select a City.

Prompts in Objects vs. Reports

Objects with prompts achieve a similar functionality as queries with prompts (Chapter 21, 'Prompts'). In Chapter 13, you will look at the pros and cons of where to put this kind of intelligence. The main difference to consider with prompts is flexibility and maintenance. If you want to give the users flexibility, put the prompts in the reports. If you want to minimize your maintenance costs, keep the prompts centralized in the universe objects. The following table summarizes some of the key differences between prompts in objects versus prompts in a query.

Object with Prompt	Query with Prompt
Designer builds into universe	User builds in query
Centralized, so cost-effective to maintain because the designer creates the prompt once	Decentralized, so expensive to maintain because users must create the prompt in every document
Users cannot remove, so it can be inflexible, but error-proof, as it requires an answer	Users can remove, providing flexibility

---