2 Replies Latest reply on Jan 26, 2015 9:14 AM by sccardais

    Help Creating Summary Report

    sccardais

      Title

      Help Creating Summary Report

      Post

      I am trying to create a report showing the total number of customers by month starting 1/1/2005. 

      My file includes more than 20,000 records including organizations who bought and later cancelled.

      Fields in FMP file show the date ordered and, if appropriate, the date cancelled.

      I can manually get a count by month by searching on accounts with a "Date Ordered" before a specific date AND a new find request that omits accounts also having a "Date Cancelled" before the same date. e.g. The account ordered before a certain date but they also cancelled before the same date.

      Our records go back several more than 10 years so it would require more than 120 manual calculations.

      I'm sure there's a way to automate this report in FMP but I don't know how.

      In the end, I would the report to have two columns. One showing the Month/Yr and the other showing a count of Active customers. e.g. Jan - 2013 would show the total number of customers through Jan 31, 2013. Feb - 2013 the total number of customers as of 2-28-2013, etc.

      Thanks for any help.

       

        • 1. Re: Help Creating Summary Report
          philmodjunk

          But the same preliminary questions have to be asked:

          What version of FileMaker are you using?

          How have you designed your data model? What tables, fields, relationships, calculations hold the data you need to reference in order to set up this report?

          Have you worked with SQL before?

          If memory serves, we've discussed summary reports for you project previously, but I don't dare trust my memory to recall these details accurately (and they may have changed since then anyway).

          • 2. Re: Help Creating Summary Report
            sccardais

            Sorry Phil. 

            Using FMP Pro V 13.4

            The table in question is called Org's. No relationships involved in this report.

            The fields needed to calculate the report are:

                   
            • DateOrdered: Date field. (populated only if Org actually purchased one version of our product.)
            •      
            • DateCancelled: Date field (populated only if Org bought and subsequently cancelled their subscription.)

            I have very, very little experience with SQL but eager to use and learn if that's the best way to generate this report.

            As I said, I can get the info by manually searching. Find DateOrdered <mm/dd/yyyy  AND omit DateCancelled <mm/dd/yyyy but I have to do this for every year starting 2005 and I'm sure this can be done in FMP.

            I don't know how to automate this search process starting with a specific month and stepping through on a monthly basis and generate a report? Eventually, I'd like to refine this to summarize this by the different versions of our product. We have about 10.

            Thanks for your help.