11 Replies Latest reply on Nov 17, 2016 7:09 AM by erolst

    Summary fields exclusions

    MikeWile

      I have a report with Employees, Department, Business Unit, Date, Hours. What I have so far is: I search for employees within a date range and Department 1. Then I extend the search to employees within the same date range that are in Department 2. My problem is I want to show only employees from this list that have SOME items check in the Business Unit. I need their total hours worked so I can't constrain the field for * in the Business Unit field. How can I show only employees in a date range from 2 departments with SOME items in the Business Unit checked.

       

      Right now the report just shows everyone that fulfills my search but it shows some employees with no items in the Business Unit. What I'm actually trying to achieve is a report of employees with some time in the Business Unit and a summary of ALL hours worked in the date range so I can calculate the percentage of time spent in the Business Unit from their total hours.

        • 1. Re: Summary fields exclusions
          Mike_Mitchell

          Your question isn't 100% clear - it appears you either want to constrain a set to a set of Business Unit values, or to those items that have some value in Business Unit. (And what "Business Unit" is isn't completely clear, either.) If you just want to show the records that have something in the Business Unit field, you can use something like this:

           

          New Find request

          Business Unit "="Choose "Omit"

          Constrain Found Set

           

          This will omit all records in the current found set that have no entry in Business Unit.

           

          If you want only some Business Unit values, you can use:

           

          New Find request

          Business Unit [first value]

          New Find Request

          Business Unit [second value]

          New Find Request

          Business Unit [third value]

          Constrain Found Set

           

          You can also solve this by using a single set of Find request, but it gets a little complicated because of the number of permutations possible.

           

          HTH

          • 2. Re: Summary fields exclusions
            MikeWile

            Sorry for the confusion. I can't constrain the BU field because then the hours that aren't BU hours will be lost. In the image below, the items circled are BUs and the hours are shown for that time. So, because the first employee has no BU hours, I wouldn't want that employee to show in the report. The second employee has 135.68 total hours and  27.5 hours spent on a BU for a percentage of 20.26. The third employee has 107.25 total hours and 6 hours (5.75 + .25) spent on BU. That percentage would be 5.59 of total hours spent on BU.

             

             

            BU.png

            • 3. Re: Summary fields exclusions
              Mike_Mitchell

              Are these all subsummary parts? What does each record in this table represent?

              • 4. Re: Summary fields exclusions
                erolst

                You want to exclude employees that have no BU entry in the found set whatsoever.

                 

                You can do it this way (utilising the "fast summary" technique):

                 

                Create a summary field of type "Count of" for the primary key field.

                Create another summary field of type "List of" for the BU field.

                 

                Add these lines to the end of your report script:

                 

                Go to Record [ first ]

                Loop

                  Set Variable [ $groupHasNoBUs ; IsEmpty ( GetSummary ( theBUListSummaryField ; theEmployeeBreakField ) ]

                  # [ the field you sort by to get the groups from your screenshot ]

                  Set Variable [ $groupCount ; GetSummary ( thePrimaryCountSummaryField ; theEmployeeBreakField ]

                  Set Variable [ $nextGroupAt ; Get ( RecordNumber + $groupCount ) ]

                  Set Variable [ $isLastGroup ; $nextGroupAt > Get ( FoundCount ) ]

                 

                  Exit Loop if [ $groupHasNoBUs and $isLastGroup ]

                 

                  If [ $groupHasNoBUs ]

                    Omit Multiple Records [ by calculation: $groupCount ]

                  Else if [ not $isLastGroup ]

                    Go to Record Number [ by calculation: $nextGroupAt ]

                  End if

                End Loop

                1 of 1 people found this helpful
                • 5. Re: Summary fields exclusions
                  MikeWile

                  Thanks. I'll try this and let you know how it goes.

                  • 6. Re: Summary fields exclusions
                    erolst

                    I just noticed there was a logic error in the original code; please note the added/modified lines in bold.

                    • 7. Re: Summary fields exclusions
                      MikeWile

                      Thanks again.

                      • 8. Re: Summary fields exclusions
                        MikeWile

                        Hi Mike, These are all subsummary. The first line shows the employee name (blurred) and their total hours worked for the selected date range. The other lines show either hours spent on BU or hours not spent on BU.

                        • 9. Re: Summary fields exclusions
                          MikeWile

                          After moving the "+ $groupCount" outside the parens, this worked GREAT. Saved me countless hours.

                           

                          Set Variable [ $nextGroupAt ; Get ( RecordNumber + $groupCount ) ]

                           

                          Thanks.

                          • 10. Re: Summary fields exclusions
                            MikeWile

                            One last change I made: Moved the "exit loop if" to right before the "end loop" statement. It was leaving the last group with no BUs.

                            • 11. Re: Summary fields exclusions
                              erolst

                              I wrote this from memory so couldn't test it. In any loop, it's always the edge cases that are giving grief