Directives

Documentation for the ChartSQL language directives

ChartSQL empowers users to create charts directly from SQL query results. You customize your charts with 'directives', embedded within SQL comment blocks.

For illustrative examples, refer to the Overview

ChartSQL Detection Modes

ChartSQL facilitates chart generation with the following input strategies:

• Auto Mode: No directives needed. Ideal for rapid visualization, determining chart types based on data types in the result set.

• Assistive Mode: When you have defined @chart ChartSQL fills in the category or series elements by sequentially scanning result columns from left to right.

  • For the @category It will pick the first column matching these types in this order: string, date, datetime, numeric

  • It will add all numeric columns as series from left to right

• Manual Mode: When you have defined @chart, @category and @series, it does not assist you and uses exactly what you define. It will leave out any columns not explicitly selected.

Directives

Directives inform ChartSQL of chart types, data series, stacks, and other features. Directives are categorized into Plotting, Style and Parsing.

Plotting Directives

These directives structure the chart data and layout:

• @chart: Declares the chart's form. Options include bar, column, line, pie, heatmap, datetime, and scatter.

• @category: Identifies primary axis data, influencing the x-axis (or y-axis for bar charts) or segments for pie charts. Categories will appear along the default axis for the chart type.

• @groups: Mutually exclusive with @category Specifies the fields for hierarchical grouping within the primary category, facilitating sub-categorization and more complex aggregated data structures.

• @series: Single entry for simple charts like pie or column; multiple entries dictate series for complex charts like line or scatter. For scatter plots, further assigns x & y values, size, and color.

• @series-types: For combo charts, specifies the type to use for each series item. Valid combo charts contain a mix of line, column, bar or area.

• @stacks: Specifies the category-group fields by which the data should be stacked within the chart. This directive is used to define layered representations of data associated with each category value.

• @stacking: true/false, determines if the stacking is applied

• @stacking-mode: percent/none, determines how the stacks are displayed. Either 'none' (default), or 'percent' to show relative contribution of the category.

• @baselines: Add reference lines to chart visualizations. These lines represent significant values such as averages, min, max, median of a series. Baselines provide visual cues for comparison against a common or target value.

Formatting Directives

Formatting Directives add clarity and and formatting to how the chart is displayed.

• @formats: Associates data output styles with the data series specified in @series. It accepts a comma-separated list, with each entry corresponding to the same position as the series in the @series list. If there are less format entries than @series, the last formatting style provided extends to the remaining series.

-- @series: TotalRevenue, NetProfit, TaxAmount
-- @formats: currency  // Formats all series as currency using the last specified format.

In the scenario where you want distinct formats for each series, explicitly define each format style as needed:

-- @series: TotalRevenue, NetProfit, TaxPercentage, UnitsSold
-- @formats: currency, currency, percentage, integer  // Each series is formatted with the corresponding style.

If only one series requires a distinct format, you can apply that format to the desired series and let the others inherit the last format style specified:

-- @series: TotalRevenue, GrossMargin, OperatingMargin
-- @formats: currency, percentage  // Revenue is in currency, GrossMargin is in percentage, and OperatingMargin inherits percentage

ChartSQL Directives Table Reference

The directives table lists available commands, their defaults, requirements, examples, and descriptions.

Directive	Default	Example	Description
@baselines	none	-- @baselines: Sales	Add reference lines to chart visualizations. These lines represent significant values such as averages, min, max, median of a series. Baselines provide visual cues for comparison against a common value. See Baselines and @baselines
@baseline-types	average	-- @baseline-types: min, max	Specifies the type of baseline to add for the series. The default baseline type is average. You can also specify min, median and max. See Baselines. See Baselines and @baseline-types
@chart	(none)	-- @chart: column	Specifies the type of chart to create based on the query results. Supported chart types include bar, column, line, pie, heatmap, and scatter and more. See @chart
@category	(none)	-- @category: Month	Defines the primary categorical axis for the chart, based on the designated field. Does not deduplicate or aggregate values. Use @groups for category aggregation. See @category
@groups	(none)	-- @groups: Month, Product	Specifies the fields for hierarchical grouping within the primary axis. See @groups
@series	(none)	-- @series: Sales	Specifies the columns to use for numerical series values. Represents data to be plotted corresponding to each category. See @series
@series-types	(none)	-- @series-types: column, line	Specifies the chart types to use for each of the defined series. If there are more series than series-types specified, the last series-type applies to all remaining series. See @series-types
@stacks	(none)	-- @category-stacks: Product	Designates categorical fields for stacking within the chart. To be used in conjunction with @groups for stacking of the primary axis.
@stacking-mode	none	Example: -- @stacking-mode: percent	Controls the type of stacking, 'none' or 'percent' where the stacks are normalized to 100% of the group total. See @stacking-mode
@tags	none	-- @tags: sales, margin	Groups scripts by their tags so that related scripts can be easily searched

Commenting Out Directives

At times, you may wish to temporarily disable a directive without removing it completely from your SQL script. To comment out a ChartSQL directive, prefix it with -- //. This indicates the line should be ignored as part of Chart configuration.

Usage

To comment out a directive, simply add two additional forward slashed ( // ) preceding the @

-- //@chart: column
-- //@series: Sales
SELECT ProductName, SUM(TotalSales) as Sales
FROM Orders
GROUP BY ProductName;

Reactivation

To reactivate the directive, remove the extra forward slashes:

-- @chart: column
-- @series: Sales
SELECT ProductName, SUM(TotalSales) as Sales
FROM Orders
GROUP BY ProductName;

Note: Only lines that start with -- // followed by @ will be treated as commented out ChartSQL directives. This feature allows you to quickly toggle chart parameters during exploration and debugging.