Top 10 records

rbuser

Hi,

We are trying to create an (end-user) report that shows top 10 records on a
sub-report. We need Autosearch criteria on the report. so we can't edit the
SQL.

We use RB 6.03
Delphi 5
DOA

Basically we need to add the criteria " where rownum<11" (rownum is an
Oracle variable, not a column).

We have tried two approaches:
#1. Trying to modify the lSQL.SQLText from RAP by extending the RTTI. No
success since there is no good event to hook this onto (Datasets seems to
have been opened before all the events). This would be useful for all the
other missing features of Query Designer as well.

#2. We have published TOracleQuery in RAP, so we can run subqueries in RAP
events. By using this we can get the Query right, but haven't found a way to
Print a band from RAP. eg:
MyQ.close;
MyQ.SQL.clear;
MyQ.SQL.Add(' select price,itemname from stock_items ');
MyQ.SQL.Add(' where rownum<11 ');
MyQ.SQL.Add(' order by price desc');
MyQ.Execute;
while not MyQ.eof do
begin
LabelPrice.caption := MQ.FieldAsString('price');
LabelItemName.caption := MQ.FieldAsString('itemname');
SubReport1.report.detail.print;
MyQ.Next;
end;

Any help/suggestions would be appreciated.

Paul Wiik,
Lynx Technologies.

rbuser

BTW: I've seen the other posts on Top-N and Bottom-N records. They suggest
using Properties of the DataPipeline or an SQL function.
I don't think I can use this because the top ten that I'm "filtering" are a
expression that gathers data from two tables. Also, it's vital for us to add
this functionality to end-user reporting.
Here is an example outlining what I want to do. The example is related to
Contracts and Contract variations/extensions:

select con.contract_id, con.contract_title,
(con.original_contract_value +
(select sum(approved_value)
from contract_variations "cv"
where cv.status='Approved'
and cv.contract_id=con.contract_id
)
) "total_sum"
from contracts "con"
where con.contract_date>=to_date('01.01.2003','dd.mm.yyyy')
and con.contract_date<to_date('31.12.2003','dd.mm.yyyy')
and rownum<11
order by total_sum desc

This would return the top 10 contract values (including additional sums from
variations).

Paul Wiik,
Lynx Technologies.

digitalmetaphors

You have to order by the field that you want the TopTen to be based on. Then
you can set the Report.Datapipeline.RangeBegin, RangeEnd and RangeEndCount
properties. That should work if the first ten records in the dataset are the
top ten that should print. Since you are loading templates, use the
Report.Template.OnLoadEnd public event to configure the report datapipeline.

Another way to do it is to build a temporary table on the data to select and
include only the top ten records. Then you don't have to do anything but
connect the report to it and print the ten record dataset.

If you have to select a record from another table, then another way is to
fire another query at runtime to get the criteria that the top ten datatset
should be filtered on. Then you don't have to include the first dataset's
records, just include the single record's criteria value in the where clause
to build a dataset that can be ordered such that the first ten records are
the top ten. This would require extracting the TdaSQL object and
mnaipulating the criteria object's value. The latest ExtractSQLObject tip
shows how to change the criteria object, which in your case is also an
autosearch criteria.
http://www.digital-metaphors.com/tips/ExtractSQLObject.zip



Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thanks,

I managed to solve it with the RangeEnd and RangeEndcount.
I set them from RAP code, on onPrint of the Subreport where the Pipeline is
used, and it works perfectly fine.

Paul Wiik,
Lynx Technologies.