Alpha Video Training
Results 1 to 13 of 13

Thread: Report sumarized by month ???

  1. #1
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default Report sumarized by month ???

    Hi all you report wizards...

    I have a set of records with costs on various dates. 10 or so dates per month. I have a report showing date, title, cost, with overall total cost. This is filtered by two variables that set start and end dates.

    How do I group the report so the dates are grouped by month with total cost for each month?

    Marc
    www.a5solutions.com

  2. #2
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Report sumarized by month ???

    Never mind. Figured it out.

    Break on cmonth(datefield) and add cmonth(datefield) to a calc field on the report to give the month name instead of a date.

    Cheers


    Marc
    www.a5solutions.com

  3. #3
    Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,765

    Default RE: Report sumarized by month ???

    You got it!

    Now, I've got another one for you. How do you do the same thing, but group by week, instead of by month?

    Gary

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default RE: Report sumarized by month ???

    Gary,

    Define "week"

    -- tom

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Report sumarized by month ???

    floor((date() - ctod("01/01/"+year(date())))/7)

    would give you a "week" number for each day in each seven day period measured from the first day of the year.

  6. #6
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Report sumarized by month ???

    If you are looking at records with a field called invoice_date, you could break on:
    dtos(invoice_date + 7-dow(invoice_date))
    This should group and break on all records between Sunday and Saturday of a week.

  7. #7
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,627

    Default RE: Report sumarized by month ???

    I use the following for a printable monthly calendar, where the days of the week are populated using an expression.

    INT(mrecno()/7-.01)

  8. #8
    Member
    Real Name
    Malcolm Harison
    Join Date
    May 2012
    Location
    Whakatane, New Zealand
    Posts
    162

    Default Re: Report sumarized by month ???

    Thanks guys, this thread really helped me out. Malcolm

  9. #9
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,561

    Default Re: Report sumarized by month ???

    Quote Originally Posted by Malcolm View Post
    Thanks guys, this thread really helped me out. Malcolm
    Malcolm

    You get the prize of the day for using the search and finding a thread.

    Good job!

    Check is in the mail.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    657

    Default Re: Report sumarized by month ???

    ? what happens if 'datefield' spans multiple years?

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report sumarized by month ???

    What happens to what?
    There can be only one.

  12. #12
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    657

    Default Re: Report sumarized by month ???

    Hi Stan...
    I was asking about Marc's solution:
    "Break on cmonth(datefield) and add cmonth(datefield) to a calc field on the report to give the month name instead of a date."
    It appeared to me that if the query spanned years the break would not work.

    Your suggestion: floor((date() - ctod("01/01/"+year(date())))/7) works fine.

  13. #13
    Member
    Real Name
    Jon Moody
    Join Date
    Sep 2010
    Location
    Brackley, UK
    Posts
    222

    Default Re: Report sumarized by month ???

    To break by year+month I usually use left(cdate(<date_value>),6)
    To break by week why not use week_iso(<date_value>) this runs Monday to Sunday (the international standard)

Similar Threads

  1. End of Month Report
    By Ben Maldonado in forum Alpha Five Version 6
    Replies: 1
    Last Post: 10-18-2005, 10:21 AM
  2. Month-at-a-Glance on Report
    By Gary Biscella in forum Alpha Five Version 6
    Replies: 4
    Last Post: 03-24-2005, 06:29 AM
  3. Last day of the month
    By George Corder in forum Alpha Five Version 5
    Replies: 7
    Last Post: 05-28-2004, 03:33 PM
  4. WEEKLY REPORT IN A MONTH OR IN A YEAR
    By nehru in forum Alpha Five Version 5
    Replies: 0
    Last Post: 05-19-2004, 09:39 PM
  5. Report Group by Month
    By Greg Ojeda in forum Alpha Five Version 5
    Replies: 3
    Last Post: 11-06-2002, 08:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •