AnsweredAssumed Answered

ExecSQL charting to produce percentage values

Question asked by Stu412 on Aug 14, 2015

Title

ExecSQL charting to produce percentage values

Post

I'm tring to use the SQL statement below to generate a margin percentage but as I understand it, FM SQL doesn't allow you to query on aliased columns?  In this case, COST and INC are fields (columns) which are generated using a CASE statement, with the source field being called AMOUNT.  I cannot return any data using the query below, but if I simply change it to Select SUM(Amount) I get the grand total of the column.  I don't want that, just a couple of sub totals.

Thanks

Let ([

CUST = Customer_Home::Cust_PK;
SQL = "Select (SUM(INC)-SUM(COST))/SUM(INC) from DataTable where

CustID_FK=? and
GroupID = ? and
PeriodNumber=?"];

ExecuteSQL(SQL;"";"";CUST;1;5) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;4) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;3) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;2) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;1) & "¶"

Outcomes