6 Replies Latest reply on Apr 1, 2017 3:05 PM by SteveMartino

    Cross tab report, total of found set

    SteveMartino

      Hello forum.  I had a question about a cross tab report, hopefully the pic will help.

      I set up a cross tab report, self join relationship where:

      TO Service::ID = TO Service_Report_5yr::ID. 

      All the totals are from the related TO, in one row, one field filtered portals.

      It appears everything totals properly-rows and columns, when all records are shown-first layout.

      However in layouts 2 & 3, I've created found sets, (based on Reason for Call).  The rows still total properly, but the columns do not (if at all).

      Is there something about the portal filter, or the relationship I am missing?

      Some more info:

      Looking at the first layout, first column the in the field is a summary field, Count of 'Reason for Call'-numbers 108, 15, 8, and the portal is filtered with this expression to calculate the dynamic date range in the label:

      Let ([       

        cd = Get ( CurrentDate ) ;

        offsetStart = Case ( cd < Date ( 9 ; 1 ; Year ( cd ) ) ;5 ;1 );

        start= Date ( 9 ; 1 ; Year ( cd ) - offsetStart);

        offsetEnd = Case ( cd < Date (8 ; 31 ; Year ( cd ) ) ;4 ; 0 );

        end= Date ( 8 ; 31 ; Year ( cd ) - offsetEnd )

            ];

      Service_Report_5yr::Date>GetAsDate(start) and Service_Report_5yr::Date<GetAsDate(end) and Service_Report_5yr::Reason For Call=Service::Reason For Call

      )

       

      The field for the total in the trailing summary part is a summary field, Count of (primary key ID-number 131 and the portal is filtered with:

      et ([       

        cd = Get ( CurrentDate ) ;

        offsetStart = Case ( cd < Date ( 9 ; 1 ; Year ( cd ) ) ;5 ;1 );

        start= Date ( 9 ; 1 ; Year ( cd ) - offsetStart);

        offsetEnd = Case ( cd < Date (8 ; 31 ; Year ( cd ) ) ;4 ; 0 );

        end= Date ( 8 ; 31 ; Year ( cd ) - offsetEnd )

            ];

       

       

      Case(IsEmpty(Service_Report_5yr::gSearch);

              Service_Report_5yr::Date>GetAsDate(start) and Service_Report_5yr::Date<GetAsDate(end);

              (Service_Report_5yr::Date>GetAsDate(start) and Service_Report_5yr::Date<GetAsDate(end)) and (Service_Report_5yr::gSearch = Service_Report_5yr::Reason For Call)

            )

      )

      Which seems to be the problem, the proper filter that only counts that column when the found set doesn't equal the total record count.

      I've done my due diligence with research, and trying many different ways, but still am unable to total the columns on the found set. 

      I tried different relationships, GetSummary, etc.

      I feel I'm dancing around it, but just can't get it over the finish line.

      Any thoughts, help, or direction would be greatly appreciated.

      Thanks

      Steve

        • 1. Re: Cross tab report, total of found set
          Jaymo

          I know this doesn't answer your question directly but I usually try to avoid using relationships whenever possible since they tend to be slow. This is especially trued for reporting. I typically setup a cross-tab report using calculation fields and summary fields. The link below is to an example file. It's written in FileMaker 11 but still valid for current versions.

           

          Database Pros:List

           

          Hope this helps.

          2 of 2 people found this helpful
          • 2. Re: Cross tab report, total of found set
            SteveMartino

            Of course that is much simpler (as if there was any doubt).  I've been on your sight for years and somehow missed that download.

            Thanks!

            Steve

            PS.  Just a note on the file, I thought it didn't work.  When it converted, it changed the field heights and they crossed the boundry parts.  So some slight adjusted needed.

            Also after you download, you should change the dates to correspond to this year and previous 2 years

            • 3. Re: Cross tab report, total of found set
              philmodjunk

              In your original approach, if you set up a found set of records, your portal filters have to also filter to only the records that meet the same criteria or they will have totals based on the wrong subset of records.

              • 4. Re: Cross tab report, total of found set
                SteveMartino

                philmodjunk wrote:

                 

                In your original approach, if you set up a found set of records, your portal filters have to also filter to only the records that meet the same criteria or they will have totals based on the wrong subset of records.

                Yes that is the crux of my problem.  I'm dancing all around it, but can't figure it out.

                This is mostly a mental exercise, because in reality, I'll only ever need to view the 5yr report as is.

                 

                I would post a sample file if I could load it up with fake records.

                 

                In between my report, and Jaymo's idea, I did find filemakerhacks Virtual Reporting series:

                https://filemakerhacks.com/2016/04/27/virtual-list-reporting-part-1/

                https://filemakerhacks.com/2016/06/26/virtual-list-reporting-part-2/

                Which I would like to work through and figure out, but it can only be opened on FM 13+ (Downloading a trial version does not let you trial Advanced). I do like the idea of doing all the reporting from a virtual list/table, and not having all the associated 'helper' fields to do all the kind of reports.

                I'll finally get around to upgrading during my slow season, when I do a complete rebuild, so I'll re-visit with FMPA 15 (or maybe 16).

                Thanks for the help.

                • 5. Re: Cross tab report, total of found set
                  philmodjunk

                  The logic in your portal filter expressions don't make sense to me. The upper filter expression compares the current record's reason for call to the related records. The lower expression compares a search field to the reason for call of the related records.

                   

                  Don't see why they would do that differently,

                  2 of 2 people found this helpful
                  • 6. Re: Cross tab report, total of found set
                    SteveMartino

                    Thanks for pointing me in the right direction.  I wasn't using the correct summary field in the subsummary part.  I fixed the portal expressions, AND, I had the relationship set wrong.

                    Perfect example of changing more than one thing at a time.