3 Replies Latest reply on Aug 28, 2014 2:54 PM by philmodjunk

    Count records based on 2 criteria for cross tab report

    robinsugiura

      Title

      Count records based on 2 criteria for cross tab report

      Post

           I am trying to make a cross tab report that shows counts for records that meet 2 criteria: field1 (30 values, Y axis of table) and field2 (5 values, X axis of table). 

           I think what I need to do is make two fields for each cell of the cross tab report table: 

           Calculation CalcField1    from TABLE = If ( FIELD1 = "F1VALUE1" and FIELD2 = "F2VALUE1" ; RESULT )

           Summary SummField1    = total of CalcField1

           and then put SummField1 in the table at the location of field 1 value1 and field 2 value 1. 

            

           Where I am stuck is what is the RESULT? 

           I found a similar table someone else made in the same database, and their RESULT is a calculation field CountRecordsAll    from TABLE = Sum (Constant) 

           I tried to use that same field as my result, but no values are being calculated in those fields. 

            

           Can someone help me figure out what I am doing wrong, or give me other ideas how to accomplish this?

           Thanks!

        • 1. Re: Count records based on 2 criteria for cross tab report
          philmodjunk

               RESULT should be whatever field has the value that you want to total in the summary field if Field1 and Field2 have the specified values in the IF function.

               There are other methods for producing a cross tab report, BTW. There are methods that use one row (Posibly filtered) portals for each "cell" of the cross tab report. Other methods use Execute SQL to produce the needed aggregate values for each cell of the report.

          • 2. Re: Count records based on 2 criteria for cross tab report
            robinsugiura

                 I just found my problem after hours and hours of fiddling with it and searching online -- I was using an ampersand instead of "and" in the calculation. 

                 It works like a dream now. 

                  

                 If I hadn't already made the 300 fields for this report, I would have totally tried one of the other ways. I know I will be asked to make more cross tab reports in the future, so if you have any good links to resources on how to do the portals method or the ExecuteSQL method to make a cross tab report, I would really appreciate it if you could point them out.

                 Thanks! 

            • 3. Re: Count records based on 2 criteria for cross tab report
              philmodjunk

                   Sorry. I don't have a good thread URL to which I can point you. There are a number of training resources out there so a web search might turn up something useful. In the meantime, I've just launched the first in what I hope will be a series of instructional files on different FileMaker Topics. I'll keep your comments in mind as I plan out what I might put into Adventures in FileMaing #3, #4, etc. (#1 is done and #2 is undergoing development...)

              Caulkins Consulting, Home of Adventures In FileMaking