Changing 'Date Range' Functions
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8673
Printed Date: 06 May 2024 at 1:31am
Topic: Changing 'Date Range' Functions
Posted By: paulizorama
Subject: Changing 'Date Range' Functions
Date Posted: 17 Dec 2009 at 4:31pm
 Dear Santa - I'm losing the will to live trying to work out if it's possible to change or rework the preset 'Date Range' functions in Crystal XI.
I'm running reports in which I need to select a reporting period of either the 'last 6months' or the 'last 12 months' - or alternatively the 'Last150Days' / 'Last350Days'.
Can Anyone tell me how I might construct a formula that replaces the existing preset 'Last7Days' one - either that or a formula based on the 'Aged0to30 Days' that extends it to 0 -100/200/300Days?
A solution to this is the only Christmas present I really want this year - do you really exist?
------------- pauliz |
Replies:
Posted By: DBlank
Date Posted: 17 Dec 2009 at 7:13pm
|
You can use te dateadd function or dateserial depending on exactly whatt you want.
Some examples...
Last 100 days :
table.date in dateadd('d',-100,currentdate) to currentdate
Last 200 days :
table.date in dateadd('d',-200,currentdate) to currentdate
Last 6 months (from today):
table.date in dateadd('m',-6,currentdate) to currentdate
|
Posted By: paulizorama
Date Posted: 18 Dec 2009 at 1:19am
|
Dear Santa Dblank - I'm a believer! I'll try this out as soon as I can. Thanks again ------------- pauliz |
Posted By: flanman
Date Posted: 18 Dec 2009 at 7:07pm
|
I do this often either going forward or back. My increments are usually something like 3 weeks back or 6 weeks forward. I usually just use currentdate()-21 for three weeks back or currentdate()+42 to go forward and it works great for me. Flanman |
Posted By: paulizorama
Date Posted: 19 Dec 2009 at 2:09am
|
Hi Flanman
Thanks for the solution - you'll no doubt have seen that 'santa' DBlank has already posted a formula (that does work - albeit with the additional use of a 'select -true/false' stage) .
Your suggestion looks elegant though - and will try it straight away.
Thanks Flanman - have a good Christmas
P ------------- pauliz |
Posted By: paulizorama
Date Posted: 19 Dec 2009 at 2:53am
 Dear Flanman / Dblank
This is an afterthought - thanks for your solutions - but a I wondered if it was possible (using either of your solutions as a basis) to set up a report 'Paramater' for 'date' - so that the report user can specify their own preferred date period by entering simply '-/+#'.
If so, what would the Select formula be - incorporating the (?date) paramater field?
With gratitude - Pauliz ------------- pauliz |
Posted By: DBlank
Date Posted: 19 Dec 2009 at 4:18pm
|
There are several different ways that you can address this.
You can have the user enter date ranges on asingle paramter.
You can have the user enter 2 date params (start end end) and use those.
You could create a predefined list and then use that (e.g. 1 week, 1 month, etc.)
You could just have them enter a number and use that as the total days from today.
I am sure there are others...
What is your preference?
|
Posted By: paulizorama
Date Posted: 20 Dec 2009 at 4:28am
|
Hi Dblank
The predefined list option would give the least margin of error for report
users - so I guess that's favourite.
best regards
P ------------- pauliz |
Posted By: DBlank
Date Posted: 20 Dec 2009 at 7:29pm
|
I think this will work but have not tested it.
Create your param and make your list of options.
If you use text options then try this replacing the text and numbers as needed:
table.date in dateadd('d',-
(if {?param}="1 week" then 7 else if {?param}="2 week" then 14 else if {?param}="3 week" then 21 ... etc.)
,currentdate) to currentdate
If you use number text in your param try:
table.date in dateadd('d',-(totext({?Param},0,'')),currentdate) to currentdate
If you use numbers in your param try:
table.date in dateadd('d',-({?Param}),currentdate) to currentdate |