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

Sorting on calc field question

edited November 2002 in End User
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

Comments

  • edited December 2002
    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

  • edited December 2002
    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


  • edited December 2002
    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

  • edited December 2002
    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


  • edited December 2002
    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

This discussion has been closed.