5 Replies Latest reply on Jul 30, 2013 9:36 AM by philmodjunk

    Calculating results based on a list sort

    DianeJohnson

      Title

      Calculating results based on a list sort

      Post

           Here is the scenario.

           I have a list of employees with a job title and a work center. I have an authorized headcount for each job title. I have a table of people with their job title and work center. I have another table that lists the job titles, the work center for each job title and the authorized headcount for each job title. 

           I am trying to create a list that will subtotal the existing employees so using a summary field in the people table, I can obtain the total number of employees for each job title. 

           This is where it gets tricky .... at least for me. I know I am missing something blatent here.

           Problem 1: I don't know how to get the authorized headcount for the job title sub-summary bar. I don't even want to get started listing all the ways I have tried. I think I have it working with the people table and the job title table related by job title. I just don't know if that's the right way because I can't get through problem 2.

           Problem 2: I want subtract the total number of actual employees subtotal from the authorized headcount for a job title in the sub-summary header. When I subtract the total authorized in a calculated field in the people table, it subtracts the total authorized heacount of all job titles from the actual count of the one in the sub-summary list. Messy to explain so I'm sorry if it doesn't make sense.

           Problem 3: On the same list, I would also like to sort by work center and there again have a total authorized versus the actual headcount. But that would require coming up with a subtotal in the job title table? I don't know. 

           In the uploaded screen shot I changed the font color to white for the names and for the position just to be anonymous. 

      Screen_Shot_2013-07-26_at_11.35.57_PM.png

        • 1. Re: Calculating results based on a list sort
          philmodjunk

               To access a sub total from a summary field like you can get in a sub summary layout part. Use the GetSummary function. The "break field" parameter is the same field that you would select as the "when sorted by" field in the sub summary layout part. Just like the sub summary part, the records have to be sorted by the break field and the total is determined by what records are in your current found set.

          • 2. Re: Calculating results based on a list sort
            DianeJohnson

                 Oh Phil....... so close.

                 I got it to display but the summary is still not correct. 

                 In the Authorized headcount table I have several job titles. Each one has an authorized headcount. I created a summary field to Total the authorized headcount.

                 In the People table I created a calculation field to GetSummary ( totalHeadCountAuth, workcenter) hoping to sum the authorized headcount based on the workcenter. I based the breakfield on the workcenter in the Headcount table, not the current work center for the people in the people table. 

                 After I based the break field on the headcount table, the get summary calculation field is now visible in the sub summary part that is sorted by workcenter.

                 But..... the total that is visible is still only the first job title authorized headcount listed in the headcount table for that work center. I can't seem to get it to total the headcount for multiple job titles in one work center. The number in the far right is the authorized headcount for the individuals specific job title.

            • 3. Re: Calculating results based on a list sort
              philmodjunk

                   Just like placing a summary field in a sub summary part to show a subtotal only works for a summary defined in the layouts table--not a related table. GetSummary must be set up to work with summary fields in the same table as your report layout's table. It only works off the current found set and sort order. You won't get accurate results when you then attempt to access a field that uses that function from the context of another table.

                   I'm not sure that I have your tables/relationships correct:

                   AuthHeadCounts-------<Employees

                   AuthHeadCounts::WorkCenter = Employees::WorkCenter AND
                   AuthHeadCounts::Job TItle = Employees::JobTitle

                   Is that correct? If not, please upload a screen shot of the relevant table occurrences.

              • 4. Re: Calculating results based on a list sort
                DianeJohnson

                     Phil,

                     I think I have gone down a rabbit hole so I am going to try creating portals.

                     I have a portal with just one job title in it (on the right in the image). I have another portal with the same job title as its filter and only one row and a summary field to tell me the total number of that job title (on the left in the image)

                     I have another field in the same table with the total authorized for that job title. For example. I have a job title of manager. I also have a summary field in the table of the total number of managers authorized. 

                     How can I subract the summary field that is filtered in the portal from the total authorized? I want the Delta

                • 5. Re: Calculating results based on a list sort
                  philmodjunk

                       You cannot access the subtotal in your filtered portal in your calculation.

                       Please post or confirm the relationships between the tables involved.