×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Retrieve the same period for mulitple years

Retrieve the same period for mulitple years

Retrieve the same period for mulitple years

(OP)
I have a simple report which pulls back sales and associated data for two user specified periods.

It is currently set-up with 4 date parameters - a start date and end date for each period:

{daily_summary.summary_date} in {?Start Date} to {?End Date} or
{daily_summary.summary_date} in {?Start Date 2} to {?End Date 2}

However, I would like to know if I can simplify the date entries so that I can specify a start date and end date, without including the year, so that I can retrieve multiple years for easy comparison.

Obviously I know I could use a cross tab report for simple data, but that isn't suitable for this report.

Is there a way of entering simply 2 dates - say 1st January - 20th January - and getting the report to pull back all records within those dates for ANY year?

Thanks in anticipation.

RE: Retrieve the same period for mulitple years

Having the user enter dates that do not include the year would mean the parameters would need to be entered as strings, which is inefficient because of the processing required to convert them to "real" dates. A better approach would be to have them enter a a start date and end date and then add the code to the record selection formula to automatically include the previous year.

There are multiple ways to do this. I would use the following:

CODE

{daily_summary.summary_date} in [{?Start Date} to {?End Date}] or
{daily_summary.summary_date} in [Date(Year({?Start Date})-1, Month({?Start Date}), Day({?Start Date})) to Date(Year({?End Date})-1, Month({?End Date}), Day({?End Date}))] 

Hope this helps.

Cheers
Pete

RE: Retrieve the same period for mulitple years

(OP)
Thanks Pete

That works a treat!

Is there a simple was to use another parameter to allow the user to enter the number of earlier years to show on the report and use this somehow in the formula?

I've tried a few ideas, but without success..... :-/

Thanks in anticipation.

Peter

RE: Retrieve the same period for mulitple years

Haven't tested this but I think it will work, and the approach is limited in that it could only ever cater for a pre-specified and finite number of previous years, but this should work. Assuming you wanted to allow for 5 previous years:

CODE

(	
	{?Years} >= 0 and
	{daily_summary.summary_date} in [{?Start Date} to {?End Date}]
)
or
(
	{?Years} > 0 and
	{daily_summary.summary_date} in [Date(Year({?Start Date})-1, Month({?Start Date}), Day({?Start Date})) to Date(Year({?End Date})-1, Month({?End Date}), Day({?End Date}))] 
)
or
(
	{?Years} > 1 and
	{daily_summary.summary_date} in [Date(Year({?Start Date})-2, Month({?Start Date}), Day({?Start Date})) to Date(Year({?End Date})-2, Month({?End Date}), Day({?End Date}))] 
)
or
(
	{?Years} > 2 and
	{daily_summary.summary_date} in [Date(Year({?Start Date})-3, Month({?Start Date}), Day({?Start Date})) to Date(Year({?End Date})-3, Month({?End Date}), Day({?End Date}))] 
)
or
(
	{?Years} > 3 and
	{daily_summary.summary_date} in [Date(Year({?Start Date})-4, Month({?Start Date}), Day({?Start Date})) to Date(Year({?End Date})-4, Month({?End Date}), Day({?End Date}))] 
)
or
(
	{?Years} > 4 and
	{daily_summary.summary_date} in [Date(Year({?Start Date})-5, Month({?Start Date}), Day({?Start Date})) to Date(Year({?End Date})-5, Month({?End Date}), Day({?End Date}))] 
) 

For every extra prior year you want to be able to accommodate just include another or statement.

There is probably a better way to do it but this is what springs to mind.

Hope it helps.

Cheers
Pete

RE: Retrieve the same period for mulitple years

So .... did that solve your problem or not?

Please provide feedback so that this thread can become more useful to others who may be having similar challenges.

Pete

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close