linking queries and datasources

rbuser

Using RB 5.53, Delphi Enterprise 5 on an Oracle db . I have the RB
upgrade, but hesitant to update this close to the finish line of this
report.

Tried linking pipelines but discovered that I have too much data for that to
work effectively. The report is data intensive, looking and doing
calculations on thousands of records. The linking of the dbpiplines might
have worked but got tired of waiting the hours for it to finish. The
alternate solutions runs in about 3-5 minutes, with one slight problem.

I created a Datasource_main ,Query_main, and DBPipeline_main. This pipeline
controls the detail line of my report. With one exception, a second query,
Query_data, after making some checks of Query_data, if no data is useful I
turn off the detail line and move to the next record in Query_main. This
works ok.

Query_data points at datasource_main and has a parameter by the same name as
in Query_main. What I read, and thought would happen, is that Query_data
would be modified/updated each time Query_main went to a new record. This
is true 99% of the time.

Finally the problem: Query_data information is referenced in the
OnTransversal for the dbpipeline_main procedure. Everything seems to work
fine most of the time. As the report traverses each record in Query_main,
Query_data is updated and the new information is available in the
OnTransversal procedure. Except when a group break and page break occur at
the same time. Then Query_data uses the old information (parameter) from
Query_main. I have monitored this with the SQL Monitor and watch Query_main
go to the next record and then backup to the previous record, WHAT IS
HAPPENING? Is there a way around this? Maybe some help on controlling page
breaks?

I talked to Nard back in February/March timeframe and this was the best
solution I could come up with. I actually thought it was working. As I
said, a couple of factors have to come into play before you see the problem.
If you only process one group at a time no problem. If you select more than
one group, but the groups don't exactly break at the bottom of the page,
everything is ok. It is only when the last detail line of a group is the
last detail line on the page. I mention talking to Nard back in February
because I have designed, and redesigned this report many times. I think I
have tried about every solution to get an accurate timely report and this is
the closest I have come.

Any suggestions?

digitalmetaphors

The report engine uses bookmarks in the dataset. This is a result of the
engine moving between records in order to generate a band at the bottom of
the page, but it won't fit, so it generates on the next page. The current
record does move forward, then backward, as you describe. The OnTraversal
event may fire more than once for a single record in the dataset. We don't
reccomend using the OnTraversal to perform calculations, because it may fire
more often, usually when groups are in the report.

Sounds like you could create a calculated field in your master query and
then only search for master records where the calculation returns a useful
value. This would eliminate the detail records from getting pulled from the
db.

Delphi TQuery linking is quite slow. DADE visual linking performs one query
on the dataview so that all records are returned for all of the data for the
dataview. This means that we don't have to send a query for every detail
dataset, like Dephi query linking does, as master records are traversed.
Could you use DADE linking?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Your suggestion of creating a calculated field in the master query will not
work.
I also use sub-strings in the queries and didn't find a way in DADE to use
sub-strings.

Your first paragraph was the most interesting. I have a report that works,
for the most part. I haven't been able to get across the complexity, size,
and number of tables and data I am working with, The main query will
select 200+ records. Based on each of those records, I will go and retrieve
thousands more records from many different tables. Then I will perform
several calculations based on information and numbers (if it's an 'A' add
this number, if it's a 'B" subtract this number, if the last record was a 'C
' then add..and so on) When all those numbers for that detail line are
processed then decide, do I want to print this detail line, yes or no. I
tried doing all of this in an OnCalc but it didn't work. I concluded that
by the time an OnCalc was performed it was to late to set the visible=false
for the detail line.

If you could give me some more insight to controlling the page break or
knowing when the query will move forward and then backward I could write the
logic to handle the few times that it happens in the report.

Thanks, you and Nard have been a great help.

digitalmetaphors

The variable's OnCalc is too late to set visible = false for the band.

When a dynamic height band attempts to generate, it will do so on the
current page. If the band does not fit on the page, then it will try to
print on the next page, refiring the before print event.

On the data traversal side, the pipeline will traverse through the records
until a new group value is found, then it backs up to the previous record so
that the current record is the last record for that group. The group footer
is then told that it should print. Once the group footer prints, then the
datapipeline moves to the next record, which is the first record for the
next group. So, when the last detail of a group fits on a page, and the
group footer doesn't fit on the current page, the page break must be causing
the datapipeline to move to the next record, but since the group footer
never printed, it must back up to the previous record again. Which fires
the OnTraversal one extra time. I don't think there is a way around keeping
this from firing.

You need to detect if the last detail band for a group has printed and there
is not enough space left on the page to print the group footer. Is your
group footer a static height band? If it is, then you can use the before
print event of the group footer band to determine the current printing
position on the page and if there is enough room on the page to print the
group footer (taking into account the page footer as well). I think if you
know that this boolean flag condition exists, you can conditionally bypass
some code inside of the OnTraversal event of the pipeline. Reset this flag
after the next group header prints, since the afterprint event should fire
only once when the band fits on the page.

You can specify a substring as search criteria on the DADE dataview with the
'Like' or 'Not Like' operator. Then just type in your expression. Is this
what you mean?


Cheers,

Jim Bennett
Digital Metaphors