8 Replies Latest reply on Nov 15, 2013 8:17 AM by bgustin

    Exporting Subsummary Reports to excel Issue

    bgustin

      Is there a way to export a subsummary report into excell with the summarized data only (not the individual records)? I know it can be done with PDF generator, but I an wanting to set it up as a server activated script (PDF generator is not supported by the FM12 server).

       

      2013-11-12_1229.png

        • 1. Re: Exporting Subsummary Reports to excel Issue
          taylorsharpe

          Not Directly, but you could write a virtual array that stores those values in a calculation fields called from a global variable.  There are all kinds of things you can do with such reporting tables.  You basically have a table with a serial number field going from 1 to whatever you need and all the other fields are unstored calculation fields that call data from a $$ global variable array.  Another nice thing about using arrays this way is that multiple people can be using the same report and values changing in them and it doesn't matter since the only thing stored is the record serial number.  All of the other values come from the global fields.  And the global field can be made from looping, but mostly I make them using ExecuteSQL since it is vastly more flexible in determining summaries, group by, dististinc, in, and other cool reporting techniques.  Does that make sense?  If not, Martha Zink from Soliant can tell you a lot more about it too.  She is the real pro using it. 

          1 of 1 people found this helpful
          • 2. Re: Exporting Subsummary Reports to excel Issue
            beverly

            Not what bgustin asked, but in addition to the 'gather-export' method that Taylor describes, I will create XML that Excel can use. A stylesheet can be created that takes your export and includes formulas and styling in Excel-xml. Or, if you can create an HTML table, Excel is really happy to open this kind of document as well. It may involve the 'gather-export' method, but more sensible to folks needing to show in Excel and/or in a browser.

            just some thoughts

            Beverly

            1 of 1 people found this helpful
            • 3. Re: Exporting Subsummary Reports to excel Issue
              taylorsharpe

              There was some presentation at Devcon in August by a guy that was really good at taking Excel's XML and having FileMaker export to XML in a way that did amazing things in Excel that I never thought FileMaker could do. 

               

              Beverly, do you remember who it was that gave the presentation.  I think they will be slowly releasing the videos to the technet and you'll want to look for it.  I was very impressed, but I just can't remember his name. 

              • 4. Re: Exporting Subsummary Reports to excel Issue
                bgustin

                Thanks for the info Taylor & Beverly. Unfortunately I don't know much about SQL or XML.  I will have to keep an eye out for the videos from the Devcon.  It looks like I am in need of returning to Devcon next year (its been about 5 years since I've been).

                • 5. Re: Exporting Subsummary Reports to excel Issue
                  beverly

                  Don't know as I was unable to attend this year, Taylor, but I've been doing export to xml and make Excel for years.

                   

                  The cool features are the formulas and styling that you just cannot get otherwise. Pretty much if you can create in Excel and export as XML, you see what it needs to be for import/open and how complex the XSLT can get for using FMP XML and transforming into the Excel XML.

                   

                  Beverly

                  • 6. Re: Exporting Subsummary Reports to excel Issue
                    BruceRobertson

                    I think you're referring to INT012, FileMaker Unleashed, Jonathan Mickelson.

                     

                    He demonstrated how you could create complex Word or Excel document templates with placeholder text.


                    Then save the templates using the option to save in XML format.

                     

                    Then copy the XML into a FileMaker field.

                     

                    Then do substitutions on the placeholder text.

                     

                    Export the field contents of the result and give it the proper file extension.

                     

                    You don't need to know SQL or XML and no XSLT is involved.

                    • 7. Re: Exporting Subsummary Reports to excel Issue
                      taylorsharpe

                      Yes, that is the name, Jonathan Mickelson.  Thanks, Bruce, for having better memory than me. 

                       

                      Jonathan was impressive and I hope he comes back to present again next year.  I'll tell more people to attend his presentation. 

                      • 8. Re: Exporting Subsummary Reports to excel Issue
                        bgustin

                        I prefered not adding another table to my invoiceing solution to do this report (though I might change my mind once I get better at reporting).  I  now have a scrip that works for my needs.  I just did a looping scrip that does a find for each day (for total sales data), and adds the sales totals in a nother line on the variable.  Once all the finds are complete it emails me the variable that contains the totals once a week.  Its not pretty, but it works.

                         

                        Thanks everyone for giving me ideas to my final solution.

                         

                        2013-11-15_0958.png

                        Email Message

                        2013-11-15_0959.png