Start Free Trial

asked on

Crystal Reports error, "This field cannot be summarized"

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
untitled.bmp

ASKER

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.

Any suggestions?

ASKER

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}

layout.bmp

The problem is SUMMARY functions are done before the report is printed. The Previous function can only run when the report is printed thus you can't do a summary on a function that uses previous.

Try this
In the report header add a formula to declare avariable
WhilePrintingRecords;
Global numberVar CountShortLunch := 0;
''

Modify your {@count}
with the code
WhilePrintingRecords;
Global numberVar CountShortLunch;
if {@shortlunch} <= 1799 then
CountShortLunch := CountShortLunch + 1;
''

To display it
WhilePrintingRecords;
Global numberVar CountShortLunch;
CountShortLunch

mlmcc

ASKER

That doesen't seem to do it. That seems more like a running total that keeps adding a number down the line when I need it for each employee via eventdate(lines are seperating each event date on the example above).

Basically the end result is that I need to get rid of all the records(seperated by lines) that show an employees lunch break(lunchlength) is greater then 30 minutes.

So on the first attached picture above I would need Jordan Ferry, Jason Rodavich, Andrew Scrima, Dylan Jacobs, and David Brooks not to show up at all.

I would need samantha kennedy, michael pheebles, and dylan jacobs when his break is equal to 28 minutes to show up only.

So I need a way to get select expert to work with the calculated lunch length with the previous function, or create a seperate field which counts every time the error occurs. Then summarize each section so that it equals to 1 if the problem occurs, then use select expert to filter out these records. Yet, I cannot use summarize or select expert because it won't allow it. So that is the dliema. Please help if you can. Iam getting some major heat at work for this b/c I have been working on it so long.

ASKER

{@count lunch}
if {VP_TIMESHEETPUNCH.STARTREASON} like 'break:lu*' then 1 else
if {VP_TIMESHEETPUNCH.STARTREASON} like 'break:meg lu*' then 1 else
if {VP_TIMESHEETPUNCH.STARTREASON} like 'break:umf lu*' then 1 else 0

{@count lunch sum}
sum({@count lunch},{@GROUP3})

-------------------------------------------------------------------------------
This is how it was sorting it before where it was showing a 1 everytime an infraction incurred and a 0 for the rest. For every record 1 would only occur once if we didn't need to use it on the report. So if we had another column that summed each record by event date. Everytime 1 occurred I could just use select expert to filter out these results. Yet, the breakfules in the tables don't work that way, and the way it's pulling data is incorrect. So now I hafta filter the results by the ACTUAL time the employees worked which is caclulated with the {@shortlunch} field above and the previous function because that is the only way to get the 'actual' break time in seconds.

ASKER

I also tryed to refrence the @shortlunch field with another field, based off another field declared as a NumValue to see if that would work, but I think it always comes back to the 'previous' part of the @shortlunch field and I can't use select expert or summarize anything.

ASKER

If I do need a running totals to summarize, it would hafta be by event date which is {@Group3} so it doesen't keep running downwards and stops after each set of records.

You can reset the value of the variable in any of the group headers or footers. So when it should be zeroed simply add the first formula.

mlmcc

ASKER

when I create a new field to the details section called

---------{@count lunch test}-----------
if {@shortlunch} > 1799 then 1 else 0

The attached file is how the 0 and 1's come back next to the person num column as you can see.
Everytime that 1 occurs as you see I need that record for that employee for that date pulled from the results.
Now if I could sum that field based on that, and use select expert. Life would be great. Yet, I don't think that's possible.
Results-with-the-my-forumla.bmp

Try this

Right click the detail section in the left margin
Click the formula button next to SUPPRESS
Try this formula
{@shortlunch} > 1799

mlmcc

ASKER

When you do that it brings back every shift that doesent have a break over 30 minutes like I orginally wanted, but also takes away the line formatting to seperate records. Which is probably a minor thing.

Yet, now I need to filter out the shifts that are less then 6 hours.

So how do I do that? With an and statement in that suprress formula area? Or how?

results.bmp

ASKER

There is also two supress sections in there.
There was a supress drilldown section and a supress blank section area. I used the original.

ASKER

Correction. That suppress field with that code in just takes out the invidual lines where a 30 minute shift shows, but still leaves the other part of the shift on a seperate line for that event date. For a current record for a person like in the pages above there might be anywhere from 1 - 5 lines for punches for a certain day.

So if one punch is more then 30 minutes, I need all the records for that eventdate to go away.

ASKER

Try this
In the report header add a formula to declare avariable
WhilePrintingRecords;
Global numberVar CountShortLunch := 0;
''
Modify your {@count}
with the code
WhilePrintingRecords;
Global numberVar CountShortLunch;
if {@shortlunch} <= 1799 then
CountShortLunch := CountShortLunch + 1;
''
To display it
WhilePrintingRecords;
Global numberVar CountShortLunch;
CountShortLunch
--------------------------------------------------------------------------------------------------
now, where do you put the last two formula fields, do you put both in the details section? or one in the footer.

and, you mentioned on how to reset the value to zero with some code in the header or footer. So if i wanted to reset the value for each new eventdate per person, how would I do that? I don't think I will be able to use select expert if I do it this way though.

Just trying to try all your reccomendations here. Ill be watching your posts back like a hawk. All day and night. Hell, ill pay for a year membership if I have to.

You may be able to supprress all the lines with

You will need to group by person then by date
Sum({@count lunch test},{DateField}) = 1

mlmcc

The @count formula goes where you had it

The display formula goes in the group footer

mlmcc

ASKER

I went to right click on details/ section expert/ then clicked on the formula behind 'suppress drilldown' and entered that formula above you just mentioned.
That takes me back to my original 'cannot summarize' error because of the {@count lunch test} field

