Conditions

 < Day Day Up > 



You use conditions to narrow your analysis to show information only for your particular subset of data. If you are a product manager, you may limit your analysis to certain products. If you are a regional manager, you may limit your analysis to certain countries. If you are a supervisor, you may limit your analysis to the employees you manage. You limit your analysis by adding conditions to a query. Conditions generate a WHERE clause in the SQL SELECT statement. In some cases, these conditions may be applied automatically through security settings in your database or in the BusinessObjects Supervisor settings. Usually, it is a combination of both. For example, if the transaction system or data warehouse contains information for multiple legal entities, the DBA may restrict your access to show you data only for the legal entity by which you are employed; you do not need to add an extra condition in your query. However, you will still need to add conditions in the query to restrict your analysis to particular products, regions, employees, and so on, within your legal entity.

When you add a condition to the query, you need to consider the impact on response time, particularly if you are accessing a transaction system or a very large data warehouse. Ideally, it is better to apply conditions to objects that have shorter values such as Product ID or Product Code, as these columns are more often indexed in the source database, rather than longer values such as Product Name or Product Description, which may not be indexed.

The Query Panel provides you with three ways to add a condition to your query:

  • You can apply a simple condition by selecting a result object, then clicking the Simple Condition button. You are prompted to select from a list of values. Simple conditions use only EQUAL TO or IN LIST operators. You apply simple conditions only to dimension or detail objects that have an associated list of values.

  • You can drag an object from the list of Classes and Objects to the Condition window of the Query Panel to create what Business Objects refers to as a complex condition. This object does not need to be a result object and can also be a measure object. The object does not have to have an associated list of values. In reality, the conditions you create this way can be fairly simple, so don’t let the terminology of complex condition mislead you.

  • You can apply a predefined condition that is created by the universe designer and includes the operators and possible values.

Adding a Complex Condition

There are four steps needed to add a complex condition to a query. In the following example, you will add a complex condition on Country, using the initial query displayed in Figure 21-2 as a starting point:

  1. Select the Country object and drag it to the Condition window.

  2. Specify an operator to evaluate the condition. In the Condition window, BusinessObjects prompts you to <Select an operator>. You must move your cursor to the left screen that displays the list of operators. Select In List by double-clicking.

    click to expand

  3. <Select an operator> is replaced by the In List operator, and you are prompted to <Specify an operand> for entering, selecting, or generating a potential list of values. Select Show List Of Values and double-click to launch the list of values.

    click to expand

  4. Choose Australia, New Zealand, South Africa. Use CTRL-click to select multiple values. Click OK to close the list of values dialog box and see the complex condition statement, as shown next. Notice that in this dialog box, commas separate multiple values, whereas in prompts discussed in Chapter 20, you used semicolons. Both conditions are connected with an AND.

To view the SQL generated, click the SQL button from the Query Panel. The conditions on color and country are connected with an AND. Because the object types are characters, the values are enclosed in single quotes automatically. If they were numeric values, they would not use quotes.

WHERE   (   WINE_FACT_PRICE_RATE.ColorClass  =  'Red'   AND  WINE_FACT_PRICE_RATE.Country  IN  ('Australia', 'New Zealand', 'South Africa')   )

Operators

Operators form the basis of comparison for the object and the values you specify. Table 21-3 lists the possible operators. Some SQL equivalents are different for specific RDBMSs. The SQL equivalents listed in Table 21-3 are Oracle-based.

Table 21-3: Operators Available in the Query Panel Conditions

Operator

SQL Equivalent

Explanation

Equal to

=

Exactly equal to one value

Different from

<> or !=

Not equal to or different from one value

Greater than

>

Greater than a particular number, date, or character

Greater than or equal to

>=

Greater than or equal to a particular number, date, or character

Less than

<

Less than a particular number, date, or character

Less than or equal to

<=

Less than or equal to a particular number, date, or character

Between

BETWEEN

Records between and including the two values— for example, Age Between 20 And 30; Price Between 100 and 150; Date Between January 1 And January 23

Not Between

NOT BETWEEN

All values outside a particular range

In List

IN

Equal to multiple values, generally to select multiple character values in a noncontiguous list

Not In List

NOT IN

Different from multiple values

Is Null

IS NULL

Rows in which no value has been entered. Null is different from zero or blank spaces

Is Not Null

IS NOT NULL

Records that do not contain a null

Matches Pattern

Like

This allows you to use a wildcard character such as % in Oracle or * in SQL Server to find all records that contain or begin with a particular string. Use underscore (_) to match one particular space. For example, B% is everything that starts with B, %B% contains a B somewhere in the string, and _B% has B as the second position. Warning: this type of condition means an index for the particular column will not be used

