Sorting on calc field question

rbuser

Hi,

I have the following scenario. I need to create a report which will
calculate whether a date occurs on a weekday, Saturday , or Sunday and then
group totals by these. Therefore I need something similar to the following:

Weekday 15.00
Saturday 12.00
Sunday 16.00

where the individual records consist of particular dates

I have been able to calculate the weekday, sat or sun from the dates, but
how do I then use this calc field to then sort and group into my result. I
have been able to use the group feature to group a calculated field, but
this is of no real use when you cant sort it.

Am I following the right track or is there an alternative solution I should
try.

We are eusing ReportBuilder 6.x and it has to be achieved using end-user
facility.

Thanks

Alex

digitalmetaphors

You should be able to add a calc field to sort by. What version are you
using? I created such a query today usign RB 6.03. There was an old issue
in an old version, from what I remember, where a linked dataview which had
two calc fields on the same field caused a problem because the linking logic
removed one of the calc fields. If you are using RB 6.03 or later you
should be fine. Check the Help | About menu from the report sesigner to see
what version you have installed.

If you can reproduce the problem in RB 7, then send us an example to
support@digital-metaphors.com


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

How would I go about sorting this? I have a variable field which has
calculations. How do I know go about using this as my sort sequence so I
can group on this variable field?

Thanks

Alex

digitalmetaphors

You should see the calculated field in the available sort fields list in the
sort page of the query designer. Choose that and the generated SQL should
include the calc field in the ORDER BY clause. Does it not do this? Can you
reproduce this using DBDEMOS? Can you send an example report template
connecting to DBDEMOS and this way I can look at it to see what is not
working. Send it to support@digital-metaphors.com


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

I can send a sample but I think you might be misunderstanding me. The
calculated field is not an SQL calculated field but a TppVariable. There is
no way to create an SQL statement that will adequately calculate what I
require, so a variable has been used. As you can see in my example, the
resultant calculation from a given date will be Weekday. Saturday and
Sunday. I need to sort and group by those descriptions.

If I am misunderstanding you, I'm sorry, but I think I am right in assuming
you cant put a TppVariable in an SQL statement. If I am right, is there any
solution as far as an end-user is concerned?

Thanks

Alex

digitalmetaphors

I misunderstood. I thought you had a calc field on the dataset, not a
TppVariable. Groups in RB with only work as desired when the data is
ordered for the group objects. If you want to sort the data, then you have
to create a calculated field in SQL so that you can order the data by that
calculation. This is the way it has to work if you want to order by Weekday,
Saturday and Sunday.

If you can't perform the calculation on the dataset, then an alternative is
to use an in-memory dataset (surf Torry's Delphi pages). This will allow you
to do some preprocessing of the data. You can build an in memory dataset
(table) that has the data with your new Weekday, Saturday and Sunday
calculations. Then you can read the data from that table in the order that
you want, because you've added the new calculation as a field in the memory
table. The only other way I can think of is to use a JITPipeline to do
something similar to the in memory table where you can preprocess the data
before the report runs.


Cheers,

Jim Bennett
Digital Metaphors