# 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](/charts/example-charts.md) ### 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 /pages/XLVj0yEk4zl6Ziw7DfcD and /pages/cy9TbS3DN8yYTmsHEvkZ
@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 /pages/XLVj0yEk4zl6Ziw7DfcD and /pages/0NMcord6aIeF378fJ6VT
@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 /pages/3elp6ltwD97QraAHXrWb
@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 /pages/FEwysIxYInvm9TeKdep0
@groups(none)-- @groups: Month, ProductSpecifies the fields for hierarchical grouping within the primary axis. See /pages/fWOLQR773AyVrxfnGuLZ
@series(none)-- @series: SalesSpecifies the columns to use for numerical series values. Represents data to be plotted corresponding to each category. See /pages/wsuSIOc5WnxeWbIPEwt6
@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 /pages/17enBBg10NJwuUqtnTSR
@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 /pages/WUMcaJy0CfiOEJrLrxSb
@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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.chartsql.com/reference/directives.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.