• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Crystal Reports subtract from minimum date range and add time

I have a report that tracks production activity by many different parameters, one of them is the shift being worked.  The problem shift is 3rd which runs from 11:00 pm to 7:00 am.  So if I want 3rd shift information for 5/12/15 I'll only get 7 hours worth of data when setting my both of my date range values to 5/12/15.  I tried to add the formula below to subtract 1 day from the minimum date and convert it to datetime so that I can get all the data for 3rd shift from 5/11/15 11:00pm and all of the data from 5/12/15.  If I set my date range to 5/11/15 and 5/12/15 I'll get data from midnight to 7:00am on 5/11/15 plus data from 11:00pm to midnight which is what I don't want.  I keep getting an error on the formula below saying:
'The ) is missing'
It highlights RelativeDate.  As far as I can tell I have all of the closing parenthesis I need.  I have set my {?DateRange} paramater field to only accept date, but the field it's searching is a datetime field.  Is there a good way to do this?  Is this formula wrong?

if {?Shift} = 3 then
(Minimum ({?Date Range}) = CDateTime(RelativeDate(Minimum ({?Date Range};-1)), "11:00 pm")

Open in new window

0
dustock
Asked:
dustock
  • 11
  • 6
  • 4
  • +1
3 Solutions
 
mlmccCommented:
Where are you trying to use the formula?

I assume it is in the SELECTION  or filter

You can't change the minimum of the range.

Local DateTimeVar minTime
if {?Shift} = 3 then
     minTime := DateTime(Minimum ({?Date Range}) - 1, Time(23,0,0))
Else
    minTime := Minimum({?DateRange});
{YourDateTime} >= minTime  AND {YourDateTime} <= Maximum({DateRange})

Open in new window


Looking at the code are you using Crystal or WebI?  The code seems to be a mix of them.  Crystal doesn't have a relativetime function

mlmcc
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't have the right number of opened and closed parenthesis?
You have one open before the first MINIMUM function. Should it be there or not? If so, where do you close it?
0
 
mlmccCommented:
If his code was at all valid in Crystal, he is missing a ) before the ;-1.

mlmcc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dustockAuthor Commented:
@mlmcc
I'm trying to do this in the Crystal selection formula.  A quick google search gave me the relativedate function, I didn't read much into it.  I also tried the code block you provided but I get an error that "The remaining text does not appear to be part of the formula' after 'Local DateTimeVar minTime

@Victor
That paren before the first minimum function wasn't supposed to be there.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then you didn't close it. I'm counting 5 opened and 4 closed. Still missing one to close.
0
 
dustockAuthor Commented:
@Vitor
I removed it, it wasn't needed.  And as mlmcc stated relativedate wasn't a valid function.  Its not the parens anymore.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, what's the error now?
0
 
dustockAuthor Commented:
back to square 1.  I had invalid code.  So I am back to needing a way to search for data that is 1 day behind the minimum date selected at 11:00pm if Shift is equal to 3
0
 
dustockAuthor Commented:
I gave this some more thought and if I can't change the minimum value of the date range, then I just need to change the the date value that the report is using.  

My selection formula currently looks like this.

{LB_DC.STATUS} <> 300
and {LB_DC.MSTIME} = {?Date Range}
and {Logbook_Operators.SHIFT} in {?Shift} 

Open in new window


I tried adding the following line and it saves fine, but I don't get any results in the report no matter what the value of Shift is.  
if {?Shift} = '3' then
     {LB_DC.MSTIME} = DateTime(Minimum ({?Date Range}) - 1, Time(23,0,0))

Open in new window


This also brings up another question, shift can be a combination of 1,2 or 3.  So if someone wants data for all 3 shifts for a day, how would I need to change my if block?  Based on some searching it looks like I might need to do something like :
if InStr({?Shift},'3') > 0

Open in new window

0
 
mlmccCommented:
If the parameter can have multiple values then just use

If '3' in {?Shift} then
   ...

What data do you have?
DO you have a start time (which is the start of the shift) or is the start time just when the task started which sometime during a shift?
Same for an end time.

What is the report trying to do?

mlmcc
0
 
dustockAuthor Commented:
Right now I have a date range parameter that users want to be able to use to select a range of production data (what time operators paused or ended their job, part count, amount of time logged in, job information).  Some cases they might want 1 day of data or a weeks worth of data.  I know 3rd shift starts at 11:00pm and runs until 7:00am.  So in my example before they want data for 3 shift for 5/12/15 so I need the data for 5/11/15 11:00pm through 5/12/15 7:00am, but if they want all three shifts for yesterday then I would want 5/11/15 11:00pm through 5/12/15 11:00pm. to capture all of the 5/12 data.

I think I might have it figured out.  Once I got passed the checking if 3 was in the shift parameter I was able to get the data from 11:00pm on the previous day until midnight, so I did a datetime conversion on the max date for a time of 23:00:00 and it looks like all the data is there.  Now I just need to make sure the correct data shows up if I have all 3 shifts and play around with the other options in my report.

if '3' in {?Shift} then
    {LB_DC.MSTIME} > DateTime(Minimum ({?Date Range}) - 1, Time(23,0,0)) 
    //and {LB_DC.MSTIME} < Maximum ({?Date Range})
    and {LB_DC.MSTIME} < DateTime(Maximum ({?Date Range}), Time(23,0,0))
else
    {LB_DC.MSTIME} = {?Date Range}

Open in new window

0
 
