2 Replies Latest reply on Feb 16, 2016 7:09 PM by avega@me.com

    Execute sql charting

    Hudi

      Good morning,

       

      I have 2 questions regarding charting using the ExecuteSQL function. What I want to do is create a line chart that displays sales by salesperson over a specific period of time (attachment 1 is as far as I've gotten).

      The X axis works fine and is a data set of the months selected by the user.

       

      My issue is with the Y Axis. I created 3 data series, one for each salesperson.

      Issue 1. When I try to use Order By (as I've done successfully in the x axis) I get an error. It might have something to do with the fact that I'm counting here. Nevertheless, I need the data series to be in order of month or its useless.

       

      Issue 2. Not all salespeople have sales every month. That means that a data series that is reflecting 6 months could only be showing 3 numbers. The third number in the set could be the data from the 6th month but its appearing as the third. Is there a way to have "fillers" for sql data series where there is no data just so that its structured correctly? I hope that makes sense.

      Here is the Y axis Calculation:

       

      ExecuteSQL (

      "SELECT Count("_k_ID_Location" )

      FROM "Location"

      WHERE "_id_salesperson" = ?

      AND

      "date_sale" >=?

      AND

      "date_sale" <=?

       

      Group BY "sale_month_year"

      //sales_month_year is a concatination of month and year.


      ";"";"";1;Global::g_start;Global::g_end

      //global fields are on the layout and constrain the time that appears in the chart, very cool.

      )

       

      Thanks!

        • 1. Re: Execute sql charting
          greglane

          Hi Hudi,

           

          One way to solve this is to add a table that defines your reporting periods. For example, create a table named Periods with two date fields named period_start and period_end and a calc field named month_year. Populate the table with one record for each period. Then you can use a correlated subquery to get the count for each of the reporting periods regardless of whether or not a particular salesperson has a record in each period.

           

          Here's the calculation for the first series:

           

          ExecuteSQL(

           

          "SELECT

            (SELECT COUNT(l.\"_k_ID_Location\" )

              FROM location l

              WHERE l.date_sale >= p.period_start

              AND l.date_sale <= p.period_end

              AND l.\"_id_salesperson\" = ?)

          FROM periods p

          WHERE period_start >= ? AND period_end <= ?"

           

          ; ""; ""; 1; Global::g_start; Global::g_end

          )

           

          Greg

          • 2. Re: Execute sql charting
            avega@me.com

            Safe my life! I got the same issue! Thanks a lot! Greg.