Different from Pattern

Not Like

Does not match the pattern specified. Warning: this type of condition means an index will not be used

Both

INTERSECT

Retrieves records in which the two values overlap. Discussed in Chapter 22

Except

MINUS

Removes records from a main query. Discussed in Chapter 22

Operands

Operands allow you to specify the values to which you want to compare the object. The list of available operands may change depending on the operator you specify in step 2. Table 21-4 describes the standard operands.

Table 21-4: Operands for Specifying Comparison Values in a Complex Condition

This Operand

Enables You To …

Type a new constant

Manually enter one or more values. If you enter multiple values, connect them with a comma. Note that in answering a prompt, you connect them with a semicolon. For character values, you do not need to enclose the values in single quotes. BusinessObjects adds the quotes directly to the SQL.

Show list of values

Pick from a list of possible values that will remain fixed for each query refresh.

Type a new prompt

Execute the query and be prompted to enter or select values at refresh time. Create a prompt for standard reports that will be used by users with varying information requirements.

Show list of prompts

Select from a list of available prompts. Use this when you have already defined a prompt and want to reuse the same prompt for another data provider.

Select an object

Specify another object as the operand—for example, to select a list of customers that are both the sold to and ship to customers. This operation generates another join statement.

Create a subquery (ANY)

Specify another query to dynamically determine the comparison values. Discussed in Chapter 22.

Calculation

Launches a calculation wizard to create a particular kind of subquery. Discussed in Chapter 22.

Predefined Conditions

In the Classes and Objects window, there are also two radio buttons that allow you to filter which objects appear in the window. By default, standard classes and objects appear. Click the Predefined Conditions radio button to display predefined conditions, as shown in Figure 21-4. Predefined conditions are a particular kind of object that have built- in operators and values to restrict the number of rows returned to the microcube and to display only the data you are interested in analyzing. For example, your universe may contain a predefined condition called Current 3 Months that automatically filters your data to retrieve the latest three months worth of information. The conditions and SQL in a predefined condition can be quite complex, performing multiple SQL translations and comparisons. See Chapter 10, “Condition Objects,” for a more thorough discussion.

click to expand
Figure 21-4: Predefined conditions are special universe objects that apply complex conditions with one mouse click.

As shown in Figure 21-4, predefined conditions are denoted with a filter icon. To add a predefined condition to the query:

  1. Click the Predefined Conditions radio button to display the condition objects. Note that any classes that do not have predefined conditions are not displayed.

  2. Double-click the predefined condition to add it to the condition pane or drag the object to the condition pane.

In Figure 21-4, the predefined condition New World Wines has the same filtering effect as selecting the three countries, as shown in the earlier complex condition.

Prompts

As seen in Chapter 20, “Handling Prompts and List of Values,” prompts allow you to refresh a query interactively. Prompts are useful when the conditions of your query change periodically or if you are a report author creating a document for other users. In developing prompts, follow these guidelines:

  • If a list of values is available, start the prompt question with Select.

  • If a list of values is not available, start the prompt question with Enter.

  • Prompts are sorted alphabetically. If the conditions require a logical sort order, precede the question with a numeric value to force a logical sort order.

  • If a document will contain multiple data providers, use the operand Show List of Prompts to select an existing prompt. For example, in Figure 21-5, you have two data providers that each contain a condition on Year; if you use the same exact prompt for each condition, when you refresh a document, you will be prompted to select a year only one time. The prompt is case sensitive, so use Show List of Prompts to select the prompt from a list of ones you have already created. The same condition value is then filled in for each of the conditions / data providers.

    click to expand
    Figure 21-5: Using the same prompt in multiple data providers allows you to specify the same condition for each query.

To create a prompt on the wine’s country:

  1. Select the Country object and drag it to the Condition window.

  2. In the Condition window, BusinessObjects prompts you to <Select an operator>. Move the cursor to the Operator list and select In List by double-clicking.

  3. <Specify an operand> by moving your cursor to the Operand list and double-clicking Type a New Prompt. The value box changes to an insertion cursor. Create the prompt as Select wine country(s). Press ENTER. This prompt will now be available for other queries in this document.

A prompt generates the following SQL, which gets evaluated when you execute the query or schedule it via BCA:

WINE_FACT_PRICE_RATE.Country  IN  @variable('Select wine country (s)')



 < Day Day Up > 



Business Objects(c) The Complete Reference
Cisco Field Manual: Catalyst Switch Configuration
ISBN: 72262656
EAN: 2147483647
Year: 2005
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net