AnsweredAssumed Answered

Execute sql charting

Question asked by Hudi on Mar 12, 2013
Latest reply on Feb 16, 2016 by avega@me.com

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!

Outcomes