3 Replies Latest reply on Feb 13, 2013 8:55 AM by philmodjunk

    Sub-Summary Report Challenge

    JoshKillham

      Title

      Sub-Summary Report Challenge

      Post

           I am relatively new to FMP, but used to be a whiz at MS Access.  Too bad that was about 20 years ago. 

           As a software company, we generate a daily report (.csv) that tracks:

             
      •           Current Date
      •      
      •           Install Date (date client installed software)
      •      
      •           Client Key (35 characters of text)
      •      
      •           Version # (text)

           Each day I import and append the new report into a FMP table ("usage").  That table is already HUGE with over 2 million records with about 40,000 records added daily. 

           I would like to be able to develop a report that demonstrates the number of times any key used in first 30 / 60 / 90 days from install, and be able to manipulate the resulting data (within FMP or export to excel).  The fact that there is a lot of data has made the trial and error process pretty tedious. 

           So far I have been able to work up a summary field that counts the number of uses per key, but using that data has escaped me. 

           Any help would be greatly appreciated.  Also, any tips to improve efficiency of this process would be welcomed.

        • 1. Re: Sub-Summary Report Challenge
          philmodjunk

               This appears to be the sticky point:

               

                    the number of times any key used in first 30 / 60 / 90 days from install

               Since the records all potentially have different install dates, the range of dates that fall into 30, 60, and 90 day intevals will vary with each installation date.

               This is all going to be quite different from MS Access and SQL...

               Define 3 calculation fields, c30day, c60day, and c90day

               c30day: Current Date < ( Install Date ) + 30
               c60day: Current Date < ( Install Date ) + 60
               c90day: Current Date < ( Install Date ) + 90

               Select Number as the return type and make sure that these are NOT unstored calculations.

               Define summary fields for each of these calculation fields that compute either the total or the count of one of these three fields.

               This should allow you to get a report with one row for each client key with an activity total for each time period in the same row.

          • 2. Re: Sub-Summary Report Challenge
            JoshKillham

                 Thanks for that insight. 

                 I'm still stuck on making a table (as opposed to list or form) that presents the results.  The best I seem to do is a table that repeats the key X times, and has X in the "count" calculation field beside it (for which I used the getsummary command with the key field as the break field).  I have tried the subsummary layout functionaility and have it present the data correctly in list view, but that does me little good.  When I switch to table view it is not correct (greyed out fields all over the place, repeated data).  I have attached a screenshot.

                 This is making me crazy, and I'm sure it's an easy fix or a step I'm missing.  Any help would be appreciated, or even point me in the direction of a quality tutorial to put me out of my misery! 

            • 3. Re: Sub-Summary Report Challenge
              philmodjunk

                   Why would you use this in table view?

                   List View layouts can be designed to present the data in tabular format that will be very similar to table view and if you remove the body layout part, you can get one row for each client key.