11 Replies Latest reply on Sep 26, 2016 2:33 PM by SamOsman

    Product Category Accounting Report

    SamOsman

      Hi,

       

      I have a database that's based on the Invoice Starter Solution. I'm wanting to create a report like the following, have tried to do this with sub-summary parts, but can not get my head around the correct way of doing it. Hope someone can put me in the right direction.

       

      InvoiceNumberInvoiceDateCustomerNameTotal Invoice PriceVATCat1Cat2Cat3
      00125/12/16Smith£16.80£2.80£11.00£3.00

       

      This would show each invoice on a line, with the invoice line items broken down by their category. For example, 2 products above are in 'Cat1' and 1 product is in Cat2. So this is totalled per invoice, and then each invoice split up the totals by the categories, which are set in the 'Product' table in the database.

        • 1. Re: Product Category Accounting Report
          philmodjunk

          Sub summary parts cannot be used to produce a report of the format shown. But a series of filtered portals accessing summary fields defined in the Invoice Data table could show these sub totals by filtering for specific categories.

           

          A summary report with sub summary reports is simpler to set up and can provide nearly the same results, but not in columns. Such a report would look like this:

           

          Invoice ABC | customer | Total | VAT |

             Cat1 subtotal

             Cat2 Subtotal

             ....

          • 2. Re: Product Category Accounting Report
            SamOsman

            Thank you Phil, your prompt response is much appreciated.

            • 3. Re: Product Category Accounting Report
              SamOsman

              Hi Phil,

               

              Are you able to shed any additional support with regards to setting up the summary fields to get the category totals, to then put into each portal?

               

              Thanks

              • 4. Re: Product Category Accounting Report
                philmodjunk

                I don't see the response that I posted earlier here.

                 

                Start with a layout based on Invoice Data (Your Line Items table), not your invoices table.

                Add a sub summary layout part "When sorted by" your category field.

                Put any summary fields that compute aggregate values on a field in your Invoice data table in this sub summary part and you'll get a sub total based on just the items in that category.

                 

                Be sure to sort your records first by Invoice, then by category.

                 

                This answer assumes that you have one field for Category in the Invoice Data table and that all line items are a member of a single category.

                • 5. Re: Product Category Accounting Report
                  SamOsman

                  Not sure why you can't see your earlier reply, but here it is:

                   

                  Sub summary parts cannot be used to produce a report of the format shown. But a series of filtered portals accessing summary fields defined in the Invoice Data table could show these sub totals by filtering for specific categories.

                   

                  A summary report with sub summary reports is simpler to set up and can provide nearly the same results, but not in columns. Such a report would look like this:

                   

                  Invoice ABC | customer | Total | VAT |

                     Cat1 subtotal

                     Cat2 Subtotal

                     ....

                   

                  An invoice will contain items from different categories. So your first solution would work. However, I'm struggling to get the summary fields working to drop into portals, which are then filtered using the categories field.

                  • 6. Re: Product Category Accounting Report
                    philmodjunk

                    That's not the one. I sent another reply after that which never made it into this thread. I've now reposted it.

                    • 7. Re: Product Category Accounting Report
                      SamOsman

                      Strange, I don't see either. Your first suggestion was regarding the portals, which could work as one invoice can contain items from multiple categories. Your second suggestion "This answer assumes that you have one field for Category in the Invoice Data table and that all line items are a member of a single category." won't work, as I have line items from different categories.

                       

                      Are you able to explain further how I could do the summary fields to get the totals for each category?

                      • 8. Re: Product Category Accounting Report
                        philmodjunk

                        Please explain what you mean by:

                         

                        as I have line items from different categories

                         

                        What I said was that each item must be a member of a single category, not that they must all be members of the same category. You might have 3 items in "Food" and 4 in "Hardware" as long as no one item is in both "Food" AND "Hardware" as it can't be listed in two different locations of the same summary report.

                        • 9. Re: Product Category Accounting Report
                          SamOsman

                          Apologies, I miss understood your reply. Yes an item only falls into one category.

                           

                          So as you suggested I would like to create the following report:

                           

                          Invoice Number: 001 | Total Sale: £10.00 | VAT: £2.50

                           

                          Category1 - Hardware | (total of all items in hardware category)

                          Category 2 - Food | (total of all items in food category)

                           

                          Invoice Number: 002 | Total Sale: £8.00 | VAT: £1.20

                           

                          Category1 - Hardware | (total of all items in hardware category)

                          Category 2 - Food | (total of all items in food category)

                           

                          Is this now possible using filtered portals? So the report would be based on the 'invoices' table, and the first portal based on 'invoice data' filtering by category column = "Hardware". Is this on the right lines, and if so how do i go about setting up the summary fields for the totals?

                          • 10. Re: Product Category Accounting Report
                            philmodjunk

                            This is the second method and would not use portals. If you reread my earlier posts, you'll find that instead, you place a single sub summary layout part on your layout which is used to list the category subtotals.

                             

                            One thing that I forgot in those other posts is that the Invoice fields should also be placed in a sub summary part located above the category sub summary and with a sorted by field that specifies an Invoice number or other primary key type field that uniquely identifies each invoice.

                             

                            Once you follow the steps from the previous posts, you'll get a report that looks like this:

                             

                            Invoice

                               category1

                                   Line item

                                   Line item

                                   ....

                               Category2

                                   Line Item

                                   ....

                            Invoice

                                ....

                             

                            You can then go into layout mode, click on the label for the body layout part and press the delete key to delete the body. Your report will then list the category sub totals without listing the individual line item entries. But be sure to correctly sort your records  by invoice, then by category or you won't see anything on your report.

                            • 11. Re: Product Category Accounting Report
                              SamOsman

                              Thanks so much Phil. I've got it working.