Field cannot be summarized, Crystal Reports, 'previous function'

[Jdbenike]

I am working on an existing report that is trying to calculate break times for all minor employees and finding all minors that worked more then 6 hours without a 30 minute break.

Based on the tables I have and not having the ability to create new ones in the environment, I have one field that grabs the actual 'lunch time'. Called {@shortlunch}, which takes two other fields, with calculations in itself which basically calculate the lunch of a minor by taking their in punch, minus their pervious out punch. Their is no actual table field that shows the break time.

So the {@shortlunch} field does not show select expert and I need to not have any records less then 1800 seconds show grouped by eventdate for each employee. Which is Group 3 because their are multiple lines for each record.

http://i2.photobucket.com/albums/y12/jdbenike/untitled-1.jpg

That's a picture of the results. The Lunch Length column is the last filter I need to be filtered.

So I created another field called {@count}
with the code
if {@shortlunch} > 1799 then 0 else 1

Then I created another field called {@count sum}
with the code
sum({@count sum},{@GROUP3})

This is when I get the cannot summarize error message. The only way I figured to show this data is to show a '1' for when the infraction incurs, and create another column that sums that's column 'cause it will only happen once. Then use select expert to say anytime '1' incurs do not show that record.

Could someone please help me

Thank You

 

[Jdbenike]

The count sum field does show correctly when I threw it on the report showing 0 and 1's down the data in the correct spot for each record by eventdate. Just getting a sum of that column then using select expert would be the key. Their are no other ways to do this report. The start reason has breakrules, but there is code behind those rules so everytime a break 30 minute rule occurs it can happen anytime after 24 minutes.

http://i34.tinypic.com/o7jdsi.jpg

These are the fields and the layout in the picture attachment.

Here is the code for the fields that would involve this problem, or might involve this problem to better clarifty how this report works. It doesent involve the sorting already in select expert for greater then 6 hours and age restrictions. Everything is working correctly except the last piece involving the break.

----{@lunchlength} field(basically just formats short lunch field for putting break time into format)-----
numbervar tot := {@shortlunch};
if {?Decimal Format} = 0 then
totext(tot/3600,2) else
totext(tot/3600,"0") + ":" +
totext(remainder(abs(tot),3600)/60, "00")

----{@short lunch} field-----
{@lunchoutpunch} - Previous({@OUTPUNCH2})

----{@OUTPUNCH2} field-----
time({@OUT PUNCH})

----{@OUT PUNCH} field-----
{VP_TIMESHEETPUNCH.OUTPUNCHDTM}

----{@lunchoutpunch} field-----
if {VP_TIMESHEETPUNCH.STARTREASON} like 'break:lu*' then time({@IN PUNCH}-1800) else
if {VP_TIMESHEETPUNCH.STARTREASON} like 'break:meg lu*' then time({@IN PUNCH}-1800) else
if {VP_TIMESHEETPUNCH.STARTREASON} like 'break:umf lu*' then time({@IN PUNCH}-1800)

----{@INPUNCH} field-----
{VP_TIMESHEETPUNCH.INPUNCHDTM}

----{@GROUP3} field-----
{VP_TIMESHEETPUNCH.EVENTDATE}

 

[Jdbenike]

The main problem is this field

----{@short lunch} field-----
{@lunchoutpunch} - Previous({@OUTPUNCH2})


I know I cant use the previous and then sum off of that field. Yet that is the only way to calculate the break time for an employee. It takes the inpunch from a field on a table minus the previous outpunch field on a table and gives me seconds.

Keep in mind the data can come back as one line for a continious shift, or two lines, or three lines based on how many breaks an employees take.

So is there another way to get that breaktime without using the function? Or is there a way to sum the fields as 0 and 1 then take a sum via the group.. which is eventdata.. and use select expert?

 

[lbass]

I can't tell what you want your final display to look like, whether this is an issue of how to display, or whether it is about how to summarize across the minors.

-LB

 

[Jdbenike]

What I need is to be able to filter out results of where a minors break is more then 30 minutes via Group3 field(which is eventdate)

So, if a minor has more then a 30 minute break, all the lines that are relevent to his shift from that date need to be excluded.

The only way I can think to do this is to make 0 and 1's for the data. So, if lunch > 30 minutes, then 1, else 0. Since they can only have one lunch longer then 30 minutes, then do a sum on that field. So that if the infraction incurred a 1 would incur for that person for that date in each line. Then, use selection expert to filter out that result.

Yet, I can't do any of that because I use the 'previous' function to calculate the break time of each person. So I can't summarize based off of that field, and/or use selection expert with it.

Sorry, it's hard to put into words on here exactly, and I can see how it's hard to get at first glance.

 

[lbass]

Since the lunch break is dependent upon sequential data, I think you have to save the report as a subreport, link it to the main report on eventdate and minor (and maybe the higher order groups--not sure what they are), and place the subreport in an eventdate group header_a section. You can then suppress all sections within the sub, format the sub to "suppress blank subreport", and format group header _a to "suppress blank section", so that it doesn't show in the report.

