If I use individual objects at the universe level with
different where clauses on each, no data is returned. If I call a database
function (passing it parameters from individual universe objects) to perform the counts, I lose the required flexability for the users in limiting their reports.
I try not to use the where clause on universe object defintions for this reason. Instead I use a decode statement so that any number of objects can be returned without worrying about the where clauses. Unfortunately decode is not very good at between statements and is only really able to cope with equality - similar in fact to the where clause in Business Objects full client function generator.
A solution would be to use a variable at the report level with an if-then-else statement which would return values 1 thru 8 based upon the categories mentioned above, i.e. Category = If ( <= 15) Then 1 Else If ( > 16 and <=30) Then 2 Else… Once done, create another variable using a count() function with the where operator on the variable you created previously to only count the 1s, i.e. Count() Where (Category=1). Then create another for the 2s and so on.
An alterntive would be to provide this functionality at the database/universe level using firstly a stored procedure to provide the categorisation, then a series of objects using decode statements to perform the count.
For example, here is a stored procedure that I created for a similar purpose:
FUNCTION SF_CATEGORISE (VAL IN NUMBER) RETURN VARCHAR2 IS
V_RET VARCHAR2(15);
BEGIN
IF NVL(VAL, 0) = 0 THEN V_RET := ‘1’;
ELSIF VAL BETWEEN 1 AND 100 THEN V_RET := ‘2’;
ELSIF VAL BETWEEN 101 AND 250 THEN V_RET := ‘3’;
ELSIF VAL BETWEEN 251 AND 500 THEN V_RET := ‘4’;
ELSIF VAL BETWEEN 501 AND 1000 THEN V_RET := ‘5’; ELSIF VAL BETWEEN 1001 AND 2500 THEN V_RET := ‘6’; ELSIF VAL BETWEEN 2501 AND 5000 THEN V_RET := ‘7’; ELSIF VAL BETWEEN 5001 AND 10000 THEN V_RET := ‘8’; ELSIF VAL > 10000 THEN V_RET := ‘9’;
END IF;
RETURN V_RET;
EXCEPTION
WHEN OTHERS THEN
Null;
END;
()
Then create an object at the universe level, say called ‘Count_1’ with the following select statement:
SUM(DECODE(sf_categorise(), 1, 1, 0))
Then another object, say ‘Count_2’ like the following
SUM(DECODE(sf_categorise(), 2, 1, 0))
and so on…
Does this help?
Brian Patterson
Listserv Archives (BOB member since 2002-06-25)