ExecuteSQL with Charts

I am attempting to place a chart on an opening layout (dashboard) in a FM solution. The chart pulls data from tables not in the layout so I am using ExecuteSQL() to get the data. I am running the SQL directly in the chart object calculation on the X and Y axis (X axis is date grouping, Y axis is a count() of inspections). the performance is terrible on 4500 records making the screen take several seconds to load. Is there a much better option? Calc field in a table perhaps?

Secondly, this is simple X axis calculation. I cant seem to get it to resolve the "year()" in the where clause of the SQL. What is the syntax to pull a year (or even month) from a date field?

Let ( thisyear = Year(Get(CurrentDate)) ;

ExecuteSQL ( "select inspdate from inspection_parent where year(inspdate)=? order by inspdate";"";""; thisyear) )

Group By queries are documented in the knowledge base as being slow to evaluate. Your query looks right to me if your inspection date field is of type date.

Another approach is to set up your chart object on a different layout so that you do not need SQL. You can then use a script and the getLayoutObjectAttribute function to set a container on your dashboard to the chart’s image to show the chart on your dashboard.

The column named "m" does not exist in any table in the column reference's scope.

But when I take the Count(*) and GROUP BY out, I get the first three characters of the MONTHNAME. These are valid statements. But, as you may find, the ExecuteSQL() is an odd duck in the SQL world. And I say that lovingly! It does what it does and no more.

If I need the value:

Left(MonthName(mydatefield);3)

... I put an auto-enter field into the database (with the above calc).

Another tip: If I create the auto-enter, it is likely a number (or text) yr_mon =:

( Year(mydatefield) * 100 ) + Month(mydatefield)

This yield a number like YYYYMM. It is highly sort-able, group-able, & is alpha, numerical, AND chronological in the sort.