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

Problem with setting the OrderBy in SQL

edited March 2003 in End User
I'm using a from om which the user can determine the OrderBy of the
report. Based on the value he enters with a combobox the following code
is executed (when a certain button is pressed)

case dspSortering.EditValue of
0 : CreateOrderBy(repCatalogus, 'BoekSoort;Barcode');
1 : CreateOrderBy(repCatalogus, 'BoekSoort;Titel');
2 : CreateOrderBy(repCatalogus, 'BoekSoort;Achternaam;VoorLetters');
end;
with repCatalogus do
begin
DeviceType := dtScreen;
Print;
end;

The CreateOrderBy procedure is a somewhat extended version of the procedure
that is used is the
http://www.digital-metaphors.com/tips/ExtractSQLObject.zip
example.

In my form the following occurs: The first time my code is executed
everything works
fine. The previewer is showing the message that the data are retrieved and
after that
the result is displayed on the screen.
When the OrderBy is changed by the user and the PreviewReport button is
pressed
again the result is not changed. The data of the previous OrderBy are
presented on
the screen. Also the time that is needed to present the report is remarkably
fast.
It seems that the data from the previous preview are cached and shown on the
screen, in stead of the newly sorted data.
Am I doing something wrong or is there a way to force the report to refresh
the
result when the OrderBy is changed?

Thanks,

Arjen Korpershoek

Comments

  • edited March 2003
    Did you clear the previous order by fields defined in the report SQL object?
    Call TdaSQL.ClearOrderByFields. Have you checked the generated SQL in the
    Report.BeforePrint event that the OrderBy is correct?

    If you change the order by, make sure the any groups defined in the report
    also change to reflect the new ordering of the data.

    You can call Report.Reset to force the report to clear its page cache.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited March 2003
    Jim,

    object?
    Yes, just like in the
    http://www.digital-metaphors.com/tips/ExtractSQLObject.zip
    the ClearOrderByFields is executed

    The generated SQL is correct

    Can you tell me what you mean by this? Im my report indeed I'm using a
    group.
    Do you have an example?

    Calling Reset doesn't seem to help much. When executing the report the
    second
    time with a different OrderBy doesn't change the result. The cached data
    seem
    not to have been cleared, because the previewing is very fast.

    Thanks,

    Arjen
  • edited March 2003
    > > If you change the order by, make sure the any groups defined in the
    report

    No, there is no example. Essentially, you can access the Report.Groups array
    property to get at the first and only group object at runtime. Then you can
    change the TppGroup.BreakName property. See the help file for more info in
    TppGroup and its members. That should do the trick, in order to match the
    ordered data to the report group definition.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited March 2003
    Jim,

    I'm sorry to say it, but I still don't exactly know what you mean
    changing the TppGroup.Breakname. Can you please provide me
    with a small code snippet to clarify things?
    I'm not that experienced with RB to do it myself.

    Arjen Korpershoek

  • edited March 2003
    Create a group in the report for the default OrderBy of the data and
    configure the group how you want, ie, NewPage, KeepTogether.... Then make a
    simple statement like this after you change the OrderBy clause of the query:

    if True then
    ppReport1.Groups[0].BreakName := 'AmountPaid'
    else
    ppReport1.Groups[0].BreakName := 'CustNo';

    ppReport1.Print;

    If you want to change the group header based on which group the repor tis
    using, then use two region components in the group header band to toggle the
    visibility of them in order to show only one of them based on the order by
    that the group is based on. Check the ppReport1.Groups[0].BreakName value to
    toggle this visibility in the GroupHeaderBand.BeforePrint event.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited March 2003
    Jim,

    Unfortunately, setting the Group.BreakName doesn't help.

    Adding some more information on the group and order by I use the
    following: The group has nothing to do with the order by in my report.
    Within the group I want the data to be ordered in different ways, in other
    words, the group is always the same, doesn't change.

    Best Reards,

    Arjen Korpershoek


  • edited March 2003
    I misunderstood. Add another ORDER BY criteria in order to change the order
    inside the group. You're right, in this case the groups should remain based
    on the same break names as before.


    Cheers,

    Jim Bennett
    Digital Metaphors


This discussion has been closed.