Solved

Crystal Syntax for finding Last Week's (Mon-Sun) data

Posted on 2009-05-06
6
698 Views
Last Modified: 2013-11-15
Hi,

I'm trying to figure out the Crystal Syntax to bring back all data for last week (Mon - Sun) no matter what the current day is, and without using parameter fields.

Thank you in advance,

Parachute  

// This is the code I currently use with date parameters, but I want to do away the paramter field
// so I can schedule the report to run any day of the week and still bring back data from last 
// Mon-Sun
 
(({Ticket.OpenDate} in datetime({?BeginDate}) to datetime({?EndDate})+1)
or 
({@ResolveDate} in datetime({?BeginDate}) to datetime({?EndDate})+1)
or
({@ResolveDate} > datetime({?Begindate}) and {Ticket.OpenDate} < datetime({?EndDate})+1))

Open in new window

0
Comment
Question by:parachute_505
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 24316243
Crystal has a LastFullWeek range function.  Unfortunately it returns Sun thru Sat

Try it this way

{Ticket.OpenDate}-1 in LastFullWeek

If you need the other date checks
(({Ticket.OpenDate} - 1 in LastFullWeek)
or
({@ResolveDate} in LastFullWeek)
or
({@ResolveDate} > Minimum(LastFullWeek) and {Ticket.OpenDate} < Maximum(LastFullWeek)+1))

mlmcc
 
0
 

Author Comment

by:parachute_505
ID: 24316915
mlmcc,

Thank you for the quick response.  I am new to using Crystal Syntax, could you explain the need to have "Maximum(LastFullWeek)+1)?  Why does the "+1" need to be there if maximum(LastFullWeek) alread refers to Sunday?

Thanks again

Parachute
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24317289
Maximum(LastFullWeek) is last Saturday.  SInce you want through sunday you have to add 1 day (actually 2 to get to Monday).

mlmcc
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:parachute_505
ID: 24317467
Perhaps I'm misunderstanding the LastFullWeek function.  My understanding is that the LasFullWeek() is Sunday-Saturday.  So I thought maybe if I wrote --> {Ticket.OpenDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)) that I would get Monday-Sunday

====================================================
This is what I came up with, but it is returning Monday-Saturday
====================================================
(({Ticket.OpenDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
({@ResolveDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
({@ResolveDate} > dateadd("d",1,minimum(LastFullWeek)) and {Ticket.OpenDate} < dateadd("d",1,maximum(LastFullWeek))))

====================================================
I need Monday-Sunday so I added "+1"
====================================================
(({Ticket.OpenDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)+1))
or
({@ResolveDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)+1))
or
({@ResolveDate} > dateadd("d",1,minimum(LastFullWeek)) and {Ticket.OpenDate} < dateadd("d",1,maximum(LastFullWeek)+1)))
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 24317970
The problem is probably you have a DATE-Time field and the time on the LastFullWeek will be 00:00

You could use
dateadd("d",2,maximum(LastFullWeek)



(Date(({Ticket.OpenDate}) in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
(Date({@ResolveDate}) in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
(Date({@ResolveDate}) > dateadd("d",1,minimum(LastFullWeek)) and {Ticket.OpenDate} < dateadd("d",1,maximum(LastFullWeek))))


mlmcc
0
 

Author Comment

by:parachute_505
ID: 24319986
mlmcc,

Thanks again for your help.  I was able to use the syntac below and have it work 99.9% successfully.  The other 0.1% I used your suggestion on a field that uses the SUM function --- CDate(dateadd("d",2,maximum(LastFullWeek))) --- or ---  CDate(dateadd("d",1,maximum(LastFullWeek)+1))

// Syntax to return data for Previous Week (Mon-Sun) without using data parameter fields
(({Ticket.OpenDate} in CDate(dateadd("d",1,minimum(LastFullWeek))) to CDate(dateadd("d",1,maximum(LastFullWeek))))
or
({@ResolveDate} in CDate(dateadd("d",1,minimum(LastFullWeek))) to CDate(dateadd("d",1,maximum(LastFullWeek))))
or
({@ResolveDate} > CDate(dateadd("d",1,minimum(LastFullWeek))) and {Ticket.OpenDate} < CDate(dateadd("d",1,maximum(LastFullWeek)))))
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question