7 Replies Latest reply on Apr 3, 2015 9:40 PM by philmodjunk

    Cross Tab Report

    sccardais

      Title

      Cross Tab Report

      Post

      I would like to create cross tab reports (pivot tables) similar to the attached screen shot.

      The report would be based on an existing base table (ORGs) currently containing over 20,000 records. Each record in the base table holds information about a single organization. The fields currently available to generate the data are described below.

             
      • ID_FMP_Record (Unique, Auto Assigned)
      •      
      • Org ID (Unique)
      •      
      • Date_FreeTrial (date - mm/dd/yyyy)
      •      
      • Date_NewOrder  (date - mm/dd/yyyy) sometimes blank
      •      
      • ProductName (text. One of 9 versions of the product)
      •      
      • Date_Canceled  (date - mm/dd/yyyy) sometimes blank
      •      
      • Count_Users (number)
      •      
      • PriceTier (looked up based on Count_Users)
      •      
      • AnnualContractValue$ (looked up based on Price Tier)

       

      Is it possible to create the cross tab report from this structure? Will I need to export summaries of this data to a separate table of can the report be generated from within the base table.

      Any help would be appreciated.

      I would also like to show the NetChange in client count and Annual Contract Value each month. (e.g. count of Date_NewOrder less count of Date_Canceled) This can be calculated from summary data but can monthly values be displayed in the cross tab format I've attached?

      CrossTab_Report.png

        • 1. Re: Cross Tab Report
          philmodjunk

          This is amazingly similar in structure to the first cross tab report that I ever created in FileMaker (Did it in version 3, no less).

          In my case, it was a 5 year monthly comparison grid where each cell reported both the total and daily average figures for a type of material purchased from the general public by a scrap metal dealer. You selected a material (or material category) from one value list and specified the most recent year in another field to then see the data update into the cross tab grid.

          Start by defining a table with one record for each month of the year. This table stores no data except the month and year match fields and only has 12 records, with one global field, gYear5 for the product and one for the most recent year. 4 unstored calculation fields then compute their values from from this global field, cYear4 was defined as gYear5 -1, cYear3 subtracts 2 an so forth...

          A number field, month, stored the month numbers 1...12. A cacluation field computes a date as Date ( month, 1 , gYear5 ). Put this field in the body of a list view report and format it to only show the month name and you get the first column of your data.

          You'd also have 3 global fields for product, metric and Tier. Set up a calculation field named cTierKey that returns the value of gTier unless the selected value in the field is "All", then it uses the list function to return a list of all Tiers.

          Set up this relationship:

          CrossTabTable-----<ORGs

          CrossTabTable::gProduct = ORGs::ProductName AND
          CrossTabTable::cTierKey = ORGs::PriceTier

          In ORGs, add a Summary field to compute the total of AnnualContractValue$

          Now set up your list view layout with a narrow body layout part with your calculated/formatted date field for column 1. For Column two add a one row portal based on ORGs. Define the following portal filter:

          CrossTab::cYear1 = Case ( gMetric = "Free Trial" ; Year ( Orgs::Date_FreeTrial );
                                     gMetric = "New Orders" ; Year ( Date_NewOrder
          ) ;
                                     gMetric = "Cancelations" ; Year ( Date_Canceled )
                                   ) // case
          And

          CrossTab::Month = Case ( gMetric = "Free Trial" ; Month ( Orgs::Date_FreeTrial );
                                     gMetric = "New Orders" ; Month ( Date_NewOrder
          ) ;
                                     gMetric = "Cancelations" ; Month ( Date_Canceled )
                                   ) // case

          Place your summary field inside the row of this single row portal. Make 4 more copies of this portal, but change the first field reference from cYear1, to cYear2....gYear5 for each additional portal.

          This does not provide all that you've asked for, but it's a start.
           

          • 2. Re: Cross Tab Report
            sccardais

            First - I'm impressed you can remember that far back and in such detail!

            I'm going to study it tomorrow morning over a cup of coffee.

            Thank you very, very much for contributing your time and expertise to this forum. You really are a tremendously generous resource for the FileMaker community.

             

             

            • 3. Re: Cross Tab Report
              philmodjunk

              The methods that I shared here wouldn't have worked back then. I had to set up 5 different table occurrences instead of using a filtered portal and I may have originally used calculation fields instead of portals--though unfiltered portals could have been used back then.

              This was a layout that I was still responsible for updating and maintaining up to last Fall when I was hired away from my old company. I'm still responsible for it as an independent consultant for that matter...

              • 4. Re: Cross Tab Report
                sccardais

                I studied your instructions and sent PM with questions to you this morning.

                • 5. Re: Cross Tab Report
                  philmodjunk

                  Please post them here. I make it a policy to not answer tech questions via forum private message or email.

                  Those systems would be overloaded if I allowed that and I prefer to keep the discussions public so that others can contribute to and learn from any such discussion.

                  • 6. Re: Cross Tab Report
                    sccardais

                    The attached summarizes my understanding of your instructions the other day about creating a cross tab report (similar to your scrap metal application). I think I understand everything except the areas highlighted in the attached summary. 

                    Off Topic ... Is there a wayu to attach pdf files to posts in this forum?

                    Thanks for any and all help.

                    • 7. Re: Cross Tab Report
                      philmodjunk

                      You are correct. You assign a value to this global field and then the other years counting backwards calculate the years for the other portals/columns of data.