# Intro

ChartSQL is a visualization language embedded in SQL scripts. ChartSQL is designed for data scientists, SQL developers, and web application developers to quickly visualize complex queries.

## Visualize any SQL Query

With ChartSQL, any valid SQL script is a ChartSQL visualization

```sql
-- @chart: column
-- @title: Column - Sales by Month
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<div align="left"><figure><img src="/files/VBrVgOZt6xVwBbA9dfdK" alt=""><figcaption></figcaption></figure></div>

See all [Available Charts](/charts/example-charts)

## Key Features

* **What you Query is what You Chart:** We call it "Whikee-Whic" - Convert SQL queries to charts directly with no additional data shaping.
* **Auto-Detection**: ChartSQL determines the optimal chart type for your data.
* **Flexible @directives**: Complete control with @directives to fine-tune chart rendering for complex visual requirements.
* **Collaborative**: Maintain SQL source code and visualization instructions together in one file.
* **Embedding**: Add visualizations to applications with dynamic, auto-updating charts that are easily maintained.

## Get Started with ChartSQL Studio

ChartSQL Studio is a desktop source code editor to create and execute SQL scripts, charts and visualizations using the ChartSQL language.

<figure><img src="/files/TuU5ChpazrLJeXytenQQ" alt=""><figcaption><p>ChartSQL Studio Desktop Editor</p></figcaption></figure>

[See Installation Instructions](/chartsql-studio/installing-studio-desktop)

## Visualizations

ChartSQL supports many chart and visualizations:

* [Column](/charts/column)
* [Bar](/charts/bar)
* [Pie](/charts/pie)
* [Line](/charts/line)
* [Scatter](/charts/scatter)
* [Bubble](/charts/bubble)
* [Heatmap](/charts/heatmap)
* [Combo Chart](/charts/combo)
* [Stacked Charts](/charts/formatting-and-rendering/stacked-charts)
* [Area](/charts/area)
* [Gauge](/charts/gauge)
* [Radar](/charts/radar)

## Practical Uses

From rapidly prototyping dashboards to delivering in-depth reports, ChartSQL serves a broad range of scenarios. See use cases on how developers, data teams and agencies use ChartSQL to visualize data.

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th></tr></thead><tbody><tr><td><a href="/pages/KLs6UIkVv53WpBON9WSF">/pages/KLs6UIkVv53WpBON9WSF</a></td><td></td><td>For SQL <br>Developers</td><td></td></tr><tr><td><a href="/pages/QPECroPfahOsGaU4PqVx">/pages/QPECroPfahOsGaU4PqVx</a></td><td></td><td>For Application Developers</td><td></td></tr><tr><td><a href="/pages/DXp2mUdspmbT4bt79sx1">/pages/DXp2mUdspmbT4bt79sx1</a></td><td></td><td></td><td></td></tr><tr><td><a href="/pages/KfXXthO2kMGLlZcKK8eI">/pages/KfXXthO2kMGLlZcKK8eI</a></td><td></td><td></td><td></td></tr></tbody></table>

## Ways to use ChartSQL

* [**ChartSQL Studio**](/chartsql-studio/overview)**:** Desktop SQL and chart editor (Available Now) for designing and presenting visualizations.
* **ChartSQL Cloud:** Online editor and chart dashboards (In Development)
* **ChartSQL.js:** Client side rendering for embedding in web applications (In Development)

### ChartSQL Studio

ChartSQL Studio is a free editor for creating SQL charts. You can write and execute SQL queries, customize chart settings, and preview the results. With ChartSQL Studio, you have full control over your data and SQL source code.

<div><img src="/files/TuU5ChpazrLJeXytenQQ" alt="ChartSQL Studio screenshot"> <figure><img src="https://www.notion.so/itr8studios/Intro-448b016d878e425d8feca1ab0727dfa5?pvs=4#c7622a3834154ecdb8d25f198e95fa01" alt=""><figcaption></figcaption></figure></div>

### ChartSQL Cloud (coming soon)

Host your ChartSQL scripts With ChartSQL Clou&#x64;*.* Our hosted editor and dashboard tools allow for professional collaboration.

* **Automatic Dashboard Sync**: When your SQL source updates, so does any dashboard using your charts.
* **Multi-Tenant Functionality**: Easily clone and customize dashboards across different clients and datasets without changing any code or manually rebuilding.
* **Chart Library**: Empower teams to create personalized dashboards that stay up to date with your source code. You write the available visualizations in SQL, but they can choose charts from your package libraries to customize their dashboards.

### ChartSQL .js

ChartSQL JS is a front-end library that enables you to render SQL-based  charts (or table like data) seamlessly within any web application. Quickly add visualizations using ChartSQL embedded directives.

See [ChartSQL.js](/chartsql-js/coming-soon)


# Quick Start

Brief overview of ChartSQL features and uses

ChartSQL allows you to transform any SQL query into interactive charts. This guide will walk you through the key features of ChartSQL.

## ChartSQL Key Concepts

### Any SQL Query Can Be a Chart

A valid SQL query is a valid ChartSQL chart. ChartSQL will inspect the SQL result and visualize your query. You can also annotate your SQL to direct ChartSQL how to visualize your query. We call these annotations 'at directives'

```sql
-- @chart: column
-- @title: Column - Sales by Month
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/VBrVgOZt6xVwBbA9dfdK" alt=""><figcaption></figcaption></figure>

There are many directives to control plotting, style and data management like `@chart`, `@series`, `@formats`

See [Directives](/reference/directives) for all of the available directives.

### What You Query is What You Chart

We call it ‘Whikee-Whic’. ChartSQL charts the data, **as it exists**, in the order it is returned by the query. It does not shape your data in any way unless told to. It does not make assumptions about how you intended to group your series unless you tell it to. It does not require you to provide aggregation directions. ChartSQL assumes your data is already aggregated.

Advanced data shaping can be done in the full power of SQL. Using SQL to shape data simplifies the charting layer.

{% hint style="info" %}
ChartSQL is designed for experts to solve the annoyances of WYSIWYG plotters and dashboard tools. It is not intended for self-service business analytics where users build their own charts — though you can build client dashboards with ChartSQL. ChartSQL is for data craftsman who can utilize SQL.
{% endhint %}

## ChartSQL Studio

