Totalling summarized fields and hiding headers before blank sections

how are you gathering the base data? a sql query? (what dbms vendor?)

can you post that sql query, most of this could be achieved in sql I believe.

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">={ts '2013-06-15 00:00:00'} AND "TIMESHT"."EVENTDATE"<{ts '2013-07-22 00:00:01'})

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
*/

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

Select allOpen in new window

Does this help?

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

:) 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.

LOL, I went into 'Show Query'

I think this will lead to an answer:
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.
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

NB  your CR version is important answers differ according to version.

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.INPUNCHDTM}, {Timesht.OUTPUNCHDTM})

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:

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

Select allOpen in new window

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.

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?? :) :)

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)

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

;( 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.

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!

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?

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.

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. :)

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.

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

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.EVENTDATE) % 7 < 5 AND SUM(tot_minutes) > (8.5 * 60)  )
       OR datediff(day,0,TIMESHT.EVENTDATE) % 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,<<anydatehere>>) 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.EVENTDATE) % 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.

To use parameters in a CR Command: ...

@James, thank you, thank you!  That's the bit I could not help with.

Cheers!

Sensational. All working beautifully. Thank you both for your help!

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

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

I'll pass on the formatting :)  CR expertise required there for sure.