Defining Objects with "where" clause - best practi

I recently had a consulting group in to review the initial progress of our Business Objects rollout. The biggest issue they had was the fact that I was defining my ‘objects’ with conditions. For instance, my quote revenue object was a sum of revenue with several conditions. This allows my report designers of all levels to get quote revenue without having to know the conditions required and also eliminate wrong numbers due to the designer not selecting all of the conditions required.

The suggestion was to NOT build any where conditions into the objects, but create separate condition(s) the user can drag onto the report. My concern is how the user will know to look for conditions and how the user will know for sure they included all the correct conditions?

Any suggestions? advice?


larryc (BOB member since 2004-04-15)

The issue is not really that your objects are defined with conditions, it is that your objects will inherently become incompatible.

For instance, say you had a GENDER object with the following defined objects:

MALE COUNT:
Count(CUST_ID) where GENDER=‘M’

FEMALE COUNT:
count(CUST_ID) where GENDER=‘F’

Should you decide to have a report that breaks down your Customers with two columns, you will ALWAYS get no rows returned. That is because your WHERE clauses are Always ANDed together. Even if you modified the Sql to have them read as OR statements, both numbers would be the same.

You REALLY should define the objects using a CASE or DECODE.
MALE COUNT:
sum( decode(gender,‘M’,1,0))

or
FEMALE COUNT:
sum( CASE WHEN GENDER = ‘F’ then 1 else 0 END )

-RM


digpen :us: (BOB member since 2002-08-15)

I forgot about the case or decode statement recommendation. Unfortunately our legacy system is not compatible with either decode or case statement (Ingres 6.1). :frowning:


larryc (BOB member since 2004-04-15)

Education, Communication, Training, and Documentation

All of these will play a vital part in the success (or lack thereof) of your BO rollout or how it is percieved by the end users. True, it is the responsibility of the Designer to make a Universe and objects as user friendly as possible; however, if you are using BO as an ad-hoc query tool allowing users to build there own reports there is certain level of responsibility on there end also. If a user does not know the data in which they are creating reports you should probably question whether they should be building reports in that environment.

On a side note, when you start using the where clause in defining objects you will ultimately run into problem with conclicting statements due to the fact that all the where clauses will be “and-ed” together. For example,

object name = REV2001 (table.rev where date btwn 1/1/2001 and 12/31/2001)
object name = REV2002 (table.rev where date btwn 1/1/2002) and 12/31/2002)

If you tried to use these 2 objects in the same report you would not return any data because a reveneue record will not have a date in both 2001 and 2002. This may be a simplisitic example but hopefully will help demonstrate some possible issues you may run into. - thanks and good luck


jswoboda :us: (BOB member since 2002-06-20)

The information mentioned here by digpen and Jeff is excellent. In addition to Jeff’s comments about educating your users, I would strongly recommend that you put this kind of information in your object’s descriptions. One of my pet peaves, as a consultant, is finding objects with no description. The description field of an object should ALWAYS be used, and is an excellent place to put information like this.

Of course, your users need to be traing to look at the descriptions. :yesnod:


MichaelWelter :vatican_city: (BOB member since 2002-08-08)