James0628Commented:
That formula doesn't look right to me.

 1) If they asked for 3rd shift, you include times from 11 PM on the previous day to 11 PM on the "maximum" day in the range.  Shouldn't the end time for 3rd shift be 7 AM?

 2) If they didn't ask for 3rd shift, you include anything in {?Date Range}.  Shouldn't you be checking to see if they asked for 1st or 2nd shift, and checking for the corresponding times?

 3) Is {?Date Range} an actual range, or just a single date?  If it's just a single date, you might want to change the parameter name, just to be clear.  If it's a range, there's a whole new set of issues.  If they don't ask for all 3 shifts, then you presumably need to look for times in the appropriate ranges on each day in the range, not just any date in the range.  For example, 3rd shift from 05/01 - 05/05 would presumably mean 11 PM on 04/30 - 7 AM on 05/01, 11 PM on 05/01 - 7 AM on 05/02, and so on.

 James
0
 
dustockAuthor Commented:
James,

The reason I am going to 11:00pm on the maximum date is if they choose do display data for all 3 shifts for 5/12.  If I went until midnight then I would get an hour of 3rd shift data for 5/13.  So far this formula appears to be working fine.  My other selection criteria limits what data is displayed so even though I'm going from 5/11 11:00pm to 5/12 11:00pm if I only select 3rd shift, I only see data for users that were on 3rd shift.

We ran the report from 5/1 to 5/17 for a 1st shift worker that has been here during 1st and 2nd shift and it appears we are getting all the data.  I will test for a 3rd shift employee and see if I run into issues.
0
 
James0628Commented:
OK.  I just didn't see any shift checks in the last formula that you posted.  But looking at one of the formulas you posted earlier, I see that you're checking a Logbook_Operators.SHIFT field.  Maybe that handles it.  I hadn't noticed that before.

 James
0
 
dustockAuthor Commented:
I appreciate you taking a look!  

This report has dynamic grouping options and now they want date as a grouping option.  So if I run the report for 5/1 to 5/12 and group by date then by machine they want to see 5/1, machine, work done on that machine, 5/2 machine, work done on that machine.  Turns out that's not as easy as I thought, but I'm working through it.
0
 
dustockAuthor Commented:
Never mind on the grouping by date, looks like SAP was nice enough to provide a formula for dynamically grouping by:

Daily
Weekly
Monthly
Quarterly
Semi-Annually
Yearly

I've gotten nastygrams from EE for posting links before,  But if you are curious about it I can always send it in a PM.  Unless EE won't complain if I send a link to an SAP blog.
0
 
James0628Commented:
Here I go again.  :-)

 I just noticed a couple other things in that last formula.

 First, you have {LB_DC.MSTIME} > DateTime(Minimum ({?Date Range}) - 1, Time(23,0,0)) .

 If the 3rd shift starts at 11 PM, shouldn't that be >= ?


 Actually, assuming that the tests in that last formula are working, the test to see if they asked for shift 3 seems kind of unnecessary.  If {?Shift} includes "3", you adjust the start date by -1.  If {?Shift} does not include 3, you just use {?Date Range}.  But that means that if they asked for shift 3, _and_ shift 1 or 2, you're using the adjusted start date, even though some of the times are for shift 1 or 2.  If that works, then you could just use the adjusted start date, regardless of which shift they asked for.


 I also just wanted to check something.
 Is LB_DC.MSTIME a datetime, or just a date?  If it's a datetime, does it actually include different times (eg. 11 PM or 7 AM), or is the time not really used and just some default value, like 12 AM?

 James
0
 
James0628Commented:
As far as the links go, my impression is that the problem is just posting a link, with no explanation or description.  I think as long as the link is relevant, and you explain why, it's OK.  But I could be wrong.

 James
0
 
dustockAuthor Commented:
LB_DC.MSTIME is a datetime field, that field tells us what time the operator clocked in and what time the operator clocked out.  On my report I exclude the clock in tranactions and I am just displaying the date of the Pause or End transactions.  There is another field that provides seconds clocked in when someone does a pause or end transaction that I use to generate how long they were clocked in for.

The nastygram I got before was for posting a solution to a problem from another site.  I figured instead of copy and pasting the solution from stackoverflow (or what ever site it was) I'd just post the link and EE didn't like that.

I guess I can roll the dice, Parameterized Date Group Intervals in Crystal Reports is where I saw the code on how to dynamically sort by date.  I guess if EE doesn't like me posting this link I can always remove it.  But since its from SAP and they own Crystal I don't see any harm since it was used for my solution.
0
 
mlmccCommented:
It would probably be better to post this extension as a new question.

The prohibition on links is in general only for "answers".  Links have a tendency to go bad so if the link is the answer and there isn't enough information without the link then the answer isn't much good when the link goes bad.

Also there is a concern with certain links pointing to competing sites.  Why drive the asker to those sites and have them quit using EE.

mlmcc
0
 
James0628Commented:
Is {?Date Range} just dates, or is the user entering times as well?

 If {?Date Range} is just dates, as the name sort of implies, then it seems like you might have some problems when comparing that with LB_DC.MSTIME, since MSTIME does include times.

 If the formula works, great.  I've seen some potential problems, but I don't have the whole picture (all of your formulas, the values in the fields and parameters, etc.).

 James
0
 
dustockAuthor Commented:
James,

Daterange is just dates.  So far I haven't had an issue, I'm having supervisors test this report like crazy so we can try to find issues before releasing it to the entire plant.
0
 
James0628Commented:
Nevermind.  The date range is probably fine.  You're comparing a datetime (MSTIME) with dates ({?Date Range}), and if the dates in the range were simply interpreted as datetimes with a default time of 12 AM, then you might have a problem.   But I was forgetting the CR is clever enough to guess what you're going for with a range like that, and replace the ending date with a datetime that will give you the entire day (instead of just cutting it off at 12 AM).

 James
0
 
dustockAuthor Commented:
With the help of mlmcc I was able to write a formula that did what I needed it to do.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now