0 Replies Latest reply on Aug 14, 2015 3:13 AM by Stu412

    ExecSQL charting to produce percentage values

    Stu412

      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) & "¶"