14 Replies Latest reply on Oct 24, 2016 5:28 AM by Magnus Fransson

    Reporting and Graphing

    Magnus Fransson

      Hi all,

       

      I’m supporting a large solution that combines CRM with Orders, Stock and Invoice for a small company. Now they want to utilize the data to get a better view of how business is going. That means statistics and graphs. Reports I’ve been doing for long (though some technics seem to elude me), but graphs are still rather new to me.

       

      I can do reports that show how much each manufacturer has sold (or each product), but I can’t seem to sort them in order “Who sold the most”, that is, on the statistics field itself. I have to export to Excel to do that.

       

      I'm also trying to do a line graph that shows how much each manufacturer has sold each month, by view one squiggly line per manufacturer. (The X-axis is Months and the Y-axis is sold per month.) Right now I can only get one squiggly line representing total sold per month.

       

      All this is based on data that exist in the “Invoice rows table”. The attached picture illustrates a simplified ERD of how the invoice, customers, products and manufactures connect. I have also attached a FileMaker file that contains a “fake” “Invoice rows table”. The first layout contains a table view of the table. The data has been deliberately anonymized (and fields removed), but the data is real. When the data is sorted correctly, layout two shows an unformatted draft for a report, illustrating sales per manufacturer per month. The third layout holds the graph I made so far.

       

      Questions:

      • How do I, in the report, sort the manufacturers in order of which sold the most?

      • Can I make a report that only shows the five most selling manufacturers?

      • How can I make the graph, dynamically, show separate lines of how much each manufacturer has sold for, per month?

       

       

      More questions will come later.

       

      With best regards Magnus Fransson.

        • 1. Re: Reporting and Graphing
          fmpdude

          To set up the y-data, I generally use SQL unless it's a super simple report. With SQL's ORDER BY, it's easy to sort your data as needed.

           

          In other cases, where SQL might be difficult, I've even resorted to creating a special "report layout" where I "Find" just the data I want to report on, then run the report using that layout. You can customize the data to a fine degree using that technique.

           

          ---

           

          However, you might want to check out some YouTube videos or the Lynda.com video series or even the FileMaker Advanced training.

           

          HOPE THIS HELPS.

          • 2. Re: Reporting and Graphing
            Magnus Fransson

            Hi all,

             

            Late yesterday afternoon I found out how to set the graph to accept “delimited values”. That opens up possibilities previously locked to me. So now I’m starting to experiment with using the ExecuteSQL() function to populate the graph.

             

            With best regards Magnus Fransson.

            • 3. Re: Reporting and Graphing
              Malcolm

              to sort by Manufacturer who has sold the most, In the sort dialog box, select Manufacturer. Now look to the bottom half of the box and find "reorder based on summary field". That will offer you the chance to select a summary field. You can choose to sort Asc or Desc. Look at the documentation for this, the PDFs are quite good.

               

              To filter the top five you have to run a full report for the period in question and then sort to obtain your summary values. If you have the sort arranged in Descending order by Summary the top five are the first five. There may be thousands of records that are in the data set so you have to write a script which walks the records until it gets to the end of the top five manufacturers records and exclude the rest.

               

              I'm not sure I understand your last question. If you mean, display manufacturer sales by month, as below. It simply a matter of switching the sort order. If you mean a cross-tab summary, then look at the documentation, there's a good explanation in the guides.

               

              Levi

                   Jan      $

                   Feb      $

                   ...

                   Dec     $

              Wrangler

                   Jan     $

                   Feb     $

                   ...

                   Dec     $

              2 of 2 people found this helpful
              • 4. Re: Reporting and Graphing
                Magnus Fransson

                Hi Malcolm,

                 

                Thank you for pointing my attention to the "reorder based on summary field" checkbox. That certainly solved my first question. (I knew that I saw a presentation several years ago, but I never succeeded in reproduce that, until now. So, thank you very much.)

                 

                Second question: To script to grab the first five manufacturers and refine the search, is some thing that I’m very capable to do. But if anyone has some “neat trick” that will make it quicker and/or easier, I’m still open for suggestions.

                 

                Third question: No. It has nothing to do with Reporting. It is about Graphing. Line graph to be precise, multiple lines, one squiggly line per manufacturer. But I think I’m past the immediate block there to, as mentioned in my previous post.

                 

                With best regards Magnus Fransson.

                • 5. Re: Reporting and Graphing
                  Magnus Fransson

                  Phew!

                   

                  Just managed to get the calculation below to produce the result I wished for. That means that I’m one step closer to get the graph I want. A big thanks to beverly for her invaluable ExecuteSQL Reference.

                   

                  It is cumbersome work, but rewarding to search for, and cobble together, information and making it work. One learns so much on the way! That is why I love to solve problems with FileMaker as tool. And a large reason of why I love my work.

                   

                  The Calculation:

                  Let
                  (
                      [
                          Table = Quote ( ~GetSQLFieldTable ( Rows::InvoiceDate ) )
                      ;
                          SumField = Quote ( ~GetSQLFieldName ( Rows::Sum ) )
                      ;
                          MatchField = Quote ( ~GetSQLFieldName ( Rows::Manufacturer ) )
                      ;
                          GroupField = Quote ( ~GetSQLFieldName ( Rows::InvoiceMonth ) )
                      ;
                          DateField = Quote ( ~GetSQLFieldName ( Rows::InvoiceDate ) )
                      ;
                          MatchValue = "Man1"
                      ;
                          StartDate = Date ( 1 ; 1 ; 2011 )
                      ;
                          EndDate = Date ( 12 ; 31 ; 2011 )
                      ;
                          SQL = "SELECT SUM( " & SumField & " ) FROM " & Table & " WHERE " & MatchField & " LIKE ? AND " & DateField & " >= ? AND " & DateField & " <= ? " & " GROUP BY " & GroupField & " ORDER BY " & GroupField & " ASC"
                      ;
                          Error = EvaluationError ( ExecuteSQL ( SQL ; "" ; "" ; MatchValue ; StartDate ; EndDate ) )
                      ;
                          IsError = GetAsBoolean( Error ≠ 0 )
                      ;
                          Result = Case( not IsError ; ExecuteSQL ( SQL ; "" ; "" ; MatchValue ; StartDate ; EndDate ) )
                      ]
                  ;
                      "SQL: " & SQL & "¶MatchValue: " & MatchValue & "¶Error: " & Error & "¶Result:¶¶" &
                      Result
                  )
                  

                   

                  The result:

                  SQL: SELECT SUM( "Sum" ) FROM "Rows" WHERE "Manufacturer" LIKE ? AND "InvoiceDate" >= ? AND "InvoiceDate" <= ?  GROUP BY "InvoiceMonth" ORDER BY "InvoiceMonth" ASC
                  MatchValue: Man1
                  Error: 0
                  Result:
                  
                  11362
                  18164
                  11894
                  3002
                  11020
                  6080
                  4940
                  9082
                  58243.66
                  10057
                  46212
                  

                   

                  It can be seen in the file I upload with this post.

                   

                  With best regards Magnus Fransson.

                  • 6. Re: Reporting and Graphing
                    Magnus Fransson

                    Hi all,

                     

                    Now I do need your help!

                    It might not be obvious from the calculation result above, but one value are missing. (There are only 11 values, but 12 are wanted.) There are no products from “Man1” sold in January. So January is missing, but that does not make any good graph.

                     

                    • How do I ensure that “0”:s are inserted when there are no sales in random months?

                     

                    Next problem is hopefully easier to fix. When looking at what “Man10” sold in September there are a “calculation mistake”. It is typical binary mathematics on floating point numbers. It is easily corrected using ROUND(), but as I tried my self, it went wrong.

                     

                    SQL: SELECT SUM( "Sum" )  FROM "Rows" WHERE "Manufacturer" LIKE ? AND "InvoiceDate" >= ? AND "InvoiceDate" <= ?  GROUP BY "InvoiceMonth" ORDER BY "InvoiceMonth" ASC
                    MatchValue: Man10
                    Error: 0
                    Result:
                    
                    81660
                    15060
                    112186.6
                    148702
                    91886
                    211309
                    140846
                    30290
                    257993.8999999999996
                    141239
                    176285.72
                    21672.9
                    
                    

                     

                    • How do I implement ROUND() in the SQL statement in previous post?

                     

                    With best regards Magnus Fransson.

                    • 7. Re: Reporting and Graphing
                      beverly

                      You will need to post-process the data after ExecuteSQL(). Several of us have discovered that you cannot 'nest' aggregates with other SQL functions.

                       

                      If you check the error, you will get code 8309 "Semantics error" with EvaluationError() around your query. More specifically, you will get "Expressions involving aggregations are not supported." if you have Data Viewer to test your query.

                       

                      beverly

                      • 8. Re: Reporting and Graphing
                        beverly

                        to answer the other question:

                        • How do I ensure that “0”:s are inserted when there are no sales in random months?

                         

                        I believe there are answers on this forum, I see if I can find them. You may need to supply the months queried with CASE.

                         

                        Something like this:

                         

                        usage of SUM(CASE ... ) is ok, but ROUND(SUM(...)) is not

                        beverly

                        1 of 1 people found this helpful
                        • 9. Re: Reporting and Graphing
                          Magnus Fransson

                          Hi All and Beverly,

                           

                          Post-processing it is. Created a recursive custom function that rounds a list of numerical values to desired precision. As it were, I had to adjust the decimal delimiter as well.

                           

                          I also updated the calculation in that way that I, took the content of the previous custom functions out and put it direct in the calculation instead. Thus getting the GetFieldName() function to make the calculation independent of context.

                           

                          The remaining question is: How to implement the CASE state, to find those “missing” months? I think I have to use a combination of StartDate and EndDate to get a “List” of expected months, and then compare the content in GroupField to that list to see if a month is missing and then insert a “0”. But it beats me how to accomplish that. To make matters worse, I’m planning to make the GroupField dynamic so that I can use the same graph to view, either months, quarters or years.

                           

                          With best regards Magnus Fransson.

                          • 10. Re: Reporting and Graphing
                            beverly

                            did you look at the other thread? Yes, you have to supply the missing months.

                            And I might use FileMaker's Case() to set up different queries for the months, quarterly, annually. Then you can pass a parameter to test for which query to call when needed for the graph.

                            beverly

                            • 11. Re: Reporting and Graphing
                              Magnus Fransson

                              Hi beverly,

                               

                              How to use a parameter to select “versions” of the graph (periods, groupings (months, quarterly or annually), manufacturers et cetera) is almost completely thought out and clear in my head. I’m planning to use a handful of global fields, a couple value lists and if necessary some script triggers (to update the graph). No worry there!

                               

                              Since the entire calculation shall reside inside the graph, no “script technology” is allowed. That means that any type of “loop” has to be realized using recursion. That is: either FileMaker recursive custom function or any recursion construct that SQL might have. But to describe my thought process, I use the word “loop”, as I believe that that is clearer and easier to understand.

                              Question: Does SQL have any recursion construct and do ExecuteSQL() support it?

                               

                              Since the user can select any period from a week to ten years, it would be overwhelming to create one calculation for every possible data point in the graph. Therefore, it has to be one adaptive calculation.

                               

                              Reading the thread you recommended and your invaluable Reference I believe that I grasp the syntax for CASE. I believe it has to be something like:

                              SUM(CASE WHEN [month exists] THEN "Sum" ELSE 0 END)

                              What I am struggling with is how to find out if a [month exists].

                               

                              Here is where I start to speculate. As the “GROUP BY” divides, what has been found by the “WHERE”, in to neat little groups there are nothing for “WHEN” to compare with, for the missing months. So a different approach is needed. What if I inverse the scenario? Instead of finding all records, group them and then try to compare with the list, we loop through the list and find the records for each month in the list one at the time. If no records are found we return “0” otherwise we return SUM(“Sum”) ?

                               

                              I start on a couple custom functions focused in the last hypothesis while waiting for the community to contribute with their ideas.

                               

                              Question: Does anyone have any alternative suggestion for finding missing months?

                               

                              With best regards Magnus Fransson.

                              • 12. Re: Reporting and Graphing
                                beverly

                                what are your divisions on your graph(s)? think about how you would write ONE query and then change one (or two) variables and change the entire result. think about how changing a NATIVE Find can produce similar results, but be a variety of ranges in the dates. think about how you would display that data on a graph.

                                 

                                the query(ies) can be DYNAMIC. you can put the query in a field or variable and call. It can be calculated or however you are currently making the changes (or plan to).

                                 

                                Ok, so asking again: what are your divisions on your graph?

                                 

                                beverly

                                • 13. Re: Reporting and Graphing
                                  Magnus Fransson

                                  Phew 2!

                                   

                                  I am very close now.

                                   

                                  Calculation:

                                  Let
                                  (
                                      [
                                          Table = GetFieldName ( Rows::InvoiceDate )
                                      ;
                                          SumField = GetFieldName ( Rows::Sum )
                                      ;
                                          DateField = GetFieldName ( Rows::InvoiceDate )
                                      ;
                                          StartDate = Date ( 1 ; 1 ; 2011 )
                                      ;
                                          EndDate = Date ( 12 ; 31 ; 2011 )
                                      ;
                                          MatchField = GetFieldName ( Rows::Manufacturer ) 
                                      ;
                                          MatchValue = "Man2"
                                      ;
                                          SelectField =GetFieldName ( Rows::InvoiceMonth2 ) 
                                      ;
                                          StartYear = Year( StartDate )
                                      ;
                                          StartMonth = Month( StartDate )
                                      ;
                                          EndYear = Year( EndDate )
                                      ;
                                          EndMonth = Month( EndDate )
                                      ;
                                          SelectValuesList = ~MakeListOfDualValues ( StartYear ; StartMonth ; EndYear ; EndMonth ; 12 ; "" )
                                      ]
                                  ;
                                      ~RequestListSQL ( Table ; SumField ; DateField ; StartDate ; EndDate ; MatchField ; MatchValue ; SelectField ; SelectValuesList ; "" )
                                  )
                                  

                                  Result:

                                  0
                                  681
                                  454
                                  0
                                  1084
                                  0
                                  0
                                  3000
                                  906
                                  0
                                  0
                                  0
                                  

                                  All I have to figure out now is the X-axis. After that, I can make the total implementation. I hope to have it completed by the  weekend.

                                   

                                  Please download the FileMaker file and dissect it thoroughly and give any suggestions.

                                  There are a few Custom functions including some Recursive custom functions, adapted calculation fields and more to examine.

                                   

                                  With best regards Magnus Fransson.

                                  • 14. Re: Reporting and Graphing
                                    Magnus Fransson

                                    Hi all,

                                     

                                    I managed to run a successful test last friday. Now I have polished the graph and added comments to the custom functions.

                                     

                                    So here are the complete demo file.

                                     

                                    Have a nice time testing and dissecting the file.

                                     

                                    With best regards Magnus Fransson.

                                    1 of 2 people found this helpful