Solved

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

Posted on 2009-05-06
6
697 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 100

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 100

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

 

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 100

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
How to increase the row limit in Jasper Server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

740 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