10 Replies Latest reply on Jan 9, 2013 11:37 AM by laguna92651

    Summarize with Pivot Table like view

    laguna92651

      I need to summarize records, in both a report and browse view, that are of the form below. I need to basically do an Excel like Pivot Table of the data.

       

      Record Form:

      Article No Product Code Style Size UnpostedSales Yds Cost
      DM13012 DM13012-XS T Shirt XS 3 15 25

       

      The Table view or List view would summarize the data and show the sizes as columns. The number beneath the XS column is the total number of DM13012-XS's in the data set, 23. The Article No consists of a family of Product Codes.

       

      Article No. DM13012, has the following family of Product Codes.

       

      DM13012-XS

      DM13012-S

      DM13012-M

      DM13012-L

      DM13012-XL

       

      Summary View desired:

      Article No Style Color XS S M L XL No. Units Mfg.
      DM13011 T SHIRT BLACK 23 12 20 15 8 78
      DM13012 T SHIRT CHARCAOL 11 5 9 27 28 80
      DM13013 T SHIRT TAUPE 15 21 16 16 23 91
      DM13014 T SHIRT OCHRE 15 29 26 21 27 118
      DM13015 T SHIRT WHITE 27 23 24 5 27 106
      DM13016 T SHIRT MINT 24 5 19 13 15 76
      DM13017 T SHIRT STRIPE 8 11 7 17 5 48
      DM13041 TANK BLACK 11 28 10 24 9 82
      DM13042 TANK CHARCAOL 27 30 21 13 10 101
      DM13044 TANK OCHRE 27 9 19 6 24 85
      DM13045 TANK WHITE 25 18 15 23 9 90
      DM13047 TANK STRIPE 5 20 30 24 9 88

       

      Thank you

        • 1. Re: Summarize with Pivot Table like view
          LyndsayHowarth

          You could do this by calculation or by relationships for the size.... or...

           

          If your summary is sorted by Article number, you can have relationship by article number and XS and another for article number and S etc. you would then display a summary field for XS::Total S::Total etc.

           

          Alternatively you can create a calculation GetSummary(XSTotal, Size) based on another calc each of the sizes. (Tedious)

           

          IF there is only one record for each of the sizes AND always one of each record, you could do it with only one self relationshp on the Article Number then the Article size sorted then display separate instances of the same portal for each of the sizes starting at a different row number on the portal. You would use row 1 for L and 2 for M and 3 for S then 4 for XL and finally 5 for XS.

           

          It would also be possible to gather all the information via a calculation used in a script and save it to a variable which is then set to a field or added as a merge-field or merge-variable on a layout. You would end up with tab-delimited text which would display nicely in a letter or such providing you set up appropriate tab-stops.

          You could also embed the data in an HTML calculation and display it in a web-viewer.

           

          I hope this all makes sense. We got too many loud things in our house for Xmas ;-)

           

          - Lyndsay

          • 2. Re: Summarize with Pivot Table like view
            laguna92651

            I hear ya (couldn't resist the pun), I'll sit down and go over your suggestions.

            thanks

            • 3. Re: Summarize with Pivot Table like view
              beverly

              Lyndsay has excellent suggestions. I'll make one more

              <http://www.filemakerhacks.com/?p=5950> Aggregates (Summary Fields) in Filtered Portals

               

              Beverly

              • 4. Re: Summarize with Pivot Table like view
                laguna92651

                I had just found that article before your post, by you, and was going to give it a read. How does your approach, Lyndsay's suggestions and the article by Kevin Frank compare for what I want to do?

                 

                "Outer Joins in FileMaker 12, part 1"

                http://www.filemakerhacks.com/?p=6165

                 

                I had also posted a question on basically doing the data entry, invoice, that woud generate the data for the reporting I want, would your approach address that also? I haven't read it yet, but will do so shortly. I am new to FM so any additional insights or detail would be helpful as I work my way through the materials. Thanks Bev and Lyndsay!

                • 5. Re: Summarize with Pivot Table like view
                  beverly

                  The summary fields are in the child records and called into "filtered portals", so you can get the "pivot" you need. It's just another option. I might also try an ExecuteSQL query to get the data. Formatting it is much more difficult than the other methods, however.

                   

                  Beverly

                  • 6. Re: Summarize with Pivot Table like view
                    laguna92651

                    Beverly, that worked great, it was just what I was looking for. I'll be doing alot of cross tabs moving forward. Actually very simple to implement.

                    Thanks again

                    Al

                    • 7. Re: Summarize with Pivot Table like view
                      ClevelandConsulting

                      All,

                       

                      I might be a little late to the party, but you could also look at our product, CCPivot. There is a free version and may be a simpler way to get where you are trying to go.

                       

                      http://www.clevelandconsulting.com/cc_pivot/

                       

                      Sorry for the shameless plug, but since it is a free product and on topic I figure it added to the discussion...

                       

                      Court

                       

                      Court Bowman, CEO

                      Cleveland Consulting, Inc.

                       

                      Visit us on the web at http://www.clevelandconsulting.com

                      • 8. Re: Summarize with Pivot Table like view
                        laguna92651

                        I implemented your approach to summarize the data in the format I wanted, it works great. I have been struggling with totaling the columns in the report.

                         

                        CrossTab.jpg

                         

                        The 1x1 portals are in the 0, 2, 4, 6 and 8 column headings. I'm trying to get a sum of each of those columns, the totals units column summed fine, that column is not on 1x1 portals. The totals are in a Trailing Grand Summary part. The 1x1 portals are in the body. What would be the approach to do the column totals.

                         

                        The parent table is Articles and the child table is Products, the columns 0, 2 ... represent the number of sales for a product for a given size (0, 2, 4, 6 & 8)

                        • 9. Re: Summarize with Pivot Table like view
                          beverly

                          Does your total units work if you have a different found set? Is it a summary field in the Articles table? Where are your other total fields located and how are they summarized? Perhaps an ExecuteSQL (based on the found set) is what is needed here?

                           

                          Beverly

                          • 10. Re: Summarize with Pivot Table like view
                            laguna92651

                            I've tried it many different ways. Initially I put a summary field in the child, Products table, it seemed that was the approach you did in your demo file.

                             

                            I also tried a summary field in the Articles table, parent, I first had to do a calculation field to go get the sales data from the products table, child.

                             

                            The total fields are located in the Trailing Grand Summary

                             

                            From the child, Products table I tried doing totals with:

                            I use a summary, sUnpostedSales = Total of UnpostedSales [Summary field]

                             

                            From the parent, Articles table I tried doing the totals with:

                            cUnpostedSales = Sum(Products::UnpostedSales) [Calculation field]

                            sUnpostedSales=Total of cUnpostedSales [Summary field]

                             

                            How would I implement the totals with an ExecuteSQL statement?