Retrieve the same period for mulitple years

[petejigsaw]

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.

 

[pmax9999]

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:

{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

 

[petejigsaw]

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

 

[pmax9999]

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:

(	
	{?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

 

[pmax9999]

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