Node - sql

Owned by Verba Support (Unlicensed)
Sept 24, 2012
3 min read

Description

This node contains the SQL query that provides data for of the axis of the chart.

Attributes

None

SELECT

Mandatory columns

  • the column that is mapped with the xAxis attribute of the dash node
  • the column that is mapped with the yAxis attribute of the dash node
  • the column that is mapped with the seriesId attribute of the dash node
  • the column that is mapped with the seriesName attribute of the dash node

Additional columns

  • if you want to group your query with the GROUP BY SQL clause, you have to add this column to your result set, because of the SQL restrictions (like the qv.user_id column in Top Performing Agents)
  • if you want to control the size of the result set, you can use the @topX variable after the SELECT TOP SQL keywords. This causes the Number of items to be selected input box to appear in the widget configuration popup to provide an input for defining the size of the result set. The @topX variable will be replaced with the value typed into this input box before the SQL query is executed.
  • in trend charts, you may want to group the result set by some time step (weeks, months etc.). This can be achieved by using the @tim ause variable which has to be added to the query in two places. Once after the GROUP BY SQL clause (to group the result set) and once more among the columns, to retrieve the values for the X-axis. More information about this can be found in the description of the tim /b> node.

WHERE

Number of WHERE clauses is not limited, you can use as many as you want.

Available variables include the following:

@filters defines the filters for the widget, which can be set on the WIdget Configuration panel under the Filter section. The variable will be replaced by the values set in this section. More information about this can be found in the description of the filters node.
@tzId this variable will be replaced by the time zone identifier selected in the drop-down menu in the Date Interval section of the Widget Configuration popup, named Display results according to time zone. You can use this option to control the time zone in which you want to see your chart. You can also use this along with the timezones table in the database to retrieve time offsets for each time zone.
@fromDate

this variable will be replaced with the start date and time set in the Date Interval section of the WIdget Configuration popup
@toDate this variable will be replaced with the end date and time set in the Date Interval section of the WIdget Configuration popup
@eid this variable will be replaced with the environment identifier of the logged in user

 

 

GROUP BY

Number of GROUP BY clauses is not limited, you can use as many as you want.

Available variables include the following:

@tim ause When you use trend reports, you can use this variable to control the timestep of the x-axis. This variable will be replaced with the value provided by the Time Axis drop-down in the Widget Configuration popup. More information about this can be found in the description of the tim /b> node.