Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!newscon02.news.prodigy.com!prodigy.com!news.stealth.net!news.cc.tut.fi!uutiset.saunalahti.fi!not-for-mail
From: "Kim Johnsson" <kim.johnsson@ineo.fi>
Newsgroups: comp.databases.oracle.tools
Subject: Re: MEDIAN in Oracle Discoverer
Date: Sat, 2 Feb 2002 11:42:51 +0200
Organization: SAUNALAHDEN asiakas
Lines: 59
Message-ID: <a3gc38$sio$1@tron.sci.fi>
References: <a3b5j6$idv$1@tron.sci.fi>
NNTP-Posting-Host: dyn-i05d.baana.weppi.fi
X-Trace: tron.sci.fi 1012642729 29272 212.83.99.93 (2 Feb 2002 09:38:49 GMT)
X-Complaints-To: newsmaster@saunalahti.fi
NNTP-Posting-Date: 2 Feb 2002 09:38:49 GMT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Xref: easynews comp.databases.oracle.tools:48414
X-Received-Date: Sat, 02 Feb 2002 02:37:42 MST (news.easynews.com)


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...