The easiest way to get started with ChartSQL is with [#chartsql-studio](#chartsql-studio "mention"), our source code editor for visualizing SQL.

Studio allows you to create ChartSQL scripts, execute SQL statements and generate visualizations.

<figure><img src="/files/TuU5ChpazrLJeXytenQQ" alt=""><figcaption><p>Screenshot of chart SQL studio</p></figcaption></figure>

## Understanding ChartSQL Detection Modes

ChartSQL will assist in automatically creating your charts, in three ways:

1. **Auto:**
   1. Simply run your SQL queries and ChartSQL will automatically select a chart type for you based on the column data types. This is the best approach when you're exploring data or need quick visual insights. There are specific rules for the auto charts as described in [#auto-mode](#auto-mode "mention")
2. **Assist:**
   1. If you want to create a specific type of chart, use the `@chart` directive to guide ChartSQL. For instance, `@chart: line` will tell ChartSQL to create a line chart. ChartSQL will then auto-detect and organize the remaining columns.
3. **Manual**:
   1. For complete control over the chart, use the `@chart` directive alongside other plotting directives like @groups and @series (see [Directives](/reference/directives)). Manually specifying directives is useful for creating more complex visualizations.

## Auto Mode

ChartSQL's auto mode eases the process of creating visualizations by inferring the best chart from your SQL query column data types.

ChartSQL will use the following data ruleset to determine the type of chart to create.

| Default Chart Type         | Non-numeric Columns | Date Columns | Datetime Columns | Numeric Columns | X-axis        | Y-axis        | Additional Axis/Property      | Rule |
| -------------------------- | ------------------- | ------------ | ---------------- | --------------- | ------------- | ------------- | ----------------------------- | ---- |
| Column                     | 1                   | 0            | 0                | 1               | Non-numeric   | Numeric       |                               | 1    |
| Grouped Column             | 1                   | 0            | 0                | 2 or more       | Non-numeric   | Numeric       |                               | 2    |
| Date-based Line            | 0                   | 1            | 0                | 1 or more       | Date          | Numeric       |                               | 3    |
| Datetime based line        | 0                   | 0            | 1                | 1 or more       | Datetime      | Numeric       |                               | 4    |
| Stacked-Grouped Column     | 2 or more           | 0            | 0                | 2               | Non-numeric 1 | Numeric       | Non-numeric 2 (subcategories) | 5    |
| Scatter                    | 0                   | 0            | 0                | 2               | Numeric 1     | Numeric 2     |                               | 6    |
| Bubble (scatter with size) | 0                   | 0            | 0                | 3               | Numeric 1     | Numeric 2     | Numeric 3 (bubble size)       | 7    |
| Heatmap                    | 2                   | 0            | 0                | 1               | Non-numeric 1 | Non-numeric 2 | Numeric (color intensity)     | 8    |

### Example 1: Auto-Detected Column Chart

When your data includes one non-numeric (category) and one numeric column (series), ChartSQL will automatically display this as a column chart.

**SQL Query:**

```sql
-- @title: Auto Column
-- @subtitle: An example of an auto selected column chart
SELECT 
Channel,
count(*) as Won_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel;
```

**Resulting Chart:** A column chart where each column represents the total deals won for the channel. The x-axis lists the channels, and the y-axis shows the count of won.

<figure><img src="/files/9f1jftT3xHbKlJYGAsew" alt=""><figcaption><p>An example of a auto selected column chart</p></figcaption></figure>

### Example 2: Auto-Detected Line Chart

For data with one date or datetime column and multiple numeric columns, ChartSQL generates a line chart.

**SQL Query:**

```sql
-- @title: Auto Line - Auto Generated Date Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

**Resulting Chart:** ChartSQL produces a date line chart. The Date column is used for the x-axis, while the numeric columns form the y-axis values.

<figure><img src="/files/CYOhrtwjeY64w59igrmb" alt=""><figcaption></figcaption></figure>

### Example 3: Auto-Detected Heatmap

If your dataset includes one numeric column and two non-numeric columns, ChartSQL will create a heatmap.

**SQL Query:**

```sql
-- @title: Auto Heatmap
-- @subtitle: An example auto generated heatmap comparing two categories
SELECT
Owner,
Channel,
count(*) as Sales
FROM Sales
WHERE Sales.Status = 'Won'
GROUP BY Owner, Channel;
```

**Resulting Chart:** A heatmap displaying the total count of sales across the channel and owner dimensions. One non-numeric column defines the x-axis, the other non-numeric column defines the y-axis, and the numeric column determines the color intensity.

<figure><img src="/files/ErCbLFbHCwnmBgGmK54O" alt=""><figcaption><p>An example auto generted heatmap chart</p></figcaption></figure>

### Example 4: Auto-Detected Scatter Chart

When the result set contains exactly two numeric columns, ChartSQL will auto-detect a scatter chart.

**SQL Query:**

```sql
-- @title: Auto Scatter
-- @subtitle: Auto generted scatter chart
SELECT 
  Profit,
  Amount
FROM Sales
WHERE Status = 'Won';
```

**Resulting Chart:** A scatter chart where the Profit is related to the sale Amount

<figure><img src="/files/5ODCcwMzGVTaCKbIln3P" alt=""><figcaption><p>An example of an auto scatter chart</p></figcaption></figure>

### Limits of Auto Detection.

These examples demonstrate how ChartSQL's auto detection feature takes the guesswork out of creating charts. Auto detection relies on specific number and types of columns being present. If that doesn’t work for your needs, you can start using directives to fully control the chart.

## Assist Mode

When you need more control than Auto detection allows, you can add annotations to direct ChartSQL how to render your chart. For a bar chart, add `-- @chart: bar` at the beginning of your SQL. These annoations are called 'at directives'.

```sql
-- @chart: bar
-- @title: Bar Chart
-- @subtitle: An basic example of a bar chart
SELECT 
Channel,
count(*) as Won_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel
ORDER BY count(*) ASC;
```

The directive '@chart: bar" instructs ChartSQL to render a bar chart. ChartSQL will attempt to detect which columns to use for the category and series from left to right. If you are not getting the results you need, then you can use the manual plotting directives described next.

<figure><img src="/files/1ZlZmT6bKXq2dJjQ70rJ" alt=""><figcaption></figcaption></figure>

## Manual Mode

Beyond the chart type, you sepecify the exact @category and @series to use for your chart. This is necessary when your result set has more columns than you want to visualize, you need to override the which columns are chosen by ChartSQL, or change their order.

* `@category` to specify the x-axis or pie segments
* `@series` to set the series for bar, column, scatter, or line charts. Provide a single column for simple charts or multiple columns for multi-series charts

Here's an example of a query that returns multiple potential categories and series, but we only want to visualize certain columns:

```sql
-- @chart: line
-- @title: Manual Category and Series
-- @subtitle: An example chart manually setting the category and series
-- @category: Date_Closed
-- @series: Amount
SELECT 
    Sub.*,
    (
      SELECT channel
      FROM Sales
      WHERE Sales.Date_Closed = Sub.Date_Closed
      GROUP BY Channel
      ORDER BY count(*) DESC
      LIMIT 1
    ) as TopChannel
FROM (
    SELECT 
      Date_Closed,
      sum(Amount) as Amount,
      sum(Profit) as Profit
    FROM Sales
    WHERE Status = 'Won'
    GROUP BY Date_Closed
    ORDER BY Date_Closed ASC
) as Sub;
```

<figure><img src="/files/aj0JbWvVDdCptdwlT5aF" alt=""><figcaption><p>An example where we are specifying the @category and @series</p></figcaption></figure>

### Using Secondary Series

For dual-axis charts, use `@secondary-series` to put series on the right side axis. You will often want this for combo charts where you need to visualize different series at different scales.

```sql
-- @chart: combo
-- @title: Combo - Column Chat with Secondary Line
-- @subtitle: A combo chart with a column series and a secondary line series
-- @category: Channel
-- @series: TotalDeals
-- @secondary-series: ConversionRate
-- @series-types: column, line
-- @formats: currency, percent
SELECT 
	Sub.*,
    ROUND(WonDeals / TotalDeals * 100, 1) as ConversionRate
FROM (
  SELECT 
  Channel,
  CAST(count(*) AS FLOAT) as TotalDeals,
  SUM(CASE WHEN Status = 'Won' THEN 1 ELSE 0 END) as WonDeals
  FROM Sales
  WHERE Status = 'Won' or Status = 'Lost'
  GROUP BY Channel
) as Sub


```

<figure><img src="/files/C47JK8zivLS87xNjXuAH" alt=""><figcaption><p>An example combo chart with a secondary series</p></figcaption></figure>

## Next Steps

For further details on each chart type and directive, visit the [Overview](/charts/example-charts) page and the [Directives](/reference/directives)for in-depth guidance.


# Overview

Browse full example charts to get inspirations for your visualizations

## Chat Types

ChartSQL supports the following chart types

* [Area](/charts/area)
* [Bar](/charts/bar)
* [Bubble](/charts/bubble)
* [Column](/charts/column)
* [Combo](/charts/combo)
* [Gauge](/charts/gauge)
* [Heatmap](/charts/heatmap)
* [Line](/charts/line)
* [Pie](/charts/pie)
* [Scatter](/charts/scatter)
* [Radar](/charts/radar)

## All Examples

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-single-baseline">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-single-baseline</a></td><td></td><td>Single Baseline</td><td></td><td><a href="/files/gwKyBu3l6WXw4tYFqzrc">/files/gwKyBu3l6WXw4tYFqzrc</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-multiple-baselines-for-one-series">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-multiple-baselines-for-one-series</a></td><td></td><td>Multiple Baselines</td><td></td><td><a href="/files/hA04UBtxTjnjlOkhClf9">/files/hA04UBtxTjnjlOkhClf9</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq">/pages/Fp7IYM3hpnOOOno6DMLq</a></td><td></td><td>Basic Column</td><td></td><td><a href="/files/WQgOpVgIlI8tPrFGt2dX">/files/WQgOpVgIlI8tPrFGt2dX</a></td></tr><tr><td><a href="/pages/XQAniOmQOHBf1SuuxMkK#column-chart-with-secondary-line">/pages/XQAniOmQOHBf1SuuxMkK#column-chart-with-secondary-line</a></td><td></td><td></td><td></td><td><a href="/files/C47JK8zivLS87xNjXuAH">/files/C47JK8zivLS87xNjXuAH</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#basic-line-chart">/pages/TrmnxOWEJwPgBgA1C5c4#basic-line-chart</a></td><td></td><td></td><td></td><td><a href="/files/EktTvLb53M96My1pNrht">/files/EktTvLb53M96My1pNrht</a></td></tr><tr><td><a href="/pages/t21pDYIGIJ3qrV5cFxqU#basic-pie-chart">/pages/t21pDYIGIJ3qrV5cFxqU#basic-pie-chart</a></td><td></td><td></td><td></td><td><a href="/files/xIVYZdbcMkmPoaBmXIG8">/files/xIVYZdbcMkmPoaBmXIG8</a></td></tr><tr><td><a href="/pages/CkH1XHlry5PfhfWmAH1o#auto-scatter">/pages/CkH1XHlry5PfhfWmAH1o#auto-scatter</a></td><td></td><td></td><td></td><td><a href="/files/5ODCcwMzGVTaCKbIln3P">/files/5ODCcwMzGVTaCKbIln3P</a></td></tr><tr><td><a href="/pages/J4oeGu0bjBxalEVBpCBX#auto-heatmap">/pages/J4oeGu0bjBxalEVBpCBX#auto-heatmap</a></td><td></td><td></td><td></td><td><a href="/files/ErCbLFbHCwnmBgGmK54O">/files/ErCbLFbHCwnmBgGmK54O</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#auto-grouped-column">/pages/Fp7IYM3hpnOOOno6DMLq#auto-grouped-column</a></td><td></td><td></td><td></td><td><a href="/files/7kXP3TVxWXL4Ip276OeW">/files/7kXP3TVxWXL4Ip276OeW</a></td></tr><tr><td><a href="/pages/6fmOVt7LuOMBCDqIiROp#auto-bubble">/pages/6fmOVt7LuOMBCDqIiROp#auto-bubble</a></td><td></td><td></td><td></td><td><a href="/files/OZzlgneB0nnwiss5P75w">/files/OZzlgneB0nnwiss5P75w</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels</a></td><td></td><td></td><td></td><td><a href="/files/pfwgbypTXCmmcmSA80Fq">/files/pfwgbypTXCmmcmSA80Fq</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing">/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing</a></td><td></td><td></td><td></td><td><a href="/files/ohM17AOfhRsjCOFl01Zn">/files/ohM17AOfhRsjCOFl01Zn</a></td></tr><tr><td><a href="/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking">/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking</a></td><td></td><td></td><td></td><td><a href="/files/01OUSfXiKmOIQphqIwpT">/files/01OUSfXiKmOIQphqIwpT</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#auto-line-chart">/pages/TrmnxOWEJwPgBgA1C5c4#auto-line-chart</a></td><td></td><td></td><td></td><td><a href="/files/CYOhrtwjeY64w59igrmb">/files/CYOhrtwjeY64w59igrmb</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column">/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column</a></td><td></td><td></td><td></td><td><a href="/files/QMG97LKgU1qjURax1soX">/files/QMG97LKgU1qjURax1soX</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels</a></td><td></td><td></td><td></td><td><a href="/files/pfwgbypTXCmmcmSA80Fq">/files/pfwgbypTXCmmcmSA80Fq</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-commented-out-directive">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-commented-out-directive</a></td><td></td><td></td><td></td><td><a href="/files/UUG2PXV3T5C4ntjZbHyB">/files/UUG2PXV3T5C4ntjZbHyB</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#basic-bar-chart">/pages/2ezY2RInAwmBulQOlYT4#basic-bar-chart</a></td><td></td><td></td><td></td><td><a href="/files/1ZlZmT6bKXq2dJjQ70rJ">/files/1ZlZmT6bKXq2dJjQ70rJ</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#manual-category-and-series-line">/pages/TrmnxOWEJwPgBgA1C5c4#manual-category-and-series-line</a></td><td></td><td></td><td></td><td><a href="/files/aj0JbWvVDdCptdwlT5aF">/files/aj0JbWvVDdCptdwlT5aF</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-parent-and-sub-category-groups">/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-parent-and-sub-category-groups</a></td><td></td><td></td><td></td><td><a href="/files/dshQViJ2oCRwVQZwMsq3">/files/dshQViJ2oCRwVQZwMsq3</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-formats">/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-formats</a></td><td></td><td></td><td></td><td><a href="/files/jAroCTyrwHnEYAM3HJku">/files/jAroCTyrwHnEYAM3HJku</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-with-stacking">/pages/2ezY2RInAwmBulQOlYT4#bar-with-stacking</a></td><td></td><td></td><td></td><td><a href="/files/hcRHE8kolLghTEzS2rIZ">/files/hcRHE8kolLghTEzS2rIZ</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-explicit-series">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-explicit-series</a></td><td></td><td></td><td></td><td><a href="/files/xe7PeAvOJn1C3bhkUfYK">/files/xe7PeAvOJn1C3bhkUfYK</a></td></tr><tr><td><a href="/pages/9RAfs2BEEuKqM0XrmIuX#basic-gauge-chart">/pages/9RAfs2BEEuKqM0XrmIuX#basic-gauge-chart</a></td><td></td><td></td><td></td><td><a href="/files/yHGxxyfjIYSk9wJ50Hxx">/files/yHGxxyfjIYSk9wJ50Hxx</a></td></tr><tr><td><a href="/pages/dZKAj6ymFsAPHbfzZQ2M#basic-radar-chart">/pages/dZKAj6ymFsAPHbfzZQ2M#basic-radar-chart</a></td><td></td><td></td><td></td><td><a href="/files/LDgzcTHVLiDRvp43GVTP">/files/LDgzcTHVLiDRvp43GVTP</a></td></tr></tbody></table>


# Auto Charts

Fully auto detected charts based on the available columns in the result set

## Auto Charts Overview

Auto charts are detected based on the data types and number of columns present in your dataset. They are useful for quick visualization of data and exploration

* Column
* Grouped Column
* Date Line
* Datetime Line
* Stacked Grouped Column
* Heatmap
* Scatter
* Bubble

See the [Auto Charts](/reference/auto-charts#full-auto-detection-reference)&#x20;

## Auto Charts Examples

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#auto-grouped-column">/pages/Fp7IYM3hpnOOOno6DMLq#auto-grouped-column</a></td><td></td><td></td><td></td><td><a href="/files/7kXP3TVxWXL4Ip276OeW">/files/7kXP3TVxWXL4Ip276OeW</a></td></tr><tr><td><a href="/pages/J4oeGu0bjBxalEVBpCBX#auto-heatmap">/pages/J4oeGu0bjBxalEVBpCBX#auto-heatmap</a></td><td></td><td></td><td></td><td><a href="/files/ErCbLFbHCwnmBgGmK54O">/files/ErCbLFbHCwnmBgGmK54O</a></td></tr><tr><td><a href="/pages/CkH1XHlry5PfhfWmAH1o#auto-scatter">/pages/CkH1XHlry5PfhfWmAH1o#auto-scatter</a></td><td></td><td></td><td></td><td><a href="/files/5ODCcwMzGVTaCKbIln3P">/files/5ODCcwMzGVTaCKbIln3P</a></td></tr><tr><td><a href="/pages/6fmOVt7LuOMBCDqIiROp#auto-bubble">/pages/6fmOVt7LuOMBCDqIiROp#auto-bubble</a></td><td></td><td></td><td></td><td><a href="/files/OZzlgneB0nnwiss5P75w">/files/OZzlgneB0nnwiss5P75w</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#auto-line-chart">/pages/TrmnxOWEJwPgBgA1C5c4#auto-line-chart</a></td><td></td><td></td><td></td><td><a href="/files/CYOhrtwjeY64w59igrmb">/files/CYOhrtwjeY64w59igrmb</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#auto-column">/pages/Fp7IYM3hpnOOOno6DMLq#auto-column</a></td><td></td><td></td><td></td><td><a href="/files/9f1jftT3xHbKlJYGAsew">/files/9f1jftT3xHbKlJYGAsew</a></td></tr></tbody></table>


# Area

Example area charts

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking">/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking</a></td><td></td><td></td><td></td><td><a href="/files/01OUSfXiKmOIQphqIwpT">/files/01OUSfXiKmOIQphqIwpT</a></td></tr></tbody></table>

## Area Chart with 100% Stacking

```sql
-- @chart: area
-- @title: Stacking-mode - 100% normalizing a stacked chart
-- @groups: Month, Channel
-- @subtitle: An example area chart that is 100% stacked
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
-- @stacking-mode: percent
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY Month, Channel
ORDER BY Month ASC;
```

<figure><img src="/files/01OUSfXiKmOIQphqIwpT" alt=""><figcaption></figcaption></figure>

| MONTH                            | CHANNEL  | SALES     |
| -------------------------------- | -------- | --------- |
| January, 01 2017 00:00:00 +0000  | coldcall | 174647.0  |
| January, 01 2017 00:00:00 +0000  | referral | 1310064.0 |
| January, 01 2017 00:00:00 +0000  | search   | 681444.0  |
| January, 01 2017 00:00:00 +0000  | event    | 502700.0  |
| February, 01 2017 00:00:00 +0000 | pr\_ad   | 509564.0  |
| February, 01 2017 00:00:00 +0000 | search   | 1109141.0 |
| February, 01 2017 00:00:00 +0000 | event    | 417651.0  |


# Bar

Example Bar Charts

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#basic-bar-chart">/pages/2ezY2RInAwmBulQOlYT4#basic-bar-chart</a></td><td></td><td></td><td></td><td><a href="/files/1ZlZmT6bKXq2dJjQ70rJ">/files/1ZlZmT6bKXq2dJjQ70rJ</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-parent-and-sub-category-groups">/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-parent-and-sub-category-groups</a></td><td></td><td></td><td></td><td><a href="/files/dshQViJ2oCRwVQZwMsq3">/files/dshQViJ2oCRwVQZwMsq3</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-formats">/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-formats</a></td><td></td><td></td><td></td><td><a href="/files/jAroCTyrwHnEYAM3HJku">/files/jAroCTyrwHnEYAM3HJku</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-with-stacking">/pages/2ezY2RInAwmBulQOlYT4#bar-with-stacking</a></td><td></td><td></td><td></td><td><a href="/files/hcRHE8kolLghTEzS2rIZ">/files/hcRHE8kolLghTEzS2rIZ</a></td></tr></tbody></table>

## Basic Bar Chart

```sql
-- @chart: bar
-- @title: Bar Chart
-- @subtitle: An basic example of a bar chart
SELECT 
Channel,
count(*) as Won_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel
ORDER BY Won_Sales DESC;
```

<figure><img src="/files/1ZlZmT6bKXq2dJjQ70rJ" alt=""><figcaption><p>An example of a bar chart</p></figcaption></figure>

| CHANNEL  | WON\_SALES |
| -------- | ---------- |
| pr\_ad   | 21.0       |
| coldcall | 37.0       |
| search   | 66.0       |
| event    | 112.0      |
| referral | 243.0      |

## Bar chart with Parent and Sub Category Groups

```sql
-- @chart: bar
-- @title: Groups - Multiple category groups
-- @subtitle: An example of multiple category groups
-- @groups: Channel, Owner
-- @series: TotalSales
-- @formats: currency
SELECT 
	Final.*
FROM (  
  SELECT 
  	Channel,    
  	Owner,
    (
      SELECT sum(Sub.Amount)
      FROM Sales Sub
      WHERE Sub.Channel = Sales.Channel
    ) as TotalChannelSales,
    sum(Amount) as TotalSales
  FROM Sales
  WHERE Owner IN (
    SELECT Owner
    FROM Sales
    GROUP BY Owner
    ORDER BY sum(Amount)
    LIMIT 10
  )
  GROUP BY Channel, Owner
) as Final
ORDER BY TotalChannelSales DESC, TotalSales DESC

```

<figure><img src="/files/dshQViJ2oCRwVQZwMsq3" alt=""><figcaption><p>An example bar chart with group parent and sub category</p></figcaption></figure>

| CHANNEL  | OWNER   | TOTALCHANNELSALES | TOTALSALES |
| -------- | ------- | ----------------- | ---------- |
| pr\_ad   | Kim     | 9181300.0         | 84603.0    |
| pr\_ad   | Roger   | 9181300.0         | 118985.0   |
| pr\_ad   | Lucy    | 9181300.0         | 138374.0   |
| pr\_ad   | Norbit  | 9181300.0         | 141711.0   |
| pr\_ad   | Justice | 9181300.0         | 160517.0   |
| pr\_ad   | Heather | 9181300.0         | 239870.0   |
| pr\_ad   | Peter   | 9181300.0         | 266655.0   |
| pr\_ad   | Tammy   | 9181300.0         | 501805.0   |
| coldcall | Justice | 1.7372264E7       | 87207.0    |
| coldcall | Heather | 1.7372264E7       | 162974.0   |
| coldcall | Norbit  | 1.7372264E7       | 333648.0   |

## Bar Chart with Formats

```sql
-- @chart: bar
-- @title: Bar Chart With Formats
-- @subtitle: An example bar chart with formats
-- @formats: currency
SELECT 
Channel,
sum(Amount) as Total_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel
ORDER BY Total_Sales DESC;
```

<div align="left"><figure><img src="/files/jAroCTyrwHnEYAM3HJku" alt=""><figcaption><p>An example bar chart with specified currency format</p></figcaption></figure></div>

| CHANNEL  | TOTAL\_SALES |
| -------- | ------------ |
| pr\_ad   | 1716590.0    |
| coldcall | 3267837.0    |
| search   | 6320993.0    |
| event    | 1.1198055E7  |
| referral | 2.6029056E7  |

## Bar with Stacking

```sql
-- @chart: bar
-- @title: Bar Stacking - Example bar chart with stacking
-- @groups: Owner, Channel
-- @subtitle: An example bar chart that has a single stack
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
SELECT *
FROM (  
  SELECT 
      Owner,
      Channel,
      (
        SELECT sum(Amount)
        FROM Sales Sub
        WHERE Sub.Owner = Sales.Owner
      ) as OwnerTotal,
      sum(amount) as Sales
  FROM sales
  GROUP BY Owner, Channel
) as Final
ORDER BY Final.OwnerTotal ASC, Sales DESC;
```

<figure><img src="/files/hcRHE8kolLghTEzS2rIZ" alt=""><figcaption><p>An example bar chart with stacking</p></figcaption></figure>

| OWNER   | CHANNEL  | OWNERTOTAL | SALES     |
| ------- | -------- | ---------- | --------- |
| Roger   | referral | 1459150.0  | 607758.0  |
| Roger   | coldcall | 1459150.0  | 365129.0  |
| Roger   | search   | 1459150.0  | 223080.0  |
| Roger   | event    | 1459150.0  | 144198.0  |
| Roger   | pr\_ad   | 1459150.0  | 118985.0  |
| Lucy    | referral | 1460022.0  | 914463.0  |
| Lucy    | event    | 1460022.0  | 267908.0  |
| Lucy    | search   | 1460022.0  | 139277.0  |
| Lucy    | pr\_ad   | 1460022.0  | 138374.0  |
| Kim     | referral | 1768826.0  | 1338296.0 |
| Kim     | search   | 1768826.0  | 177605.0  |
| Kim     | event    | 1768826.0  | 168322.0  |
| Kim     | pr\_ad   | 1768826.0  | 84603.0   |
| Grenda  | referral | 2411989.0  | 1567753.0 |
| Grenda  | event    | 2411989.0  | 511020.0  |
| Grenda  | search   | 2411989.0  | 333216.0  |
| Irene   | referral | 2798575.0  | 1698665.0 |
| Irene   | search   | 2798575.0  | 706034.0  |
| Irene   | event    | 2798575.0  | 393876.0  |
| Norbit  | referral | 2921665.0  | 1282432.0 |
| Norbit  | event    | 2921665.0  | 722410.0  |
| Norbit  | search   | 2921665.0  | 441464.0  |
| Norbit  | coldcall | 2921665.0  | 333648.0  |
| Norbit  | pr\_ad   | 2921665.0  | 141711.0  |
| Justice | referral | 3292632.0  | 1971882.0 |
| Justice | search   | 3292632.0  | 638796.0  |
| Justice | event    | 3292632.0  | 434230.0  |
| Justice | pr\_ad   | 3292632.0  | 160517.0  |

## Heading


# Bubble

Bubble charts show the relationship between three numerical variables

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/6fmOVt7LuOMBCDqIiROp#auto-bubble">/pages/6fmOVt7LuOMBCDqIiROp#auto-bubble</a></td><td></td><td></td><td></td><td><a href="/files/OZzlgneB0nnwiss5P75w">/files/OZzlgneB0nnwiss5P75w</a></td></tr></tbody></table>

## Auto Bubble

A bubble chart is a scatter chart but with different sized data points. When 3 numeric columns exist a bubble chart will be automatically created.

```sql
-- @title: Auto Bubble
-- @subtitle: Auto generted bubble chart
SELECT 
  Profit,
  Amount,
  (CAST(Profit AS FLOAT) / Amount) * 500 as Margin
FROM Sales
WHERE Status = 'Won'
LIMIT 10;
```

<div data-full-width="true"><figure><img src="/files/OZzlgneB0nnwiss5P75w" alt=""><figcaption></figcaption></figure></div>

| PROFIT  | AMOUNT   | MARGIN           |
| ------- | -------- | ---------------- |
| 27064.0 | 84572.0  | 160.005675637327 |
| 45307.0 | 133253.0 | 170.003677215522 |
| 14746.0 | 73728.0  | 100.002712673611 |
| 21198.0 | 75705.0  | 140.003962750149 |
| 37394.0 | 196808.0 | 95.001219462623  |
| 15454.0 | 67191.0  | 115.000520903097 |
| 13263.0 | 78013.0  | 85.005063258688  |
| 11532.0 | 76878.0  | 75.00195114337   |
| 13092.0 | 72732.0  | 90.001649892757  |


# Column

Column charts show numerical values across categories

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#basic-column-chart">/pages/Fp7IYM3hpnOOOno6DMLq#basic-column-chart</a></td><td></td><td></td><td></td><td><a href="/files/WQgOpVgIlI8tPrFGt2dX">/files/WQgOpVgIlI8tPrFGt2dX</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column">/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column</a></td><td></td><td></td><td></td><td><a href="/files/QMG97LKgU1qjURax1soX">/files/QMG97LKgU1qjURax1soX</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels</a></td><td></td><td></td><td></td><td><a href="/files/pfwgbypTXCmmcmSA80Fq">/files/pfwgbypTXCmmcmSA80Fq</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#auto-grouped-column">/pages/Fp7IYM3hpnOOOno6DMLq#auto-grouped-column</a></td><td></td><td></td><td></td><td><a href="/files/7kXP3TVxWXL4Ip276OeW">/files/7kXP3TVxWXL4Ip276OeW</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-single-baseline">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-single-baseline</a></td><td></td><td></td><td></td><td><a href="/files/gwKyBu3l6WXw4tYFqzrc">/files/gwKyBu3l6WXw4tYFqzrc</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-multiple-baselines-for-one-series">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-multiple-baselines-for-one-series</a></td><td></td><td></td><td></td><td><a href="/files/hA04UBtxTjnjlOkhClf9">/files/hA04UBtxTjnjlOkhClf9</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-commented-out-directive">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-commented-out-directive</a></td><td></td><td></td><td></td><td><a href="/files/UUG2PXV3T5C4ntjZbHyB">/files/UUG2PXV3T5C4ntjZbHyB</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#auto-column">/pages/Fp7IYM3hpnOOOno6DMLq#auto-column</a></td><td></td><td></td><td></td><td><a href="/files/9f1jftT3xHbKlJYGAsew">/files/9f1jftT3xHbKlJYGAsew</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-explicit-series">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-explicit-series</a></td><td></td><td></td><td></td><td><a href="/files/xe7PeAvOJn1C3bhkUfYK">/files/xe7PeAvOJn1C3bhkUfYK</a></td></tr></tbody></table>

## Basic Column Chart

```sql
-- @chart: column
-- @title: Column - Basic Column Chart
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/WQgOpVgIlI8tPrFGt2dX" alt=""><figcaption></figcaption></figure>

| MONTH                            | SALES     |
| -------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 |
| February, 01 2017 00:00:00 +0000 | 3951547.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 |
| May, 01 2017 00:00:00 +0000      | 2576305.0 |
| June, 01 2017 00:00:00 +0000     | 3100093.0 |

## Stacked Column

```sql
-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY Month, Channel
ORDER BY Month ASC;
```

<figure><img src="/files/QMG97LKgU1qjURax1soX" alt=""><figcaption><p>An example of a stacked column chart</p></figcaption></figure>

| MONTH                            | CHANNEL  | SALES     |
| -------------------------------- | -------- | --------- |
| January, 01 2017 00:00:00 +0000  | coldcall | 174647.0  |
| January, 01 2017 00:00:00 +0000  | referral | 1310064.0 |
| January, 01 2017 00:00:00 +0000  | search   | 681444.0  |
| January, 01 2017 00:00:00 +0000  | event    | 502700.0  |
| February, 01 2017 00:00:00 +0000 | pr\_ad   | 509564.0  |
| February, 01 2017 00:00:00 +0000 | search   | 1109141.0 |
| February, 01 2017 00:00:00 +0000 | event    | 417651.0  |

## Column with Series Labels

```sql
-- @chart: column
-- @title: Series Labels - Label at Top of Series
-- @subtitle: An example column chart with a series label above the top
-- @formats: currency
-- @series-labels: top
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
-- Select just 1 year so that the labels are not too cluttered
WHERE year(date_closed) = 2017
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/pfwgbypTXCmmcmSA80Fq" alt=""><figcaption><p>An example column chart with series labels</p></figcaption></figure>

| MONTH                             | SALES     |
| --------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000   | 2668855.0 |
| February, 01 2017 00:00:00 +0000  | 3951547.0 |
| March, 01 2017 00:00:00 +0000     | 4403443.0 |
| April, 01 2017 00:00:00 +0000     | 3079040.0 |
| May, 01 2017 00:00:00 +0000       | 2576305.0 |
| June, 01 2017 00:00:00 +0000      | 3100093.0 |
| July, 01 2017 00:00:00 +0000      | 3112476.0 |
| August, 01 2017 00:00:00 +0000    | 4980193.0 |
| September, 01 2017 00:00:00 +0000 | 4335105.0 |
| October, 01 2017 00:00:00 +0000   | 3552154.0 |
| November, 01 2017 00:00:00 +0000  | 3426988.0 |
| December, 01 2017 00:00:00 +0000  | 3761158.0 |

## Auto Grouped Column

When 1 string columns and 2 numeric columns exist

```sql
-- @title: Auto Grouped Column
-- @subtitle: Auto grouping columns when there is one category and two values
SELECT 
	Channel,
	SUM(
      CASE WHEN Sales.Status = 'Won' OR Sales.Status = 'Lost' THEN 1 ELSE 0 END
    ) as "Sales_Closed",
    SUM(
      CASE WHEN Sales.Status = 'Won' THEN 1 ELSE 0 END
    ) as "Sales_Won"
FROM sales
GROUP BY Channel;
```

<figure><img src="/files/7kXP3TVxWXL4Ip276OeW" alt=""><figcaption><p>An example of columns being automatically grouped</p></figcaption></figure>

| CHANNEL  | SALES\_CLOSED | SALES\_WON |
| -------- | ------------- | ---------- |
| coldcall | 206.0         | 37.0       |
| referral | 1069.0        | 243.0      |
| search   | 344.0         | 66.0       |
| event    | 427.0         | 112.0      |
| pr\_ad   | 110.0         | 21.0       |

## Column with Single Baseline

```sql
-- @chart: column
-- @title: Baselines - Single Baseline
-- @subtitle: An example column chart with a default average baseline
-- @baselines: Sales
-- @series: SALES
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/gwKyBu3l6WXw4tYFqzrc" alt=""><figcaption><p>An example column chart wtih a baseline </p></figcaption></figure>

| MONTH                            | SALES     |
| -------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 |
| February, 01 2017 00:00:00 +0000 | 3951547.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 |
| May, 01 2017 00:00:00 +0000      | 2576305.0 |

## Column with Multiple Baselines for one Series

```sql
-- @chart: column
-- @title: Baselines - Multiple Baseline for one series
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales, Sales, Sales
-- @baseline-types: average, min, max
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/hA04UBtxTjnjlOkhClf9" alt=""><figcaption><p>An exmaple column chart with multiple baselines</p></figcaption></figure>

| MONTH                            | SALES     |
| -------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 |
| February, 01 2017 00:00:00 +0000 | 3951547.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 |
| May, 01 2017 00:00:00 +0000      | 2576305.0 |
| June, 01 2017 00:00:00 +0000     | 3100093.0 |
| July, 01 2017 00:00:00 +0000     | 3112476.0 |

## Column with Commented out Directive

```sql
-- @chart: column
-- @title: Directive Comments
-- @subtitle: An example of commenting out a directive
-- @formats: currency
-- @series: Sales
-- //@baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/UUG2PXV3T5C4ntjZbHyB" alt=""><figcaption><p>An example chart with @baselines commented out</p></figcaption></figure>

| MONTH                            | SALES     |
| -------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 |
| February, 01 2017 00:00:00 +0000 | 3951547.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 |
| May, 01 2017 00:00:00 +0000      | 2576305.0 |
| June, 01 2017 00:00:00 +0000     | 3100093.0 |

## Auto Column

```sql
-- @title: Auto Column
-- @subtitle: An example of an auto selected column chart
SELECT 
Channel,
count(*) as Won_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel;
```

<figure><img src="/files/9f1jftT3xHbKlJYGAsew" alt=""><figcaption><p>An example of an auto selected column chat</p></figcaption></figure>

| CHANNEL  | WON\_SALES |
| -------- | ---------- |
| search   | 66.0       |
| coldcall | 37.0       |
| event    | 112.0      |
| referral | 243.0      |
| pr\_ad   | 21.0       |

## Column with Explicit Series

There are multiple columns defined, but we are only picking a specieis series using the `@series` directive:

```sql
-- @chart: column
-- @title: Series - Basic chart
-- @subtitle: An example of explicitly defining the series
-- @series: Sales
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales,
    sum(Profit) as Profit
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/xe7PeAvOJn1C3bhkUfYK" alt=""><figcaption><p>An example column chart wtih more than one column but only a specific series defined</p></figcaption></figure>

| MONTH                            | SALES     | PROFIT    |
| -------------------------------- | --------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 | 801583.0  |
| February, 01 2017 00:00:00 +0000 | 3951547.0 | 1117761.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 | 1224108.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 | 862533.0  |
| May, 01 2017 00:00:00 +0000      | 2576305.0 | 631699.0  |
| June, 01 2017 00:00:00 +0000     | 3100093.0 | 870893.0  |
| July, 01 2017 00:00:00 +0000     | 3112476.0 | 871875.0  |

### Heading2


# Combo

Combine multiple types of charts into a single chart

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/XQAniOmQOHBf1SuuxMkK#column-chart-with-secondary-line">/pages/XQAniOmQOHBf1SuuxMkK#column-chart-with-secondary-line</a></td><td></td><td></td><td></td><td><a href="/files/U7ceFRtrQZgPGBVVIBQT">/files/U7ceFRtrQZgPGBVVIBQT</a></td></tr></tbody></table>

## Column Chart with Secondary Line

```sql
-- @chart: combo
-- @title: Combo - Column Chat with Secondary Line
-- @subtitle: A combo chart with a column series and a secondary line series
-- @category: Channel
-- @series: TotalDeals
-- @secondary-series: ConversionRate
-- @series-types: column, line
-- @formats: currency, percent
SELECT 
	Sub.*,
    ROUND(WonDeals / TotalDeals * 100, 1) as ConversionRate
FROM (
  SELECT 
  Channel,
  CAST(count(*) AS FLOAT) as TotalDeals,
  SUM(CASE WHEN Status = 'Won' THEN 1 ELSE 0 END) as WonDeals
  FROM Sales
  WHERE Status = 'Won' or Status = 'Lost'
  GROUP BY Channel
) as Sub

```

<figure><img src="/files/C47JK8zivLS87xNjXuAH" alt=""><figcaption><p>An example combo chart that has a column and a line series</p></figcaption></figure>

| CHANNEL  | TOTALDEALS | WONDEALS | CONVERSIONRATE |
| -------- | ---------- | -------- | -------------- |
| coldcall | 206.0      | 37.0     | 18.0           |
| referral | 1069.0     | 243.0    | 22.7           |
| search   | 344.0      | 66.0     | 19.2           |
| event    | 427.0      | 112.0    | 26.2           |
| pr\_ad   | 110.0      | 21.0     | 19.1           |


# Gauge

Gauge Chart Examples

Gauge charts are useful to show simple values as a percentage of a target or maximum

<table data-card-size="large" data-view="cards" data-full-width="false"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/9RAfs2BEEuKqM0XrmIuX#basic-gauge-chart">/pages/9RAfs2BEEuKqM0XrmIuX#basic-gauge-chart</a></td><td></td><td></td><td></td><td><a href="/files/yHGxxyfjIYSk9wJ50Hxx">/files/yHGxxyfjIYSk9wJ50Hxx</a></td></tr><tr><td></td><td></td><td></td><td></td><td></td></tr></tbody></table>

## Basic Gauge Chart

```sql
-- @chart: gauge
-- @title: Basic Gauge
-- @subtitle: A Basic Gauge Chart
SELECT Conversion_Rate
FROM (
  VALUES (35)
) AS t1 (Conversion_Rate);
```

<figure><img src="/files/yHGxxyfjIYSk9wJ50Hxx" alt=""><figcaption><p>An example of a basic gauge chart</p></figcaption></figure>

| CONVERSION\_RATE |
| ---------------- |
| 35.0             |


# Heatmap

Heatmaps show the strength of a value compared to others across two dimensions

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/J4oeGu0bjBxalEVBpCBX#auto-heatmap">/pages/J4oeGu0bjBxalEVBpCBX#auto-heatmap</a></td><td></td><td></td><td></td><td><a href="/files/ErCbLFbHCwnmBgGmK54O">/files/ErCbLFbHCwnmBgGmK54O</a></td></tr></tbody></table>

## Auto Heatmap

When 2 string columns and 1 numeric column exists

```sql
-- @title: Auto Heatmap
-- @subtitle: An example auto generated heatmap comparing two categories
SELECT
Owner,
Channel,
count(*) as Sales
FROM Sales
WHERE Sales.Status = 'Won'
GROUP BY Owner, Channel
```

<figure><img src="/files/ErCbLFbHCwnmBgGmK54O" alt=""><figcaption><p>An example auto generated heatmap chart</p></figcaption></figure>

| OWNER   | CHANNEL  | SALES |
| ------- | -------- | ----- |
| Earl    | search   | 2.0   |
| Bernard | coldcall | 5.0   |
| Oliver  | event    | 3.0   |
| Oliver  | coldcall | 1.0   |
| Carrie  | search   | 9.0   |
| Norbit  | referral | 3.0   |
| Norbit  | event    | 5.0   |
| Norbit  | search   | 2.0   |
| Bernard | referral | 25.0  |
| Artie   | referral | 50.0  |
| Devon   | referral | 12.0  |
| Max     | event    | 4.0   |
| Norbit  | pr\_ad   | 1.0   |
| Carrie  | pr\_ad   | 3.0   |
| Norbit  | coldcall | 1.0   |
| Bernard | search   | 7.0   |

##


# Line

Line charts show how different numerical values compare, typically across time

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#basic-line-chart">/pages/TrmnxOWEJwPgBgA1C5c4#basic-line-chart</a></td><td></td><td></td><td></td><td><a href="/files/EktTvLb53M96My1pNrht">/files/EktTvLb53M96My1pNrht</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#auto-line-chart">/pages/TrmnxOWEJwPgBgA1C5c4#auto-line-chart</a></td><td></td><td></td><td></td><td><a href="/files/CYOhrtwjeY64w59igrmb">/files/CYOhrtwjeY64w59igrmb</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing">/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing</a></td><td></td><td></td><td></td><td><a href="/files/ohM17AOfhRsjCOFl01Zn">/files/ohM17AOfhRsjCOFl01Zn</a></td></tr><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#manual-category-and-series-line">/pages/TrmnxOWEJwPgBgA1C5c4#manual-category-and-series-line</a></td><td></td><td></td><td></td><td><a href="/files/aj0JbWvVDdCptdwlT5aF">/files/aj0JbWvVDdCptdwlT5aF</a></td></tr></tbody></table>

## Basic Line Chart

```sql
-- @chart: Line
-- @title: Line - Simple Line Chart
-- @subtitle: An example line chart with a detected category and series
-- @formats: currency
SELECT
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/EktTvLb53M96My1pNrht" alt=""><figcaption><p>An example date line chart </p></figcaption></figure>

| MONTH                            | SALES     |
| -------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 |
| February, 01 2017 00:00:00 +0000 | 3951547.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 |
| May, 01 2017 00:00:00 +0000      | 2576305.0 |
| June, 01 2017 00:00:00 +0000     | 3100093.0 |
| July, 01 2017 00:00:00 +0000     | 3112476.0 |

## Auto Line Chart

```sql
-- @title: Auto Line - Auto Generated Date Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/CYOhrtwjeY64w59igrmb" alt=""><figcaption><p>Example auto line chart</p></figcaption></figure>

| MONTH                            | SALES     |
| -------------------------------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 |
| February, 01 2017 00:00:00 +0000 | 3951547.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 |
| May, 01 2017 00:00:00 +0000      | 2576305.0 |

## Line with Series Title Spacing

```sql
-- @chart: Line
-- @title: Series Title Spacing
-- @subtitle: Spacing on series titles is determined by underscores
-- @formats: currency
SELECT
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as total_sales_amount
FROM sales
GROUP BY Month
ORDER BY Month ASC;
```

<figure><img src="/files/ohM17AOfhRsjCOFl01Zn" alt=""><figcaption><p>An example line chart with underscores converted to spaces in the series titles</p></figcaption></figure>

| MONTH                            | TOTAL\_SALES\_AMOUNT |
| -------------------------------- | -------------------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0            |
| February, 01 2017 00:00:00 +0000 | 3951547.0            |
| March, 01 2017 00:00:00 +0000    | 4403443.0            |
| April, 01 2017 00:00:00 +0000    | 3079040.0            |
| May, 01 2017 00:00:00 +0000      | 2576305.0            |
| June, 01 2017 00:00:00 +0000     | 3100093.0            |
| July, 01 2017 00:00:00 +0000     | 3112476.0            |

## Manual @Category and @series Line

```sql
-- @chart: line
-- @title: Manual Category and Series
-- @subtitle: An example chart manually setting the category and series
-- @category: Date_Closed
-- @series: Amount
SELECT 
    Sub.*,
    (
      SELECT channel
      FROM Sales
      WHERE Sales.Date_Closed = Sub.Date_Closed
      GROUP BY Channel
      ORDER BY count(*) DESC
      LIMIT 1
    ) as TopChannel
FROM (
    SELECT 
      Date_Closed,
      sum(Amount) as Amount,
      sum(Profit) as Profit
    FROM Sales
    WHERE Status = 'Won'
    GROUP BY Date_Closed
    ORDER BY Date_Closed ASC
) as Sub;
```

<figure><img src="/files/aj0JbWvVDdCptdwlT5aF" alt=""><figcaption><p>An example chart with a manually specified @category and @series</p></figcaption></figure>

| DATE\_CLOSED                    | AMOUNT   | PROFIT  | TOPCHANNEL |
| ------------------------------- | -------- | ------- | ---------- |
| January, 02 2017 00:00:00 +0000 | 78013.0  | 13263.0 | referral   |
| January, 06 2017 00:00:00 +0000 | 130899.0 | 40938.0 | event      |
| January, 12 2017 00:00:00 +0000 | 75705.0  | 21198.0 | coldcall   |
| January, 14 2017 00:00:00 +0000 | 72732.0  | 13092.0 | event      |
| January, 17 2017 00:00:00 +0000 | 84572.0  | 27064.0 | search     |
| January, 20 2017 00:00:00 +0000 | 76878.0  | 11532.0 | event      |
| January, 31 2017 00:00:00 +0000 | 127149.0 | 49589.0 | referral   |


# Pie

Pie charts show the relative contribution of values in relationship to the whole

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/t21pDYIGIJ3qrV5cFxqU#basic-pie-chart">/pages/t21pDYIGIJ3qrV5cFxqU#basic-pie-chart</a></td><td></td><td></td><td></td><td><a href="/files/xIVYZdbcMkmPoaBmXIG8">/files/xIVYZdbcMkmPoaBmXIG8</a></td></tr></tbody></table>

## Basic Pie Chart

```sql
-- @chart: pie
-- @title: Pie - Basic Pie Chart
-- @subtitle: Pie chart of sales won by channel
SELECT
	channel,
	count(*) as TotalSales
FROM sales
WHERE status = 'Won'
GROUP BY channel
ORDER BY TotalSales DESC;
```

<figure><img src="/files/xIVYZdbcMkmPoaBmXIG8" alt=""><figcaption><p>An example pie chart</p></figcaption></figure>

| CHANNEL  | TOTALSALES |
| -------- | ---------- |
| search   | 66.0       |
| coldcall | 37.0       |
| event    | 112.0      |
| referral | 243.0      |
| pr\_ad   | 21.0       |


# Radar

Radar Chart Examples

Radar charts are useful to compare the range of one or more categories

<table data-card-size="large" data-view="cards" data-full-width="false"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/dZKAj6ymFsAPHbfzZQ2M#basic-radar-chart">/pages/dZKAj6ymFsAPHbfzZQ2M#basic-radar-chart</a></td><td></td><td></td><td></td><td><a href="/files/LDgzcTHVLiDRvp43GVTP">/files/LDgzcTHVLiDRvp43GVTP</a></td></tr><tr><td></td><td></td><td></td><td></td><td></td></tr></tbody></table>

## Basic Radar Chart

```sql
-- @chart: radar
-- @title: Radar Chart
-- @category: Channel
-- @series: Won_Sales
-- @subtitle: An basic example of a radar chart
SELECT 
Channel,
count(*) as Won_Sales 
FROM Sales as ChannelSales
WHERE Status = 'Won'
GROUP BY Channel
ORDER BY Won_Sales DESC;
```

<figure><img src="/files/LDgzcTHVLiDRvp43GVTP" alt=""><figcaption></figcaption></figure>

| referral | 243.0 |
| -------- | ----- |
| event    | 112.0 |
| search   | 66.0  |
| coldcall | 37.0  |
| pr\_ad   | 21.0  |


# Scatter

Scatter charts show the relationship between two numerical variables

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/CkH1XHlry5PfhfWmAH1o#auto-scatter">/pages/CkH1XHlry5PfhfWmAH1o#auto-scatter</a></td><td></td><td></td><td></td><td><a href="/files/5ODCcwMzGVTaCKbIln3P">/files/5ODCcwMzGVTaCKbIln3P</a></td></tr></tbody></table>

## Auto Scatter

When 2 numeric columns exist

```sql
-- @title: Auto Scatter - Sales by Profit
-- @subtitle: Auto generted scatter chart
SELECT 
  Profit,
  Amount
FROM Sales
WHERE Status = 'Won';
```

<figure><img src="/files/5ODCcwMzGVTaCKbIln3P" alt=""><figcaption><p>An example scatter chart</p></figcaption></figure>

| PROFIT  | AMOUNT   |
| ------- | -------- |
| 27064.0 | 84572.0  |
| 45307.0 | 133253.0 |
| 14746.0 | 73728.0  |
| 21198.0 | 75705.0  |
| 37394.0 | 196808.0 |
| 15454.0 | 67191.0  |
| 13263.0 | 78013.0  |
| 11532.0 | 76878.0  |
| 13092.0 | 72732.0  |
| 25484.0 | 63708.0  |

##


# Formatting & Rendering

Examples of charts with changed formatting, rendering and additional options


# Baselines

Horizontal marks added to charts to mark major comparative values of a series like the average, median, min or max.

## Overview

ChartSQL introduces baseline marks/lines to add context and reference points 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.

See the [Baselines](/chartsql-studio/creating-charts/baselines) documentation

## Baseline Examples

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-single-baseline">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-single-baseline</a></td><td></td><td></td><td></td><td><a href="/files/gwKyBu3l6WXw4tYFqzrc">/files/gwKyBu3l6WXw4tYFqzrc</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-multiple-baselines-for-one-series">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-multiple-baselines-for-one-series</a></td><td></td><td></td><td></td><td><a href="/files/hA04UBtxTjnjlOkhClf9">/files/hA04UBtxTjnjlOkhClf9</a></td></tr></tbody></table>


# Formats

Example charts with customized formats

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-formats">/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-formats</a></td><td></td><td></td><td></td><td><a href="/files/jAroCTyrwHnEYAM3HJku">/files/jAroCTyrwHnEYAM3HJku</a></td></tr></tbody></table>


# Series Titles

Example charts controlling series titles

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing">/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing</a></td><td></td><td></td><td></td><td><a href="/files/ohM17AOfhRsjCOFl01Zn">/files/ohM17AOfhRsjCOFl01Zn</a></td></tr></tbody></table>


# Series Labels

Charts with series labels applied

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels">/pages/Fp7IYM3hpnOOOno6DMLq#column-with-series-labels</a></td><td></td><td></td><td></td><td><a href="/files/pfwgbypTXCmmcmSA80Fq">/files/pfwgbypTXCmmcmSA80Fq</a></td></tr></tbody></table>


# Stacked Charts

Examples of charts using stacking

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking">/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking</a></td><td></td><td></td><td></td><td><a href="/files/01OUSfXiKmOIQphqIwpT">/files/01OUSfXiKmOIQphqIwpT</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column">/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column</a></td><td></td><td></td><td></td><td><a href="/files/QMG97LKgU1qjURax1soX">/files/QMG97LKgU1qjURax1soX</a></td></tr><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-with-stacking">/pages/2ezY2RInAwmBulQOlYT4#bar-with-stacking</a></td><td></td><td></td><td></td><td><a href="/files/hcRHE8kolLghTEzS2rIZ">/files/hcRHE8kolLghTEzS2rIZ</a></td></tr></tbody></table>


# Grouped Category

Examples of charts with Grouped Categories

<table data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-parent-and-sub-category-groups">/pages/2ezY2RInAwmBulQOlYT4#bar-chart-with-parent-and-sub-category-groups</a></td><td></td><td></td><td></td><td><a href="/files/dshQViJ2oCRwVQZwMsq3">/files/dshQViJ2oCRwVQZwMsq3</a></td></tr></tbody></table>


# Overview

ChartSQL Studio is a source code editor to create and execute SQL scripts, charts and visualizations

<figure><img src="/files/TuU5ChpazrLJeXytenQQ" alt=""><figcaption></figcaption></figure>

## ChartSQL Studio Desktop

ChartSQL Studio is currently available as a downloadable desktop application for Windows, Mac and Linux.

## [Installing Studio Desktop](/chartsql-studio/installing-studio-desktop)

{% hint style="info" %}
ChartSQL Studio Cloud is in development.
{% endhint %}

## ChartSQL Studio Basics

***

Learn about the basic concepts of ChartSQL Studio and how to make your way around the editor

[Interface Overview](/chartsql-studio/basic-concepts/interface-overview)

[SQL Scripts & Charts](/chartsql-studio/basic-concepts/sql-scripts-and-charts)

[Folders](/chartsql-studio/basic-concepts/folders)

[Datasources](/chartsql-studio/basic-concepts/datasources)

[Thinking in ChartSQL](/chartsql-studio/basic-concepts/thinking-in-chartsql)

## Creating Charts

***

Learn about the major areas of ChartSQL Studio and how to create visualizations from your SQL queries

[Editor Panels](/chartsql-studio/creating-charts/editor-panels)

[Column Data Types](/chartsql-studio/creating-charts/column-data-types)

[Chart Types](/chartsql-studio/creating-charts/chart-types)

[Directives](/reference/directives)

[Stacking](/chartsql-studio/creating-charts/stacking)

[Baselines](/charts/formatting-and-rendering/baselines)

[Series Titles](/charts/formatting-and-rendering/series-titles)

## Presenting, Sharing & Publishing

***

Learn about presenting your visualizations to an audience and sharing your charts to others

[Presenting](/chartsql-studio/presenting#entering-presentation-mode)

[Presenting](/chartsql-studio/presenting#stories)

[Presenting](/chartsql-studio/presenting#drawing-annotations)

[Sharing & Publishing](/product-and-community/in-development/sharing-and-publishing)

## Settings & Customization

***

Learn about what settings and customization options exist

[Settings & Customization](/chartsql-studio/settings-and-customization#editor-settings)

[Settings & Customization](/chartsql-studio/settings-and-customization#datasources)

[Settings & Customization](/chartsql-studio/settings-and-customization#folders)

## Troubleshooting & Support

***

Get help, services and support

[Troubleshooting & Support](/chartsql-studio/troubleshooting-and-support#common-issues)

[Troubleshooting & Support](/chartsql-studio/troubleshooting-and-support#professional-support)

[Troubleshooting & Support](/chartsql-studio/troubleshooting-and-support#professional-services)

[Troubleshooting & Support](/chartsql-studio/troubleshooting-and-support#community)


# ChartSQL Studio Cloud

ChartSQL Studio Cloud is a browser accessible version of the ChartSQL Studio editor that you can access at ChartSQL.com

{% hint style="warning" %}
COMMINNG SOON\
ChartSQL Studio Cloud is not yet available. Join the development preview by [Installing Studio Desktop](/chartsql-studio/installing-studio-desktop)
{% endhint %}

## ChartSQL.com Account

Studio Web will require a free ChartSQL.com account. Coming soon.


# Installing Studio Desktop

ChartSQL Studio Desktop is a local desktop version of ChartSQL Studio and Studio Server.

{% hint style="warning" %}
ChartSQL Studio is currently in development Preview. It is suitable for use by developers who wish to aid in testing and are comfortable troubleshooting.
{% endhint %}

## Join the ChartSQL Developer Community

ChartSQL is developed and supported on Discord. Join the ChartSQL discord to get access to downloads, updates and community support.

{% embed url="<https://discord.gg/UbHYA6nyTg>" %}

## Install ChartSQL Preview

During the pre-release, the **ChartSQL Studio Preview** build is accessible via the [ChartSQL Preview Github Repo](https://github.com/roryl/chartsql-preview)

The preview repo tracks development and will be regularly updated with the latest build.

## Clone the Developer Preview

Clone the repo to a folder on your local machine.

## Start the ChartSQL Studio Server

The development preview starts a local copy of the ChartSQL Studio Server that the editor communicates with.&#x20;

Within the root of the repo, there are scripts to start and stop the ChartSQL Studio Server for Windows and Mac.

### Windows

* start.bat
* stop.bat

### Mac

* start.sh
* stop.sh

## Launching ChartSQL Studio Editor

When the server is finished launching, it will automatically open ChartSQL Studio in your default browser.

## Updating ChartSQL

To update ChartSQL, git pull the latest changes and reload the editor from your settings.

<figure><img src="/files/23HH5yohNgMzHp9cahuB" alt=""><figcaption></figcaption></figure>


# Basic Concepts

Basic concepts of ChartSQL that you should know to work effectively with your charts


# Interface Overview

Overview of the main ChartSQL Studio interface panels and features

ChartSQL Studio has conventions that should be familiar to software developers. We have borrowed a number of idioms from editors like VSCode, but added features that best suit the needs of SQL charting.

<div align="left"><figure><img src="/files/l3qV1Y2JXcigQ0xG639M" alt=""><figcaption><p>Screenhot of the ChartSQL Studio Editor</p></figcaption></figure></div>

## Menu

The leftmost menu contains links to the primary features. You can open and collapse it with the hamburger menu icon

<div align="left"><figure><img src="/files/hmWGczzKdBAiXrXcFDFk" alt=""><figcaption></figcaption></figure></div>

## Header

The header area displays information about your current open package and changing the current datasource.

<figure><img src="/files/DTOZw6wCLmwIQRapYXtB" alt=""><figcaption></figcaption></figure>

## File Browser

The File Browser displays your currently available charts from your currently open package and allows you to filter and open them. Clicks a file will open it in preview. ctrl (command) + click will fully open the file.

<div align="left"><figure><img src="/files/jYN6IpnxEeenAYh5S0kT" alt=""><figcaption></figcaption></figure></div>

## Open Files

The Open Files tabs shows you currently open files. Open files can be from any package. One open file is your Active File at any given time.

<div align="left"><figure><img src="/files/OGYd3I8CK9h1J1sell1W" alt=""><figcaption></figcaption></figure></div>

## Closing All Open Files

The dropdown at the end of the open files tabs can close all open files. You can also right click on an open file tabl to close that file, or close other files.

<div align="left"><figure><img src="/files/8wDgXjCEpRgY9ieg05gR" alt=""><figcaption></figcaption></figure></div>

## Renaming a File

You can rename a file by clicking the dropdown at the end of the open files tables. It will enabling renaming the current open file.

<div align="left"><figure><img src="/files/8wDgXjCEpRgY9ieg05gR" alt=""><figcaption></figcaption></figure></div>

## Editor Panel

The Editor Panel allows you to create and edit SQL files and create charts.

<div align="left"><figure><img src="/files/yXTin73VePsYvLb5ZLzC" alt=""><figcaption></figcaption></figure></div>

## Preview Panel

The Preview Panel shows you the current rendering of your chart from your SQL data.

<div align="left"><figure><img src="/files/DW4Vpa25zRhvMxFunAC7" alt=""><figcaption></figcaption></figure></div>

## Info Panel

The Info Panel shows you details about your current chart data, SQL executions, server performance, and other information.

<figure><img src="/files/3pDbPE11HX5wB0rEU0RR" alt=""><figcaption></figcaption></figure>


# Workspace

Understanding the ChartSQL Studio Workspace

## Workspace Overview

The ChartSQL Studio Editor is organized into a Workspace. Within your Workspace you add **Folders** from your file system, and **Datasources** to connect to.

## Folders

Add Folders to your workspace to be able to edit SQL scripts and create charts. Typically each folder will contains SQL scripts that are related to a particular client or that are related together. Typically SQL scripts are stored in source control.

See [Folders](/chartsql-studio/basic-concepts/folders) for more details.&#x20;

{% hint style="info" %}
A 'Scratchpad' folder that is stored in your Users directory is added automatically to your installation so you can get started right away writing scripts. Typically your SQL scripts will live in source control on your file system.
{% endhint %}

## Datasources

Add Datasources to your work space to connect to SQL databases that your charts will run against. Any SQL script can be run against any Datasource, but typically you will define a default Datasource that scripts in each Folder will execute against.

See [Datasources](/chartsql-studio/basic-concepts/datasources) for more details.&#x20;


# SQL Scripts & Charts

Explanation of SQL scripts and charts

ChartSQL visualizes plain SQL. Your SQL scripts live in folders on your file system (or in ChartSQL Studio Cloud) in what we call a **Folder**. Typically, you will have your SQL scripts in source control like git.

Each chart is a self contained SQL file and set of `@directives` (we say 'at directives' or 'directives'). Directives define how you want your chart to display. Any SQL file can be a visualization if it has appropriate columns that can be used for categories and series.

```sql
-- @chart: column
-- @category: product
-- @series: units_sold
SELECT 'Shoes' AS product, 120 AS units_sold
UNION ALL
SELECT 'T-Shirts', 200
UNION ALL
SELECT 'Jeans', 80
UNION ALL
SELECT 'Sunglasses', 150
UNION ALL
SELECT 'Hats', 90;
```

You can manage your charts for the currently open Folder from the file list

<div align="left"><figure><img src="/files/jYN6IpnxEeenAYh5S0kT" alt=""><figcaption></figcaption></figure></div>

## Filtering

Typing into the “Filtering Folder Charts…” input will begin searching from your package files. You can also begin filtering with ctrl + f. You can exit filtering by hitting ESC.

{% hint style="info" %}
If you are already filtering, hitting ctrl + f again will bring up the browser’s default find.
{% endhint %}

You can filter on the following content:

* The name of the physical sql file
* The [@title](/reference/directives/title) directive of the SQL script&#x20;
* Tags defined in the script using [@tags](/reference/directives/overlay-series-1) directive

## Previewing & Opening

Clicking on any file in the list will open it in your editor. Only one file is open in Preview at a time, if you click a different file it will take the first file’s place. When you start editing the file it will convert to being an open file.

<figure><img src="/files/8y2D0MmecnPwNtLkqYtB" alt=""><figcaption><p>An open SQL script and chart preview</p></figcaption></figure>


# Folders

A collection of SQL scripts in a folder added to your workspace

Your SQL Scripts are organized into a file system folder. You add folders to your workspace to be able to edit SQL scripts. All subfolders and files are added for every folder added to the workspace.

## Adding a Folder

Go to [Settings & Customization](/chartsql-studio/settings-and-customization) and click ‘Add Folder’ to add a folder from your file system.

<figure><img src="/files/pCD0i2d9WkM2zu9bZ6z1" alt=""><figcaption><p>Example of adding a folder to the workspace</p></figcaption></figure>

## Folder Config

When you add a new folder from your file system, a package.json file will be placed in the root of the folder and contains metadata about the scripts in the folder.

## Folder Name

You can define a friendly name for the folder that will show up in the editor in various places.

## Default Datasource

ChartSQL Studio allows you to run any script against any datasource. You can mark a Datasource as default for a folder so that it uses that datasource when opening scripts from that folder.&#x20;

See [Datasources](/chartsql-studio/basic-concepts/datasources)


# Datasources

SQL, NoSQL and APIs that you wish to query and visualize

Data sources tell Studio how to connect to the SQL databases that you wish to run your SQL scripts against. You can add an unlimited number of data sources. Any script can be run against any data source.

## Adding Data Sources

Add a Datasource from the [Settings & Customization](/chartsql-studio/settings-and-customization) page. You will be taken through a configuration wizard.

<figure><img src="/files/KvT7kgj28ajaO5ea3gWv" alt=""><figcaption><p>Starting the datasource configuration workflow</p></figcaption></figure>

## Switching Data sources

From any open package, you can switch your current data source from the header

<div align="left"><figure><img src="/files/EQtMdpYN49gqU5dobWXt" alt=""><figcaption><p>Selecting a datasource from your editor</p></figcaption></figure></div>


# Thinking in ChartSQL

Tips about creating visualizations with ChartSQL

### Introduction

In ChartSQL, data visualization meets the familiarity and power of SQL. ChartSQL is designed for developers who are comfortable working in tables, relationships, and queries. If you have ever wanted to chart your database results directly without switching gears into a different programming language or visualization tool, that is why we made ChartSQL. In this article, we dive into how to think about visualizations with ChartSQL.

### ChartSQL Thought Process

#### 1. Start with Your Data Query

In traditional visualization workflows, you might extract data using SQL, export it to CSV, process it in a programming language like JavaScript, and then use a charting library or copy it into Excel to visualize. In ChartSQL, you begin with what you know best: writing a SQL query. The emphasis here is on how you would normally query your database to retrieve the desired dataset.

**Example**: To analyze sales data, you might start with a query like:

```sql
SELECT year(OrderDate), month(OrderDate), SUM(TotalSales) AS Sales
FROM Orders
GROUP BY year(OrderDate), month(OrderDate);
```

#### 2. Picture the Story You Want to Tell

Imagine the visual outcome you are aiming for. What insights are you hoping to share? Are you looking at trends over time, relationships between variables, or the contribution of a variable to the whole? This foresight guides the use of ChartSQL [Directives](/reference/directives), helping you to frame your data story effectively.

**Example**: If you want to show sales trends over time, you might envision a line chart with `OrderDate` on as the category (the x-axis) and `Sales` as the series (the y-axis data).

#### 3. Assign Columns to Series

ChartSQL is most similar to Excel Charting. You select a chart type, and assign columns as series.

ChartSQL provides ‘at directives’ that enable you to assign columns in your result set your charts. Directives like `@category`, `@series` and `@formats`

* **Category**: What is the major axis of your visualization? The Category forms the x-axis and will typically be dates, or strings like product categories.
* **Series**: What are the quantitative insights? Series data, usually numeric, give substance to your story, acting as the y-axis values or pie chart segments.
* **Groups**: Do you need a hierarchical view? Like subplots in a larger narrative, grouping enabled sub-categories in your chort.

**Example**: To create a line chart of sales over time, you would use:

```sql
-- @chart: line
-- @category: OrderDate
-- @series: Sales
SELECT OrderDate, SUM(TotalSales) AS Sales
FROM Orders
GROUP BY OrderDate;

```

#### 4. ChartSQL Assist: Auto, Assistive, or Manual Mode

ChartSQL offers a spectrum of control, from fully automatic detection to precisely guided directives. This helps you start out exploring and then refine your visualization.

* **Auto Mode**: Let ChartSQL decide the chart type based on your source data. This is great for quick insights and iterations. A defined set of charts can be auto detected. See the [Auto Charts](/charts/auto-charts)examples.
* **Assist Mode**: Define some aspects, such as the chart type or a category, and let ChartSQL figure out the rest. This mode helps when you quickly customize visualizations.
* **Manual Mode**: Take full control by defining all aspects of the chart. Use this when your visualization requires exact precision.

**Example**: To automatically generate a chart without specifying the type, you could simply write:

```sql
-- @title: Auto Line - Auto Generated Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/CYOhrtwjeY64w59igrmb" alt=""><figcaption><p>Example of an auto generated line chart</p></figcaption></figure>

ChartSQL will auto-detect a date column and a numeric column as a line chart with `Month` as the category and `Sales` as the series.

#### 5. Utilize Formatting Directives

Ensure your chart is self explanatory. Formatting directives help you present data in the most understandable way, be it currency, percentage, or another style.

**Example**: To format the `Sales` series as currency, you would add `@formats`

```sql
-- @title: Auto Line - Auto Generated Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

#### 6. Refine and Iterate

Developing the right visualization may require custom directives. With ChartSQL, you can adjust directives and immediately see the impact. Experiment with different chart types, stacking options, and formats to discover the visual representation that best conveys your data story.

**Example**: To switch to stacking a column chart, you simply add the `@stacks` directive

```sql
-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH'), Channel
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

```

<figure><img src="/files/QMG97LKgU1qjURax1soX" alt=""><figcaption><p>An example column chart with stacking</p></figcaption></figure>

#### 7. Comment Out to Debug

ChartSQL allows you to temporarily disable directives without erasing them. This feature is handy for experimentation.

**Example**: To temporarily disable the bar chart directive:

```sql
-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- //@stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH'), Channel
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

```

<figure><img src="/files/zBjHJ5UChEST49lfkGKt" alt=""><figcaption><p>Commenting out the stacking disables it but keeps it in the source code</p></figcaption></figure>

### Tips for Thinking in ChartSQL

* **Write your SQL naturally**: Continue thinking in SQL as you naturally would. Construct your visualization with the same logic you'd use for a database query or to show a table. You shouldn’t need to contort your SQL to fit the visualization. ChartSQL is designed to work with the way you’d naturally query.
* **SQL data types are important:** ChartSQL detects your data types as strings, numbers or dates, and works appropriately with them. You can use CAST in your SQL to assign different data types if necessary to change the way ChartSQL is interpreting your columns.
* **Visualize As You Query**: As you define each column, think ahead to how it relates to your visual endgame. Is it a category, a series, or a sub-category?
* **What you Query is What you Chart**: We call it "Whikee-Whic" — Your knowledge of SQL functions, joins, and aliases becomes your data shaping toolkit. ChartSQL directives deal with layout, and some light formatting and grouping. If you want to significantly manipulate your data, you do it in SQL.
* **Toggle and Tweak**: Try different directive combinations and toggle them on and off to see their effect. It’s a hands-on approach to charting akin to playing with an interactive SQL result set.
* **Opt for Clarity**: Use formatting directives to ensure the visual narrative is as clear and meaningful as possible. Legibility is key.

### Final Thoughts

ChartSQL puts your SQL queries at the forefront. By extending your SQL work with [@directives](/reference/directives), ChartSQL streamlines the data storytelling workflow. Remember to start with a strong query, envision the story you want your visualization to tell, and then customize.


# Creating Charts

Key steps and concepts in creating charts and visualizations within ChartSQL


# Editor Panels

Overview of the ChartSQL Studio Editor Features

ChartSQL Studio contains three major areas you will use to create your charts, which we call the Editor, Preview, and Info panels

## Editor Panel

The Editor Panel contains your currently active SQL Script and allows you to edit the SQL and directives that control the chart.

<div align="left"><figure><img src="/files/pLCCXmPrPleiUUnHNvSM" alt="" width="438"><figcaption></figcaption></figure></div>

## Preview Panel

The Preview Panel shows you the rendering of your SQL chart.

<div align="left"><figure><img src="/files/dlDhV8N2XIAWxGe6UALW" alt="" width="563"><figcaption></figcaption></figure></div>

## Info Panel

The Info Panel shows you the data content of your SQL query, and other information like SQL execution history and performance.

<figure><img src="/files/kwpFkfXAmzuFNJSqGi6U" alt=""><figcaption></figcaption></figure>


# Column Data Types

Understanding ChartSQL column data types

## ChartSQL and SQL Data Types

ChartSQL detects the columns and data types of your SQL queries to determine how to visualize your query. ChartSQL has 4 data types:

* Date
* Datetime
* String
* Numeric

## SQL Data Type Mapping

Every supported SQL database column type is mapped to one of the ChartSQL datatypes automatically. For example, MySQL int, double, and bigint are mapped to `numeric` while MySQL date is mapped to `date`&#x20;

## Manual CAST

If your SQL query returns values that you want ChartSQL to detect as a different type, you need to CAST your value in SQL to the appropriate type.&#x20;

See CAST notes about your particular [datasource](/chartsql-studio/datasources/datasources)

Example casting a string in MySQL to a date:

```sql
SELECT CAST('2021-01-01' AS DATE);
```


# Chart Types

Explanation of Chart SQL chart types and visualization modes

## Chart Types

ChartSQL supports the following chart types. You can see [Overview](/charts/example-charts) for all of the types available

* [Column](/charts/column)
* [Pie](/charts/pie)
* [Bar](/charts/bar)
* [Area](/charts/area)
* [Line](/charts/line)
* [Scatter](/charts/scatter)
* [Bubble](/charts/bubble)
* [Combo](/charts/combo)
* [Heatmap](/charts/heatmap)

{% hint style="info" %}
Many more chart types, tables, cards and advanced visualizations are being planned.
{% endhint %}

## Detection Modes

ChartSQL helps you create visualizations quickly by detecting your column types, categories and series. It has three modes: Auto, Assist and Manual

### Auto Mode

In Auto Mode, an appropriate chart type will be chosen based on the data type and number of columns in your SQL query. This is useful for quickly visualizing your data.

See [Auto Charts](/charts/auto-charts) for examples of all the available auto mode charts

### Assist Mode

In Assist Mode, you define the @chart directive, and optionally the @category and @series directives. ChartSQL will fill in any unspecified @category or @series based on the available columns and their data types. Use Assist Mode when you know the chart type, but want ChartSQL to select the columns.

### Manual Mode

In Manual Mode, you define the @chart, the @category and @series, and ChartSQL will only display those columns in your visualizations. Any extra columns in your query are ignored. You use this when you want full control over the data being visualized.


# Directives

Directives allow precise control over all aspects of your visualization

Directives can be added to your SQL source code, or with the visual Directives Editor. See the reference of the various available [Directives](/reference/directives)

## Directives in SQL Code

```sql
-- @chart: column
-- @title: Column - Basic Column Chart
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

Directives are specified as a SQL comment (any line starting wtih --) followed by '@' symbol and the directive name, followed by a colon:

```sql
-- @chart: Line
```

## Directives Editor

From any open SQL script, you can visually edit the directives. This allows you play with different directives. When you change directives in the editor, they are automatically updated in the SQL source code.

<div align="left"><figure><img src="/files/GcxsagPDmXUO5WFlk7HP" alt=""><figcaption><p>A screenshot of the directives visual editor</p></figcaption></figure></div>

## Directive Comments

When developing, sometimes you may wish to quickly toggle on/off the directive without removing it from your source code. ChartSQL supports "directive comments" which will disable that directive.

You comment a directive by adding two forward slashed directly before the @ symbol

```sql
-- @chart: column
-- @title: Directive Comments
-- @subtitle: An example of commenting out a directive
-- @formats: currency
-- @series: Sales
-- //@baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/7ApLZsxVTaaxL5DxXXTj" alt=""><figcaption><p>An example chart that has @baselines defined but it is commented out</p></figcaption></figure>


# Stacking

Grouping series together to show relationships to part of a whole

Stacking refers to combing series values into a single value while retaining their name. It is useful when you want to compare values as part of a whole. Certain chart types support stacking, they are: line, area, column, bar and combo.

<figure><img src="/files/QMG97LKgU1qjURax1soX" alt=""><figcaption></figcaption></figure>

## Adding @stacks

Stacking is enabled for a chart by specifying the `@stacks` directive

```sql
-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH'), Channel
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

## Stacking Examples

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking">/pages/N6Jr8TfhmU6pCqG1xGgL#area-chart-with-100-stacking</a></td><td></td><td></td><td></td><td><a href="/files/01OUSfXiKmOIQphqIwpT">/files/01OUSfXiKmOIQphqIwpT</a></td></tr><tr><td><a href="/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column">/pages/Fp7IYM3hpnOOOno6DMLq#stacked-column</a></td><td></td><td></td><td></td><td><a href="/files/QMG97LKgU1qjURax1soX">/files/QMG97LKgU1qjURax1soX</a></td></tr></tbody></table>


# Baselines

Horizontal marks added to charts to mark major comparative values of a series like the average, median, min or max.

ChartSQL introduces baseline marks/lines to add context and reference points to chart visualizations. These lines represent significant values such as the average, min, max, median of a series. Baselines provide visual cues for comparison against a common or target value.

See [Baselines](/charts/formatting-and-rendering/baselines) examples and the [@baselines](/reference/directives/baselines) and [@baseline-types](/reference/directives/baseline-types) directives.

```sql
-- @chart: column
-- @title: Baselines - Average Baseline
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/gwKyBu3l6WXw4tYFqzrc" alt=""><figcaption><p>An example column chart with a baseline</p></figcaption></figure>

## Baseline Directives Overview

* `@baselines`: Specifies the series to which baselines are applied and indicates the presence of baseline lines in the chart.
* `@baseline-types` (optional): Sets the type of baselines on the specified series. If omitted, the default type, 'average', is applied. Supported types include: 'average', 'min', 'max', or 'median'

## Examples

A column chart showcasing average sales with a baseline for the average value:

```sql
-- @chart: column
-- @title: Baselines - Average Baseline
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/gwKyBu3l6WXw4tYFqzrc" alt=""><figcaption></figcaption></figure>

A chart with multiple baselines with specified types:

```sql
-- @chart: column
-- @title: Baselines - Multiple Baseline Types on one Series
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales, Sales, Sales
-- @baseline-types: average, min, max
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/hA04UBtxTjnjlOkhClf9" alt=""><figcaption></figcaption></figure>

## Default Behavior

Without specifying types or labels, baselines default to drawing an 'average' line across the specified series in the chart.


# Series Titles

Controlling how series are titled

## Series Titles Overview

Series Titles are the names of the series that go in legend area

<figure><img src="/files/C47JK8zivLS87xNjXuAH" alt=""><figcaption></figcaption></figure>

The title of the series are the same as the column that defines the series. If you wish to change the title of your series, you change the name of the column in SQL:

```sql
column as MyNewName
```

## Underscores to Spaces

If there are underscores in the column name, they will be converted to spaces

```sql
-- @chart: Line
-- @title: Series Title Spacing
-- @subtitle: Spacing on series titles is determined by underscores
-- @formats: currency
SELECT
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as total_sales_amount
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/ohM17AOfhRsjCOFl01Zn" alt=""><figcaption></figcaption></figure>

## Examples

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing">/pages/TrmnxOWEJwPgBgA1C5c4#line-with-series-title-spacing</a></td><td></td><td></td><td></td><td><a href="/files/ohM17AOfhRsjCOFl01Zn">/files/ohM17AOfhRsjCOFl01Zn</a></td></tr></tbody></table>


# Dynamic SQL Charts

Creating dynamic SQL charts from user input

## Overview

There are some situations where it is convenient to be able to dynamically build SQL based off of runtime information.

Dynamic SQL is achieved with an embedded scripting language called Handlebars that is interpreted before the SQL is executed by ChartSQL Studio.

```sql
-- @title: Select List - Dynamic SQL query from user selection
-- @subtitle: An example SQL query which uses a dynaic SQL query from user selection
-- @select-list-channel: all, referral, coldcall, search, event
-- @chart: line
-- @formats: currency
SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM sales
{{#unless (eq select-list-channel.selected "all")}}
  WHERE Channel = '{{select-list-channel.selected}}'
{{/unless}}
GROUP BY Month
ORDER BY Month ASC;
```

### Data Context

The data variables that are available for use within the Handlebars script are provided by ChartSQL's various features.

* User Select Lists

## Handlebars Overview

Handlebars is a lightweight templating language embedded within ChartSQL Studio that allows you to dynamically build SQL queries based on runtime data. This flexibility enables customizable and responsive SQL generation tailored to user input and other conditions.

### Variable Output

To display the value of a variable, simply wrap the variable name in double curly braces. This is useful for injecting dynamic data directly into your SQL query.

**Example:**

```sql
SELECT
product_name,
price
FROM
products
WHERE
category = '{{select-list-category.selected}}';
```

In this example, '{{select-list-category.selected}}\` outputs the value of the variable \`select-list-category.selected\`, allowing the user to filter products by category interactively.

### Control Flow

Handlebars provides control structures such as \`#if\`, \`#unless\`, \`#each\`, and \`#with\` to manage the logic within your templates. In SQL, these structures help conditionally include or exclude query parts.

**Example:**

```sql
SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM
sales
{{#unless (eq select-list-channel.selected "all")}}
WHERE
Channel = '{{select-list-channel.selected}}'
{{/unless}}
GROUP BY
Month
ORDER BY
Month ASC;
```

**Explanation:**

* &#x20;`{{#unless}}`: This is the inverse of `{{#if}}`. In the provided example, if the selected channel is not "all", the SQL \`WHERE\` clause filters results by the specified channel.

These examples offer a starting point for using Handlebars in your SQL queries. For more advanced usage, refer to the [Handlebars documentation](https://handlebarsjs.com/guide/) for additional functionalities and helpers.

## User Select Lists

Chart SQL queries can be dynamically altered based on a user accessible 'Select List', provided by the [@select-list directive](/reference/directives/select-list).

With @select-list, you can alter the SQL query to dynamically choose different columns, table, WHERE conditions in order to create highly customizable charts.

<figure><img src="/files/BGF0cXYi1yV0dj1jp4nK" alt=""><figcaption><p>An example of a chart with a Select List opion.</p></figcaption></figure>

See [@select-list directive](/reference/directives/select-list) for details


# Dynamic Data Functions

Complex excel like formulas for recursive and external data

{% hint style="info" %}
Dynamic Data Functions are currently in development. This documentation is for feedback and development purposes.
{% endhint %}

## Overview

There are certain types of calculations that are difficult to perform in SQL, particularly recursive calculations that depend upon the result of prior calculations, or that depend on external data. Dynamic Data Functions (DDF) allow you to perform these operations within ChartSQL for advanced use cases.&#x20;

DDFs are like advanced excel formulas. Your ChartSQL queries can return functions which are executed before the data is rendered.

DDFs are particularly useful for complex, iterative, and conditionally dependent calculations often found in financial modeling, simulations, and analytics

## Quick Start

You enable Dynamic Data Functions by adding the directive `@ddf: true` to your script. This tells ChartSQL Studio to check each cell in your result for DDFs to be evaluated.

A DDF is any cell that returns a string like `=function(){ //code }`

```sql
-- @ddf: true
SELECT
	'James' as first_name,
	'Smith' as last_name,
	'=function(row){ return row.first_name & " " & row.last_name }' as full_name

```

## DDFs vs Common Table Expressions (CTEs) and Recursive CTEs

SQL Common Table Expressions (CTE) and Recursive CTEs are the native way to perform complex hierarchical and self-referential calculations, however DDFs can do more than CTEs:&#x20;

* **Dynamic Execution**: DDFs can execute additional runtime logic, manipulate data dynamically, and even fetch or integrate live data during execution.
* **Inline Functionality**: With DDFs, additional logic (like calling external APIs, conditional recursion) can be executed inline without needing additional stored procedures or scripts.
* **Direct Data Manipulation**: DDFs allow on-the-fly data manipulation (like adding rows based on runtime conditions), which is not possible directly within SQL or its CTEs.

## Evaluation Order

DDF cells are always evaluated from top to bottom and left to right.

## Limitations

### Post Processing Only

Dynamic Data Functions are processed by ChartSQL after the data is returned, and so you cannot wrap the result in an outer query or process it further with SQL.

### ChartSQL Studio Desktop Only

Dynamic Data Functions have full access to the ChartSQL Studio Server process, and so for security purposes, are not available on ChartSQL Studio Cloud, and cannot be published to DashSQL.&#x20;

## Execution Context

For each DDF invocation, your DDF can access variables about the invocation. You can use these variables to access data and perform dynamic calculations.

In the quick start example, the DDF makes use of the `row` context variable, which returns a structure (hashmap) of the currently executing row. It uses this data to concatenate the first name and last name into the full name.

```
=function(row){ return row.first_name & " " & row.last_name }
```

### Context Variables Reference

| Name  | Description                                                                                                                                     |
| ----- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| rid   | The current integer index of the executing row. Starting at 1.                                                                                  |
| cid   | The current integer index of the executing column. Starting at 1.                                                                               |
| field | The name of the currently executing column field.                                                                                               |
| data  | A reference to the query data.                                                                                                                  |
| prev  | The result of the previous calculation.                                                                                                         |
| row   | The data for the currently executing row.                                                                                                       |
| func  | The string function of the currently executing DDF. Use this to reappend the function onto new rows if creating dynamically generating queries. |

## Examples

#### You will typically use DDFs for the following situations:

1. **Recursive calculations.** Complex recursive calculations are straightforward and computationally efficient as DDFs. DDFs naturally accommodate recursion without complex SQL constructs.
2. **Integration with real-time data** from external APIs. DDFs allow ChartSQL to embed external functionality directly into queries, enhancing flexibility and reducing the latency you might face in a traditional ETL process that periodically updates a data warehouse.

### Fibonacci Series

Consider this complex SQL query to generate a Fibonacci Series (for each row, add the previous two rows). This is a complicated query, hard to read and debug. These kinds of recursive queries are difficult to do in SQL when the business logic gets more complex.

```sql
-- MySQL 8.0 Fibonacci function
WITH RECURSIVE Fibonacci (n, fib_n, next_fib_n) AS (
  -- Initial subquery: defines the starting point of the recursion.
  SELECT 
    1 AS n, 
    0 AS fib_n,         -- F(0)
    1 AS next_fib_n     -- F(1)

  UNION ALL

  -- Recursive subquery: defines the recursive step.
  SELECT 
    n + 1, 
    next_fib_n AS fib_n, 
    fib_n + next_fib_n AS next_fib_n
  FROM Fibonacci
  WHERE n < 10         -- Specifies how many Fibonacci numbers to generate.
)

-- Final SELECT to output the result
SELECT n, fib_n FROM Fibonacci;
```

However with DDFs, this is more understandably accomplished:

```sql
-- @ddf: true
SELECT 0 as num
UNION ALL SELECT 1 as num
UNION ALL SELECT '=function(
	rid,
	data,
	func
){
	// Calculate the next value in the series
	var result = data["num"][rid - 1] + data["num"][rid - 2];

	// Add a new row to the end of the query using the same function
	// which achieves the recursion
	if(data.recordCount < 10){
		data.addRow({num: func});
	}

	return result;

}' as num
```

### Compound Interest Rate

```sql
-- @ddf: true
SELECT
    1 as year,
    10000 as starting_amount,  -- Starting amount
    0.05 as interest_rate,
    '=function(rid, data, func, row){

		var result = row.starting_amount + row.starting_amount * row.interest_rate;

		if (row.year < 10) {  // Recurse for 10 years

			data.addRow({
				year: row.year + 1,
				starting_amount: result,
				interest_rate: row.interest_rate,
				final_amount: func
			});
        	}

		return result;

    }' as final_amount
```

The equivalent Recursive CTE (MySQL 8.0)&#x20;

```sql
WITH RECURSIVE CompoundInterest AS
(
    -- Initial subquery: defines the starting point of the recursion.
    SELECT 
        1 AS year,
        10000 AS starting_amount,
        0.05 AS interest_rate,
        10500 AS final_amount  -- Initial final_amount equals the starting amount

    UNION ALL

    -- Recursive subquery: defines the recursive step.
    SELECT 
        year + 1 AS year,
        final_amount AS starting_amount,  -- Previous year's final_amount becomes this year's starting_amount
        0.05 AS interest_rate,  -- Assuming the interest rate remains constant
        final_amount + final_amount * 0.05 AS final_amount  -- Calculate the new final_amount
    FROM CompoundInterest
    WHERE year < 10  -- Continue recursion until year reaches 10
)

-- Final SELECT to output the result
SELECT * FROM CompoundInterest;
```


# Presenting

Presentation mode hides most of the features of the editor and makes the charts full screen so that you can present your visualizations to an audience.

## Entering Presentation Mode

Enter presentation mode clicking the the main menu ‘Present’. You can quickly switch back and forth between the Editor and Present mode.

<div align="left"><figure><img src="/files/XZ2f4xfCMfm6YotpRd2J" alt=""><figcaption><p>Main menu Present item</p></figcaption></figure></div>

<figure><img src="/files/YGCBkCtqtqdkGAdN44xb" alt=""><figcaption><p>An example of a chart in presentation mode</p></figcaption></figure>

## Stories

You can add charts as slides to a story, which allows you to create an ordered presentation of just a particular set of charts. Stories are saved within folders.

### Create a Story or add a Chart to a Story

From the preview panel, you can create a story starting with the currently open chart, or add that chart to an existing Story.

<figure><img src="/files/eugg9XvWhKrLVcpkA3lD" alt=""><figcaption><p>Screenshot of a dropdown to add a chart to your story</p></figcaption></figure>

### Viewing Stories

You can view your existing stories in the package by clicking on the stories icon at the bottom of the file list

<div align="left"><figure><img src="/files/DBL7mYSPAoSHE9W23xtX" alt=""><figcaption><p>Screenshot of the stories list in the file editor</p></figcaption></figure></div>

### Playing Stories

From the story slides list, you can edit your story slides, and play the story like a slide deck presentation

<div align="left"><figure><img src="/files/xBVjc7bHMpEWta2VAcBE" alt=""><figcaption><p>Screenshot of playing a story</p></figcaption></figure></div>

<figure><img src="/files/OWWvSmyNVjCNI6qoYTjt" alt=""><figcaption><p>Full screen story presentation mode</p></figcaption></figure>

## Drawing Annotations

At any time, you can draw on top of your charts in Presentation Mode by holding down the ctrl key (command on Mac) and click dragging your mouse around the chart. Press escape to clear your drawings.

<figure><img src="/files/5Ha4Isp0BjZIRWngORG3" alt=""><figcaption><p>Screenshot of drawing on top of a chart</p></figcaption></figure>

## Slicers

While in Presentation Mode, common chart slicers are readily available to help you navigate the data.

### Date Slicer

For date charts, common date ranges are available at the top of the presentation. They will constrain the dates in the visualization.

<figure><img src="/files/aya5LHGqX9EWwXHjnIqg" alt=""><figcaption></figcaption></figure>


# Settings & Customization

Details about all of the settings available to you to customize

## Editor Settings

Overall settings for the ChartSQL Studio Editor

### Reload ChartSQL Studio

Reloads the ChartSQL Studio instance and all of your folders and datasources. You might need to do this when installing extensions or other advanced cases.

<div align="left"><figure><img src="/files/vcmkzKMG1cfbXS0tszxj" alt=""><figcaption></figcaption></figure></div>

## Datasources

Add and edit your existing datasources. Datasources are SQL. NoSQL and API services that you will query to visualize.

### Add Datasource

To add a datasource, click on the "Add Datasource" button on the datasources page

<div align="left"><figure><img src="/files/qBCPyu3bhCZqFnt8ALHk" alt=""><figcaption></figcaption></figure></div>

You will be presented with a selection of available datasources that you can add

<div align="left"><figure><img src="/files/Zw6A0nyCXRnrNbcd394T" alt=""><figcaption></figcaption></figure></div>

### Edit Datasource

You can edit an existing datasource, test the connection, and remove them

<div align="left"><figure><img src="/files/A5FoaLQQSAUDmPPopb8V" alt=""><figcaption></figcaption></figure></div>

## Folders

Add and edit your existing folders. Folders are collections of SQL files that are your charts and visualizations.

### Add New Folder

Add new folders from your file system to Studio\\

<figure><img src="/files/pCD0i2d9WkM2zu9bZ6z1" alt=""><figcaption></figcaption></figure>

### Edit Folder

Edit your existing folders

<div align="left"><figure><img src="/files/157Sg3hctA19rGcChcTJ" alt=""><figcaption></figcaption></figure></div>

### Assign Default Datasource

When you are working with multiple folders and multiple datasources, it can be useful to assign a default datasource to your folders. when you open the folder, the datasource will be automatically selected.

<figure><img src="/files/a4FP3O6ujOdrI7KuAezn" alt=""><figcaption></figcaption></figure>

## Assign Default Folder

Any of your folders can be assigned as the default folder when you open your ChartSQL Studio editor.

<div align="left"><figure><img src="/files/NNCLyQQ9OAPyyTkfIH1d" alt=""><figcaption></figcaption></figure></div>

## Branding

You can change the logo and mascot used by ChartSQL so that you can have your own company brand in the application.

<div align="left"><figure><img src="/files/HvnJYVMYPUa9SLSQyYOJ" alt=""><figcaption></figcaption></figure></div>

### Logo

The logo is a 170 x 25 px logo

<div align="left"><figure><img src="/files/AL1kAEpPwjownrlXBrrf" alt=""><figcaption></figcaption></figure></div>

### Mascot

The mascot is a 25 x 25 icon

<div align="left"><figure><img src="/files/RGz5Muqge1Fw4M8Mvxn8" alt=""><figcaption></figcaption></figure></div>


# Troubleshooting & Support

Common issues, support & community

## Common Issues

<details>

<summary>Not seeing my changed files / editor is out of sync</summary>

Try reloading your editor from the settings page [Settings & Customization](/chartsql-studio/settings-and-customization#reload-studio)

</details>

## ChartSQL Support

If you have a ChartSQL.com Cloud account, you can receive email support. Signup at ChartSQL.com

## Professional Services

The ChartSQL team provides paid database, reporting, and dashboard design services. Discuss your needs with us at ChartSQL.com/services.

## Community

Join the discord community to troubleshoot and get advice on your visualizations.&#x20;

{% embed url="<https://discord.com/invite/UbHYA6nyTg>" %}


# Datasources


# Overview

Data sources that you can query against to produce charts and visualizations

## Overview

ChartSQL queries databases and services with SQL that you can visualize.

## SQL RDBMS

Currently Support SQL databases:

* [MySQL](/chartsql-studio/datasources/mysql)
* [SQLite](/chartsql-studio/datasources/sqlite)
* [HyperSQL](/chartsql-studio/datasources/hypersql)
* [PostgreSQL](/chartsql-studio/datasources/postgresql)

## NoSQL

NoSQL databases are supported via a SQL translation layer. Collections in the database are queried and stored in a local SQLite database that ChartSQL can query.

Currently supported NoSQL databases

* [MongoDB](/chartsql-studio/datasources/mongodb)

## Local Files

Local files can that be queried with a SQLite translation layer

* [CSV File](/chartsql-studio/datasources/csv-file)


# CSV File

Local CSV file that you can query against

## Local CSV FIle Overview

The CSV File connector will load a CSV file into a local [SQLite](/chartsql-studio/datasources/sqlite) database that can be visualized.&#x20;

{% hint style="info" %}
See the [SQLite](/chartsql-studio/datasources/sqlite)connector for details about querying SQLite for visualizations.
{% endhint %}

## Column Headers

The first row of the CSV is considered as headers for the columns. Ensure that your CSV has column headers.

## Column Data Types

The CSV data will be automatically inspected and the appropriate data type selected. All of the data in a column must resolve to the same type, or it will be considered a string.

The data types are checked in order of the following. If a Datetime, Date or Numeric is not found, the type will be string

1. Datetime
2. Date
3. Numeric
   1. Will also check for 'numberish' values (see [#numberish-values](#numberish-values "mention") below)
4. String
   1. If maximum length < 255, then data type will be VARCHAR with the length being the maximum length found

## Numberish Values

Many common real world number notations will be converted to numbers during import.&#x20;

* Negative parenthesis like (150) will be converted to -150
* Scientific notation like 10^2 will be converted to 100
* Thousands notation like 7.5k will be converted to 7500

{% hint style="info" %}
All number conversions are based on US/English comma/decimal standard. For example, international 1.200,5 will get converted to 1.2005 instead of 1200.5. As such, do not currently detect commas as decimal separators.
{% endhint %}

A full example list of conversions is described below:

<table><thead><tr><th width="271">RawInput</th><th width="227">ExpectedOutput</th><th width="287">Description</th></tr></thead><tbody><tr><td>100</td><td>100</td><td>Plain number</td></tr><tr><td>1,000</td><td>1000</td><td>Comma as thousand separator</td></tr><tr><td>$100</td><td>100</td><td>Dollar sign</td></tr><tr><td>€200</td><td>200</td><td>Euro sign</td></tr><tr><td>£300</td><td>300</td><td>Pound sign</td></tr><tr><td>50%</td><td>0.5</td><td>Percentage</td></tr><tr><td>(400)</td><td>-400</td><td>Parentheses for negatives</td></tr><tr><td>5k</td><td>5000</td><td>Text for thousands</td></tr><tr><td>7.5M</td><td>7500000</td><td>Text for millions</td></tr><tr><td>1,234.56</td><td>1234.56</td><td>Period as decimal separator</td></tr><tr><td>-100</td><td>-100</td><td>Negative sign</td></tr><tr><td>₹500</td><td>500</td><td>Indian Rupee sign</td></tr><tr><td>¥600</td><td>600</td><td>Yen sign</td></tr><tr><td>500₩</td><td>500</td><td>Won sign</td></tr><tr><td>200m</td><td>200000000</td><td>Text for hundreds of millions</td></tr><tr><td>1B</td><td>1000000000</td><td>Text for billions</td></tr><tr><td>2.5%</td><td>0.025</td><td>Decimal percentage</td></tr><tr><td>3,456.789</td><td>3456.789</td><td>Number with comma and period</td></tr><tr><td>(1,234.56)</td><td>-1234.56</td><td>Negative with comma and period</td></tr><tr><td>10^2</td><td>100</td><td>Scientific notation</td></tr><tr><td>2e3</td><td>2000</td><td>Exponential notation</td></tr><tr><td>-.5</td><td>-0.5</td><td>Leading decimal negative</td></tr><tr><td>.75</td><td>0.75</td><td>Leading decimal</td></tr></tbody></table>

## Externally Connecting to the SQLite Database

You may wish to connect to the imported CSV with another database browser like DBeaver. You can find the sqlite file in:&#x20;

./{{home}}/ChartSQL/{{installPath}}/db/csvfile/{{datasourceName}}/data.sqlite

Where as:&#x20;

* {{home}} is your system user data directory
* {{installPath}} is the location of your ChartSQL Studio Installation
* {{datasourceName}} is the name of your configured CSV Datasource


# HyperSQL

HyperSQL Datasource Connector Details

## HyperSQL Overview

HyperSQL (HSQL, HSQLDB) is a Java Embedded RDBMS. ChartSQL uses a HSQL database to execute the example charts provided with the editor. You can also connect to other HSQLDB's as a local analytical database.


# MongoDB

MongoDB Datasource Connector Details

## Overview

MongoDB is a document database and does not have a direct SQL interface. ChartSQL uses a local SQLite database to convert documents into SQL tables that ChartSQL can use for charting.

{% hint style="info" %}
See [SQLite datasource documentation](/chartsql-studio/datasources/sqlite) for working with SQLite's flavor of SQL
{% endhint %}

## Installing MongoDB

Install a MongoDB datasource in ChartSQL studio following the typical [datasource instructions](/chartsql-studio/settings-and-customization#datasources)

## Understanding MongoDB Charts

ChartSQL visualizations for MongoDB have a two step process:&#x20;

1. Write a MongoDB collection or aggregation pipeline query
2. Shape the final data with SQL

The ChartSQL Editor has an additional tab, "MongoDB Query" for writing the Mongo collection query

<div align="left"><figure><img src="/files/UoctYSPUqzM2bzcDdfaM" alt=""><figcaption><p>Screenshot of the MongoDB collection query editor</p></figcaption></figure></div>

### Write a MongoDB Collection Query

To query a MongoDB colleciton, you will specify a `collection`, and a `find` structure, which maps to a [mongodb.collection.find](https://www.mongodb.com/docs/v3.0/reference/method/db.collection.find/) method call

When find() is executed against the collection, the projected result set will be stored in a local SQLite database for further querying. The name of the table is the same as the collection.

<details>

<summary>Example MongoDB Collection Data</summary>

![](/files/NiTKucNymwqCjUthxHeY)

</details>

{% hint style="warning" %}
You should not find\* an entire collection, because it will necessarily be copied to your local database. Match and limit the items of your collection to just those you need to visualize&#x20;
{% endhint %}

```json
{
	"collection":"sales",
	"find":{
		"match":{
			"Status":"Lost" 
		},
		"projection":{
			"Amount":1,
			"DateClosed":1,
			"Name":1
		},
		"sort":{
			
		},
		"limit":10,
		"skip":0
	}
}
```

### Shape the Final Data with SQL

The result of find() is transferred to a local SQLite database. You can then query the result with SQL.&#x20;

Select all records returned from the mongodb find:

```sql
-- @chart: column
-- @mongodb-query: {"collection":"sales","find":{"match":{"Status":"Lost" },"projection":{"Amount":1,"DateClosed":1,"Name":1},"sort":{},"limit":0,"skip":0}}
SELECT *
FROM sales
```

<figure><img src="/files/vz3m1Heu0ugVtjrawez4" alt=""><figcaption></figcaption></figure>

If necessary you can further shape the data with SQL:

```sql
-- @chart: column
-- @mongodb-query: {"collection":"sales","find":{"match":{"Status":"Lost" },"projection":{"Amount":1,"DateClosed":1,"Name":1},"sort":{},"limit":0,"skip":0}}
SELECT
TRUNC(DateClosed, 'MONTH') AS DateClosed,
sum(Amount) as Amount
FROM sales
GROUP BY TRUNC(DateClosed, 'MONTH')
ORDER BY TRUNC(DateClosed, 'MONTH') asc;
```

<figure><img src="/files/K62qNOrf71rrgr7759sP" alt=""><figcaption></figcaption></figure>

## ChartSQL SQL Dialect

ChartSQL uses SQLite as the local database to query collections. See the [SQLite datasource](/chartsql-studio/datasources/sqlite) for syntax to query with.&#x20;

## MongoDB Atlas SQL

MongoDB has a new [SQL interface](https://www.mongodb.com/docs/atlas/data-federation/query/query-with-sql/). However, as of 1/14/2024 we could not get it to work reliably, and it is not very documented. In the future, we may be able to provide direct support for MongoDB Atlas SQL.


# MySQL

MySQL Datasource Connector Details

## MySQL Overview

MySQL is a flexible database for transaction and analytical workloads. ChartSQL can connect to any MySQL database like MySQL Server, MariaDB, Planetscale, and any other MySQL compatible wire protocol database.

## Chart Data Type Casting

ChartSQL detects four types of columns: Date, Datetime, String, Numeric. It maps all MySQL types to one of these four chart data types.

If you need to manualy specify the type, you can follow these examples

### Date

1. Using `CAST`:

```sql
SELECT CAST('2021-01-01' AS DATE);
```

2. Using `STR_TO_DATE` for custom formats:

```sql
SELECT STR_TO_DATE('01-01-2021', '%d-%m-%Y');
```

### Datetime

Using `CAST`:

```sql
SELECT CAST('2021-01-01 13:25:20' AS DATETIME);
```

Using `STR_TO_DATE` with custom formats:

```sql
SELECT STR_TO_DATE('01-01-2021 01:25:20 PM', '%d-%m-%Y %r');
```

### String

Using `CAST`:

```sql
SELECT CAST(column_name AS CHAR) FROM table_name;
```

Using implicit conversion with concatenation:

```sql
SELECT column_name + '' FROM table_name; 
-- MySQL treats + as string concatenation in this context, implicitly converting column_name to a string
```

Or by concatenation with the `CONCAT` function:

```sql
SELECT CONCAT(column_name, '') FROM table_name; 
-- CONCAT function automatically converts and concatenates the values as strings
```

These methods work for converting numbers or dates to strings. When converting dates or times to strings, you can also format them using the `DATE_FORMAT` function:

```sql
SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:%i:%s') FROM table_name; -- Formats a date/datetime value as a string
```

Use the `DATE_FORMAT` function if you need specific formatting for your date or datetime values.

### Numeric

To cast strings or other types of data to numbers in MySQL, you can use `CAST` or `CONVERT`. Here are examples of how to cast to integer (`SIGNED`) or decimal (`DECIMAL`) numbers:

Casting to an integer (whole number):

```sql
SELECT CAST(column_name AS SIGNED) FROM table_name;
```

Casting to a decimal (floating-point number):

```sql
SELECT CAST(column_name AS DECIMAL(10,2)) FROM table_name; -- Where 10 is the precision and 2 is the scale
```

These functions are appropriate for converting from string representations of numbers or from real or floating-point data types to integers or decimals. If your string has non-numeric characters or improper formatting, the conversion will result in a zero (0) in `CAST` functions.

For an implicit conversion, you can use an arithmetic operation such as addition or subtraction with a number, which will coerce the string to a numeric type:

```sql
SELECT column_name + 0 FROM table_name; -- Converts to a number by adding zero
```

This method will work best when you are confident that the string can be converted directly to a number without the need for specifying the exact numeric type (integer or decimal).

## MySQL Data Tips

Visualization tips for working with MySQL.

### Summarizing by Month

When you want summarize data by month, it is helpful to normalize your dates to the month floor (1st of the month) so that you can group all of your records into that month:

```sql
SELECT DATE_FORMAT(SaleDate, '%Y-%m-01') as MonthStart, sum(sales) as MonthSales
FROM SalesData
GROUP BY DATE_FORMAT(SaleDate, '%Y-%m-01')
```


# PostgreSQL

PostgreSQL Datasource Details

## PostgreSQL Overview

PostgreSQL is a flexible database for transaction and analytical workloads. ChartSQL can connect to any PostgreSQL database and any other PostgreSQL compatible wire protocol database, like Supabase.


# SQLite

SQLite Connector Details

## Overview

SQLite is a popular embedded SQL database engine. It is great s a portable lightweight analytics engine.

## Date Behavior

SQLite does not have a native date/time types, yet ChartSQL expects dates to detect date/time series charts. The ChartSQL SQLite datasource connector will convert any columns that look like dates to a Java timestamp/datetime after executing the query.&#x20;

For very large data sets this may add significant processing. Consider using a database like MySQL or Postgres that has native date types if visualizing a significant number of dates becomes necessary.

## Common SQLite Query Tips

### Month Floor

Use `strftime('%Y-%m-01', DateClosed)` to format a date to the floor of the month

```sql

SELECT
	strftime('%Y-%m-01', DateClosed) as MonthStart,
	sum(amount) as Sales
FROM sales
GROUP BY MonthStart
ORDER BY MonthStart ASC;

```


# Custom Datasources

Developing Custom Datasources for ChartSQL Studio

## Overview

ChartSQL has a pluggable datasource architecture. If a supported SQL database is not available in the production build, it is possible to create one.

## Datasources Location

Datasources are stored in ./chartsql/core/model/datasources/your\_datasource\_name/your\_datasource\_name.cfc

A datasource named "CustomDatasource.cfc" would be saved like:

./chartsql/core/model/datasources/customdatasource/CustomDatasource.cfc

## Datasource API

The following empty class is the minimum necessary functions to implement a datasource. In many cases, you can simply extend com.chartsql.core.model.JdbcDatasource. You only need to override functions if your needs differ.

```cfscript
component 
    isStudioDatasource="true"
    accessors="true"
    extends="com.chartsql.core.model.JdbcDatasource"    
{    
    /**
    * @sql A SQL statement to execute
    * @returns query 
    */
    public query function executeSql(string sql) {
        //code...
    }
    
    /**
    * Returns a lucee datasource struct
    */
    public struct function getConnectionInfo() {
        //code...
    }
    
    /**
    * Throws an error if the datasource connection cannot 
    * be verified otherwise we assume it is successfull
    */
    public void function verify(numeric timeout=5){
        //code...
    }
    
    /**
    * Returns an array of TableInfo for ChartSQL to show
    * available table in the schema browser
    */
    public TableInfo[] function getTableInfos(){
	//code...
    }

    /**
    * Returns an array of FieldInfo for ChartSQL to show
    * available fields/columns in the schema browser
    * @tableName A name of a table in the database
    */
    public FieldInfo[] function getFieldInfos(required string tableName){
        //code...		
    }    
    
    public DatasourceProcess[] function getProcesses(){
        //code...
    }
    
    
    public function killProcess(required DatasourceProcess DatasourceProcess){
        //code...
    }


}
```

## Setup Metadata

The settings datasource setup wizard uses meta data defined within your datasource .cfc to determine the title, icon and form fields that are displayed in the editor.

<figure><img src="/files/fSnuIiDNWZcW1Kb8dT4K" alt=""><figcaption><p>An example of a datasource setup wizard fields</p></figcaption></figure>

### Display Name, Description and Icon

The friendly display name, description and icon are setup in the component meta data attributes

```cfscript
component
	accessors="true"
	extends="com.chartsql.core.model.JdbcDatasource"
	isStudioDatasource="true"
	displayName="SQLite"
	description="SQLite Local Database"
	iconClass="ti ti-file-database"
{
	//... code... 
}

```

| Attribute   | Example                | Description                                                                                                                 |
| ----------- | ---------------------- | --------------------------------------------------------------------------------------------------------------------------- |
| displayName | My Datasource          | Friendly title for the datasource that shows up in the wizard                                                               |
| description | My Datasource Is Great | Short description for the datasource that shows up in the list of available datasources                                     |
| iconClass   | ti ti-file-database    | A CSS class for an icon to use as the icon for the datasource. Available icons can be found here: <https://tabler.io/icons> |

### Datasource Properties

Typically you need the user to specify properties for the datasource connector. You configure the properties that the wizard will display by adding properties to the datasource .cfc

```cfscript
component
	accessors="true"
	extends="com.chartsql.core.model.JdbcDatasource"
	isStudioDatasource="true"
	displayName="SQLite"
	description="SQLite Local Database"
	iconClass="ti ti-file-database"
{

	property name="FolderPath" required="true" description="The local folder to store the database file.";
	property name="Database" required="true" description="The name of the database to connect to";
	
	// code...
}
```

### Input Types

#### Text

The default HTML input type for a property is a simple text input

### Custom Methods

A datasource can have custom methods that can be executed from the Studio settings page as additional buttons. This allows you to specify custom actions that users can take in regards to the datasource. It is defined as a remote method.&#x20;

```cfscript
component
	accessors="true"
	extends="com.chartsql.core.model.JdbcDatasource"
	isStudioDatasource="true"
	displayName="SQLite"
	description="SQLite Local Database"
	iconClass="ti ti-file-database"
{

	property name="FolderPath" required="true" description="The local folder to store the database file.";
	property name="Database" required="true" description="The name of the database to connect to";
	
	remote function customMethod(){
		//... code
	}
}
```

## Datasource Caching

An instance of the datasource is created and cached within the life of the Studio application when the Studio configuration is loaded. If the datasource source code has changed, or its instance needs to be refreshed, you can [Settings & Customization](/chartsql-studio/settings-and-customization#reload-studio) or update the datasource from the settings.

## Process Cancellation

By default, when the user cancels a running script, the query was likely already sent to the database. In this case, although the ChartSQL execution is marked as cancelled, the database query might still be running on the database. You can add Process Cancellation functions to your datasource so that ChartSQL can also terminate the process running on the datasource.

### Cancellation Functions

Implementing process cancellation requires two things:&#x20;

1. Implement the function `getProcesses()` which returns an array `DatasourceProcess` instances which represent running processes on the datasource
2. Implement the function `killProcess()` which ChartSQL will call to kill the process on the underlying datasource

{% tabs %}
{% tab title="Function Signatures" %}

```cfscript
public DatasourceProcess[] function getProcesses(){
    //code...
}


public function killProcess(required DatasourceProcess DatasourceProcess){
    //code...
}
```

{% endtab %}

{% tab title="MySQL Example" %}

```cfscript
/**
 * A MySQL database connector
*/
import com.chartsql.core.model.DatasourceProcess;
component
	extends="com.chartsql.core.model.JdbcDatasource"
	accessors="true"
	isStudioDatasource="true"
	displayName="MySQL Database"
	description="MySQL Database connnector or MySQL wire protocol"
	iconClass="ti ti-brand-mysql"
{
	property name="Class" default="com.mysql.jdbc.Driver" description="The class name of the JDBC driver to use";
	property name="Port" default="3306" description="The port number of the MySQL server";
	property name="Host" required="true" description="The host name of the database server";
	property name="Database" required="true" description="The name of the database to connect to";
	property name="Username" required="true" description="The username to use when connecting to the database";
	property name="Password" required="true" description="The password to use when connecting to the database";

	public DatasourceProcess[] function getProcesses(){

		var result = this.executeSql("SELECT * FROM information_schema.processlist WHERE command != 'Sleep'");

		var out = [];
		for(var row in result){
			out.append(
				new DatasourceProcess(
					Id = row.ID,
					Sql = row.Info
				)
			)
		}

		return out;

	}

	public function killProcess(required DatasourceProcess DatasourceProcess){

		this.executeSql("KILL #DatasourceProcess.getId()#;");

	}
}

```

{% endtab %}
{% endtabs %}

### Process Identification

ChartSQL knows which datasource process relates to which execution based on a unique Id that was generated and inserted into the SQL script.


# Extensions


# Overview

Overview of ChartSQL Extensions

{% hint style="info" %}
Extensions are in development. They will allow you to customize the editor with additional functionality and third party tools.
{% endhint %}

ChartSQL Studio is an extensible editor. ChartSQL Studio has interfaces that allow you to add additional menu items, panels and views, or otherwise override any visual aspect of the editor. Many of the default features of Studio are implemented as extensions.

There are broadly three types of extensions, Core Extensions, Pro Extensions, and User Extensions.

## Core Extensions

Core Extensions are extensions that ship with ChartSQL Studio and are enabled by default for baseline functionality. They cannot be removed from the installation but they may be configured or disabled depending on the purposes of the extension.

## Pro Extensions

Pro Extensions are commercial extensions that can be enabled to enhance the ChartSQL Studio experience. Pro Extensions require a ChartSQL Cloud account and may require additional fees.

## User Extensions

User Extensions are extensions that can be created and installed by users into their particular ChartSQL Studio installation. They implement additional functionality or override existing features of the ChartSQL Studio User Interface.

User Extensions are a powerful feature and can manipulate anything related to the ChartSQL Studio Server process. Therefore they are only available on ChartSQL Studio Desktop and cannot be installed in ChartSQL Studio Cloud.

User Extensions are written in [Lucee Script](https://www.lucee.org/) and require advanced programming capability. See [Extension Points](/chartsql-studio/extensions/extension-points) and [Extensions API](/chartsql-studio/extensions/extensions-api-reference) for building your own extensions.


# Extension Points

Descriptions of the elements in the ChartSQL Studio UI that are user extensible

{% hint style="info" %}
Extensions are in development. They will allow you to customize the editor with additional functionality and third party tools.
{% endhint %}

## Overview

ChartSQL Studio provides extension points for developers to customize behavior and add functionality. Many of ChartSQL Studio's core features are implemented as [Core Extensions](/chartsql-studio/extensions/core-extensions)

There are fundamentally four ways to manipulate the behavior of ChartSQL Studio:

* Manipulate the `ChartSQLStudio` global singleton instance and its related data.
* Manipulate the HTTP request data, or response data.
* Manipulate the HTML output that was generated for each render.
* Implement functionality via the provided Extension Points described below.

## Built In Extension Points

Certain features of the ChartSQL Studio editor have classes you can implement and functions you can call that allow you to customize the editor. These Extension Points are summarized below. See The [Extensions API Reference](/chartsql-studio/extensions/extensions-api-reference) for complete reference of the Built In Extension Points.

### File Browser Views

You can add additional "File Browser Views" into the File Browser by instantiating new FileBrowserView instances from your extension. See [FileBrowserView API](/chartsql-studio/extensions/extensions-api-reference#class-filebrowserview).

<div align="left"><figure><img src="/files/gNsVhRKyFcooVZ5wSWZA" alt=""><figcaption></figcaption></figure></div>

### Info Panel Views

You can add additional "Info Panel Views" to the Info panel area by instantiating new InfoPanelView instances from your extension. See [InfoPanelView API](/chartsql-studio/extensions/extensions-api-reference#class-infopanelview)

<figure><img src="/files/nYmAH12yOSwFKxv8J0cs" alt=""><figcaption></figcaption></figure>


# Core Extensions

Descriptions of the extensions that ship with ChartSQL Studio

{% hint style="info" %}
Extensions are in development. They will allow you to customize the editor with additional functionality and third party tools.
{% endhint %}

Core Extensions are extensions that ship with ChartSQL Studio and are enabled by default. Core Extensions implement the basic functionality of the ChartSQL Studio editor. Core Extensions cannot be removed from the installation but they may be configured or disabled depending on the purposes of the extension.

## Examples

The 'Examples' Core Extension adds a package of example charts to the main menu (the same examples in this documentation) so that you can experiment with basic charts.

## Present

The 'Present' Core Extension adds full screen presentation capabilities to ChartSQL Studio so that you can can hide the editor features when presenting charts to an audience.

## Publish

The 'Publish' Core Extension adds chart publishing functionality to ChartSQL Studio so that you can share and make available their charts to others.

## SchemaBrowser

The 'SchemaBrowser' Core Extension Implements browsing a datasource schema within a package so that you can easily see the datasource tables and columns from within ChartSQL Studio.

## Story

The 'Story' Core Extensions implements slide deck functionality into ChartSQL Studio so that you can group particular charts together into a story that you can present to an audience.


# Extensions API Reference

API Reference for the ChartSQL Studio Extensions API

{% hint style="info" %}
Extensions are in development. They will allow you to customize the editor with additional functionality and third party tools.
{% endhint %}

## Interface `com.chartsql.studio.model.Extension`

Every Extension entry point is a single class file that implements `com.chartsql.studio.model.Extension` interface

```cfscript
/**
*/
component accessors="true" implements="com.chartsql.studio.model.Extension" {

	property name="ChartSQLStudio";

	public void function onRequest(
		required struct context
	){

	}

	public void function onResult(
		required struct requestContext,
		required struct result
	){

	}

	public void function onRender(
		required struct requestContext,
		required struct result,
		required object doc
	){

	}

}

```

## Extension Lifecycle Methods

Your Extension class has "lifecycle methods" that are called at different points in the request and rendering lifecycle. You use these lifecycle methods to intercept and implement UI features.

### onRequest

Called at the start of a user HTTP request, the lifecycle method `onRequest()` allows you to inspect and inject data into the request context object prior to any work performed by ChartSQL Studio.

#### onRequest Function Arguments

| Name    | Type   | Description                                                         |
| ------- | ------ | ------------------------------------------------------------------- |
| context | struct | The request context structure that contains all of the HTTP request |

### onResult

Called after ChartSQL Studio has finished processing the request but before it has rendered the HTML output. The `onResult()`lifecycle method allows you to change the ChartSQL Studio's data structure output that will be used by the rendering.

#### onResult Function Arguments

| Name           | Type   | Description                                                                            |
| -------------- | ------ | -------------------------------------------------------------------------------------- |
| requestContext | struct | The request context structure that contains all of the HTTP request                    |
| result         | struct | The result of any controller processing for the request before rendering is completed. |
|                |        |                                                                                        |

### onRender

Called after ChartSQL Studio has rendered the next response. The `onRender()` lifecycle methods allows you to manipulate the HTML output that will be sent to the user.

#### onRender Function Arguments

<table><thead><tr><th width="294">Name</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td>requestContext</td><td>struct</td><td>The request context structure that contains all of the HTTP request</td></tr><tr><td>result</td><td>struct</td><td>The result of any controller processing for the request before rendering is completed.</td></tr><tr><td>doc</td><td>Jsoup Object</td><td>Jsoup document object model (DOM) that can be used to manipulate the content that will be returned to the UI</td></tr></tbody></table>

## Remote HTTP Methods

You can add methods to your extension that can be called from the UI via HTML forms or HTTP POST. Remote HTTP methods allow you to wire up buttons and forms that communicate with the backend ChartSQLStudio process.

Defining a HTTP method requires annotating a function in your extension with the `remote` keyword.

```cfscript

remote struct function echo(
    required string foo,
    required string bazz
){
    return arguments;
}

```

You will then be able to POST to the function with HTML forms from the UI:

{% code fullWidth="false" %}

```html
<form action="/studio/extensions/chartsql.sample.Sample/runRemote?method=echo" method="POST">
    <input type="hidden" name="params.foo" value="bar">
    <input type="hidden" name="params.bazz" value="buzz">
</form>
```

{% endcode %}

## Class `InfoPanelView`

`com.chartsql.studio.model.InfoPanelView`

InfoPanelViews allow you to define additional panels that show up in the info area of the editor. Your Extension may instantiate instances of InfoPanelViews.

### Constructor Arguments

<table data-full-width="false"><thead><tr><th width="177">Name</th><th data-type="checkbox">Required?</th><th width="257">Default</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td>ChartSQLStudio</td><td>true</td><td>n/a</td><td>ChartSQLStudio</td><td>The instance of the ChartSQLStudio editor singleton.</td></tr><tr><td>Name</td><td>true</td><td>n/a</td><td>string</td><td>The name for your InfoPanelView which shows up in the tab name in the UI.</td></tr><tr><td>IconClass</td><td>true</td><td>n/a</td><td>string</td><td>The CSS class name for the icon to use for the tab. See <a href="https://tabler.io/icons">https://tabler.io/icons</a></td></tr><tr><td>content</td><td>false</td><td>""</td><td>string</td><td>The HTML content for the body of the info panel view. You can set this on creation or update later.</td></tr></tbody></table>

### Property Summary

<table><thead><tr><th>Name</th><th>Type</th><th data-type="checkbox">ReadOnly?</th><th>Description</th></tr></thead><tbody><tr><td>Name</td><td>string</td><td>false</td><td>The name for your InfoPanelView which shows up in the tab name in the UI.</td></tr><tr><td>Link</td><td>string</td><td>true</td><td>The URL link to the InfoPanelView that is rendered in the UI. You do not set this directly, it will be calculated.</td></tr><tr><td>IconClass</td><td>string</td><td>false</td><td>The CSS class name for the icon to use for the tab. See <a href="https://tabler.io/icons">https://tabler.io/icons</a></td></tr><tr><td>Content</td><td>string</td><td>false</td><td>The HTML content for the body of the info panel view. You can set this on creation or update later.</td></tr></tbody></table>

## Class `FileBrowserView`

`com.chartsql.studio.model.FileBrowserView`

FileBrowserViews allow you to define additional views hat show up in the file broser area of the editor. Your Extension may instantiate instances of FileBrowserView

### Constructor Arguments

<table data-full-width="false"><thead><tr><th width="177">Name</th><th data-type="checkbox">Required?</th><th width="112">Default</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td>ChartSQLStudio</td><td>true</td><td>n/a</td><td>ChartSQLStudio</td><td>The instance of the ChartSQLStudio editor singleton.</td></tr><tr><td>Name</td><td>true</td><td>n/a</td><td>string</td><td>The name for your FileBrowserView which shows up in the tab name in the UI.</td></tr><tr><td>IconClass</td><td>true</td><td>n/a</td><td>string</td><td>The CSS class name for the icon to use for the tab. See <a href="https://tabler.io/icons">https://tabler.io/icons</a></td></tr><tr><td>content</td><td>false</td><td>""</td><td>string</td><td>The HTML content for the body of the FileBrowserView. You can set this on creation or update later.</td></tr></tbody></table>

### Property Summary

<table><thead><tr><th>Name</th><th>Type</th><th data-type="checkbox">ReadOnly?</th><th>Description</th></tr></thead><tbody><tr><td>Name</td><td>string</td><td>false</td><td>The name for your FileBrowserView which shows up in the tab name in the UI.</td></tr><tr><td>Link</td><td>string</td><td>true</td><td>The URL link to the FileBrowserView that is rendered in the UI. You do not set this directly, it will be calculated.</td></tr><tr><td>IconClass</td><td>string</td><td>false</td><td>The CSS class name for the icon to use for the tab. See <a href="https://tabler.io/icons">https://tabler.io/icons</a></td></tr><tr><td>Content</td><td>string</td><td>false</td><td>The HTML content for the body of the FileBrowserView. You can set this on creation or update later.</td></tr></tbody></table>


# Coming Soon

Getting started with Dashboards for ChartSQL

{% hint style="info" %}
Dashboards are currently in development. Dashboard documentation is for feedback purposes. You can see the current progress of the feature in [Dashboards](/product-and-community/in-development/dashboards/dashboards)
{% endhint %}


# Coming Soon

Embedded SQL based charts in any web application

ChartSQL.js is in development. You can follow the progress of the feature in [ChartSQL.js](/product-and-community/in-development/chartsql.js/overview)


# Auto Charts

Fully auto detected charts based on the available columns in the result set

### Full Auto-Detection Reference

When you have not specified `@chart`, ChartSQL will automatically choose a chart type when the following columns of the specified data types exist in the result set.&#x20;

<table data-full-width="false"><thead><tr><th width="238">Default Chart Type</th><th>Non-numeric Columns</th><th>Date Columns</th><th>Datetime Columns</th><th>Numeric Columns</th><th>X-axis</th><th>Y-axis</th><th>Additional Axis/Property</th><th>Rule</th></tr></thead><tbody><tr><td>Column</td><td>1</td><td>0</td><td>0</td><td>1</td><td>Non-numeric</td><td>Numeric</td><td></td><td>1</td></tr><tr><td>Grouped Column</td><td>1</td><td>0</td><td>0</td><td>2 or more</td><td>Non-numeric</td><td>Numeric</td><td></td><td>2</td></tr><tr><td>Date-based Line</td><td>0</td><td>1</td><td>0</td><td>1 or more</td><td>Date</td><td>Numeric</td><td></td><td>3</td></tr><tr><td>Datetime based line</td><td>0</td><td>0</td><td>1</td><td>1 or more</td><td>Datetime</td><td>Numeric</td><td></td><td>4</td></tr><tr><td>Stacked-Grouped Column</td><td>2 or more</td><td>0</td><td>0</td><td>2</td><td>Non-numeric 1</td><td>Numeric</td><td>Non-numeric 2 (subcategories)</td><td>5</td></tr><tr><td>Scatter</td><td>0</td><td>0</td><td>0</td><td>2</td><td>Numeric 1</td><td>Numeric 2</td><td></td><td>6</td></tr><tr><td>Bubble (scatter with size)</td><td>0</td><td>0</td><td>0</td><td>3</td><td>Numeric 1</td><td>Numeric 2</td><td>Numeric 3 (bubble size)</td><td>7</td></tr><tr><td>Heatmap</td><td>2</td><td>0</td><td>0</td><td>1</td><td>Non-numeric 1</td><td>Non-numeric 2</td><td>Numeric (color intensity)</td><td>8</td></tr></tbody></table>


# 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](/charts/example-charts)

### 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.

<table><thead><tr><th width="171">Directive</th><th width="114">Default</th><th>Example</th><th>Description</th></tr></thead><tbody><tr><td>@baselines</td><td>none</td><td>-- @baselines: Sales</td><td>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 <a data-mention href="/pages/XLVj0yEk4zl6Ziw7DfcD">/pages/XLVj0yEk4zl6Ziw7DfcD</a> and <a data-mention href="/pages/cy9TbS3DN8yYTmsHEvkZ">/pages/cy9TbS3DN8yYTmsHEvkZ</a></td></tr><tr><td>@baseline-types</td><td>average</td><td>-- @baseline-types: min, max</td><td>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 <a data-mention href="/pages/XLVj0yEk4zl6Ziw7DfcD">/pages/XLVj0yEk4zl6Ziw7DfcD</a> and <a data-mention href="/pages/0NMcord6aIeF378fJ6VT">/pages/0NMcord6aIeF378fJ6VT</a></td></tr><tr><td>@chart</td><td>(none)</td><td>-- @chart: column</td><td>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 <a data-mention href="/pages/3elp6ltwD97QraAHXrWb">/pages/3elp6ltwD97QraAHXrWb</a></td></tr><tr><td>@category</td><td>(none)</td><td>-- @category: Month</td><td>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 <a data-mention href="/pages/FEwysIxYInvm9TeKdep0">/pages/FEwysIxYInvm9TeKdep0</a> </td></tr><tr><td>@groups</td><td>(none)</td><td>-- @groups: Month, Product</td><td>Specifies the fields for hierarchical grouping within the primary axis. See <a data-mention href="/pages/fWOLQR773AyVrxfnGuLZ">/pages/fWOLQR773AyVrxfnGuLZ</a></td></tr><tr><td>@series</td><td>(none)</td><td>-- @series: Sales</td><td>Specifies the columns to use for numerical series values. Represents data to be plotted corresponding to each category. See <a data-mention href="/pages/wsuSIOc5WnxeWbIPEwt6">/pages/wsuSIOc5WnxeWbIPEwt6</a></td></tr><tr><td>@series-types</td><td>(none)</td><td>-- @series-types: column, line</td><td>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 <a data-mention href="/pages/17enBBg10NJwuUqtnTSR">/pages/17enBBg10NJwuUqtnTSR</a></td></tr><tr><td>@stacks</td><td>(none)</td><td>-- @category-stacks: Product</td><td>Designates categorical fields for stacking within the chart. To be used in conjunction with @groups for stacking of the primary axis.</td></tr><tr><td>@stacking-mode</td><td>none</td><td>Example: -- @stacking-mode: percent</td><td>Controls the type of stacking, 'none' or 'percent' where the stacks are normalized to 100% of the group total. See <a data-mention href="/pages/WUMcaJy0CfiOEJrLrxSb">/pages/WUMcaJy0CfiOEJrLrxSb</a></td></tr><tr><td>@tags</td><td>none</td><td>-- @tags: sales, margin</td><td>Groups scripts by their tags so that related scripts can be easily searched</td></tr></tbody></table>

## 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.


# @baselines

Horizontal marks added to charts to mark major comparative values of a series like the average, median, min or max.

## Quick Reference

```sql
-- @baselines: Amount, UnitsSold 
```

## Valid Values

`List`

Baselines must be a comma separated list of columns that map to the columns used within the @series or @secondary-series

{% hint style="info" %}
See also [@baseline-types](/reference/directives/baseline-types) for defining if the baseline is an average, median, min or max
{% endhint %}

## Full Example

```sql
-- @chart: column
-- @title: Baselines - Average Baseline
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/gwKyBu3l6WXw4tYFqzrc" alt=""><figcaption></figcaption></figure>

## Use Cases

Baselines are good to show how a series value compares to to the average, min, max or median of the whole set. It helps give some context to the series data.

## Example Charts

<table data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/XLVj0yEk4zl6Ziw7DfcD">/pages/XLVj0yEk4zl6Ziw7DfcD</a></td><td></td><td></td><td></td><td><a href="/files/gwKyBu3l6WXw4tYFqzrc">/files/gwKyBu3l6WXw4tYFqzrc</a></td></tr><tr><td><a href="/pages/XLVj0yEk4zl6Ziw7DfcD#multiple-baselines-for-one-series">/pages/XLVj0yEk4zl6Ziw7DfcD#multiple-baselines-for-one-series</a></td><td></td><td></td><td></td><td><a href="/files/hA04UBtxTjnjlOkhClf9">/files/hA04UBtxTjnjlOkhClf9</a></td></tr></tbody></table>


# @baseline-types

Determines the type of baseline to use in conjunction with the @baselines directive

## Quick Reference

```sql
-- @baseline-types: min, max, average, median
```

## Valid Values

`List`

A comma separated list of types that matches the order of columns defined in @baselines

* average
* min
* max
* median

{% hint style="info" %}
Used in conjunction with [@baselines](/reference/directives/baselines)
{% endhint %}

## Full Example

```sql
-- @chart: column
-- @title: Baselines - Multiple Baseline Types on one Series
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales, Sales, Sales
-- @baseline-types: average, min, max
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/hA04UBtxTjnjlOkhClf9" alt=""><figcaption></figcaption></figure>

## Use Cases

By default, 'average' is used for all baselines. You use baseline-types to override when you want something other than average.

## Example Charts

<table data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/XLVj0yEk4zl6Ziw7DfcD">/pages/XLVj0yEk4zl6Ziw7DfcD</a></td><td></td><td></td><td></td><td><a href="/files/gwKyBu3l6WXw4tYFqzrc">/files/gwKyBu3l6WXw4tYFqzrc</a></td></tr><tr><td><a href="/pages/XLVj0yEk4zl6Ziw7DfcD#multiple-baselines-for-one-series">/pages/XLVj0yEk4zl6Ziw7DfcD#multiple-baselines-for-one-series</a></td><td></td><td></td><td></td><td><a href="/files/hA04UBtxTjnjlOkhClf9">/files/hA04UBtxTjnjlOkhClf9</a></td></tr></tbody></table>


# @chart

Defines the type of chart like line, area, column, combo

## Quick Reference

```sql
-- @chart: pie
```

## Valid Values

Must be one and only one of the valid chart types supported by ChartSQL

`One Of`

* column
* bar
* pie
* line
* area
* combo

## Full Example

```sql
-- @chart: column
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/WQgOpVgIlI8tPrFGt2dX" alt=""><figcaption></figcaption></figure>

## Use Cases

When you want to specify the type of chart exactly, or the full auto-detection does not meet your needs, you use the @chart directive.

If you have not specified @category, @series or @secondary, they will be inferred from the columns in the result set.


# @category

Defines the column from the result set that will serve as the primary xAxis category or pie chart category.

## Quick Reference

```sql
-- @category: Month
```

## Valid Values

`One Of`

Must be a column name from the result set

## Full Example

```sql
-- @chart: Line
-- @title: Line - Sales by Month
-- @subtitle: An example line chart showing sales by month
-- @category: Month
-- @formats: currency
SELECT
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC
```

<figure><img src="/files/qVJdTrtpTlh9eOC3thkP" alt=""><figcaption></figcaption></figure>

<details>

<summary>Sample Data</summary>

<img src="/files/O6X7Q5NEtUZc8FGBgu1x" alt="" data-size="original">

</details>

## Use Cases

When you do not want the @category columns auto-detected, you specify @category to pick the particular columns that you want used on the main axis.


# @formats

Specify the visual formatting of series data in the chart

## Quick Reference

```sql
-- @formats: integer, currency 
```

## Valid Values

`List`

@formats must be a comma separated list of formats that map to the columns used within the @series or @secondary-series

* none
* integer
* currency
* percent

## Full Example

```sql
-- @chart: bar
-- @title: Bar Chart With Formats
-- @subtitle: An example bar chart with formats
-- @formats: currency
SELECT 
Channel,
sum(Amount) as Total_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel
ORDER BY Total_Sales DESC;
```

<figure><img src="/files/jAroCTyrwHnEYAM3HJku" alt=""><figcaption><p>An example bar chart with currency formatting applied to the series</p></figcaption></figure>

## Use Cases

Formats allows you to visually improve the look of number data in your charts to be more clear and visually appealing.

## Example Charts

<table data-card-size="large" data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/41ZFw9Jp5ut2mspSKZHa">/pages/41ZFw9Jp5ut2mspSKZHa</a></td><td></td><td></td><td></td><td><a href="/files/jAroCTyrwHnEYAM3HJku">/files/jAroCTyrwHnEYAM3HJku</a></td></tr><tr><td></td><td></td><td></td><td></td><td></td></tr></tbody></table>


# @series

Comma separated list of columns to use for data on the chart

## Quick Reference

```sql
-- @series: sales, profit
```

## Valid Values

`List`

A comma separated list of column names from the result set that should be displayed on the chart

## Full Example

```sql
-- @chart: column
-- @title: Series - Basic chart
-- @subtitle: An example of explicitly defining the @series
-- @series: Sales
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales,
    sum(Profit) as Profit
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/DyspTP1KLhtimUPRsARI" alt=""><figcaption><p>An example chart with an explicitly defined series. Columns not added are ignored</p></figcaption></figure>

| MONTH                            | SALES     | PROFIT    |
| -------------------------------- | --------- | --------- |
| January, 01 2017 00:00:00 +0000  | 2668855.0 | 801583.0  |
| February, 01 2017 00:00:00 +0000 | 3951547.0 | 1117761.0 |
| March, 01 2017 00:00:00 +0000    | 4403443.0 | 1224108.0 |
| April, 01 2017 00:00:00 +0000    | 3079040.0 | 862533.0  |
| May, 01 2017 00:00:00 +0000      | 2576305.0 | 631699.0  |
| June, 01 2017 00:00:00 +0000     | 3100093.0 | 870893.0  |
| July, 01 2017 00:00:00 +0000     | 3112476.0 | 871875.0  |
| August, 01 2017 00:00:00 +0000   | 4980193.0 | 1321417.0 |

## Use Cases

You use @series when you need to explicitly control which columns are going to be rendered on the chart. Typically this is necessary when your SQL returns more columns than you wish to display.


# @title

Primary name for your chart that is displayed throughout the UI

## Quick Reference

```sql
-- @title: Sales by Month
```

## Valid Types

`String`

A string value of how you want to title the chart

## Full Example

```sql
-- @title: Auto Heatmap
-- @subtitle: An example auto generated heatmap comparing two categories
SELECT
Owner,
Channel,
count(*) as Sales
FROM Sales
WHERE Sales.Status = 'Won'
GROUP BY Owner, Channel
```

Chart titles are used in various places thoughout the UI

File menu:

<div align="left"><figure><img src="/files/pFDA8OX0OxZJjXADAaFZ" alt=""><figcaption><p>Example of a chart title showing in the file list</p></figcaption></figure></div>

File List:

<figure><img src="/files/Uv6Vc0hiPAzw3W8jLdqp" alt=""><figcaption></figcaption></figure>

## Use Cases

Setting a @title gives your chart a more user friendly title. When no title is set, the file name is the default title.


# @subtitle

A short description about the visualization displayed under the title throughout the UI

## Quick Reference

```sql
-- @subtitle: Sales over time grouped by month
```

## Valid Types

`String`

A short sentence string that describes the chart.

## Full Example

```sql
-- @title: Auto Heatmap
-- @subtitle: An example auto generated heatmap comparing two categories
SELECT
Owner,
Channel,
count(*) as Sales
FROM Sales
WHERE Sales.Status = 'Won'
GROUP BY Owner, Channel
```

## Use Cases

Use a subtitle when the title is insufficient to convery the purpose of the chart, and you want to add a short description. Subtitles are used in various places in the ChartSQL UI

<div align="left"><figure><img src="/files/vFvwS6wdDyDgm0ho0guR" alt=""><figcaption><p>Example of a subtitle in the file list</p></figcaption></figure></div>


# @groups

Defines a list of hierarchical columns to group along the primary axis

```sql
-- @groups: Month, Product
```

## Valid Types

`List`

A comma separated list of columns to use as categories

## Full Example

```sql
-- @chart: bar
-- @title: Groups - Multiple category groups
-- @subtitle: An example of multiple category groups
-- @groups: Channel, Owner
-- @series: TotalSales
-- @formats: currency
SELECT 
	Final.*
FROM (  
  SELECT 
  	Channel,    
  	Owner,
    (
      SELECT sum(Sub.Amount)
      FROM Sales Sub
      WHERE Sub.Channel = Sales.Channel
    ) as TotalChannelSales,
    sum(Amount) as TotalSales
  FROM Sales
  WHERE Owner IN (
    SELECT Owner
    FROM Sales
    GROUP BY Owner
    ORDER BY sum(Amount)
    LIMIT 10
  )
  GROUP BY Channel, Owner
) as Final
ORDER BY TotalChannelSales ASC, TotalSales ASC

```

<figure><img src="/files/dshQViJ2oCRwVQZwMsq3" alt=""><figcaption><p>An example bar chart grouped by a primary and secondary category</p></figcaption></figure>

| CHANNEL  | OWNER   | TOTALCHANNELSALES | TOTALSALES |
| -------- | ------- | ----------------- | ---------- |
| pr\_ad   | Kim     | 9181300.0         | 84603.0    |
| pr\_ad   | Roger   | 9181300.0         | 118985.0   |
| pr\_ad   | Lucy    | 9181300.0         | 138374.0   |
| pr\_ad   | Norbit  | 9181300.0         | 141711.0   |
| pr\_ad   | Justice | 9181300.0         | 160517.0   |
| pr\_ad   | Heather | 9181300.0         | 239870.0   |
| pr\_ad   | Peter   | 9181300.0         | 266655.0   |
| pr\_ad   | Tammy   | 9181300.0         | 501805.0   |
| coldcall | Justice | 1.7372264E7       | 87207.0    |
| coldcall | Heather | 1.7372264E7       | 162974.0   |
| coldcall | Norbit  | 1.7372264E7       | 333648.0   |
| coldcall | Tammy   | 1.7372264E7       | 335206.0   |
| coldcall | Roger   | 1.7372264E7       | 365129.0   |
| coldcall | Peter   | 1.7372264E7       | 725052.0   |
| search   | Lucy    | 3.3377707E7       | 139277.0   |
| search   | Kim     | 3.3377707E7       | 177605.0   |
| search   | Roger   | 3.3377707E7       | 223080.0   |
| search   | Grenda  | 3.3377707E7       | 333216.0   |

## Use Cases

Use groups to heirarchically group series columns together when you want to make visually distinct buckets. In the example above, without the @groups parameter, the chart would render without separating the parent category.

<figure><img src="/files/CkJ8IzlwLmRoTUmOC0JI" alt=""><figcaption><p>An example of a recordset with two groups but not rendering the groups</p></figcaption></figure>


# @series-types

Describes the chart types to apply to individual series when the @chart: combo is defined

## Quick Reference

```sql
-- @series-types: column, line
```

## Valid Types

`List`

* column
* bar
* pie
* line
* area
* combo

## Full example

```sql
-- @chart: combo
-- @title: Combo - Column Chat with Secondary Line
-- @subtitle: A combo chart with a column series and a secondary line series
-- @category: Channel
-- @series: TotalDeals
-- @secondary-series: ConversionRate
-- @series-types: column, line
-- @formats: currency, percent
SELECT 
	Sub.*,
    ROUND(WonDeals / TotalDeals * 100, 1) as ConversionRate
FROM (
  SELECT 
  Channel,
  CAST(count(*) AS FLOAT) as TotalDeals,
  SUM(CASE WHEN Status = 'Won' THEN 1 ELSE 0 END) as WonDeals
  FROM Sales
  WHERE Status = 'Won' or Status = 'Lost'
  GROUP BY Channel
) as Sub
```

<figure><img src="/files/ldQFoZyYhESQjtXtaNot" alt=""><figcaption><p>Example of using series types within a combo chart</p></figcaption></figure>

| CHANNEL  | TOTALDEALS | WONDEALS | CONVERSIONRATE |
| -------- | ---------- | -------- | -------------- |
| coldcall | 206.0      | 37.0     | 18.0           |
| referral | 1069.0     | 243.0    | 22.7           |
| search   | 344.0      | 66.0     | 19.2           |
| event    | 427.0      | 112.0    | 26.2           |
| pr\_ad   | 110.0      | 21.0     | 19.1           |

## Use Cases

Use @series-types when you need to define  combo chart&#x20;


# @series-labels

Adds labels to series items, placing the text at the top, bottom, inside and various positions

## Quick Reference

```sql
-- @series-labels: inside, top
```

## Type&#x20;

&#x20;`List`

Comma separated list of label positions that ordinally match the @series and @secondary-series

## Full Example

```sql
-- @chart: column
-- @title: Series Labels - Label at Top of Series
-- @subtitle: An example column chart with a series label above the top
-- @formats: currency
-- @series-labels: top
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
-- Select just 1 year so that the labels are not too cluttered
WHERE year(date_closed) = 2017
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/pfwgbypTXCmmcmSA80Fq" alt=""><figcaption></figcaption></figure>

## Valid Values

* true
* false
* top
* left
* right
* bottom
* inside
* insideLeft
* insideRight
* insideTop
* insideBottom
* insideTopLeft
* insideBottomLeft
* insideTopRight
* insideBottomRight

## Related Directives

Other directives that have an impact on the rendering of @series-labels

### @formats

Series formats of `currency` , `percent`, `integer`, & `decimal` will also apply the same styling rules to the series labels

## Use Cases

Adding series labels is helpful when you intend to review the actual value for each item in a series, rather than just look at the overall trend. It is easier to read the label then to have to scan the axis values. The downside of labels is they add more visual clutter to the visualization. You should only use series labels when there are not too many elements and the value of each series item is always important to see at a glance.

## Example Charts

<table data-view="cards"><thead><tr><th data-card-target data-type="content-ref"></th><th data-hidden></th><th data-hidden></th><th data-hidden></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><a href="/pages/iBAhlcaJnKbXW3Hg05ey#basic-labels">/pages/iBAhlcaJnKbXW3Hg05ey#basic-labels</a></td><td></td><td></td><td></td><td><a href="/files/pfwgbypTXCmmcmSA80Fq">/files/pfwgbypTXCmmcmSA80Fq</a></td></tr><tr><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td></tr></tbody></table>


# @stacking-mode

Whether the stacking for the chart is shown as 100% of the total or none

## Quick Reference

```sql
-- @stacking-mode: percent
```

## valid Values

`String`

* none
* percent

{% hint style="info" %}
Used with @stacks
{% endhint %}

## Full Example

```sql
-- @chart: area
-- @title: Stacking-mode - 100% normalizing a stacked chart
-- @groups: Month, Channel
-- @subtitle: An example area chart that is 100% stacked
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
-- @stacking-mode: percent
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH'), Channel
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/01OUSfXiKmOIQphqIwpT" alt=""><figcaption><p>Example of stacking-mode: percent </p></figcaption></figure>

| MONTH                            | CHANNEL  | SALES     |
| -------------------------------- | -------- | --------- |
| January, 01 2017 00:00:00 +0000  | coldcall | 174647.0  |
| January, 01 2017 00:00:00 +0000  | referral | 1310064.0 |
| January, 01 2017 00:00:00 +0000  | search   | 681444.0  |
| January, 01 2017 00:00:00 +0000  | event    | 502700.0  |
| February, 01 2017 00:00:00 +0000 | pr\_ad   | 509564.0  |
| February, 01 2017 00:00:00 +0000 | search   | 1109141.0 |
| February, 01 2017 00:00:00 +0000 | event    | 417651.0  |
| February, 01 2017 00:00:00 +0000 | referral | 1149197.0 |

## Use Cases

Use `@stacking-mode: percent` when you want to show the relative contribution to the whole stack of the items in the stack. Use `@stacking-mode: none` if you want to reset the stacking-mode&#x20;


# //@directive: comments

Commenting out directives during development

When developing, sometimes you may wish to quickly toggle on/off the directive without removing it from your source code. ChartSQL supports "directive comments" which will disable that directive.

You comment a directive by adding two forward slashed directly before the @ symbol

```sql
-- @chart: column
-- @title: Directive Comments
-- @subtitle: An example of commenting out a directive
-- @formats: currency
-- @series: Sales
-- //@baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

<figure><img src="/files/7ApLZsxVTaaxL5DxXXTj" alt=""><figcaption><p>An example chat that includes a @baselines directive that is commented out</p></figcaption></figure>


# @dash-id

Globally unique identifier given to a ChartSQL chart that is published to DashSQL.com

{% hint style="info" %}
You will not usually set @dash-id manually. Instead the dash-id will be automatically created when publishing your chart to DashSQL. See [Sharing & Publishing](/product-and-community/in-development/sharing-and-publishing)
{% endhint %}

## Quick Reference

```
-- @dash-id: ghjeiof02
```

## Valid Values

`String`

## Full Example

```sql
-- @title: Auto Heatmap
-- @subtitle: An example auto generated heatmap comparing two categories
-- @dash-id: gjt30fj
SELECT
Owner,
Channel,
count(*) as Sales
FROM Sales
WHERE Sales.Status = 'Won'
GROUP BY Owner, Channel
```

## Use Cases

@dash-id uniquely and globally identifies your chart in the DashSQL database. It is used for lookups and collaboration purposes. You will typically not need to edit this manually.


# @overlay-series

Overlays the specified series on top of each other

## Quick Reference

```
-- @overlay-series: sales, profit
```

## Valid Values

`List`

A comma separated list of series names that should be overlayed one on top of the other.

## Use Cases

Use `@overlay-series` to more visually clarify two series that have a relationship, but you do not want them stacked. For example, Revenue and Gross Profit.


# @tags

Describe your SQL script with searchable tags

{% hint style="info" %}
@tags is a directive used in ChartSQL Studio. It does not have any effect on rendering or ChartSQL.js
{% endhint %}

## Quick Reference

```
-- @tags: sales, costs
```

## Valid Values

`List`

A comma separated list of tags that can be searched in the file browser to group related scripts together.

## Use Cases

Use `@tags` to group your SQL scripts together for easy searching. You can search by the tag name in the file browser search and all scripts which contain the tag will be shown.


# @select-list

Update the SQL query dynamically from a dropdown list in the ChartSQL Studio UI

<figure><img src="/files/szWNne0sw0x8E6V3P7jB" alt=""><figcaption><p>Example chart with a @select-list filter</p></figcaption></figure>

## Quick Reference

```sql
-- @select-list-{NAME}: option1, option2
```

## Valid Values

`List`

A list of values to use in the select list options that will transform the SQL query based on the selected option.

## Full Example

{% tabs %}
{% tab title="SQL" %}

```sql
-- @title: Select List - Dynamic SQL query from user selection
-- @subtitle: An example SQL query which uses a dynaic SQL query from user selection
-- @formats: currency
-- @select-list-Channel: referral, coldcall, search, event
-- @chart: line
SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM sales
WHERE Channel = '{{select-list-channel.selected}}'
GROUP BY Month
ORDER BY Month ASC;

```

{% endtab %}

{% tab title="Chart" %}

<figure><img src="/files/eZZogn5XctN4wro0BfOa" alt=""><figcaption></figcaption></figure>
{% endtab %}
{% endtabs %}

## Use Cases

@select-list allows you to define a custom drop down selection that can be used as a variable within the query. This is useful when you have chart data that you want to change at the query level, including complex dynamic SQL.

@select-list allows you to create customizable reports using a single base query that the user can then select within the UI. @select-list allows you to consolidate certain queries which are very similar.

### Default First Value

The value of the select list is never null, therefore your SQL query does not need to handle the empty case. The first element in the @select-list is the default value.&#x20;

If you want to simulate any/null/none, have your first select value be a value like 'none' or 'any' and handle this yourself in code.

### Templating Language

The templating language that processes the dynamic SQL is [Handlebars.js](https://handlebarsjs.com/). This allows for basic control flow (if/then/else), loops and equality checks.

```plsql
-- @chart: column
-- @series: TotalSold
-- @select-list-channel: All, Referral, Organic, Paid
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
{{#if (eq select-list-channel.selected 'All')}}
	-- Do nothing we are going to return all channels
{{else}}
	WHERE Channel = '{{select-list-channel.selected}}'
{{/if}}
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;
```

#### Handlebars Object Reference

The following variables are provided to the SQL script Handlebars processor that you can use in your handlebars logic.

```json5
{
    //The value selected, defaults to the first value in the list
    'select-list-channel': {
        'selected': 'All'
        'values': ['All', 'Referral', 'Organic', 'Paid']
}
```


# Glossary

<table><thead><tr><th width="203">Term</th><th>Description</th></tr></thead><tbody><tr><td>Folder</td><td>A collection of SQL scripts on the file system that are the source code for visualizations.</td></tr><tr><td>Datasource</td><td>A SQL or NoSQL database that ChartSQL can connect to you run visualization queries against.</td></tr><tr><td>Directives</td><td>'Directives', @directives or 'at directives' - These are the annotations in a script that tell ChartSQL how to visualize your query.</td></tr><tr><td>Publisher</td><td>An individual with publishing rights to a ChartSQL.com folder/package</td></tr><tr><td>Script</td><td>A SQL Script that you wish to visualize.</td></tr><tr><td>Studio</td><td>Shorthand for ChartSQL Studio.</td></tr></tbody></table>


# Shortcuts

## Global Shortcuts

<table><thead><tr><th width="159">Shortcut</th><th width="183">Key Combination</th><th>Context</th><th>Note</th></tr></thead><tbody><tr><td>Find File</td><td>ctrl + f</td><td>Editor / Present</td><td>Find files in the current package. Pressing ctrl+f again will open the browser default finder.</td></tr><tr><td>Global Find</td><td>ctrl + p</td><td>Any</td><td>Opens a global search window that will search all folders</td></tr></tbody></table>

## SQL Editor Shortcuts

| Shortcut          |             | Context                                | Note                                                                                                                              |
| ----------------- | ----------- | -------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------- |
| Comment/Uncomment | ctrl + /    | Current line or selected lines         | Will comment out or uncomment lines in a SQL file. When the line contains a directive, it will commend or uncomment the directive |
| Indent            | tab         | Cursor Location or selected lines      |                                                                                                                                   |
| De-Indent         | shift + tab | Cursor location line or selected lines |                                                                                                                                   |

## Presentation Shortcuts

| Shortcut      | Key Combination |                                                                  |
| ------------- | --------------- | ---------------------------------------------------------------- |
| Draw on chart | ctrl (hold)     | Holding ctrl allows you to draw on a chart in presentation mode. |
| Clear Drawing | esc             | Pressing escape will clear the currently active drawing          |


# Publishing API

API description and procedures for publishing charts to ChartSQL.com

## Overview

You can publish your charts to ChartSQL Cloud to share and create dashboards. ChartSQL Studio provides all of the capabilities for publishing, but you may also use the API if you are doing your own automations.

This document describes the API calls for publishing charts to ChartSQL.com

## Publisher Authentication

In order to publish charts, you need to be an approved `Publisher` for a `Package`. You will need a ChartSQL Cloud account.

If you are not the creator/owner of a Package on ChartSQL Cloud, the Package owner needs to give your user Publisher access.

### Publishing Key

From your ChartSQL Cloud account settings, go to Packages. From there you can create a new Publisher Key. A Publisher Key is a unique password that gives you API access to publish to the Package. Do not share your Publishing Key with anyone else, it is like a password, and it is tied to your ChartSQL Cloud account.

Publishing Keys are securely stored and cannot be recovered by ChartSQL support. If you forget or lose your key, you will need to create another.

#### Publishing Key Format

Typically you will not need to do this encoding yourself. The Publishing Key format is a base64 encoded string of your Publisher Key Id and its password. When you created your publishing key in the ChartSQL Cloud UI, you will have received your base64 encoded string.

toBase64("{{publisherKeyId}}:{{password}}")

The publishing key allows ChartSQL Cloud to identify the account by `publisherKeyId` and verify access by comparing the salted and hashed value of the `password`.

## Base URL

<http://api.chartsql.com/PublisherKey/\\{{publishingKey\\}}/>

## Endpoints

### Publish Chart

POST <http://api.chartsql.com/PublisherKey/\\{{publishingKey\\}}/publishDashChart.json>

#### Fields

| Name     | Required | Description                             |
| -------- | -------- | --------------------------------------- |
| sql      | yes      | The SQL content of the chart            |
| fileName | yes      | The local file system name of the chart |

### Update Chart

POST <http://api.chartsql.com/PublisherKey/\\{{publishingKey\\}}/updateDashChart.json>

#### Fields

| Name     | Required | Description                                                        |
| -------- | -------- | ------------------------------------------------------------------ |
| sql      | yes      | The SQL content of the chart                                       |
| fileName | yes      | The local file system name of the chart                            |
| dashId   | yes      | The @dash-id that uniquely identifies the already published chart. |

### Verify/Stats

POST <http://api.chartsql.com/PublisherKey/\\{{publishingKey\\}}/stats.json>

You use the /stats endpoint to verify access to the PublisherKey. If the publishing key fails, an error will be returned.


# About

About ChartSQL Development, why we built it and where we're headed.

## What you Query is What you Chart

Our primary philosophy is SQL is the language of data. There should be zero friction to go from a SQL report, to a beautiful client accessible chart. Our mission with ChartSQL is simple: ***What you Query is What you Chart.*** Our vision is for ChartSQL to be the easiest way for developers to visualize SQL.

## We built ChartSQL for SQL Developers

ChartSQL was created by [Rory Laitila](https://www.linkedin.com/in/rory-laitila/) at [SalesInsights.io](https://salesinsights.io/). We are an engineering first company that builds our data models in SQL. We love using SQL as our data pipeline and reporting layer. However when it comes to charting, nothing met our needs.

### Why not PowerBI?

Business visualization tools like Excel, PowerBI and Tableau are oriented towards WYSIWYG editors and business people, which does not meet our needs as developers. We want to keep our visualizations close to our data models which are all in SQL.

### Why not the Cloud?

Cloud "self-service" analytics platforms like Metabase and Google/Looker Data Studio are inflexible, slow and not great for multi-tenant agency work. We want to be able to write our SQL models once, and use them across customers.

### Why not Pyhton/R/Matlab?

We build models in SQL. We don't like to switch between SQL and programming languages just to visualize. We also needed a platform that was web first, embeddable, customizable, and beautiful to show to clients.

## Future Vision for ChartSQL

We want ChartSQL and its declarative visualization language to become the defacto standard for visualizing SQL models. We will continue to add more advanced features, while keeping it easy to start. "Make the easy things easy, and the hard things possible" is our motto.


# Features

High level overview of ChartSQL features

## SQL Visualizations

Chart and visualize SQL directly in source code without complicated point and click dashboards

[See Creating Charts](/chartsql-studio/creating-charts)

## Quick Exploration

With "auto-mode", ChartSQL will pick the most likely chart based on the number and types of your columns

[See Auto Chart Examples](/charts/auto-charts)

## Deep Customization

Control all aspects of your visualization with "@directives"&#x20;

[See Creating Charts](/chartsql-studio/creating-charts)

## Rich Charts

A wide array of chart types to cover most SQL visualization scenarios

[See All Examples](/charts/example-charts#all-examples)

## Present & Share

Immediately share and present your charts to an audience directly from your editor like a slide presentation

[See Presenting](/chartsql-studio/presenting)

## Publishing

Publish your charts to DashSQL Dashboards to create beautiful dashboards for yourself and clients.

## Multi-Tenant

Run your ChartSQL scripts against different instances of the same data model. This allows you to share your visualizations across customers.

[See ChartSQL Studio Datasources](/chartsql-studio/basic-concepts/datasources)


# Use Cases

Development scenarios that ChartSQL is well suited for


# General Uses

General benefits and use cases of ChartSQL

## Key Benefits

* **Quick Prototyping:** Quickly explore and visualize SQL queries
* **SQL & Charts Together:** Keep your SQL query and visualization together in one file so that they stay in sync
* **Query, Chart, Present:** Quickly move from a query, to a chart, to an attractive presentation in a seamless workflow without any copy/paste or manual work
* **Collaborate:** Collaborate on your SQL and charts in source code like git

## ChartSQL Limitations

* ChartSQL is best as the visualization layer with relatively simple shaping that can be done in a single query. If you need to do complex and expensive SQL calculations, using something like [DBT](https://www.getdbt.com/) or your data warehouse to prepare the data is recommended.
* Although ChartSQL Studio has SQL management features, is not a general SQL editor. We like [Dbeaver](https://dbeaver.com/) for SQL administration and editing.


# For SQL Developers

ChartSQL is great for SQL developers to quickly add visualizations to your SQL scripts

* **SQL Familiarity:** Leverage existing SQL knowledge to create powerful data visualizations.
* **No Data Transformation Needed:** ChartSQL plots the data exactly as its returned, reducing the need for complex data shaping to meet the needs of the visuals.
* **Enhanced SQL Scripts:** Combine visualization instructions within SQL scripts for easier management.
* **Better Exploration and Presentation:** Use intelligent auto-detection and customizable @directives for presenting data insights.


# For Application Developers

ChartSQL is a great addition for Application Developers. You can quickly visualize your data and add charts to your application.

* **Quick Integration:** Simplifies the process of adding data visualizations into applications by using familiar SQL.
* **Dynamic Visualizations:** Create auto-updating charts within applications without additional coding.
* **Customizable Visualizations:** Fine-tune visual appearance and behavior of charts with @directives to match app UI/UX.
* **Efficiency:** Avoid the overhead of learning new visualization tools or libraries.


# For Agencies

ChartSQL is a great addition to a web, software or analytics development agencies

* **Client-Specific Dashboards:** Rapidly prototype and deliver custom dashboards with minimal overhead.
* **Multi-Tenant Functionality:** Easily replicate and customize dashboards for different clients without code changes.
* **Streamlined Development:** Maintain client visualizations efficiently by controlling them with SQL scripts.
* **Professional Hosting with ChartSQL Cloud:** Offer clients hosted dashboards which automatically sync with SQL source updates.


# For Data Science Teams

How Data Scientists and data teams can use ChartSQL

* **Data Exploration:** Swiftly explore data patterns and trends through ChartSQL's auto-detecting visualizations.
* **Collaboration:** Share visualizations and insights by keeping SQL code and charts in one place, which can be version controlled.
* **Advanced Analysis:** Utilize the full power of SQL to shape data for complex visualizations that tell a compelling story.
* **Scalable Reporting:** Produce informative and interactive reports for in-depth analysis without the need for third-party tools.


# Community & Support

Reach out to the ChartSQL community or get support from ChartSQL

## ChartSQL Discord

All ChartSQL support is provided in our Discord community.&#x20;

{% embed url="<https://discord.gg/UbHYA6nyTg>" %}

## Social

{% embed url="<https://twitter.com/chartsql>" %}


# Roadmap

Product roadmap for ChartSQL

ChartSQL is being developed with the help of community involvement and existing users. Below is the broad product roadmap and what you can expect.

## :star: Preview (current Release)

Early release for those who wish to follow along. Expect bugs and many changes to the editor and language. Not intended for production use.

* Basic directives
* Installable and useable editor
* All basic chart types: column, bar, line, area pie, etc

## Alpha

User is able to install and update the Studio edtior without issues. You can use ChartSQL for production purposes but the directives may change without backward compatibility.

* Examples for all charts
* Complete documentation
* Manually updateable studio client

## Beta

ChartSQL has all major features we feel are needed for basic production use, and the language is stabilized.

* All client bugs addressed
* Testing in production and getting user feedback

## Release

ChartSQL is released to the public and available for general use


# Release Notes

ChartSQL development release notes

## 2024-07-11

This update brings a range of enhancements improving user experience, from bug fixes to new features and performance improvements for faster rendering times.

**General Improvements:**

* Updated all documentation links to the latest versions.
* Enhanced filtering functionality to be case-insensitive.
* Improved MongoDB query encoding with base64 to preserve JSON structure.

**UI and Functional Bug Fixes:**

* Corrected issues with MongoDB query editor tabs.
* Fixed tooltip display problems when using stacked charts.
* Addressed cases where SQL chart titles and page titles were not updating properly.
* Resolved a variety of button issues on the page, including trash buttons and the 'Rename' story button.

**Chart and Visuals:**

* Added new gauge and radar chart types, including a radar chart with multiple series.
* Implemented a 'tags' directive for easy chart searching.
* Improved chart rendering times.

**Studio and Dashboard Improvements:**

* Refined the ChartSQL Studio interface by removing unused properties and improving the visual layout.
* Updated the Publisher flow to handle chart filenames more intuitively within the DashSQL.
* Worked on data source improvements and the ability to null out the default data source when needed.

**Quality of Life Tweaks:**

* Comma-delimited integers by default for better readability.
* Enhanced modals for errors and configuration to improve user interaction.
* Implemented ability to copy chart results as sharable SQL directly from the user interface.

**Refactoring and Code Maintenance:**

* Conducted significant codebase cleanup, removing unused code and properties.
* Refactored error handling to produce more meaningful messages.
* Streamlined the EChartsOption configuration for more efficient loading.

## 2024-04-12

**Enhanced Search, UI Refinements, and User Experience**

This update focuses on improving user quality of life and refining UI elements. Check out the key updates below!

#### **Global Search Enhancements**

* Implemented a more capable text search algorithm.
* Added keyboard navigation in global search results using Up and Down arrow keys.
* Enhanced display of search results with a line divider between folders, datasources, and SQL files.
* Added a configuration pill to folders and datasources in the global search results for quick access.

#### **UI and Usability Enhancements**

* Updated UI elements like the mascot logo and added a new default page when no package is opened.
* Introduced a right-click context menu for easier file management.
* Added loading indicators for smoother file operations.
* Enhanced SQL file display to show titles instead of file names where applicable.

#### **Miscellaneous Improvements**

* Various bug fixes, including corrections for dropdown issues, layout shifts, and tooltip enhancements.
* Numerous back-end improvements for better handling of data connections, including updates to MongoDB and PostgreSQL connectors.

#### **Refinements in Code Management and Navigation**

* Cleaned up unused classes and refactored several components for better performance and maintainability.
* Updated navigation and file handling to enhance user experience with smoother transitions and less visual shift.

## 2024-01-13

📁 New Feature:

* Introduced subfolder support for better chart organization.

🖥️ UI/UX Improvements:

* Settings menu items are now at the bottom of the navigation menu for easier access.
* The editor header remains visible while scrolling, ensuring constant access to tools.

📊 Chart Enhancements:

* Added `baselines` and `baseline-types` directives to fix error messages and enhance baseline customization.
* Updated presentation mode for better MacOS support and improved slicers and drawing functionality.
* Fixed "time" type xAxis on Combo charts for consistent time-based visualizations.
* Improved bar chart formatting with new axis labeling features.

💡 Directives and Comments:

* Series labels now support all four format types, with multi-series labels included.
* Directive toggles have been fixed to only show when applicable, and you can now comment out directives easily.

🐛 Bug Fixes and Minor Tweaks:

* Fixed the issue where uppercase characters added unnecessary spaces in column names display.
* Corrected refreshing behavior in presentation mode.
* Enhanced data representation with added grid in bar chart options to prevent label cutoff.
* Chart sharing via URL now configurable for testing purposes, and link handling in slideshows more versatile.

## 2024-01-06

* Fixed a path issue in Application.cfc to ensure proper storage of settings in the OS user folder on both Windows and Mac.
* Implemented a temporary fix in the PerformanceInfo routine to avoid CPU performance counter errors on macOS.
* Improved display of the currently open file path to include subfolder info, so you know exactly where your file is located within the project.
* Updated main controller to show examples by default if no user package is specified, getting you started with sample content right away.
* Renamed the editor to 'Studio' to bring consistency across all our platforms.
* Clean-up on the editor (now Studio!) to get it ready for distribution.


# In Development

Documentation for features that are in development.


# Workspaces

Workspaces combine multiple folders and datasources that you can work on together

{% hint style="warning" %}
Workspaces are coming soon. Documentation is for feedback purposes
{% endhint %}

## Overview

Workspaces allow you to combine multiple folders and default datasources together into a single set of charts to work on and present together. Workspaces enable you to:

* Combined scripts together in the editor to work on them as one group of files.
* Each workspace has a distinct set of folders and datasource configurations, so you can easily switch between clients by switching between workspaces.

Workspaces are typically used when you have many clients using multiple different folders of scripts. In these cases, each client has their own datasource for each of the folders in the workspace.

## Creating a Workspace

Go to Settings > Workspaces > Add Workspace

### Add a Folder to the Workspace

Add a Folder to the Workspace. You will select the default Datasource to use for that Folder when you have opened the Workspace.

## Opening a Workspace

You can open one of your existing workspaces from the left hand menu or from the global search (ctrl + p)


# Dashboards


# Intro

Getting started with Dashboards for ChartSQL

{% hint style="info" %}
Dashboards are currently in development. Dashboard documentation is for feedback purposes.
{% endhint %}

## Dashboards Overview

ChartSQL allows you to organize and display your charts in a customizable dashoard. Dashboards are used to see relevant data together and continually update your charts when new data is available.

Dashboards run on ChartSQL Cloud and can be shared with external parties so that others can view your charts.

Your Dashboards are accessible from within your ChartSQL Studio Editor and also on ChartSQL.com

## Getting Started

Publishing your charts to a dashboard requires a ChartSQL Cloud Account. Signup at chartsql.com/cloud.

## Understanding ChartSQL, Packages and Dashboards

ChartSQL works differently from many other dashboard tools. Your charts exist as source code inside folders. You publish your folders as a 'Package' to ChartSQL Cloud where that Package can be assigned to Dashboards. Therefore your Dashboard can display many charts from many Packages to create the experience you are looking for.

When you publish changes to an existing package, all Dashboards that have charts from that package are automatically updated to the latest source code.

## Dashboards, Pages and Charts

A single Dashboard can contain many pages, and each Page can contain many Charts. You will typically create a Dashboard to represent a particular client, business, or major division. You will use Pages to organize the Charts that go logically together.

## Understanding Dashboard Datasources

Dashboards run on the ChartSQL Cloud because they can be accessible to anyone, even if they do not use ChartSQL Studio Desktop. Therefore, ChartSQL.com must be able to execute SQL queries against datasources. Even when you are viewing your Dashboards from within ChartSQL Studio, they are still executing on ChartSQL Cloud. ChartSQL Cloud provides the caching and refresh functionality for all users of the dashboards.


# Dashboards

Details about creating and managing Dashboards

{% hint style="info" %}
Dashboards are currently in development. Dashboard documentation is for feedback purposes.
{% endhint %}

## Dashboard Overview

A Dashboard is a collection of charts organized in a grid. Dashboards allow you to see multiple charts together in one view.

You will need a ChartSQL Cloud Account in order to create Dashboards.

## Creating a Dashboard

From within ChartSQL Studio, click on Create New Dashboard.

### Dashboard Name

Give your Dashboard a name that clearly describes what the dashboard is about. Typically this will be the company or organization name.

### Assign Packages

Assign the Packages that the Dashboard should have access to. Any of the charts from within the package can be added to a Page on the Dashboard.


# Packages

A Package is a collection of ChartSQL charts that can be added to the Dashboard

{% hint style="info" %}
Dashboards are currently in development. Dashboard documentation is for feedback purposes.
{% endhint %}

## Packages Overview

Each Dashboard is assigned one or many Packages of charts. Users can add charts from those packages to the Dashboard to display.

## Adding a Package

From the Dashboard Settings, click Add Package.&#x20;

When you add a new Package, you will be required to setup the Datasource required by that Package in order to begin adding charts to the Dashboard.




---

[Next Page](/llms-full.txt/1)