Solved

Totalling summarized fields and hiding headers before blank sections

Posted on 2013-07-02
32
632 Views
Last Modified: 2013-07-03
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!
0
Comment
Question by:spectrum17
  • 16
  • 12
  • 3
  • +1
32 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 

Author Comment

by:spectrum17
Comment Utility
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'})
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Open in new window

Does this help?
0
 

Author Comment

by:spectrum17
Comment Utility
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....
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:) 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.
0
 

Author Comment

by:spectrum17
Comment Utility
LOL, I went into 'Show Query'
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 

Author Comment

by:spectrum17
Comment Utility
OK, thank you. I'll try that out and see how it goes...
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:) 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

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

Author Comment

by:spectrum17
Comment Utility
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?? :) :)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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)
0
 

Author Comment

by:spectrum17
Comment Utility
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
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
;( 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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
Comment Utility
Here is a sql query that I believe does most of what you indicate is needed, but there are still things you would do to this in CR. I have included comments (that can be removed). The largest difference between the output of this query and the data you currently get is that you would not see 2 records (1 for morning, 1 for afternoon) but a required calculation is still performed at that level, but then summarized. Additionally because I have included a "having clause" only those people who have performed overtime would be included (i.e. you should get less data).

A few things to note:
a. I calculate the 'hours'; but as a number of minutes to give greater accuracy
b. I have provided a column that indicates day of week, but the method chosen always returns Monday as zero - you may prefer to use a more familiar CR method instead
c. you would need to learn how to include your data parameters where indicated.

If not immediately, perhaps this will be of some assistance if you explore SQL a bit more one day.
SELECT
       TIMESHT.PERSONFULLNAME
     , TIMESHT.PERSONNUM
     , TIMESHT.EVENTDATE
     , TIMESHT.LABORLEVELNAME3
     , MIN(TIMESHT.INPUNCHDTM)               AS INPUNCHDTM -- min's and max's of the punch in and punch out
     , MAX(TIMESHT.OUTPUNCHDTM)              AS OUTPUNCHDTM
     , MIN(TIMESHT.STARTDTM)                 AS STARTDTM
     , MAX(TIMESHT.ENDDTM)                   AS ENDDTM
     , SUM(tot_minutes)                      AS tot_minutes    --  can convert to hours in CR (e.g. /60 )
     , SUM(tot_minutes) - (8.5 * 60)         AS overtime_hrs   -- formula 'hours - 8.5' to get the overtime
     , datediff(day,0,TIMESHT.EVENTDATE) % 7 AS day_of_week_no -- 0 is Monday! by this method, or stick with CR approach
FROM (
        select
              PERSONFULLNAME
            , PERSONNUM
            , EVENTDATE
            , LABORLEVELNAME3
            , INPUNCHDTM
            , OUTPUNCHDTM
            , STARTDTM
            , ENDDTM
            , datediff(MINUTE,INPUNCHDTM,OUTPUNCHDTM) as tot_minutes -- calculate the difference in the 2 (max punch - min punch) 
        from wfcv4.dbo.TIMESHT
        WHERE (
                  EVENTDATE >= {ts '2013-06-15 00:00:00' }      -- your date parameter goes here
                  AND EVENTDATE < {ts '2013-07-22 00:00:01' }   -- and here
                  )
     ) AS TIMESHT
GROUP BY
       TIMESHT.PERSONFULLNAME
     , TIMESHT.PERSONNUM
     , TIMESHT.EVENTDATE
     , TIMESHT.LABORLEVELNAME3
HAVING
       SUM(tot_minutes) > (8.5 * 60) -- only those employee's who did work any overtime
;

Open in new window

0
 

Author Comment

by:spectrum17
Comment Utility
I thought you said you were a novice? The code worked brilliantly. Just working through the rest of it now.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
novice at CR, not novice at SQL :)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
btw: does this mean you solved how to use a SQL query in CR - congratulations!
0
 

Author Comment

by:spectrum17
Comment Utility
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 

Author Comment

by:spectrum17
Comment Utility
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. :)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 100 total points
Comment Utility
To use parameters in a CR Command:

 Create the parameters in the Command edit window.  It has to be there, as opposed to the main CR editor window.  Once the parameters are created, they can be edited from the main window.

 Then, basically, insert the parameter name where you need it in the SQL code.  The simplest thing is to position the cursor where you want the parameter and then double-click the parameter name in the parameter list on the right.  When CR runs the query, it will simply replace the parameter name with its value.  If the parameter value could be a string, put the parameter name in single quotes.  For a date or datetime, I don't think you'll need them.

 James
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
To use parameters in a CR Command: ...

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

Cheers!
0
 

Author Comment

by:spectrum17
Comment Utility
Sensational. All working beautifully. Thank you both for your help!
0
 

Author Closing Comment

by:spectrum17
Comment Utility
Excellent feedback on how to complete the instructions, very easy to follow. Awesome.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:) 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
0
 

Author Comment

by:spectrum17
Comment Utility
The report is super speedy. Very happy. Just need to format it a bit more now. About to post a formatting question! :)
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'll pass on the formatting :)  CR expertise required there for sure.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now