Re: Discoverer Plus Reporting values associated with Max values

From: RyanWirth <ryandwirth_at_hotmail.com>
Date: 11 Feb 2003 12:11:47 -0800
Message-ID: <911543fb.0302111211.6bcb5633_at_posting.google.com>


This is an easy problem for SQL, but not so easy in discoverer as there is no way to perform the subqueries or summary filters needed in the front end. So the change needs to be done in the BA as a custom SQL folder where the sql command would look something like this: SELECT * FROM Sales sales1
WHERE (date=(select max(date) from Sales sales2

             where sales2.SalesID=sales1.SalesID
             GROUP BY SalesID))

ORDER BY SalesID;

Now in the BA you will have a folder with the custom filter you want, and you can join this to Sales People there (in Administrator). Then when you go to your discoverer plus web based front end, you can select anything from Sales People, and anything from sales, and be left with just the most current sales you required.

ryandwirth_at_hotmail.com (RyanWirth) wrote in message news:<911543fb.0302051440.3f7472d9_at_posting.google.com>...
> Discoverer Plus Reporting values associated with Max values. This
> little problem has been bothering me for a while. Hypothetically, if
> I had two tables, Sales People (with SalesPersonID, Name) related in a
> one-to-many relationship with another table, Sales (with SalesID,
> Date, Amount, SalesPersonID). This is all set up nice in the EUL and
> BA, and I can get all the info fine in a report:
>
> Name ID SalesID Date Amount
> Ryan 1 1 01-01-01 30.00
> 2 02-01-01 50.00
> 3 03-01-01 25.00
>
> But if I want to know what Ryans last sales amount is (eg.
> SalesPersonID, Name, MAX Date, Amount) discoverer cannot show me just
> the last amount, it shows all amounts and all dates. I can get just
> the Max date, but as soon as I pull in the amount the Max no longer
> applies. Is this a problem with my BA? Am I missing something?
> Thanks for any help,
> Ryan
Received on Tue Feb 11 2003 - 21:11:47 CET

Original text of this message