Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

2nd post: opening datasets on loading reports

edited September 2002 in End User
After a minor hiccup last week I now have my ASTA 2.06 and RB 6.03 project
working well in terms of performance and report production. I now have just
2 issues on loading reports from the explorer to solve. I have already
posted 1 question but didnt receive any information so I thought I would
repost and ask 1 other question.

Q1. The report explorer appears to need to read all items from the rbItems
table when it is opened including the blob field that holds the report
definition. As I am using ASTA I would prefer to extract only the none blob
fields initially and then when a report is required to be run from the
explorer fetch the blob data then. Is this possible?

Q2. When a report is loaded either from the Report Explorer or from a file
RB
opens the dataset, this is only to display the designer and not actually run
the report, even if the report is only previewed from the explorer the
dataset is opened once when the report is loaded and then reopened (and thus
re-executed) for the preview. This effectively means the query is executed
twice for every report. If this query returns a large result set this gives
a very slow performance. Can be minutes to just load a report. I am using
ASTA 2.6 and RB 6.03. Can something be done about this? Is it necessary for
RB to execute the query on loading a report? I have no objections to users
waiting several minutes for their report to print but just to load a report
is unacceptable to most people. ASTA does have the facility to restrict the
number of rows returned to its client dataset, is there a way I could modify
the Asta Dade such that it creates the dataset for loading the report with a
restricted result set and then removes this when the dataset is recreated
for the report execution, in other words is there a way to tell the
difference between these to events from inside the Dade?

Regards

Simon Callcott

Comments

  • edited September 2002
    There is the ASTA SQL options property which allows it to conditionally load
    BLOBs or not. However, the current report explorer does not support this
    functionality. With ASTA you might be able to get this working by modifying
    the source or using the Template.OnLoadStart event to add FetchBlobs to the
    SQL options set property. Haven't tried it, but it might work. This is a
    public event on the template that you'll have to assign in code.

    RB should not execute a query when a report is loaded. We'll have to
    install ASTA 2.6 and test it to see if we get the same behavior as you are
    experiencing.


    Cheers,

    Jim Bennett
    Digital Metaphors

  • edited September 2002
    Thanks Jim,

    I am familar with the FetchBlobs in the ASTA SQL options but not the
    Template.OnLoadStart. Where can I find the Template object for the report
    explorer. Sorry for my ignorance. If possible I would rather avoid modifying
    the explorer source directly if possible, but if I only have to assign code
    to an event that should be OK.

    I can confirm that the query is being run on loading the report, either from
    a file or from the explorer. The ASTA server shows me all SQL statements
    executed and the reports query is executed just going to the designer. What
    functionaility of the Dade should be executed on loading a report? I could
    then check using my line profiler to see what functions are actually called.

    Best Regards

    Simon Callcott

  • edited September 2002
    See the Templates thread in our TechTips newsgroup for info on using
    template events.


    Cheers,

    Jim Bennett
    Digital Metaphors

  • edited September 2002
    We'll have t research why it is firing it for ASTA. For the other databases
    we connect to, it seems to work ok. We'll let you know how our test goes.


    Cheers,

    Jim Bennett
    Digital Metaphors

  • edited September 2002
    Jim,

    I have found the code in daAsta that is causing the SQL to be executed as
    the report is loaded,
    procedure TdaAstaQueryDataView.Loaded;
    begin
    inherited Loaded;
    { TODO -ochris -cGeneral : Is this definitely necessary? }
    SQLChanged; <<<< --- THIS LINE DOES IT.
    end;

    In the Loaded event the SQLChanged function is called, this sets the
    astaclientdatasets created for the dataview to the magicSQL for the report
    and opens them. I have removed it and the reports seem to work fine, so as
    the comment above suggests it looks as if this is unnecessary. Do you see
    any need for this call?

    Best Regards

    Simon Callcott

  • edited September 2002
    Good work! Sure, take it out. It is calling OpenDataset and that causes the
    query to be set to Active = True, which you definitely do not want happening
    with this particular timing. Looks like someone needed this call for their
    own good reasons, but obviously you have no need to refresh the query. That
    should be the fix as SQLChanged should be called by the ancestor dataview
    classe when the dataview is out of sync or the sql is set on it. Those
    should be the only two times in which that routine should be called.


    Cheers,

    Jim Bennett
    Digital Metaphors

This discussion has been closed.