12 Replies Latest reply: May 15, 2014 9:59 AM by Bob Yarnall RSS

    Trouble getting right data

    Bob Yarnall

      I constantly have problems with trying to extract data from Kronos timekeeping reports.

      In this case, I need the hours transferred from one department to another for last year.  Everything works fine until Monarch (V6 Pro) gets to the end of the report. 

        :confused:  

      [font="courier"] Hours Report                                                         Page 0020

      12/22/02 through 12/20/03                                     02/23/2004 15:07

       

      -


       

      111111111  SWOGGER, MEGHAN                  01 6400 0500           PRN-30     

       

      Reporting from 01/03/03 to 12/20/03 for this employee

       

          CO DEPT SKMX : 01 7260 0500

       

             01-REG   : 3.00

       

          Employee grand totals :

       

             01-REG   : 3.00

       

      -


       

      222222222  SCHLUTERMAN, JACK                01 6400 1630           HOURLY-30  

       

      Reporting from 01/03/03 to 12/20/03 for this employee

       

      -


       

      333333333  TUCKER, MISTY                    01 6400 1630           HOURLY-30  

       

      Reporting from 01/03/03 to 12/20/03 for this employee

       

      -


       

      Report summary totals :

       

          CO DEPT SKMX : 01 7260 0200

       

             01-REG   : 47.25

       

          CO DEPT SKMX : 01 7260 0300

       

             01-REG   : 353.00       02-OVT   : 16.50

       

          CO DEPT SKMX : 01 7260 0500

       

             01-REG   : 52.50        45-PREM  : 0.50

       

      /font[/quote]My detail line is trapping on CO DEPT SKMX :

      I’m extracting 01 7260 0500 from line one and 01-REG   : 3.00 from line 3.

      I then append 111111111  SWOGGER, MEGHAN                  01 6400 0500 from the line above.

      The problem is that all of the Report summary totals are being associated with employee 333333333 when in fact there are no hours to report for this employee.

        • Trouble getting right data
          Bruce _

          Bob

           

          I tried something & it seems to work out. One caveat is that I have Ver. 7.01 pro, but I think it will also work in Ver. 6, as I did not use anything fancy. Also I assume you ONLY want the summery info and not the employee details.

           

          For the detail line I used a double-line trap on "CO DEPT", picked up the department name, regular & OT/extended hours. Note that the information for employee 11111111 is being picked up, but don't worry. Next I added an append that starts a line with "Report", which picks up the lines "reporting from 01/...." and the "report summary totals". You should now have 4 lines of data. One line has a comment of "reporting from 01/01/03 to..this employee (which is bogus data) and 3 lines of totals that start with "report summary totals. Then filter out anything that starts with "Reporting" (compared to "report").

           

          I hope this is clearer than mud to you, but if not please post back & I will try again. If you want I can also private email the mod file to you.

           

          Good luck

          Bruce

          • Trouble getting right data
            Grant Perkins

            Bob,

             

            A variation on Bruce's idea.

             

            If you set up 2 line append based on "Reporting" (or however much of the line you feel is necessary) with the 'field' being the second line you will get a field continaing a number of -'s ("----


            ") for any employee record with no time details. If they have time details, depending on how you set up the field length, you may see "CO DEPT .... etc".

             

            That gives you some filtering options and also provides for a list of employees with no times recorded in the period of the report.

             

            Caveat - the comments are based on the sample provided!!!

             

            I feel there must be another way that we have not yet spotted but it's certainly not obvious. I can think of a few approaches that would only be potentially useful if no employee had more than one detial record - but my guess is that such a rule may not be reliable.

             

            Hope this helps.

             

            Grant

            • Trouble getting right data
              Bob Yarnall

              Obviously I was not clear enough with my first post.

              The summary totals[/b] are the problem and I want to ignore them.  What I want[/i] is to tell Monarch that when it finds Report summary totals[/b] that it should stop processing the report.  I only want the individuals that have hours that were transferred.   In the example I want MEGHANs hours and I do get that.  But what I also get is the summary hours associated with MISTY when in fact she had none.  I can not trap on Employee grand totals because the employee may have had hours transferred to other departments also and I want to pull which department the hours went into.

              • Trouble getting right data
                Bruce _

                Bob I can look at this at home tonight. Can you post what the end report should look like?

                 

                Thx

                • Trouble getting right data
                  Bob Yarnall

                  Here is a sample of the desired output.

                  [font="courier"]SSAN            NAME          HOME   HSKM   TO     TSKM   PAY1      HRS1   PAY2      HRS2

                  111111111   SWOGGER, MEGHAN   6400   0500   7260   0500   01-REG   3.00     

                  111111111   SWOGGER, MEGHAN   6400   0500   7340   0500   01-REG   6.25   45-PREM   2.50

                  444444444   TALBOT, SHANNON   6400   0300   7340   0300   01-REG   4.50     

                  555555555   TOUBIA, CARRIE    6400   0300   7260   0300   01-REG   32.75     

                  555555555   TOUBIA, CARRIE    6400   0300   7280   0300   01-REG   6.75     

                  555555555   TOUBIA, CARRIE    6400   0300   7340   0300   01-REG   3.50      /font[/quote]The first line for MEGHAN is from the previously posted report.  I am running this model against eight separate reports which is where the other record come from.

                  What I have to do now is go back to each report, find the last person on that report and delete their record from the output file.  From the sample report above, I have to delete 3 records for MISTY – hours = 47.25, 353.00, and 52.50.

                  • Trouble getting right data
                    Bob Yarnall

                    I figured out a solution.

                    I added another Append selection trapping on the word Report[/b] and selecting the left 14 characters.  I hide this field because I really do not want it in the output.  Then I filter the field to be not equal to Report summary /b.  That will suppress the summary totals.

                    • Trouble getting right data
                      Bob Yarnall

                      Maybe I didn’t figure it out.  After re-reading Grant’s solution I guess that is what he was suggesting.  For some reason it didn’t click right away.

                      • Trouble getting right data
                        Grant Perkins

                        Bob,

                         

                        Sounds like you reached pretty much the same point as I was at. Much better that you do it yourself as it should have a longer 'life' in memory that way!

                         

                        Does it work for several samples of the report? If so it may be reliable enough to use with minimal future checking.

                         

                        Grant

                         

                        Originally posted by Bob Yarnall:

                        Maybe I didn’t figure it out.  After re-reading Grant’s solution I guess that is what he was suggesting.  For some reason it didn’t click right away. /b[/quote]

                        • Trouble getting right data
                          Bruce _

                          I gave it a try last night, but it realy did not lead anywere. I tried using the line that starts "REPOR" to catch those lines, appended the employee data and used a footer for the time data.

                           

                          Grant's idea of trapping "----" seems the smarter way to go. Fooled around with it for 2 minutes, but now I have to get to work

                           

                          Bruce

                          • Trouble getting right data
                            Grant Perkins

                            Bruce,

                             

                            Re-reading my post I still did not make the idea clear - apologies once more.

                             

                            The idea is to trap on "Reporting" (i.e. more than "Report") to get at the employee record areas and exclude the summary area, then use the data in the field from the second line to filter the records.

                             

                            So an employee with no hours to show on this report would have some dashes in the field. The last employee will have the summary fields appended to their record but, if they have no valid hours to report, they can be filtered out by excluding all records with dashes in that field.

                             

                            There is another potetential problem I have subsequently though of and not yet looked at.

                             

                            If the last employee records has some valid times I assume that the filter idea may fail but that an apparently duplicate record would appear. In that case we woould need to find a suitable way identify that situation and eliminate the unrequired record.

                             

                            Maybe all that is needed is to discard the last record?

                             

                            Grant

                             

                             

                            Originally posted by Bruce:

                            I gave it a try last night, but it realy did not lead anywere. I tried using the line that starts "REPOR" to catch those lines, appended the employee data and used a footer for the time data.

                             

                            Grant's idea of trapping "----" seems the smarter way to go. Fooled around with it for 2 minutes, but now I have to get to work

                             

                            Bruce /b[/quote]

                            • Trouble getting right data
                              Bruce _

                              Bob

                               

                              Can you use an text editor to chop off the last section of the report before sending to Monarch? Making this part go away would solve a lot of your problems.

                               

                              Bruce

                              • Trouble getting right data
                                Bob Yarnall

                                I ran a report in which MISTY had transferred hours.  My filter worked in that case also so it appears that I have a good solution for this[/i] report.  Now I just have to find a solution to some of the other Kronos reports.

                                 

                                Bruce,

                                I did use a text editor to chop the report but I was hoping that I would not have to do that every time I needed to run these reports.  As I said before, I am using this extract on 8 reports at a time and that number may increase depending on what Finance wants next time.