AnsweredAssumed Answered

SQL Chart Group, return single column

Question asked by jtorpy on Feb 28, 2018
Latest reply on Mar 1, 2018 by jtorpy

I have this as he y-axis data for a chart. (x-axis is a select distinct on YrMon) I need to return one set of data but first I need to group by YrMon. I guess FM requires group by fields to be in the select argument. So that means the sql returns two columns rather one. So I'm trying to do a subsequent sql to take that two column SQL and return only one.


I also want that one column summed across the YrMon.


I could create a whole chart into a variable with a separate script, but I think it's possible to just do in the chart data field. It doesn't seem to be working. Maybe I could just sum in the first sql and then return _firstSQL(2) or something... not sure.


Everyone has been really great here. I appreciate the help. Hopefully I'll get good enough to help others one of these days.


Let ([_Row = 1;

_Item = GetValue (tblGlobals::g_Metric ; _Row );

_firstSQL = ExecuteSQL("

SELECT YrMon, Quantity

FROM impDataRpt

WHERE Metrics = ?

AND (\"Year\" >=? AND Mo >=?)

AND (\"Year\" <=? AND Mo <=?)


ORDER BY \"Year\", Mo";


_Item ; tblGlobals::g_YrStart; tblGlobals::g_MoStart;tblGlobals::g_YrEnd;tblGlobals::g_MoEnd)




SELECT Sum(Quantity)


FROM _firstSQL";"";""