i have a minor problem in filemaker pro 12
i'm using ExecuteSQL as calculation in a field as below. i'm new to SQL functions.
i need a chart to see top 5 clients in the current year where X axis should be client (company field) and Y axis is (total amount field) and below is the single calculation field.
the field is working fine and the results are correct its just i can't use it in the chart (chart is showing delimited data).
year = Company Dashboard::Current Year ;
decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
SQL = LeftValues(ExecuteSQL (
SELECT a.\"Company\", SUM(b.\"Total\") as InvoiceTotal
FROM \"Customers\" a
LEFT JOIN \"Invoices\" b ON a.\"CUSTOMER ID MATCH FIELD\" = b.\"CUSTOMER ID MATCH FIELD\"
WHERE b.\"Year\" = ?
GROUP BY a.\"Company\"
ORDER BY InvoiceTotal DESC
; "|" ; ¶ ;
) ; 5 )
Substitute ( SQL ; "." ; decimal )
any help i will appreciate.