Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Date Range Formula Post Reply Post New Topic
Author Message
willw
Newbie
Newbie
Avatar

Joined: 31 Dec 2008
Location: United States
Online Status: Offline
Posts: 2
Quote willw Replybullet Topic: Date Range Formula
     Posted: 31 Dec 2008 at 9:41am

I am stuck and could use some assistance.  The current report has the date ranges hard coded and I want to change them to a formula.

For example, the current report has the following formula in the Running Totals Field
 
{MEMB_HPHISTS_V.HPFROMDT} in Date (2007, 10, 01) to Date (2007, 10, 31) and IsNull ({MEMB_HPHISTS_V.OPTHRUDT}) and ({MEMB_HPHISTS_V.CURRHIST}) = "C" and Count({MEMB_HPHISTS_V.OPT},{@MEMBER}) = 1
 
This is repeated 12 times once for each month of the year!
 
I basically the formula to use the Current Date and go back twelve months so that I don't have manually change the numbers each month.
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 31 Dec 2008 at 12:46pm

You should be able to add a variable as a counter. Set its value to 12 and then subtract one month from the current date.  Then subtract 1 from the counter 12. You could use a do while or do until loop logic continuting the loop while the counter is greater than 0. 

The best place to put that control logic will need some more thought.
 
Regards,
 
John W.
IP IP Logged
willw
Newbie
Newbie
Avatar

Joined: 31 Dec 2008
Location: United States
Online Status: Offline
Posts: 2
Quote willw Replybullet Posted: 05 Jan 2009 at 2:11pm

I appreciate your suggestion about adding a variable as a counter.  When you finish thinking about the control logic please include in your explanation exactly how I would program something like that because I only know very basic crystal formulas.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 05 Jan 2009 at 2:52pm
Another possible appoach would be to use a select statment to look at records in the last year as
{MEMB_HPHISTS_V.HPFROMDT} in today to (dateadd("yyyy",-1,today))
 then group on the months
then add a formula field to conditionally count the records on your other criteria and then insert a summary to sum that formula field at the month group level.
 
"{MEMB_HPHISTS_V.HPFROMDT} in Date (2007, 10, 01) to Date (2007, 10, 31)"
would be handled by grouping on the month
Set up a formula field called something "Monthly_Count" as: 
"if IsNull ({MEMB_HPHISTS_V.OPTHRUDT}) and ({MEMB_HPHISTS_V.CURRHIST}) = "C" and Count({MEMB_HPHISTS_V.OPT},{@MEMBER}) = 1 then 1 else 0"
will handle the
"and IsNull ({MEMB_HPHISTS_V.OPTHRUDT}) and ({MEMB_HPHISTS_V.CURRHIST}) = "C" and Count({MEMB_HPHISTS_V.OPT},{@MEMBER}) = 1"
Do a summary as a SUM of the @Monthly_Count field at the group level of the month and you should have what you are looking for.
 
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.