Problem with setting the OrderBy in SQL

rbuser

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

digitalmetaphors

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

rbuser

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

digitalmetaphors

> > 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

rbuser

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

digitalmetaphors

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

rbuser

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

digitalmetaphors

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