spectrum17
asked on
Totalling summarized fields and hiding headers before blank sections
Hello!
I've got a Crystal Report which I'm having a little difficulty with. I'm looking for the best way to summarise some fields that have been grouped. The report is a payroll report such as follows:
Date Employee Clock In Clock Out Start Time End Time Hours (formula)
1/7/2013 1234 6.56am 11.40am 7.00am 11.30am 4.5
1/7/2013 1234 12.10pm 3.30pm 12.30pm 3.30pm 3
1/7/2013 3456 6.50am 3.35pm 7.00am 3.30pm 8.5
For my purposes, I need to summarise the hours worked by employee each day (so if they have 2 records like employee 1234, then I need to show only the earliest Clock in time then the latest Clock Out time & same with Start and End Time), and I need a total for the hours for which I then need to work out how much the hours are greater than the standard day ie 8.5.
I then need to summarise all of this data by Department and sum also the 'overtime hours' ie 8.5 minus whatever hours have been worked.
I also want to hide any Department headings for which there are no records underneath.
I've tried using a running sum total fields for the clock in & out and start and end times, but I'm stuck with no total for the Department, and I can't hide the Department heading if there is no record.
HELP!
I've got a Crystal Report which I'm having a little difficulty with. I'm looking for the best way to summarise some fields that have been grouped. The report is a payroll report such as follows:
Date Employee Clock In Clock Out Start Time End Time Hours (formula)
1/7/2013 1234 6.56am 11.40am 7.00am 11.30am 4.5
1/7/2013 1234 12.10pm 3.30pm 12.30pm 3.30pm 3
1/7/2013 3456 6.50am 3.35pm 7.00am 3.30pm 8.5
For my purposes, I need to summarise the hours worked by employee each day (so if they have 2 records like employee 1234, then I need to show only the earliest Clock in time then the latest Clock Out time & same with Start and End Time), and I need a total for the hours for which I then need to work out how much the hours are greater than the standard day ie 8.5.
I then need to summarise all of this data by Department and sum also the 'overtime hours' ie 8.5 minus whatever hours have been worked.
I also want to hide any Department headings for which there are no records underneath.
I've tried using a running sum total fields for the clock in & out and start and end times, but I'm stuck with no total for the Department, and I can't hide the Department heading if there is no record.
HELP!
ASKER
Hi Portlet Paul
I'm gathering the data by connecting to a database on an SQL Server. The data is in 'view' form.
Here's the SQL query:
SELECT "TIMESHT"."PERSONFULLNAME" , "TIMESHT"."PERSONNUM", "TIMESHT"."EVENTDATE", "TIMESHT"."INPUNCHDTM", "TIMESHT"."OUTPUNCHDTM", "TIMESHT"."LABORLEVELNAME3 ", "TIMESHT"."ENDDTM", "TIMESHT"."STARTDTM"
FROM "wfcv4"."dbo"."TIMESHT" "TIMESHT"
WHERE ("TIMESHT"."EVENTDATE">={t s '2013-06-15 00:00:00'} AND "TIMESHT"."EVENTDATE"<{ts '2013-07-22 00:00:01'})
I'm gathering the data by connecting to a database on an SQL Server. The data is in 'view' form.
Here's the SQL query:
SELECT "TIMESHT"."PERSONFULLNAME"
FROM "wfcv4"."dbo"."TIMESHT" "TIMESHT"
WHERE ("TIMESHT"."EVENTDATE">={t
If it helps: I would expect the following results from the query below (without changing too much)
/*
Date Employee Clock In Clock Out Start Time End Time
1/7/2013 1234 6.56am 3.30pm 7.00am 3.30pm
1/7/2013 3456 6.50am 3.35pm 7.00am 3.30pm
*/
/*
Date Employee Clock In Clock Out Start Time End Time
1/7/2013 1234 6.56am 3.30pm 7.00am 3.30pm
1/7/2013 3456 6.50am 3.35pm 7.00am 3.30pm
*/
SELECT
TIMESHT.PERSONFULLNAME
, TIMESHT.PERSONNUM
, TIMESHT.EVENTDATE
, TIMESHT.LABORLEVELNAME3
, MIN(TIMESHT.INPUNCHDTM) AS INPUNCHDTM
, MAX(TIMESHT.OUTPUNCHDTM) AS OUTPUNCHDTM
, MIN(TIMESHT.STARTDTM) AS STARTDTM
, MAX(TIMESHT.ENDDTM) AS ENDDTM
FROM wfcv4.dbo.TIMESHT TIMESHT
WHERE (
TIMESHT.EVENTDATE >= {ts '2013-06-15 00:00:00' }
AND TIMESHT.EVENTDATE < {ts '2013-07-22 00:00:01' }
)
GROUP BY
TIMESHT.PERSONFULLNAME
, TIMESHT.PERSONNUM
, TIMESHT.EVENTDATE
, TIMESHT.LABORLEVELNAME3
Does this help?
ASKER
Hmmn, it probably would help if I knew how to edit an SQL query.. :)
Sorry, I'm a wee bit of a novice, can you help me with that one? I might need some help on the Syntax too....
Sorry, I'm a wee bit of a novice, can you help me with that one? I might need some help on the Syntax too....
:) both novices then - my Crystal skills are near zero. Good news is I do know it can be done - but not how.
how did you locate the query you gave me? i.e. changing the query is probably there someplace. (there's also Help :)
anyway - there are Crystal Gurus here at E-E - so if I can't get you there someone else probably will.
how did you locate the query you gave me? i.e. changing the query is probably there someplace. (there's also Help :)
anyway - there are Crystal Gurus here at E-E - so if I can't get you there someone else probably will.
ASKER
LOL, I went into 'Show Query'
I think this will lead to an answer:
http://scn.sap.com/thread/1275877
http://scn.sap.com/thread/1275877
In Crystal Reports (CR) 8.5 and earlier, it is possible to edit the SQL statement in the 'Show SQL Query' dialog box. Doing so allows the report designer to modify the SQL statement that CR generates. Starting with Crystal Reports version 9, users are no longer able to modify the SQL in the 'Show SQL Query' window.NB your CR version is important answers differ according to version.
How can you control the SQL statement that Crystal Reports sends to the database?
Resolution
To control the SQL statement that Crystal Reports 9 and later uses, use the 'Add Command' feature to create a Command Object. The 'Add Command' feature replaces the ability to edit SQL statements in the 'Show SQL Query' dialog box. Use this dialog box to write your own SQL command (query) which will be represented in Crystal Reports as a Table object.
More Information
-
Additional information about creating and using Command Objects ('Add Command') can be found on our support site and within the Online Help file contained in Crystal Reports.
On our support site search for the technical brief, cr_query_engine.pdf and knowledge base article c2016641 at
http://support.businessobjects.com/search
ASKER
OK, thank you. I'll try that out and see how it goes...
You don't want to edit the SQL to use that since you lose the hour off for lunch during the day. You need each time record to account for the time between the punch out and punch back in during the day.
I am curious why you are getting departments with no data since the query shouldn't be pulling that information in unless there were time sheet records.
In the report you can use a formula like this to get the hours worked in a time card.
DateDiff('h',{Timesht.INPU NCHDTM}, {Timesht.OUTPUNCHDTM})
That formula can be summarized in the person footer and department footer to show the total hours worked.
mlmcc
I am curious why you are getting departments with no data since the query shouldn't be pulling that information in unless there were time sheet records.
In the report you can use a formula like this to get the hours worked in a time card.
DateDiff('h',{Timesht.INPU
That formula can be summarized in the person footer and department footer to show the total hours worked.
mlmcc
:) similar calcs could be performed in SQL too of course - but I'll defer to CR expertise.
as an example, the hours calculated record by record (to account for lunch), but then still summarized as before:
as an example, the hours calculated record by record (to account for lunch), but then still summarized as before:
SELECT
TIMESHT.PERSONFULLNAME
, TIMESHT.PERSONNUM
, TIMESHT.EVENTDATE
, TIMESHT.LABORLEVELNAME3
, MIN(TIMESHT.INPUNCHDTM) AS INPUNCHDTM
, MAX(TIMESHT.OUTPUNCHDTM) AS OUTPUNCHDTM
, MIN(TIMESHT.STARTDTM) AS STARTDTM
, MAX(TIMESHT.ENDDTM) AS ENDDTM
, SUM(hrs_1) as hrs_1
, SUM(hrs_2) as hrs_2
FROM (
select
PERSONFULLNAME
, PERSONNUM
, EVENTDATE
, LABORLEVELNAME3
, INPUNCHDTM
, OUTPUNCHDTM
, STARTDTM
, ENDDTM
, datediff(hour,INPUNCHDTM,OUTPUNCHDTM) as hrs_1 -- not sure which to use
, datediff(hour,STARTDTM,ENDDTM) as hrs_2 -- so do both
from wfcv4.dbo.TIMESHT
WHERE (
EVENTDATE >= {ts '2013-06-15 00:00:00' }
AND EVENTDATE < {ts '2013-07-22 00:00:01' }
)
) AS TIMESHT
GROUP BY
TIMESHT.PERSONFULLNAME
, TIMESHT.PERSONNUM
, TIMESHT.EVENTDATE
, TIMESHT.LABORLEVELNAME3
however, sql server will provide integer hours in this example. One could use minutes instead, than allow CR to apply a formula on the result for presentation.
ASKER
Hi mlmcc
The data coming in would also contain records for those employee's who didn't work any overtime, hence the blank headers.
Any idea of the best way of summing these records by department and person? At the moment I am using a running total to get the min's and max's of the punch in and punch out, then using a formula to calculate the difference in the 2 (max punch - min punch) *24 to get the hours, then in a separate formula 'hours - 8.5' to get the overtime and also factoring in whether the day is a 1 or a 7 to work out if it is all overtime because it is the weekend.
I need to sum all of this data though, and hide those blank headers. This is where my problem lies.
Any ideas? Pretty please?? :) :)
The data coming in would also contain records for those employee's who didn't work any overtime, hence the blank headers.
Any idea of the best way of summing these records by department and person? At the moment I am using a running total to get the min's and max's of the punch in and punch out, then using a formula to calculate the difference in the 2 (max punch - min punch) *24 to get the hours, then in a separate formula 'hours - 8.5' to get the overtime and also factoring in whether the day is a 1 or a 7 to work out if it is all overtime because it is the weekend.
I need to sum all of this data though, and hide those blank headers. This is where my problem lies.
Any ideas? Pretty please?? :) :)
shall we overlook that most/all of it could be done in SQL?
>> also contain records for those employee's who didn't work any overtime
sql: exclude these in the where clause
>> min's and max's of the punch in and punch out
sql: can do this without need of running total
>> calculate the difference in the 2 (max punch - min punch)
sql: what units would you like (there is a choice) and *24 isn't then required
>>separate formula 'hours - 8.5' to get the overtime
sql: can also do this bit
>>whether the day is a 1 or a 7
sql: can also do this
>> sum all of this data though
you would use CR for this
>>hide those blank headers
sql: by excluding the unwanted data this is no longer required
ready to help if you are willing (and able to use SQL)
>> also contain records for those employee's who didn't work any overtime
sql: exclude these in the where clause
>> min's and max's of the punch in and punch out
sql: can do this without need of running total
>> calculate the difference in the 2 (max punch - min punch)
sql: what units would you like (there is a choice) and *24 isn't then required
>>separate formula 'hours - 8.5' to get the overtime
sql: can also do this bit
>>whether the day is a 1 or a 7
sql: can also do this
>> sum all of this data though
you would use CR for this
>>hide those blank headers
sql: by excluding the unwanted data this is no longer required
ready to help if you are willing (and able to use SQL)
ASKER
Willing to give it a go, but somewhat scared about the syntax.. :)
Did I mention that I also need a parameter in there as the report would need to be run for a range of dates. The dates in the SQL currently are just some dates that I put in there for testing.
Can you change the code you have there for the parameter, then I'll give it a try...
Sorry, would have tried it before, but trying to juggle end of financial year duties at the moment, will have a clear couple of hours tonight hopefully
Did I mention that I also need a parameter in there as the report would need to be run for a range of dates. The dates in the SQL currently are just some dates that I put in there for testing.
Can you change the code you have there for the parameter, then I'll give it a try...
Sorry, would have tried it before, but trying to juggle end of financial year duties at the moment, will have a clear couple of hours tonight hopefully
;( as you know I'm not too familiar with the innards of CR (I have access to report writers who do that for me if I've ever needed it)
But I've certainly seen the results of CR that used SQL and took parameters.
I do not wish to take you down an unproductive path (esp. given end-of-year) and you may want to defer the SQL approach until you have more time. Particularly as you may need help adapting to (& learning some) SQL.
& don't forget mlmcc may have the right CR magic to solve your immediate needs.
After this post I will prepare another sql query for you taking latest information into account - maybe it would prove helpful if you do get the time to explore this option. But remember I'm not the CR person and don't know exactly how parameters would be fed in.
But I've certainly seen the results of CR that used SQL and took parameters.
I do not wish to take you down an unproductive path (esp. given end-of-year) and you may want to defer the SQL approach until you have more time. Particularly as you may need help adapting to (& learning some) SQL.
& don't forget mlmcc may have the right CR magic to solve your immediate needs.
After this post I will prepare another sql query for you taking latest information into account - maybe it would prove helpful if you do get the time to explore this option. But remember I'm not the CR person and don't know exactly how parameters would be fed in.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought you said you were a novice? The code worked brilliantly. Just working through the rest of it now.
novice at CR, not novice at SQL :)
btw: does this mean you solved how to use a SQL query in CR - congratulations!
ASKER
Clearly not a novice at SQL! :)
Yes, I used the Add Command, and all worked perfectly. Just doing some verifying now.
Can you help with how to set the parameters in the SQL query? or can I remove the dates that you've put in the SQL query and create a parameter using the event date in CR?
Yes, I used the Add Command, and all worked perfectly. Just doing some verifying now.
Can you help with how to set the parameters in the SQL query? or can I remove the dates that you've put in the SQL query and create a parameter using the event date in CR?
this is where I 'don't know' and my inexperience with CR will become very obvious.
The dates you see in the query as I provided have to be "replaced" by "something" the CR will recognize. I don't know what that "something" is exactly.
Think it will look something like this:
EVENTDATE >= {?startDate}
AND EVENTDATE < {?endDate}
use whatever names you like of course, 'startDate' and 'endDate' are just samples.
The dates you see in the query as I provided have to be "replaced" by "something" the CR will recognize. I don't know what that "something" is exactly.
Think it will look something like this:
EVENTDATE >= {?startDate}
AND EVENTDATE < {?endDate}
use whatever names you like of course, 'startDate' and 'endDate' are just samples.
ASKER
Hi Portlet Paul,
Another question, the data all looks great and the sums are working but I need to add into the SQL query that if the day is a 1 or a 7 any hours worked are overtime. Can you help with this?
That and the parameter and you've solved my problem. :)
Another question, the data all looks great and the sums are working but I need to add into the SQL query that if the day is a 1 or a 7 any hours worked are overtime. Can you help with this?
That and the parameter and you've solved my problem. :)
day 1 = Saturday and day 7 = Sunday I presume
Yes I can add that logic to the SQL, but the day numbers won't match - this is a long story I won't bore you with but different systems use different methods. The technique I will use works in ALL SQL Server versions regardless of language or other settings.
ps: I really cannot give you "the good oil" regarding the parameters - reminder I'm not a CR person.
back soon.
Yes I can add that logic to the SQL, but the day numbers won't match - this is a long story I won't bore you with but different systems use different methods. The technique I will use works in ALL SQL Server versions regardless of language or other settings.
ps: I really cannot give you "the good oil" regarding the parameters - reminder I'm not a CR person.
back soon.
OK, so I wrote all of the following before seeing the latest posts where you're using a SQL query, so none of it may apply anymore. :-)
If the report is grouped by day and then emp, you can use Minimum and Maximum to get the earliest StartTime and ClockIn, and the latest EndTime and ClockOut, for each emp, for each day. If you don't want to see the individual records for each emp on each day, you can suppress the detail section and put the minimums and maximums, and any other fields that you want to see, in the emp group header or footer (if you're using running totals, they'll need to be in the group footer).
In your original post, your Hours column seems to be based on StartTime and EndTime, not ClockIn and ClockOut. Correct?
You can use DateDiff to calculate the hours between StartTime and EndTime, as mlmcc suggested, but you don't want to use "h" (for hours), because then DateDiff would only look at the hours and ignore the minutes, and return an integer result. For example, DateDiff ("h", #07/02/2013 7:59 AM#, #07/02/2013 8:05 AM#) produces 1 (hour), even though it's only 6 minutes. And DateDiff ("h", #07/02/2013 7:00 AM#, #07/02/2013 11:30 AM#) produces 4, instead of 4.5.
You want to use "n" for minutes (or "s" for seconds) and then convert the result to hours. For example:
DateDiff ("n", {Clock In}, {Clock Out}) / 60
You could then summarize that formula for each emp on each day. You could also leave out the "/ 60", to get the minutes, summarize that, and then divide the total by 60 to convert it to hours.
Overtime is trickier though. I think you may have to use a variable for that. If your OT is based on working more than 8 (?) hours in a day (as opposed to more than 40 hours total for the week), you could summarize a formula like the one above to get the total hours for an emp for each day, and then have a formula subtract 8 hours (or whatever) from that to get the OT for the day, but you can't summarize that second formula to get the total OT, because CR won't do a summary on a summary. So, if you need a total for the OT, the OT formula may have to accumulate the total in a variable instead.
You also mentioned weekends, so those would have to be factored in too, but that check could probably be done along with the other checks and calculations.
James
If the report is grouped by day and then emp, you can use Minimum and Maximum to get the earliest StartTime and ClockIn, and the latest EndTime and ClockOut, for each emp, for each day. If you don't want to see the individual records for each emp on each day, you can suppress the detail section and put the minimums and maximums, and any other fields that you want to see, in the emp group header or footer (if you're using running totals, they'll need to be in the group footer).
In your original post, your Hours column seems to be based on StartTime and EndTime, not ClockIn and ClockOut. Correct?
You can use DateDiff to calculate the hours between StartTime and EndTime, as mlmcc suggested, but you don't want to use "h" (for hours), because then DateDiff would only look at the hours and ignore the minutes, and return an integer result. For example, DateDiff ("h", #07/02/2013 7:59 AM#, #07/02/2013 8:05 AM#) produces 1 (hour), even though it's only 6 minutes. And DateDiff ("h", #07/02/2013 7:00 AM#, #07/02/2013 11:30 AM#) produces 4, instead of 4.5.
You want to use "n" for minutes (or "s" for seconds) and then convert the result to hours. For example:
DateDiff ("n", {Clock In}, {Clock Out}) / 60
You could then summarize that formula for each emp on each day. You could also leave out the "/ 60", to get the minutes, summarize that, and then divide the total by 60 to convert it to hours.
Overtime is trickier though. I think you may have to use a variable for that. If your OT is based on working more than 8 (?) hours in a day (as opposed to more than 40 hours total for the week), you could summarize a formula like the one above to get the total hours for an emp for each day, and then have a formula subtract 8 hours (or whatever) from that to get the OT for the day, but you can't summarize that second formula to get the total OT, because CR won't do a summary on a summary. So, if you need a total for the OT, the OT formula may have to accumulate the total in a variable instead.
You also mentioned weekends, so those would have to be factored in too, but that check could probably be done along with the other checks and calculations.
James
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AS IS (line 35 & 36 in my original at ID: 39295846)
HAVING
SUM(tot_minutes) > (8.5 * 60) -- only those employee's who did work any overtime
change that to (TO BE)
HAVING
( datediff(day,0,TIMESHT.EVE NTDATE) % 7 < 5 AND SUM(tot_minutes) > (8.5 * 60) )
OR datediff(day,0,TIMESHT.EVE NTDATE) % 7 IN (5,6)
-----------------
This logic is:
Include in results IF:
day of week is Monday to Friday AND hours > 8.5
OR
day of week is Saturday or Sunday
------------------
technical notes:
1900-01-01 was a Monday
datediff(day,0,<<anydatehe re>>) is the number of days since 1900-01-01
if we use "modulus 7" of that datediff number, we get the number of days "remaining"
so,
if a date is a Monday, "modulus 7" of that date = 0
...
if a date is a Saturday, "modulus 7" of that date = 5
if a date is a Sunday, "modulus 7" of that date = 6
In SQL Server "modulus" is performed with the percent symbol, i.e. that is what this does
datediff(day,0,TIMESHT.EVE NTDATE) % 7
clear (as mud)? well I hope it's a bit clearer than it was.
------------
let me know if you are still having difficulty with those parameters - but I'm not an expert in those.
HAVING
SUM(tot_minutes) > (8.5 * 60) -- only those employee's who did work any overtime
change that to (TO BE)
HAVING
( datediff(day,0,TIMESHT.EVE
OR datediff(day,0,TIMESHT.EVE
-----------------
This logic is:
Include in results IF:
day of week is Monday to Friday AND hours > 8.5
OR
day of week is Saturday or Sunday
------------------
technical notes:
1900-01-01 was a Monday
datediff(day,0,<<anydatehe
if we use "modulus 7" of that datediff number, we get the number of days "remaining"
so,
if a date is a Monday, "modulus 7" of that date = 0
...
if a date is a Saturday, "modulus 7" of that date = 5
if a date is a Sunday, "modulus 7" of that date = 6
In SQL Server "modulus" is performed with the percent symbol, i.e. that is what this does
datediff(day,0,TIMESHT.EVE
clear (as mud)? well I hope it's a bit clearer than it was.
------------
let me know if you are still having difficulty with those parameters - but I'm not an expert in those.
To use parameters in a CR Command: ...
@James, thank you, thank you! That's the bit I could not help with.
Cheers!
@James, thank you, thank you! That's the bit I could not help with.
Cheers!
ASKER
Sensational. All working beautifully. Thank you both for your help!
ASKER
Excellent feedback on how to complete the instructions, very easy to follow. Awesome.
:) and so your adventure into SQL begins. My bet is this report is also quite quick?
One of the advantages of the approach taken is that less data is pumped into CR (e.g those records you needed to hide are no longer even in existence). There is of course a little more processing time taken-up on the sql server side but it will be very very slight (as long as the SQL query is good).
Enjoy! I learned a bit on "how" in CR along the way too. Excellent.
Thanks for the grade.
Cheers, Paul
One of the advantages of the approach taken is that less data is pumped into CR (e.g those records you needed to hide are no longer even in existence). There is of course a little more processing time taken-up on the sql server side but it will be very very slight (as long as the SQL query is good).
Enjoy! I learned a bit on "how" in CR along the way too. Excellent.
Thanks for the grade.
Cheers, Paul
ASKER
The report is super speedy. Very happy. Just need to format it a bit more now. About to post a formatting question! :)
You're welcome. Glad I could help.
James
James
I'll pass on the formatting :) CR expertise required there for sure.
can you post that sql query, most of this could be achieved in sql I believe.