# 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 [example-charts](https://docs.chartsql.com/charts/example-charts "mention") ### 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. ```sql -- @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: ```sql -- @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: ```sql -- @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.
DirectiveDefaultExampleDescription
@baselinesnone-- @baselines: SalesAdd 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-typesaverage-- @baseline-types: min, maxSpecifies 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: columnSpecifies 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: MonthDefines 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, ProductSpecifies the fields for hierarchical grouping within the primary axis. See groups
@series(none)-- @series: SalesSpecifies 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, lineSpecifies 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: ProductDesignates categorical fields for stacking within the chart. To be used in conjunction with @groups for stacking of the primary axis.
@stacking-modenoneExample: -- @stacking-mode: percentControls the type of stacking, 'none' or 'percent' where the stacks are normalized to 100% of the group total. See stacking-mode
@tagsnone-- @tags: sales, marginGroups 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 `@` ```sql -- //@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: ```sql -- @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.