Condition Objects

---

Condition objects are unique objects that allow users to access predefined conditions that the designer specifies in the WHERE clause. Condition objects are denoted with a filter symbol.

The Island Resorts Marketing universe, shown next, contains several predefined conditions. To access a condition object, click the Classes/Conditions filter in the lower-left corner. Click the + sign next to each class to see the Condition Objects.

Most of these conditions are fairly simple. In building condition objects, you, as the designer, must evaluate if the objects add clutter or value. If it saves the users time, create the object. If it defines some unique groupings that do not otherwise exist in the dimension tables, create the object. Objects that contain one value such as Year 1993 do not add much value; users probably could have added the condition themselves. Young American, on the other hand, contains two conditions (Customer.age <= 30 AND Country.country = 'US'). Nesting conditions can be confusing and cumbersome, so such an object would be very helpful to users.

In the following example, you will create a condition object, Platinum Customers, for customers who generate more than $100,000 in revenue in any given year. The next report shows seven customers who had revenues of $100,000 or more. You will add each customer code to the condition clause. Unfortunately, it is not possible to create condition objects based on measures (so you can't say Where Revenue >= 100,000) as doing so requires a different SQL clause. Conditions on measures use a HAVING clause (explained in Chapter 22) rather than a WHERE clause.

1. Using the Island Resorts Marketing universe, set the Classes and Objects pane to display the condition objects. Position your cursor in the class you want the new object to appear-in this example, Customer.

2. Click the Insert Condition button on the toolbar or select Insert | Condition from the pull-down menus.

3. In the Name box, enter Platinum Customer.

4. In the Description box, enter some meaningful help text such as: A platinum customer is a predefined list of customer codes with revenues of $100,000 or more in any given year.

5. In the Where box, click >> to call up SQL Editor.

6. At this point, you could manually enter the WHERE statement. Under Classes and Objects, scroll to the class that contains the object you want to use for the WHERE clause. In this example, click the + next to Customer.

7. For response time reasons, you want the WHERE clause on the indexed Customer Id object rather than the nonindexed Customer Name. Double-click Customer Id to have it added to the WHERE statement. Note that Designer uses the @Select function.

   

8. To view the SQL syntax, click the Show object SQL check-box, shown next. Notice that Designer grays the SQL statement box and does not allow you to edit the statement.

   

9. In the Operators box, scroll to IN and double-click to add this to the SQL statement.

10. You do not need to transform your values, so you will not use any functions. Manually enter the list of customer IDs (106,502,104,102,506,402,406) generated by the preceding report. Because Customer Id is a numeric object, it does not require single quotes around each condition value.

11. Always click Parse to test the validity of the SQL statement.

12. Click OK to close the SQL Editor. The condition object properties should now look like this:

    

13. Click OK to close the object properties screen.

14. Click Save to save the universe changes. If necessary, export the changes to the repository with File | Export.

When users build a query, they can now add the condition object to the condition panel. To test this, select Tools | Run | BusinessObjects to launch the BusinessObjects user module. The next image shows how this new object appears to users in the Query Panel.

Time Conditions

Condition objects involving time functions are popular. Additionally, BusinessObjects does not allow users to add calculations within the query panel, so condition objects become the only way in which users can create rolling reports. The following objects use SYSDATE, which returns the current date according to the RDBMS.

The following SQL creates a One Year Ago condition object. The comparison column is in a date format. Because SYSDATE is a date column including the day of the year, you subtract 365 days to arrive at the same date last year.

TIMES.END_OF_CAL_YEAR=SYSDATE-365

If the comparison column is not in a date format but is numeric, then you must also convert the SYSDATE calculation to numeric with TO_NUMBER. The following SQL creates a Last Year object in which the year is four digits. To extract only the four digits, use the TO_CHAR function.

TIMES.CALENDAR_YEAR=TO_NUMBER(TO_CHAR(SYSDATE-365,'YYYY'))

You can further nest date functions to create a Current 3 Months condition object. In the following example, -3 shows that three months should be subtracted from the SYSDATE.

SH.TIMES.CALENDAR_MONTH_NUMBER  BETWEEN TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,- 3),'MM') ) AND TO_NUMBER(TO_CHAR(SYSDATE,'MM') ) 

Caution 

The WHERE statements from each Condition Object and Row Restrictions set through Supervisor (Chapter 12) are appended to the entire SQL statement. If users combine incompatible condition objects, they may get no rows returned.

---