Graphing using SQL

Creating graphs in ISQL (Interactive and Embedded SQL) is very easy to do. As you create a query, and execute it, you can graph the results.

Converting Result Sets to Graphs

• If there are only two columns in your SQL results, the system will chart the results as X-AXIS, DATAPOINT
• If there are more than two columns in your SQL results, the system will chart the results as LABEL, X-AXIS,DATAPOINT,(next data point, next data point, next data point)
• If you wish to skip the LABEL in a multi-series graph, use a literal of '' as your first column such as SELECT '',X-AXIS,DATAPOINT1,DATAPOINT2,DATAPOINT3

Here is an example of a simple Graph:

SELECT BEDTYPE AS 'ROOM TYPE',COUNT(*) AS 'TOTALROOMS' FROM ROOMS

  GROUP BY BEDTYPE

  ORDER BY BEDTYPE

  EXPORT AS GRAPH USING 'BAR'

In a simple graph form, the first column would be the Series Label and the second column would be the Data point.

Here is an example of a multi-series Graph:

SELECT RATE_REQ AS 'RATE',MONTH(ENTRYDATE) AS 'MONTH',COUNT(ROOMS) AS 'TOTALROOMS' FROM RESERVE

  WHERE RATE_REQ <> '' AND RATE_REQ <> 'COMP'

  GROUP BY MONTH(ENTRYDATE),RATE_REQ

  ORDER BY MONTH(ENTRYDATE),RATE_REQ

 EXPORT AS GRAPH USING 'BAR'

In a multi-series graph form, the first column is the Series Label, the second column X-Axis and the third column would be the Data Point.

Here is an example of a multi-series Graph with Month Name extended:

SELECT RATE_REQ AS 'RATE',MONTH(ENTRYDATE) AS 'MONTH',COUNT(ROOMS) AS 'TOTALROOMS' FROM RESERVE

  WHERE RATE_REQ <> '' AND RATE_REQ <> 'COMP'

  GROUP BY MONTH(ENTRYDATE),RATE_REQ

  ORDER BY MONTH(ENTRYDATE),RATE_REQ

FORMATCOLUMN 2, COLUMNPICTURE=@MONTHNAME

 EXPORT AS GRAPH USING 'BAR'

In this example, we've formatted the month (1 through 12) as the actual month name (January, February, etc.) using the FormatColumn SQL Extension.

Here is an example of a multi-series Graph where the multiple series data is on the same database record:

SELECT 'RESERVED' AS 'SERIES', RESERVE.ENTRYDATE AS 'DATE', COUNT(RESERVE.CONFNUM) AS 'RESERVATIONS'

FROM RESERVE

WHERE RESERVE.ENTRYDATE BETWEEN {?SELECT LOWER AND HIGHER DATE%%} AND RESERVE.CXLNUM IS NULL
GROUP BY RESERVE.ENTRYDATE

UNION ALL

SELECT 'CANCELLED' AS 'SERIES', RESERVE.CXLDATE AS 'DATE', COUNT(RESERVE.CONFNUM) AS 'RESERVATIONS'

FROM RESERVE

WHERE RESERVE.ENTRYDATE BETWEEN {?SELECT LOWER AND HIGHER DATE%%} AND RESERVE.CXLNUM IS NOT NULL
GROUP BY RESERVE.ENTRYDATE

EXPORT AS GRAPH USING 'STACKEDBAR'

In this example, we've queried the same records twice, creating one series for "Accessed" and one for "Booked" and labeled them appropriately.