2 Replies Latest reply on Jul 11, 2017 9:35 AM by mikemccabe29

    On getting a count of unique instances of sub-groups in a sub-summary report

    mikemccabe29

      I’m trying to find an easy way to count unique instances of a field in a sub-summary report that also calculates numerical totals for the records in that table.

       

      Here is a simple example of that table:

       

       

      Transactions Table:

      id_TXN   Tx_Type       id_donor      Tx_Amount

      txn1        txTYPEA       donor1        $1

      txn2        txTYPEA       donor1        $3

      txn3        txTYPEA       donor2        $5

      txn4        txTYPEB       donor2        $5

      txn5        txTYPEB       donor3        $10

       

       

      REPORT Needed:

      Total txTYPEA donations =  $9.00

      # donors for txTYPEA = 2

       

      Total txTYPEB donations =  $15.00

      # donors for txTYPEB = 2

       

      How do I create a field that gives me the # donors for each txType_  ?

       

       

      Thanks

       

       

      I have tried the reciprocal method by creating the following 3 fields:

       

      _TotCount = count of donor_id (donor1…3)

      ### NOTE: I’ve tried checking ‘running count’ and unchecking ‘running count’

       

      This just returns the total of my found set.

       

       

      _FractionOf_idCon = 

      1/GetSummary ( _TotCount ; donor_id )

       

      ### NOTE: This just returns BLANKS in my report

       

       

       

       

      &

      _Unique_idCons =

      Summary = Total of _FractionOf_idCon

       

      ### NOTE: This also just returns BLANKS in my report

        • 1. Re: On getting a count of unique instances of sub-groups in a sub-summary report
          beverly

          Welcome, @mikemccabe29! The Community Feedback space/place is for posting comments & questions on the forum. You may try to move this post, by following these instructions:

           

          In the meantime, perhaps a simple sub-summary report using the aggregates (summary fields) are needed? The Count type and Total type summary fields, for example, can be placed in various parts and the value will change according to that location and the Sort of the found records. The Count of a "Tx_Type" will be just that particular type if the sub-summary part is for that summary field and sorted by it. The same summary field in the footer will be ALL found records.

           

          References

          Summary field types:

          "Summary layout parts":

          "Sorting records":

           

          If the summarized report is not what you meant, then there is a function to extract using SQL functions. If you need help with that, repost back.

          This is not "found set aware", so the queries must use that as part of the WHERE to narrow down a 'found set'.

           

          The calculated aggregates you tried, don't quite work the way(s) you tried.

          These work well, but with the caveats (only with this type of data):

          several fields in a record.

          related fields whether displayed in a portal or not.

          repeating fields.

          If you have your data as related fields, perhaps?

           

          Another possibility is the GetSummary function (calculation):

          This works on the same principle as the summary field in the sub-summary part, but you specify the summary field and the sort field. There is no need to add/show in a sub-summary part.

           

          Tell us more of what you need to do with the data once you have it? Attachments may be posted by clicking the "Use advanced editor" in the upper right of a post. You may post a sample FM file or PDFs. Screenshots may be added "inline" with the Insert Image icon in the editor(s). Screenshot of your relationships or layouts may be needed to answer more fully, for example.

          HTH,

          Beverly

          • 2. Re: On getting a count of unique instances of sub-groups in a sub-summary report
            mikemccabe29

            Hi Beverly,

             

            Thanks for letting me know about moving this to 'Discussions.'  I wasn't clear on that, but will try to keep them straight.

             

            Your notes were helpful.  It seems as if it was the SORT that was getting me.  For some reason the sort didn't affect my stand-alone file, but in my production file, that field (the id_donor field) had to be included in the sort in order to see any data in my sub-summary report.

             

            For some reason I was being thick in getting this concept, so it also helped me keep the flow of the logic by numbering the fields so I could see keep track of what was happening within each field -- this also seemed to help me understand why the sort could affect this:

             

            1SumCount_idDonor -- Summary = Count of idDonor {NOT a 'Running count' & Summarize repetitions 'All Together'}

            2FractionOf_idDonor -- Calculation  = Unstored, from Transaction, = 1/ GetSummary ( 1SumCount_idDonor ; idDonor )

            3CountUnique_idDonor -- Summary = Total of 2FractionOf_idDonor {NOT a 'Running total' & Summarize repetitions 'All Together'}

             

            Thanks again.

             

            Mike