1 2 Previous Next 26 Replies Latest reply on Oct 4, 2016 4:59 AM by fmpdude

    google line chart with data via ExecuteSql

    rivet

      I would like to create a google line chart to show monthly sales over two years.

      Not worrying about the quotes and brackets, is it possible for executeSql to return a summary like the one below.

       

      ['Month', '2016', '2015'],
      ['JAN',  1000,      400],
      ['FEB',  1170,      460],
      ['MAR',  660,       1120]

       

      Single column is straight ahead

      SELECT

      mth, sum (amount)

      FROM

      sales

      WHERE

      yr = 2015

      GROUP BY

      mth

       

      Its just the 'double' summary that is the question.

        • 1. Re: google line chart with data via ExecuteSql
          rivet

          FWIW in mysql this works.

           

          SELECT

          mm, SUM(TY) as TYS, SUM(LY) as LYS

          FROM

            (

            SELECT mm,

            amount as TY,

            0 as LY

            FROM

            sales

            WHERE

            yy = 2016

           

            UNION

           

            SELECT mm,

            0 as TY,

            amount as LY

            FROM

            sales

            WHERE

            yy = 2015

           

            ) as Temp

          GROUP BY

          mm

          • 2. Re: google line chart with data via ExecuteSql
            okramis

            Something like this should do it:

             

            Let ( [

             

            _r = ExecuteSQL ( "

             

            SELECT '[''' || mm || '''',

            SUM(CASE yy WHEN 2016 THEN amount ELSE 0 END),

            SUM(CASE yy WHEN 2015 THEN amount ELSE 0 END)

            FROM sales

            GROUP BY mm

             

            " ; "," & Char(9) ; "],¶" ) & "]"

             

            ] ;

            "['MONTH', '2016', '2015']¶" &

            _r

            )

             

            Best regards

            Otmar

            2 of 2 people found this helpful
            • 3. Re: google line chart with data via ExecuteSql
              beverly

              I can confirm that this is valid with my data. Even with parameters for the years instead of hard-coded. Nice Otmar!

               

              Sent from miPhone

              • 4. Re: google line chart with data via ExecuteSql
                fmpdude

                Nice job.

                 

                I really don't like the nasty "Let" syntax (not your fault) over the clean clear actual SQL above, but Let can get the job done.

                • 5. Re: google line chart with data via ExecuteSql
                  beverly

                  Let() can add variables used in the query that otherwise might break (the renaming of a field, for example). Because our query is just TEXT used by the ExecuteSQL() function, we have to resort to some 'nasty' that otherwise we would not. <sigh/>

                   

                  Let (

                     [ $prequery = " SELECT name, id, 'id' FROM salesperson " // set up query

                   

                     /***********************

                        now create "mappings"

                     ***********************/

                     ; $name = Substitute ( GetFieldName ( salesperson::name ) ; "::" ; "." )

                     ; $id = Substitute ( GetFieldName ( salesperson::salespersonID ) ; "::" ; "." )

                   

                     /***********************

                        final substitutions

                     ***********************/

                     ; $query = Substitute ( $prequery

                        ; [ "name" ; $name ]

                        ; [ "id" ; $id ]

                        )

                   

                     ; $result = ExecuteSQL ( $query

                        ; Char(9) ; Char(13) & Char(13) )

                   

                     ]; $result

                  ) // if field names are changed AND this is a calculation, the fields name would be corrected automatically by FileMaker

                   

                   

                  /* NOTE: be cautious of the final Substitute(), as it will change every pattern match in the $query! */

                  • 6. Re: google line chart with data via ExecuteSql
                    fmpdude

                    Gotcha, but I was referring to the ugliness of the Let function overall (and, of course, there's no code assist to create or any debugging capability while the CF (or report, in this case) is running) vs. the clean look and much easier to create SQL.


                    Thanks Bev.

                    • 7. Re: google line chart with data via ExecuteSql
                      okramis

                      I agree, the combination of SQL and FM functions won't win a beauty contest, but on the other hand it's quite powerful and almost exceeds the limitations of FMs SQL implementation. The only things I miss is, calculations with aggregates and use of sub-selects as derive tables and of course it would be nice if it would perform better;-)

                      1 of 1 people found this helpful
                      • 8. Re: google line chart with data via ExecuteSql
                        fmpdude

                        I really liked your solution.

                         

                        In fact, it's been a while since I had to do any SQL like that so I spent a fair amount of time considering that type of data presentation.

                         

                        Great job.

                        • 9. Re: google line chart with data via ExecuteSql
                          fmpdude

                          This SQL is so interesting in this post, it made me go back and try to see what was going on. I don't use UNONs that often so the syntax of the outer query took me a little while to figure out. This is very important to understand: all columns needed must be in both UNION (inner) queries to generate separate columns so the outer query can then SUM on them. (I had forgotten that!). It was only after I deconstructed the query and created some play data that I saw what was going on.

                           

                          Thanks for this excellent posting!!!

                           

                          Below, was my attempt at creating play data that uses your query, as written.

                           

                          I've also attached my "play data" as a CSV below.

                           

                          Hope this additional information is useful to someone.

                           

                          Note: the data display below is using MySQL

                           

                          • 10. Re: google line chart with data via ExecuteSql
                            okramis

                            Yes, that's how it works and that's what I mean with I miss "use of sub-selects as derive tables" in FQL.

                            You can simulate it by filling a Virtual List with the result of the "inner" query and do the "outer" query on the Virtual List, but then we're back to the ugly Let()-function again;-)

                            1 of 1 people found this helpful
                            • 11. Re: google line chart with data via ExecuteSql
                              fmpdude

                              Without seeing your output, doesn't your Let() statement do something like this (using MySQL):

                               

                              SELECT yy as "Year", IF(yy='2015', SUM(amount), sum(amount)) as theSum from sales group by  yy

                               

                              Giving this output:

                               

                              ?

                              • 12. Re: google line chart with data via ExecuteSql
                                okramis

                                nono, the output is the asked output of the OP. Crosstab mm, sum(year 1), sum(year 2) grouped by mm with quoted strings, comma + tab as delimiter and the rows in sqare brackets:

                                 

                                ['Month', '2016', '2015'],
                                ['JAN',  1000,      400],
                                ['FEB',  1170,      460],
                                ['MAR',  660,       1100]

                                • 13. Re: google line chart with data via ExecuteSql
                                  rivet

                                  Thanks to all for chiming in. This is working really well but there is a twist.

                                   

                                  My initial question was simplified. What I really need is a two year comparison from current month.  So I have modified Otmar's CASE:

                                  SUM(CASE WHEN  date_start >= '11/1/2014' AND date_start < '11/1/2015' THEN amount ELSE 0 END),

                                  SUM(CASE WHEN  date_start >= '11/1/2015' AND date_start < '11/1/2016' THEN amount ELSE 0 END)

                                   

                                  which finds the correct data but in the incorrect order :

                                  ['MONTH', '2015', '2016'],

                                  ['1', 0, 0],

                                  ['2', 0, 0],

                                  ['3', 0, 0],

                                  ['4', 0, 0],

                                  ['5', 0, 0],

                                  ['6', 0, 0],

                                  ['7', 0, 0],

                                  ['8', 0, 0],

                                  ['9', 0, 0],

                                  ['10', 0, 0],

                                  ['11', 0, 0],

                                  ['12', 0, 0]

                                   

                                  to chart the date properly for an October report, this is the order that is required.

                                  ['MONTH', '2015', '2016'],

                                  ['11', 0, 0],

                                  ['12', 0, 0],

                                  ['1', 0, 0],

                                  ['2', 0, 0],

                                  ['3', 0, 0],

                                  ['4', 0, 0],

                                  ['5', 0, 0],

                                  ['6', 0, 0],

                                  ['7', 0, 0],

                                  ['8', 0, 0],

                                  ['9', 0, 0],

                                  ['10', 0, 0]

                                   

                                  I attempted a CASE under ORDER BY, which I could not get to work, plus I believe it would be messy just trying to generate the appropriate string.

                                  ORDER BY

                                       CASE

                                            WHEN mm = '11' THEN '1'

                                            WHEN mm = '12' THEN '2'

                                       ELSE mm END ASC

                                   

                                  I think it is down to manipulating the result, within the Let, using some FMP functions.

                                  _pos = Position ( _r ; "¶" ; 1 ; Month ( Get ( CurrentDate ) ) ) ;

                                  _top = Middle ( _r ; _pos + 1 ; 9999 ) & "," ;

                                  _bot = Left ( _r ; _pos - 2  )

                                  ];

                                   

                                  "['MONTH', '2015', '2016'],¶" &

                                  _top &"¶"&

                                  _bot

                                  )

                                   

                                  which is fine, but I am curious if there is any more ExecuteSql magic to be had.

                                   

                                  Thanks again!

                                  David

                                  • 14. Re: google line chart with data via ExecuteSql
                                    okramis

                                    You could add a un-stored calc to your sales table:

                                     

                                    mmsort =

                                    If ( mm < Month ( Get ( CurrentDate ) ) //this would start today with month 10, "<=" would start with month 11

                                    ; mm + 12

                                    ; mm

                                    )

                                     

                                    and add this field to the GROUP BY and the ORDER BY clause

                                     

                                    or do it like this:

                                     

                                    Let ( [

                                     

                                    _mtoday = Month ( Get ( CurrentDat ) )

                                     

                                    ; _rp1 = ExecuteSQL ( "

                                     

                                    SELECT '[''' || mm || '''',

                                    SUM(CASE WHEN  date_start >= '11/1/2014' AND date_start < '11/1/2015' THEN amount ELSE 0 END),

                                    SUM(CASE WHEN  date_start >= '11/1/2015' AND date_start < '11/1/2016' THEN amount ELSE 0 END)

                                    FROM sales

                                    GROUP BY mm

                                    HAVING mm > ?

                                     

                                    " ; "," & Char(9) ; "],¶" ; _mtoday ) & "],"

                                     

                                    ; _rp2 = ExecuteSQL ( "

                                     

                                    SELECT '[''' || mm || '''',

                                    SUM(CASE WHEN  date_start >= '11/1/2014' AND date_start < '11/1/2015' THEN amount ELSE 0 END),

                                    SUM(CASE WHEN  date_start >= '11/1/2015' AND date_start < '11/1/2016' THEN amount ELSE 0 END)

                                    FROM sales

                                    GROUP BY mm

                                    HAVING mm <= ?

                                     

                                    " ; "," & Char(9) ; "],¶" ; _mtoday ) & "]"

                                     

                                     

                                    ] ;

                                    List ( "['MONTH', '2016', '2015']" ; _rp1 ; rp2 )

                                    )

                                    1 2 Previous Next