1 2 Previous Next 23 Replies Latest reply on Nov 20, 2012 4:05 PM by comment

    Multiple Summary Report

    AjEGfmTech

      This is probably pretty simple but I'm having difficulty wrapping my head around it.

       

      I am looking to create a monthly report that shows the weekly sales and monthly total for each of our stores. The database is setup to collect the daily sales for each store.

       

      I'm looking for something like below with each page of the report showing just one store's data, a page for each store.

       

      February Report

      StoreID

      SalesWeek Sales

      5 2,536.12

      6 2,125.42

      7 1,956.95

      8 2,478.36

      9 2,913.54

      Total 12,010.39

       

      I can generate a report that shows the weekly sales for one or all of the stores. I can generate a report that shows the monthly sales for one or all of the stores. I just can't figure out how to show multiple weeks of sales and a monthly total for each store individually.

       

      In searching the forum here it seems like I might need to look into using a crosstab/pivot report? However, it seems like that might be overkill for what seems like an array to me? I saw a thread that someone posted looking for something similar and the advice was to create two leading sub-summaries (one summarized by SalesWeek and one summarized by StoreID) and then create two trailing sub-summaries, again by SalesWeek and StoreID. Then sort the report by both SalesWeek and StoreID.

       

      I wasn't sure about the details of how that would work. It seems like I would need two summary field for Sales, one when sorted by SalesWeek and one when sorted by StoreID with each field being placed in the appropriate sub-summary part (SalesWeek with SalesWeek and StoreID with StoreID). Then, in order to get the report I'm looking for, I need to have the StoreID sub-summary (leading) before the SalesWeek sub-summary (leading). However, I'm not sure what the trailing sub-summaries will be doing?

       

      Any help, clarification or direction would be appreciated. Thanks.

        • 1. Re: Multiple Summary Report
          DavidJondreau

          You'll want two sub-summary parts:  one based on Store ID and one based on SalesWeek. Sort by both those fields, in that order. You apparently have a total field for SalesWeek by StoreID? You should also have a summary field for Total, and you can use that on the sub-summary parts.

          • 2. Re: Multiple Summary Report
            comment

            ajegfmtech wrote:

             

            It seems like I would need two summary field for Sales, one when sorted by SalesWeek and one when sorted by StoreID

             

            No, not at all. The one and the same summary field will display different values in different layout parts. In your example, you would have a leading sub-summary by StoreID part (set to break page before each occurrence), a sub-summary by Week part (this can be either leading or trailing with no difference between the two), and a trailing sub-summary by StoreID part. Note that this layout has no body part.

             

             

            This is assuming you know how to divide a month into weeks - something I am not sure of myself.

            • 3. Re: Multiple Summary Report
              AjEGfmTech

              Thanks David. That worked. Yes, I do have a summary (total of) field for Sales. I included that in the trailing sub-summary and it showed me the monthly total sales for the store. By checking the 'Page Break after every '1' occurence' in the trailing sub-summary, I was able to show just one store per page of the report.

               

              Solution Summary

              So, to summarize...

              • Created leading sub-summary with StoreID break field and placed StoreID field in the part.
              • Created leading sub-summary with SalesWeek break field and placed SalesWeek and TotalSales (summary field, total of, no running total) fields in the part.
              • Created trailing sub-summary with StoreID break field and placed TotalSalesStoreID (summary field, total of, running total with break field of StoreID) field in the part. Checked the box for 'Page Break after every '1' occurence'.

               

              This works great and shows the weekly sales totals and monthly total for each store, one per page.

               

              Next question. Embedding a report within a report?

              Each of our stores pay royalties to the corporate office based on their sales. The royalties are paid on a weekly basis. For this report, it is useful to see that royalties paid per week AS WELL AS the actual sales per week based on the days of the month.

               

              What this means is that if a week is partially in both months (as is typically the case), the stores want to see the partial week sales for that week for that month. For example, 2012 Week 5 ending 02.05.12 has five days in Feb and two days in Jan. The 'actual' month-week sales for that week should just be the sum of those five days.

               

              Creating this is not a problem since the sales data is collected daily, I just do a find based on the month number and the report summarized above dutifully shows the actual partial month-week sales correctly. This is not my question.

               

              My question is how could I show two versions of the report above ON THE SAME PAGE? I would like to do a find based on month number (ie. 2) AND then a find based on a range of week number (ie. 5...9) and have both report results show up on the same page.

               

              Is this even possible with FileMaker? Is there anyway to take a snapshot of one report find and embed it in another report? There may be other solutions, that was all I could think of. Again, any help or direction is appreciated. Thanks.

              • 4. Re: Multiple Summary Report
                AjEGfmTech

                Thanks Michael. Sorry, I was typing my response to David when your post came in. I was able to solve the report issue based on David (and your) advice. Thankfully, I had already setup a field called 'SalesWeek' that used the calculation 'WeekOfYearFiscal ( DateWeekEnding ; 2)' to grab the week number from the SalesDate of the record, so knowing the sales week number is figured out.

                 

                My issue now is the ability to embed two reports with different find results into the same overall report? I described it in my reply to David above. Again, any help or direction is appreciated. Thanks.

                • 5. Re: Multiple Summary Report
                  comment

                  Usually, a report summarizes the found set - and there can be only one found set at a time.  However, your report can also include summaries of a related set. For example, if you define a self-join based on matching store and week, you can then use a calculation field to sum the related values. The downside of this is that the related set is more or less constant, so that if you exclude a specific type of sales from the found set, the related set won't automatically follow.

                   

                  In version 12 you also have the option to use SQL to assemble a "faux" found set (or sets) in addition to the "real" one.

                  • 6. Re: Multiple Summary Report
                    AjEGfmTech

                    Thanks Michael. That makes sense (that a report summarizes the found set). The self-join related set and SQL faux sets sound a bit above me. I'm creating this database as a replacement for an existing MS Access database. MS Access seems to have an ability to embed a 'sub-report' based on a different set of queries (searches). It is quite a bit more complicated of a setup, but it did allow them to see both the month-day sales totals and month-week sales totals on the same report. Maybe that's just not something FileMaker is suited for? I will find out tomorrow if that is truly a requirement of this report or if it was just something that 'had always been there' without any current need for it.

                     

                    Thanks for your help.

                    • 7. Re: Multiple Summary Report
                      comment

                      ajegfmtech wrote:

                       

                      The self-join related set and SQL faux sets sound a bit above me.

                       

                      Come now, it's not that complicated, esp. not the self-join. For the SQL option, you do need to know some SQL.

                       

                       

                       

                      ajegfmtech wrote:

                       

                      MS Access seems to have an ability to embed a 'sub-report' based on a different set of queries (searches).

                       

                      You can do practically the same thing in v.12 with the ExecuteSQL() function (the Q in SQL stands for 'query').

                      • 8. Re: Multiple Summary Report
                        AjEGfmTech

                        Thanks Michael. Can you explain a bit more about this 'self-join' solution? I do not have access to a SQL database to try the ExecuteSQL function.

                         

                        I just verified the requirements of this report. The requirements are that I need to show the Week totals for the full-weeks of the month (by Finding the range of week numbers) and the Month total for the days of the month (by Finding the days in the month). So the week sub-summaries can be as they are, it is the month sub-summary that needs to come from a different search result.

                         

                        So, what I presume from your 'self-join' description is that there will be another table that will store the month total sales results and then be able to be accessed in the report? Sorry for my dullness, further explanation would be helpful. Thanks.

                        • 9. Re: Multiple Summary Report
                          comment

                          ajegfmtech wrote:

                           

                          The requirements are that I need to show the Week totals for the full-weeks of the month (by Finding the range of week numbers) and the Month total for the days of the month (by Finding the days in the month).

                           

                          I am afraid that's not clear enough to me. Suppose you are producing a report for the month of February 2012. As per your original example, the month spans five weeks: three full weeks and two (the first and the last) partial ones. Together with the monthly total, that's six sub-summary values. Now, which sub-summaries need to be confined to the month boundaries, and which should be extended? By default, if your found set contains all (and only) February sales, you will have:

                           

                          WeekStart
                          Sales
                          Days
                          Jan 29
                          4
                          Feb 5
                          7
                          Feb 12
                          7
                          Feb 19
                          7
                          Feb 26
                          4
                          TOTAL
                          29
                          • 10. Re: Multiple Summary Report
                            AjEGfmTech

                            Sorry Michael. Nice table btw, mine won't look as nice, but this what I'm looking for. FYI: our sales weeks follow ISO 8601 week standards of Mon-Sun.

                             

                            February Report

                                 StoreID

                                      (Full Weeks Only from Find of week range = 5...9)

                                      SalesWeek     WeekEnd    Sales         Days

                                      5                    02.05.12     2,536.12     7

                                      6                    02.12.12     2,125.42     7

                                      7                    02.19.12     1,956.95     7

                                      8                    02.26.12     2,478.36     7

                                      9                    03.04.12     2,913.54     7

                                      No Total

                             

                                      (Full & Partial Weeks from Find of month = 2)

                                      SalesWeek     WeekEnd    Sales         Days

                                      5                    02.05.12     1,784.26     5

                                      6                    02.12.12     2,125.42     7

                                      7                    02.19.12     1,956.95     7

                                      8                    02.26.12     2,478.36     7

                                      9                    03.04.12     1,253.24     3

                                      Monthly Total                    9,598.23     29

                             

                            My report basically needs to look like this for each store, one per page.

                            Thanks for your help with this.

                            • 11. Re: Multiple Summary Report
                              DavidJondreau

                              FileMaker's ExecuteSQL() function is used to query FileMaker tables, not necessarily (or even?) external SQL tables.

                               

                              If you're used to Access's querying, it should be easy to transition to ExecuteSQL().

                              • 12. Re: Multiple Summary Report
                                comment

                                Would something like the attached work for you?

                                • 13. Re: Multiple Summary Report
                                  AjEGfmTech

                                  Thanks a bunch for creating that Michael. I appreciate your time and effort. Unfortunately, no, it did not produce what I need to for the report. The example database you provided does show sales data for the weeks and the month for two stores, but the week and month sales are all based on the days of the month and therefore both show partial and full weeks. The report I need requires that the week sales are shown as all full weeks based on the week numbers and the month sales are shown as the combination of partial and full weeks based on the days of the month.

                                   

                                  Thanks again.

                                  • 14. Re: Multiple Summary Report
                                    AjEGfmTech

                                    Thanks David. I didn't realize ExecuteSQL was for within a FileMaker database. While I am little familiar with the MS Access queries, it is mainly from maintaining the old database, not creating it. Also, the method in MS Access is still GUI, not CLI - I am unfamiliar with SQL query statements (though could probably learn?). It sounds like a plausible method, but I'm concerned about the time it might take for me to learn, try and then troubleshoot. At this point, I need a solution that I can deliver before Wednesday.

                                     

                                    Though this may seem like a cop out, based on what I've seen regarding this and knowing that I can successfully create both types of reports separately, I think I'm going to output each type of report for all stores as PDF and then join them into a single page report via Adobe InDesign multi-page PDF import. The result will be a PDF report of one page per store with both sets of report data. I would prefer to stay within one app (FileMaker) for the whole process, but that's something I'll have to work on for the future.

                                     

                                    Thanks for your help.

                                    1 2 Previous Next