9 Replies Latest reply on Dec 31, 2014 3:57 PM by philmodjunk

    ExecuteSQL : Calculation based on 2 calculated members

    FredH

      Title

      ExecuteSQL : Calculation based on 2 calculated members

      Post

      Hi Experts,

      I would like to create a line graph that shows a ratio using ExecuteSQL. 

      The formula should look like this : 
      ExecuteSQL("SELECT (sum(E.\"NOP Frais incl.\") / sum(E.\"Nombre\") AS Result FROM Exploitation AS E GROUP BY E.\"Année\" ";"";"")

      It seems that ExecuteSQL has some issues with calculations based on calculated members because the query (via graph as also via the data viewer) gives "?" as a result.  Do you know what type of trick I could use to make this ExecuteSQL work? 

      Thanks in advance for your input!
      Kind regards,

      _Fred_

        • 1. Re: ExecuteSQL : Calculation based on 2 calculated members
          philmodjunk

          This is a limitation of FileMaker SQL. Using the SQL.Debug custom function to test this expression, I get this error message:

          Expressions involving aggregations are not supported.

          For those interested, this is the SQL.Debug custom function:

          If (

          //the sql call results in an error, return empty so the error will be returned
          _executeSQL = "?" ; "" ;

          //the sql call is executed correctly, just return the result
          _executeSQL
          )

          // ===================================
          /*

              This function is published on FileMaker Custom Functions
              to check for updates and provide feedback and bug reports
              please visit http://www.fmfunctions.com/fid/335

              Prototype: sql.debug( _executeSQL )
              Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
              Last updated: 28 July 2012
              Version: 2.2

          */
          // ===================================

          You can use this with FileMaker Advanced's data viewer to get actual error messages out of failed uses of ExecuteSQL.

          • 2. Re: ExecuteSQL : Calculation based on 2 calculated members
            FredH

            Hi PhilModJunk,

            Thanks for your input!  I was not aware of this SQL.Debug custom function.
            Do you think there is a workaround for my specific case?  

            I found following example on another forum but I am not sure how to adapt this to my specific case, knowing that it is to be used with a graph : 

            Let ( [
             
            $$vl = ExecuteSQL ( 
            "SELECT COUNT(tab), SUM(\"amounts\") 
            FROM SummaryData 
            WHERE tab >15 GROUP BY tab"
            ; "||" ; "" )
             
            ] ;
             
            ExecuteSQL ( 
            "SELECT Field01, Field02, NUMVAL(Field01) * NUMVAL(Field02)
            FROM VirtualList
            WHERE Field01 IS NOT NULL"
            ; "" ; "" )
             
            )

            Thanks in advance for your additional input!
            KR,

            _Fred_ 

            • 3. Re: ExecuteSQL : Calculation based on 2 calculated members
              philmodjunk

              I don't quite follow how that would work. But I can see a way to chart this data using older "FileMaker" methods in place of the SQL. If you chart summary values from a found set, you can use summary fields and the getSummary function to compute and chart the same data.

              • 4. Re: ExecuteSQL : Calculation based on 2 calculated members
                FredH

                Is it possible to combine the GetSummary together with a "filter"?  

                I could use Exploitation::Année as BreakField but I would also like to "filter" on Exploitation::Region as I would like to have one line per Region.
                 

                • 5. Re: ExecuteSQL : Calculation based on 2 calculated members
                  philmodjunk

                  That would be a bit of a problem. It's not insurmountable, but I can see why ExecuteSQL would be a preferable approach here.

                  You'd need to set up a series of calculation field such as:

                  If ( Exploitation::Region = "region 1" ; Field with value to sum here )

                  You can then define a different summary field for each such calculation field. That's far from ideal, but if your set of regions don't change very often, it might be possible to use.

                  I'm not sure if there is a "pure SQL" way to generate these values or not, but I can conceive of a recursive custom function that takes two lists of values and returns a single list of delimited values for charting by dividing the items in list one by the items in list 2....

                  • 6. Re: ExecuteSQL : Calculation based on 2 calculated members
                    FredH

                    Hi PhilModJunk,

                    I could work with the additional fields but do you have an example of your second option? (recursive custom function)

                    • 7. Re: ExecuteSQL : Calculation based on 2 calculated members
                      FredH

                      Hi PhilModJunk,

                      After some tests, I am not sure that the GetSummary will work for me as I have to calculate a ratio of 2 summary fields (based on 2 group by's).
                      Do you have an example of this recursive custom function to check if I could use this ?

                      Thanks in advance for your input!
                      Kind regards,

                      _Fred_

                      • 8. Re: ExecuteSQL : Calculation based on 2 calculated members
                        philmodjunk

                        GetSummary could still be used. You specify the "inner" break field and still sort your data by both break fields.

                        Example: If you sorted "cities" by State and then by County, you'd use use "county" as your break field.

                        A recursive custom function requires Filemaker Advanced to create and install into your file. If you are charting large sets of numbers (1,000's of data points)  it may not work or may be too slow:

                        //ListDivide ( List1 ; List2 )
                        //
                        //Divide each item in list1 by the corresponding item in List2. The same number of list elements must be present in both parameters:
                        //
                        Let ( [ vc1 = ValueCount ( List1 ) ;
                                   vc2 = ValueCount ( List2 )
                                 ] ;
                                 Case (vc1 ≠ vc2 ; "" ; // end and return no value if lists do not have equal numbers of values
                                            IsEmpty ( List1 ) ; "" ; // terminate recursion when lists are empty
                                            List ( GetValue ( list1 ; 1 ) / GetValue ( list2 ; 1) ; ListDivide ( RightValues ( List1 ; vc1 - 1 ) ; RightValues ( List2 ; vc1 -1)))

                                          ) // Case
                                ) // Let

                        • 9. Re: ExecuteSQL : Calculation based on 2 calculated members
                          philmodjunk

                          Just remembered a crucial limitation of charting groups of summarized data. Unlike a summary report, you can only sort on one break field. The getsummary calcs will work as I described, but you can't get the right groups for data points in the chart. sad

                          The work around is to define a calculation field that combines the two break field values such that by sorting on this calculation field, you get the same groups that you would if you sorted on your two break fields.

                          Referring to my States, Counties sorting example, You'd create a calculation field such as: State & "|" & County with a text result type and then sort on that calculation field.