5 Replies Latest reply on Nov 23, 2011 3:10 PM by philmodjunk

    Trouble with creating a meaningful report/summary issue

    JoshGriffin

      Title

      Trouble with creating a meaningful report/summary issue

      Post

      I am constantly updating the POS system I am using for our retail business. It started with the Invoices Starter Solution and has grown quite a bit from that. My current issue is creating a daily sales report that shows what was bought on a certain day, who bought it, for how much, how they paid for it, etc. I have gotten all of that together and the report seems to be working okay. My problem now is I can't figure out how to have daily sales totals after each day. I tried grouping the sales by date, and then creating a leading subtotal for sales but it only shows the total of the last sale of the day. Further, I can only get this to display in Table View and not in List view where I built the report. Any ideas for getting this to work? Am I approaching this from the wrong direction? I am using FM11 by the way.

        • 1. Re: Trouble with creating a meaningful report/summary issue
          philmodjunk

          I tried grouping the sales by date, and then creating a leading subtotal for sales but it only shows the total of the last sale of the day. Further, I can only get this to display in Table View and not in List view where I built the report.

          Enter layout mode

          Select Part set up from the Layouts menu.

          Add a sub summary part, "when sorted by" your date field.

          Put a summary field that computes the sales total in this sub summary layout part.

          Return to browse mode and sort your records in a an order that includes the "when sorted by" field.

          For more on summary reports with sub summary parts, see this tutorial:  Creating Filemaker Pro summary reports--Tutorial

          This is a pretty old thread. If you post a comment to it, it will not appear in recent items. Thus, it's easy for me to miss the fact that you have done so. Instead, feel free to post any comments/questions about that thread here where they'll be easier for me to see and respond to.

          • 2. Re: Trouble with creating a meaningful report/summary issue
            JoshGriffin

            Ok, Between your help and that tutorial's walk through I've almost got it to where I want it to be. I am having trouble figuring out how to make it all work together. Ideally, the report should be sorted by date (which is in the sub summary field) and the Day's sales should be listed next to that (also in the subsummary field). So when I create the Day's Sales field and put it in the sub summary part it works perfectly. The downside, is that under each invoice, it only displays one item from each invoice instead of all invoices. I tried looking for solutions to that and managed to change the table that the layout is linked to from Invoices to Line Items. This fixed the problem of only showing one item in the invoice, but the Day's Sales Field only shows the total amount of the first invoice instead of every invoice for the day. I tried recreating a summary field in this table but it wont let me select invoice total as an option for making a summary. I have tried a number of things and can't seem to get it to work right. Any ideas?

             

            BTW, you were the one that answered my First Forum post last week, and I really appreciate your help.

            • 3. Re: Trouble with creating a meaningful report/summary issue
              philmodjunk

              Yes, the layout should be based on line items instead of invoices in order to list the individual items from the invoices.

              To get sales totals, there should be a field in each line item record that computes the total cost of that one line item. Define a summary field to compute the total of that field. This one summary field can be used to display a number of different subtotals depending on which layout part to place it on. Put it on the header, footer or a grand summary part and you'll get the total sales for all the records in your current found set. Put it in a summary part sorted by date and you'll get a daily sales total. Put it in a sub summary that's sorted by InvoiceID and you'll get an invoice total.

              • 4. Re: Trouble with creating a meaningful report/summary issue
                JoshGriffin

                Ok, I've got that working but still not quite what I need for valuable reporting. I've spent a bit more trying to figure it out but haven't had much luck. I took your advice and managed to get the report to work using the Extended Price field. But, that is just the extended price, it won't factor in discounts nor sales tax. In a perfect world, I'd have a daily report that has a total of the day's extended prices, discounts, taxes, and then the final net from the day. I'm sorry I keep bothering you about this.

                • 5. Re: Trouble with creating a meaningful report/summary issue
                  philmodjunk

                  It's not a bother and I anticipated that this might be the next issue. Such totals are easy to produce from the invoices layout but not line items as the data is not specific to any one record in line items.

                  Frankly, I'm not sure how you'd want to report that data on a summary report listing individual line items given that discounts, shipping charges etc. aren't specific to any one line item record. Perhaps totals at the beggining or end of a report? Totals for the specified date shown in a by date sub summary? (Those can be done.)

                  Sales tax can be totaled just like extended price if you add a calculation field for computing the sales tax for each line item record. Such a calculation should be kept unrounded to avoid round off errors in your summary totals.

                  Here's an example of how you could compute the total discounts for all discounts applied to invoice totals on a given date:

                  Make a new occurrence of Invoices like this:

                  LineItems::Date = InvoicesSameDate::InvoiceDate

                  Date could be an auto-entered date or it can be an unstored calculation field that returns the value of Invoices::Date.

                  If you have a summary field defined in Invoices that computes the total discounts, you can then add

                  InvoicesSameDate::DiscountSummaryField to a sub summary part "when sorted by date" to show the total invoice discounts for that date.

                  A relationship based on a date range could be set up if you wanted a grand total for all the invoices linked to line item records in your summary report.

                  A simpler option might be to make a report of invoice level quantities in a separate report, using a layout based on invoices.