----------@count lunch test field---------
{@shortlunch} > 1799 then 1 else 0

and {@shortlunch} refrences the {@lunchlength} forumla above in the beginning which has the previous function in it to calculate breaktime.

Sorry, forgot about that.

You may have to use a subreport to calculate the short lunch value in the group header then you can suppress the details bsed on that value.

mlmcc

ASKER

So since Iam sort of new to sub-reports besides reading out of the guide I have.

Am I just creating the subreport with the shortlunch field calculation in it? Then pulling that back to the main report somehow and filtering the results? Then hoping that if we do it that way the previous function isn't found?

Because even if we get the short lunch values to only show less then 30 minutes it will just keep hitting values less then 30 till it finds one, then suppress it again I think. Unless we do a column with 0 and 1's and then do a sum of that column based on eventdate, then filter... ect.

I guess what Iam asking for is more of a detail step on step instruction on how to do what you think will work from scratch with the subreport.

Basically the subreport needs to look a lot like your current report but only do the

---------{@count lunch test}-----------
if {@shortlunch} > 1799 then 1 else 0

Then sum them usiing a running total.

Something like this
In the main report add a formula to the report header
WhilePrintingRecords;
Shared NumberVar ShorLunchCount;
''

Group by employee then date
In the date group header add the subreport

Suppress the detail section with
Shared NumberVar ShorLunchCount;
ShorLunchCount >= 1

In the subreport use this as your formula
---------{@count lunch test}-----------
Shared NumberVar ShorLunchCount;
if {@shortlunch} > 1799 then
ShorLunchCount := ShorLunchCount + 1;
''

mlmcc

ASKER

That still does not give me what I need. When running the report it doesen't finish and just sits. Is there any other way without a subreport to do this process? Any other more complicated coding using variables?

Plus I think I see where your going with this, but I think the results would still not be filtered the correct way.
I assumed sending the actual file is off limits on doing on this site and you guys just stick to technical writing advice?

I know you woudln't have my database and or tables, but you could still throw some coding into it.

ASKER

I guess the main issue I have with this report is the fact when I use the previous function it really limits what I do.

So looking at the results on this attachment. Is their another way to get breaktime between two punches on seperate lines of data besides this code snippet?
"""{@lunchoutpunch} - Previous({@OUTPUNCH2})"""

Like, The example below is for lacey meinhardt, first record

10:00am - 1:16pm
1:46pm - 3:57pm
4:13pm - 6:00pm

now between these lines of records which could vary based on employee and could take more or less breaks.. is their another way to design the code to get the break time between the 1:16-146 or 3:57 to 4:13???

The field names for those two values are VP_TIME.INPUNCHDTM, AND VP_TIME.OUTPUNCHDTM
Report-Ran.pdf

ASKER

keep in mind those are seperate lines of records so a simple minus or add won't work

ASKER

By the way.. I do appreciate all the help and time you guys are giving to me. Even if I will end up paying for it, I think it will be far more worth the value if we get this to work. I even mentioned this to my boss and he is considering keeping us on this site and having the company cover for it.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

There has been a lot of details talked about. So let's start fresh. This is the last thing you posted
'''''''''''''''''''
Basically the subreport needs to look a lot like your current report but only do the

---------{@count lunch test}-----------
if {@shortlunch} > 1799 then 1 else 0

Then sum them usiing a running total.

Something like this
In the main report add a formula to the report header
WhilePrintingRecords;
Shared NumberVar ShorLunchCount;
''

Group by employee then date
In the date group header add the subreport

Suppress the detail section with
Shared NumberVar ShorLunchCount;
ShorLunchCount >= 1

In the subreport use this as your formula
---------{@count lunch test}-----------
Shared NumberVar ShorLunchCount;
if {@shortlunch} > 1799 then
ShorLunchCount := ShorLunchCount + 1;
'''''''''''''''''''''''''''''''''''''''''''''''''''''

What do you mean only do ''''Basically the subreport needs to look a lot like your current report but only do the

---------{@count lunch test}-----------
if {@shortlunch} > 1799 then 1 else 0'''''''''''''''''

Is this the one you wanted in the main report?

Also, does the subreport basically look exactly the same besides this count lunch field?

ASKER

Then you go on saying use this
In the subreport use this as your formula
---------{@count lunch test}-----------
Shared NumberVar ShorLunchCount;
if {@shortlunch} > 1799 then
ShorLunchCount := ShorLunchCount + 1;

in the same field.. so which one goes into the subreport for that field and which one doesent.. is one field called something else like count lunch test 2 or something?

ASKER

how do you group by employee then date

ASKER

i think they already are grouped in their group fields as @Group2, and @group3 if you look above, unless u mean something further

ASKER

Ahh, I see the same field can have different code in subreports.

ASKER

Ok, this is what I did. I created a subreport based on a existing report. Which, is basically the report iam using now so it had all the same fields.

Then, in the main report I did this..
---------{@count lunch test}-----------
if {@shortlunch} > 1799 then 1 else 0

Then sum them usiing a running total.

Something like this
In the main report add a formula to the report header
WhilePrintingRecords;
Shared NumberVar ShorLunchCount;
''
In the date group header add the subreport
Suppress the detail section with
Shared NumberVar ShorLunchCount;
ShorLunchCount >= 1

THEN the only thing I did in the subreport was add
---------{@count lunch test}-----------
Shared NumberVar ShorLunchCount;
if {@shortlunch} > 1799 then
ShorLunchCount := ShorLunchCount + 1;
'''''''''''''''''''''''''''''''''''''''''''''''''''''
that.

Anyways This causes the report to never finish and just hangs up when I run it.