4 Replies Latest reply on Feb 7, 2016 9:37 PM by RobertWard

    Counting a SubSummary Value

    RobertWard

      I hope someone will be able to assist on this or point me in the right direction.

       

      I have a child table with program records and a Fiscal Year (FY). I need to produce a report which displays the number of people and how many programs they took e.g.

      2 people took 1 program

      1 person took 2 programs

      4 people took 3 programs.

       

      The interesting part is that even if a person took 2 programs, each program may have a different FY, which means they took 1 program in FY 2012 and 1 program in FY 2013 for the same person. The initial found set contains just the one FY. The sub-summary works fine, I can count the number of programs by each person. My problem is that I need to count the people who have 1 program, 2 programs, 3 programs and so on. When my records are sorted (by the Client_ID) and grouped I get the names together (see attachment). In the screen shot you see that the are (2) people who took (2) programs. But how do I get to count those two people? That is where I am stuck at. I am trying to get a count, not sum up the values. I hope that makes some sense?

       

      I have been searching all around the internet and even in this community, but I haven't quite found the same situation.

       

      Thanks.

        • 1. Re: Counting a SubSummary Value
          kglsong

          Create a calculation field in the parent table (assume you have the relationships built properly between the parent and child) to count the number of enrolments in the child table; then you can do search on this calculation field.

          • 2. Re: Counting a SubSummary Value
            RobertWard

            No, I don't feel that is what I am looking for, but thank you for you reply Kglsong.

            • 3. Re: Counting a SubSummary Value
              user19752

              Export programs field with "Group by" option, then you get summary of each person. You can get result in the file as adding count of summary field to it.

              • 4. Re: Counting a SubSummary Value
                RobertWard

                I managed to resolve the issue using a multitude of steps.

                 

                The first step was to create a calculation field which tired itself to the FY. A simple Case ( FY = 2015 ; 1 ; "" ), just assigned a 1, created in the child table.

                 

                Step 2 was to create a calculation field to count those 1's in my child table.

                 

                Step 3 was to use that calculation in my parent table and use that as the basis for my summarized break field, and sort by that value in my child table.

                 

                This sub summarized my programs by FY wonderfully. However, I changed the FY hard coded value to use a global variable, capturing what the user types in for which FY report the user wants to run.

                 

                Since my program table is a child table it contains many programs by a one person (one to many). I then added scripting code to omit duplicate names, which reduced my counts to their perfect numbers, such as when 1 person takes 4 programs. A simple count of ID's gives the the grouped people count contained in the grouped programs.

                 

                This all worked together beautifully! Thank you everyone for your replies. I do appreciate your time and suggestions.