Creating Charts

A chart in TSQL.APP is a temporary table with three columns and one procedure call. sp_api_modal_chart reads the table, builds a Chart.js chart and shows it in the dialog. There is no charting code to write.

The shape of the data

dataset, label and number are required. If one is missing, the procedure shows a message saying so instead of a chart.

A first chart

DECLARE @CloseButton NVARCHAR(MAX);

EXEC sp_api_modal_get_value @name = N'@CloseButton', @value = @CloseButton OUT;

CREATE TABLE #Sales (dataset NVARCHAR(20), label NVARCHAR(20), number DECIMAL(10,2), [order] INT);
INSERT INTO #Sales (dataset, label, number, [order]) VALUES
    (N'2025', N'Jan', 1000, 1), (N'2025', N'Feb', 1200, 2), (N'2025', N'Mar', 1500, 3),
    (N'2026', N'Jan', 1150, 1), (N'2026', N'Feb', 1380, 2), (N'2026', N'Mar', 1720, 3);

EXEC sp_api_modal_text  @text = N'Sales per month', @class = N'h3';
EXEC sp_api_modal_chart @tmptable = N'#Sales', @type = N'bar', @title = N'Revenue', @height = N'360px';
EXEC sp_api_modal_button @name = N'@CloseButton', @value = N'Close',
     @valueout = @CloseButton OUT, @class = N'btn-outline-secondary';

IF @CloseButton IS NOT NULL EXEC sp_api_modal_clear;

Two datasets, 2025 and 2026, give two bars per month side by side. In a real application the INSERT ... VALUES becomes a SELECT ... GROUP BY over your own data.

Chart types and options

@type takes the Chart.js chart types: bar, line, pie, doughnut, radar, polarArea. @height sets the height (default 400px); @title adds a heading inside the chart.

@options passes extra Chart.js settings as JSON. Top-level settings with a simple value go to the chart as they are — for example, indexAxis turns a bar chart on its side, which suits long labels:

DECLARE @Options NVARCHAR(MAX) = N'{"indexAxis": "y"}';

EXEC sp_api_modal_chart @tmptable = N'#Sales', @type = N'bar', @options = @Options;

The axes, the legend and the animation are configured by the framework itself, so the chart looks consistent across the application without any options at all.

A chart you can click

Give the chart a @name and it becomes a control like any other. When the user clicks a bar or a slice, the next run receives what was clicked, through three OUT parameters:

DECLARE @Clicked       NVARCHAR(MAX);
DECLARE @ClickedSeries NVARCHAR(MAX);
DECLARE @ClickedValue  FLOAT;
DECLARE @Detail        NVARCHAR(MAX);

CREATE TABLE #Sales (dataset NVARCHAR(20), label NVARCHAR(20), number DECIMAL(10,2));
INSERT INTO #Sales (dataset, label, number) VALUES
    (N'Revenue', N'North', 4200), (N'Revenue', N'South', 3100), (N'Revenue', N'West', 2650);

EXEC sp_api_modal_chart @tmptable = N'#Sales', @type = N'pie', @name = N'@RegionChart',
     @value = @Clicked OUT, @dataset_value = @ClickedSeries OUT, @number_value = @ClickedValue OUT;

IF @Clicked IS NOT NULL
BEGIN
    SET @Detail = CONCAT(N'You clicked ', @Clicked, N': ', @ClickedValue);
    EXEC sp_api_modal_text @text = @Detail, @class = N'h5';
END

@value returns the label, @dataset_value the series and @number_value the number. That is the hook for drill-down: show a table of the orders behind the slice, or restart into a detail action with the label as a parameter.