4 Replies Latest reply on Dec 13, 2012 9:45 AM by WendellNeeley

    Need some ideas for some advanced reporting for my database



      Need some ideas for some advanced reporting for my database


           Hi all,


           I currently have my database mostly set up to my liking and am trying to figure out the best ways to extract the relevant statistics and data that I need from it.  This is for my orthodontic practice and I'm attempting to keep track of which patients go into treatment, what sort of treatment they take and how that relates to the individual referring doctors.  I also am looking at the health history of these patients, marketing visits to referring doctors and also the birthdays of the employees of referring doctors.  In a nutshell, you could say it is the ultimate marketing database for what I do.  

           Current setup:  

           Tables (one to many as you go down the list):

      1.           Doctor Information-  First name, last name, Portals to Patient Info, New Exam Info, New Start Info, Debond Information
      3.           Patient Information-  Birthdate, Insurance type, primary office, etc., Portals to New Exam Info, New Start Info and Debond Info
      5.           New Exam Info-  Exam Date, Exam Outcome, Portal to New Start Info
      7.           New Start Info-  Start Date, Fee, Treatment Type, Discount, other relevant financial data
      9.           Debond Info-  Finish date, financial information, Early removal of braces, etc.  

           I won't get into the other tables just yet as I haven't really fleshed that part out yet and it should be infinitely easier than what I'm dealing with right now.  What I currently have is a couple of layouts to look at my global statistics that I care about, such as average fee, percentage of new exams that start, etc. and I have, through portals, put all of the information that I wish to see on the Doctor Information page, thus limiting the data observed to each doctor.  This is important to me as I'd like to see where to focus my marketing.  

           What I'd like to do now, with both the global statistics and also the portal related data on the doctor's data page, is to divide these up in to defined date ranges, for instance all the months of 2011.  I'd like for this data to be all on one layout so that I can both easily print it and also to see how the values change over time.  

           Example:           Doctor-  Johnny Johnnerson 

                                    January 2011      February 2011        March 2011      April 2011

            New Start % 

             Total Fee

             Average Fee

           Right now I can see all this information as a whole, for the entirety of the database's dates, but I have to figure out a way to easily narrow the date range in a reapeatable fashion so that I can see trends.  I've tried to do a date limitation on the portals centered on the examination date (New Exam Table), but it doesn't appropriately cull out the dates, i.e. I get the same numbers with the portal filtere as I do when I don't filter it.


           Can anyone help me with some ideas to make this work?  I suppose that I could do individual equations for each doctor, but that would be a real pain.


        • 1. Re: Need some ideas for some advanced reporting for my database

               Before you go for a report where the monthly stats are in columns, you might want to consider this format--which is much easier to set up:

               Doctor- Johnny Johnnerson
                  Month-Year         New Start %         Total fee      Avg Fee
                  January 2011
                  February 2011
                  //and so forth...

               Doctor- Next doctor goes here if you report on more than one doctor in the same report (Optional)

               This format can be set up with a very straight forward summary report layout based on the lowest level table in your chain of one to many related tables that records data needed for this report. Each row of information in what I have posted here is represented by a sub summary layout part and the body layout part has been removed as you don't want data from individual records listed for this report, just sub totals/averages. The top sub summary layout part would be "sorted by" DoctorID, The indented rows would be produced by a sub summary layout part "sorted by" a calculation field set to return a date. That calculation field would be defined to use this calculation:

               DateField - Day ( DateField ) + 1

               It computes the date of the first day of the month for the date in DateField, so all reocrds with a date from the same month and year will have the same date in this field and if you sort your records by this date, they will automatically sort into groups by month in chronological order.

               Then to get reports for specific doctors over specific date ranges, you'd perform a find specifying the doctor(s) and the date range.

               For more on summary reports, you might look at this tutorial on the subject: Creating Filemaker Pro summary reports--Tutorial

               If you have any questions about that tutorial, please post them here not in that older thread as posts to it will not pop that thread back up into Recent Items.

          • 2. Re: Need some ideas for some advanced reporting for my database

                 Okay, thanks for the reply.  I have a couple of questions about this before I fully understand it:

                 1.  By "lowest level" table you meant the Doctor ID table or the New Start Info Table?  

                 2.  Which table should I place the date field into?  Does it matter?

            • 3. Re: Need some ideas for some advanced reporting for my database

                   1) You've specified a chain of one to many relationships that you posted in a left to right list from Doctor to New Start. By Lowest Level, I meant the table closest to the New Start end of the chain that contains data you need to see in your report.

                   2) Yes it matters. You appear to have date fields in more than one table, so you'll need to figure out which one correctly groups your data into columns. It should be a date field in that same "lowest level" table on which you base your report layout.

              • 4. Re: Need some ideas for some advanced reporting for my database

                     Thanks man.  You're awesome, as usual.