more on the max function

building reports desktopintelligence
1

I have seen some posts recently about using the max function. I would like to add
my own question about this because I did not see it solved (or I missed it!)

We have the following table:

Account
Account Status
Time

I set up 2 objects:
Account Time which directly references the Time column in the table and
Most Recent Account Time which is defined as max(Time)

I would like to set up a built-in condition or change this somehow so a user
can simply pull in the most recent account status. I’ve seen how to
do it inside a report using the query panel and setting up a calculation
or have the report do a subquery. However, I don’t think
our users will be able to handle this for awhile and I’d like to find
a more user-friendly solution where they may have to simply drag in a
condition or just put Account Time = Most Recent Account Time.
Any thoughts???

Thanks,
Cori

Listserv Archives (BOB member since 2002-06-25)

2

Cori,

Maybe this helps:
Create a new objects as follows:

NAME: Most Recent Status
SELECT: substr(max(to_char(Time,‘hh24mi’)||Account_Status),7)

    (sorry, I only have the oracle syntax)

What the object does is find the maximum time/account status
combination (within whatever group by BO generates due to other
objects selected). This will have the most recent account status
within the group in it, because time is the most significant.
The object finally returns only the account status part.

It doesn’t matter how many objects the user selects in this way,
finding the maximum only requires the main group by execution.
Its even faster than finding the maximum in a subquery only once.

Good luck,
Marianne Wagt

Listserv Archives (BOB member since 2002-06-25)