Alpha Video Training
Results 1 to 13 of 13

Thread: How do you create a quarterly and yearly report using dates?

  1. #1
    Member
    Real Name
    Pat Domanski
    Join Date
    Jun 2009
    Posts
    79

    Default How do you create a quarterly and yearly report using dates?

    Greetings,

    How do you create a quarterly and yearly report using dates?

    Regards,

    Pat

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

    Default Re: How do you create a quarterly and yearly report using dates?

    Probably with groups. I would create a global variable on a form, numeric, to allow the user to specify a year

    If you create a group and make it break on the expression

    case(between(month(somedatefield),1,3) .and. year(somedatefield) = var->yearvariable,1,between(month(somedatefield),4,6) .and. year(somedatefield) = var->yearvariable,2,between(month(somedatefield),7,9) .and. year(somedatefield) = var->yearvariable,3,between(month(somedatefield),10,12) .and. year(somedatefield) = var->yearvariable,4)

    And make the report filter

    year(somedatefield) = var->yearfield

    You would get quarterly totals in the group footer and yearly totals in the report footer.
    There can be only one.

  3. #3
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default Re: How do you create a quarterly and yearly report using dates?

    There are so many ways to do this.

    I just finished one for my Membership database.

    For me, I just wanted the user to pick a from and to date so I created a form and placed two supercontrol calendars on it.

    I created two global variables... varRptFromDate and varRptToDate

    The two supercontrol calendars are "bound" to these variables.

    I placed a button on the form to run the report.

    In the report itself I specified a filter which filtered the records based on the variables for the dates.

    You could do somewhat the same, but on your form have a dropdown or radio buttons... something that the user can select a quarter or YTD from and maybe a dropdown for the user to select a year from.

    In the code to run your report you'd figure out the specific from and to dates from the choices the user made, set the variables and run the report.

    Like I said, so many ways to do this... this is just one.

    David

  4. #4
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: How do you create a quarterly and yearly report using dates?

    You also should specify whether you want this to appear vertically or horizontally on the report....if horizontal, then use calculated fields most likely with or without subreports dependent upon the scenario.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  5. #5
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: How do you create a quarterly and yearly report using dates?

    Hi Pat,

    Another method of filtering by date.

    If you have a date field in your table, create an ASK variable for the First Date and Last Date. When the report is run a dialog pops up and asks fro the two dates.

    This is done from right mouse click on the bar of the detail section, selecting Properties and the Records Tab.

    See thumbnail.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  6. #6
    Member
    Real Name
    Pat Domanski
    Join Date
    Jun 2009
    Posts
    79

    Default Re: How do you create a quarterly and yearly report using dates?

    Greetings,

    I thank you all for your responses. Would you happen to know if their are any sample files of a quarterly/yearly report that is on this message board that I could look at? Again, thank you.

    Regards,

    Pat

  7. #7
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: How do you create a quarterly and yearly report using dates?

    Pat,

    We all like to help when someone is stuck on a routine or some code but, I do think it is time for you to get your hands dirty.

    As shown there are many ways to do what you want, I suggest you have a good play with what has been shown to you so far and test the results.

    You will end with the desired result. What ever you do that works will be a good solution. It may be that another developer would have done it differently and possibly in a more logical way. That really does not matter, if it works the way you want and you have learnt from it then you have got the knowledge go forward.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  8. #8
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: How do you create a quarterly and yearly report using dates?

    I know there are some examples both of what can be done and actual samples of what you want attached to messages.....search the board first for these if a sample is absolutely necessary for you.

    ....and I suggest that if you (or anyone reading) are seriously trying to learn Alpha, especially xbasic, to glean everything you can from the messageboard. There is a wealth of information here with actual samples given.

    I remember just going through the messageboard and downloading every sample I ran across and putting them into my own little help file for future use---literally hundreds of them--took quite a few hours just to grab them. I have done so ever since when someone posts a sample that shows how to do something.....same for text files of code snippets the users here so generously contribute to this knowledgebase.....it sure saves time in the end by mostly only having to search my own files than the messageboard (which is a bit difficult to learn how as well).

    But when you do have a problem that you cannot resolve, definitely ask here---I did not want to deter you or anyone from doing so. :)
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  9. #9
    Member
    Real Name
    Pat Domanski
    Join Date
    Jun 2009
    Posts
    79

    Default Re: How do you create a quarterly and yearly report using dates?

    Hi Keith,

    Good point and I thank you. I shall give it a whirl tomorrow, and if I get stuck, I hope you do not mind me asking you possible, additional questions. Again, thank you.

    Regards,

    Pat



    Quote Originally Posted by Keith Hubert View Post
    Pat,

    We all like to help when someone is stuck on a routine or some code but, I do think it is time for you to get your hands dirty.

    As shown there are many ways to do what you want, I suggest you have a good play with what has been shown to you so far and test the results.

    You will end with the desired result. What ever you do that works will be a good solution. It may be that another developer would have done it differently and possibly in a more logical way. That really does not matter, if it works the way you want and you have learnt from it then you have got the knowledge go forward.

  10. #10
    Member
    Real Name
    Pat Domanski
    Join Date
    Jun 2009
    Posts
    79

    Default Re: How do you create a quarterly and yearly report using dates?

    Hello MikeC,

    Thanks for the suggestion. I am sure some time will be needed to review all of the messages, including the ones with samples. If I get stuck on a problem that I cannot resolve, I will ask for help here on this message board. Again, thank you.

    Regards,

    Pat



    Quote Originally Posted by MikeC View Post
    I know there are some examples both of what can be done and actual samples of what you want attached to messages.....search the board first for these if a sample is absolutely necessary for you.

    ....and I suggest that if you (or anyone reading) are seriously trying to learn Alpha, especially xbasic, to glean everything you can from the messageboard. There is a wealth of information here with actual samples given.

    I remember just going through the messageboard and downloading every sample I ran across and putting them into my own little help file for future use---literally hundreds of them--took quite a few hours just to grab them. I have done so ever since when someone posts a sample that shows how to do something.....same for text files of code snippets the users here so generously contribute to this knowledgebase.....it sure saves time in the end by mostly only having to search my own files than the messageboard (which is a bit difficult to learn how as well).

    But when you do have a problem that you cannot resolve, definitely ask here---I did not want to deter you or anyone from doing so. :)

  11. #11
    Member
    Real Name
    Pat Domanski
    Join Date
    Jun 2009
    Posts
    79

    Default Re: How do you create a quarterly and yearly report using dates?

    Hi Keith,

    I have tried what you have mentioned about the ASK variable, but for some reason it keeps bringing up the other records. The pop-up screen shows up for the First Date/Last Date.

    That part works, but when I enter the first date of 4/6/2010 and the last date of 4/20/2010 it shows up on the report, but it also shows the other record on the report dated for 6/9/2010 to 6/16/2010.

    What do I need to do to only show a specific date range on the report? I have attached the ZIP file so you may see the result I get. I thank you for your help.

    Regards,

    Pat



    Quote Originally Posted by Keith Hubert View Post
    Hi Pat,

    Another method of filtering by date.

    If you have a date field in your table, create an ASK variable for the First Date and Last Date. When the report is run a dialog pops up and asks fro the two dates.

    This is done from right mouse click on the bar of the detail section, selecting Properties and the Records Tab.

    See thumbnail.

  12. #12
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default Re: How do you create a quarterly and yearly report using dates?

    Hey Pat,

    You have your Ask variables defined in the Order Section.

    Put them in the Filter section (just above the Order) and I think you want the filter something like this...

    Code:
    Start_Date>=Var->askD_Get_First_Date .and. Start_Date <= Var->askD_Get_Last_Date
    This goes after records with a Start_Date between two input variables.

    David

  13. #13
    Member
    Real Name
    Pat Domanski
    Join Date
    Jun 2009
    Posts
    79

    Default Re: How do you create a quarterly and yearly report using dates?

    Hi David,

    I thank you for your help and it worked! I also noticed what I was missing in the string too. Again, thank you.

    Regards,

    Pat



    Quote Originally Posted by Davidk View Post
    Hey Pat,

    You have your Ask variables defined in the Order Section.

    Put them in the Filter section (just above the Order) and I think you want the filter something like this...

    Code:
    Start_Date>=Var->askD_Get_First_Date .and. Start_Date <= Var->askD_Get_Last_Date
    This goes after records with a Start_Date between two input variables.

    David

Similar Threads

  1. What is the formula to create a monthly and yearly report?
    By Patd in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 06-22-2009, 10:00 AM
  2. quarterly report assigned
    By nehru in forum Alpha Five Version 5
    Replies: 2
    Last Post: 05-24-2004, 12:19 AM
  3. Report Yearly Total
    By Ed Barley in forum Alpha Five Version 5
    Replies: 3
    Last Post: 03-24-2004, 06:53 AM
  4. create/modify dates
    By dik_coleman in forum Alpha Five Version 5
    Replies: 8
    Last Post: 12-13-2003, 11:41 AM
  5. Quarterly report
    By Mortie in forum Alpha Five Version 5
    Replies: 3
    Last Post: 04-07-2003, 02:11 AM

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
  •