Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retrieve the same period for mulitple years

Status
Not open for further replies.
Apr 11, 2008
68
GB
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.
 
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

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

 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top