Then in the sub in the detail section, create a shared variable like this:
whileprintingrecords;
shared numbervar max;
if onfirstrecord or
{@shortlunch} >= max then
max := shortlunch;

In the sub report footer, add this formula:

whileprintingrecords;
shared numbervar max;

In the group footer (eventdate) of the main report, add a reset formula:

whileprintingrecords;
shared numbervar max := 0;

Then format the detail section, and both GH#3_b and GF#3 to suppress with this formula:

whileprintingrecords;
shared numbervar max;
max >= 1800

-LB

 

[Jdbenike]

What's GH#3_b and GF#3? Format which detail section? The main report that is blank, or the subreport with all the info?

What's a reset formula?

How do you create a shared variable?

How do you link it to the main report on eventdate and minor ?

 

[lbass]

First, I assumed your original report contained no subreports, and I was suggesting that you save this original report and insert it back into the original report as a subreport.

To answer your questions:

GH#3b--Group header#3_b. GF#3 - Group Footer #3.

Format the detail section of the main report.

The reset formula resets the variable "max" to 0 after each group.

The formula I showed you creates the shared variable.

When you insert a subreport, a linking tab appears. Otherwise, go to edit->subreport links and add the links there.

-LB

 

[Jdbenike]

In the subreport should I delete all the fields out of the detail section?

 

[Jdbenike]

Anyways, it hangs up when I do it. Doesen't complete. I followed your words as much as I could.

Now, is there any easy way we are overlooking this?

So, with this {@count lunch test} field equalling 1 if it's over the 30 minute else 0.

Can't we create another field off of that to turn the rest of the group by eventdate blue or something. Or, somehow get around the 'previous' code function. Like, keep it in the report because it gives me an actual numeric value. There has to be another simpler way.

 

[lbass]

I'm lost. No, why would you want to do that? Please explain where you are with my suggestion--what you have done so far and what the remaining issues are.

-LB

 

[Jdbenike]

There has to be a simpler way to sort by group with this one field that can't be smmarized.

 

[lbass]

Unfortunately, no there isn't.

-LB

 

[Jdbenike]

Do you think I can do this in reporting services a lot easier?

 

[CR85user]

LBass is suggested the most appropriate way to get the information you need.

The only thing you could try is a datediff function.

//{@Checktime}
Datediff('n',previous{{@OUTPUNCH2},{@lunchoutpunch})


And then another formula to flag

//{@Flagtime}
if {@Checktime} > x then 'Exceeded by: ' + Totext({@Checktime} - x)


Obviously replacing x with your required interval.

'J

CR8.5 / CRXI - Discovering the impossible

 

[lbass]

What I suggested is not that complex. Maybe you can outline how you tried to implement it and we can trouble shoot it.

-LB

 

[Jdbenike]

First step to last from beginning.

Insert subreport- create from existing- selected the actual report- clicked links tab Added fields eventdate and personnum.
Inserted subreport into group #3 header.

Opened subreport and suppressed every single section.
Then went to format sub, and clicked 'suppress blank subreport'.

I have three group headers. So I clicked on each of the three, went to section expert, and clicked the 'suppress blank section' button for each.

Then in the sub in the detail section, create a new field called {@count lunch test} and added this code below.

whileprintingrecords;
shared numbervar max;
if onfirstrecord or
{@shortlunch} >= max then
max := shortlunch; ----------> this part i made it {@shortlunch} so it worked b/c you didn't type it that way.

In the sub report footer, added another field called
{@count lunch test 2} and added this code below.

whileprintingrecords;
shared numbervar max;

In the group footer (eventdate) of the main report, added another field called {@count lunch test 3} and added this code to reset.Added this into the event date group which is group 3.

whileprintingrecords;
shared numbervar max := 0;

Then formated the detail section, and both GH#3_b and GF#3 to suppress with this formula: Did this by going to the section expert in the subreport and clicked the formula button next to the supress ( no drill down) tab.

whileprintingrecords;
shared numbervar max;
max >= 1800


All this work was done in the subreport.
Saved it off.
Ran it in production. Will let you know if it finishes or not in a few minutes

 

[Jdbenike]

When I run, it just doesen't complete. It usually takes 5 minutes with the data Iam running it against. Yet, it just hangs up for hours.

 

[Jdbenike]

That's word for word what I did.

 

[Jdbenike]

http://i37.tinypic.com/2u61wdw.jpg

Is the timesheetitem one.. i ran just with select * from

and

http://i34.tinypic.com/2kjwc9.jpg

is the vp_timesheetpunch one i ran with

select * from vp_timesheetpunch
where (VP_TIMESHEETPUNCH.EVENTDATE >= '2008-04-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-04-10 00:00:00.000')
AND VP_TIMESHEETPUNCH.PERSONNUM = '095032'