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

Group by Month(Date)

edited September 2001 in General
I have not been using Report Builder in a while, and now I am back making a
report and I am not quite sure if I can easily group by the month of a date
field.

For example:

If I have 9/13/2001 in record 1 and 9/14/2001 in record 2 then I want to
have the group be September and list both dates.

The normal grouping is putting 9/13/2001 as a group date and 9/14/2001 as
another group header.

Any ideas?

Douglas

Comments

  • edited September 2001
    You should be able to create a calculated field on the dataset, also sort
    the data by that field , and then base the report group on that field value.
    Usually there is a Month function you can use on your database when you
    submit the query.

    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited September 2001
    Could you give me a quick example if it is possible to do it with SQL only.

    I am using Advantage Database TQuery component and I use the same component
    for all reports, I just write the SQL differently. So if I can do the
    calculated field somehow in SQL then that would be great, but I am not sure
    how to do it the regular OnCalc method in order to use it for everything.

    Douglas


  • edited September 2001
    Consult the advantage documentation on the sql syntax, but it would be
    something like:

    SELECT Employee.Name,
    Employee.HireDate,
    Extract(Month From Employee.HireDate) AS Extract_Month
    FROM Employee


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited September 2001
    Hi Douglas,

    I think this has been answered in Advantage's NGs.

    regards,
    Chris Ueberall;
  • edited September 2001
    Yes, It was answered to me on the Advantage NGs. This is the answer for
    those interested.

    SELECT a.Fullname, a.Phone, a.BirthDate, Month(a.BirthDate) as MonthDate
    FROM addressbook a
    GROUP By Month(a.BirthDate), a.Birthdate, a.Fullname, a.Phone

    Then in the report I group by MonthDate.

    Thanks for all of your help guys, I do appreciate it.

    Douglas

This discussion has been closed.