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

[Patd]

Greetings,

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

Regards,

Pat

[Stan Mathews]

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.

[Davidk]

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

[MikeC]

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.

[Keith Hubert]

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.

[Patd]

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

[Keith Hubert]

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.

[MikeC]

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. :)

[Patd]

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

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.

[Patd]

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

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. :)

[Patd]

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

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.

[Davidk]

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...

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

[Patd]

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

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...

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