Re: MEDIAN in Oracle Discoverer

Let me rephrase the question: can it be done?!?

(see below)

> Hi there!
>
> Does anyone know how to solve this problem: I need to make a Discoverer
> report containing the median age of people matching certain criteria, say
I
> need the number of graduates graduated 1999, 2000 and 2001 as well as
their
> median age, sorted by their majoring subject. I can easily enough write a
> normal SQL-query to find the middle element in a sorted list, but with
> Discoverer it isn't that easy, as the positioning of the axis elements,
the
> conditions used etc all affect the set of data from which the median age
is
> to be selected. I would like the solution to be easy on the end user, so
the
> optimal solution would be to have a drag-and-droppable object defined on
the
> EUL level, as opposed to having a report-specific calculated item defined
on
> Discoverer User-level.
>
> All my options, as I see it, are:
>
> - a calculated item (how should I define the set of ages to pick the
median
> from?) on Discoverer User level.
> - a PL/SQL-function (what parameters should it have?), called from a
> calculated item in Discoverer User Edition or from a EUL level item
defined
> in the Admin Edition
> - a MEDIAN-Item in the same EUL-folder that contains the people's ages
> (although, as it is out of context, defining the median might not be that
> easy **)
>
> None of these seem straightforward. I actually don't even know where to
> begin...
>
> How are the other available statistical functions (AVG etc) implemented?
Is
> there any reason why I couldn't implement median in a similar way?
>
> Any help appreciated,
>
> Kim Johnsson
>
> ** an idea I had was to define the median age item in the EUL folder as
the
> PL/SQL-selected median age divided by the actual item count. That way, all
> the individual items in the report would accumulate towards the final
median
> value, i.e. (MEDIAN / count) * count = MEDIAN. Don't know how to do that,
> though, as it needs a context...
Received on Sat Feb 02 2002 - 10:42:51 CET