Sales Dashboard Example

A dashboard is not a separate product in TSQL.APP. It is an action script that draws several things in one dialog: some headline figures, a chart, a table. Because the script re-runs on every click, a filter at the top redraws the whole page with no extra code.

The sales data here is generated in a temporary table so the example runs anywhere. Replace the first INSERT with a query over your own orders.

The script

-- Variables
DECLARE @Period       NVARCHAR(MAX);
DECLARE @CloseButton  NVARCHAR(MAX);
DECLARE @FromDate     DATE;
DECLARE @Revenue      DECIMAL(12,2);
DECLARE @Orders       INT;
DECLARE @AvgOrder     DECIMAL(12,2);
DECLARE @Kpi          NVARCHAR(MAX);

-- Sample data: a year of orders
CREATE TABLE #Orders (order_date DATE, region NVARCHAR(20), product NVARCHAR(40), amount DECIMAL(10,2));
INSERT INTO #Orders (order_date, region, product, amount)
SELECT DATEADD(DAY, -(n * 7 % 365), CAST(GETDATE() AS DATE)),
       CHOOSE(n % 3 + 1, N'North', N'South', N'West'),
       CHOOSE(n % 4 + 1, N'Starter plan', N'Team plan', N'Business plan', N'Support hours'),
       100 + (n * 37 % 900)
FROM (SELECT TOP 200 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects) t;

-- Filter: the period buttons sync themselves
EXEC sp_api_modal_get_value @name = N'@CloseButton', @value = @CloseButton OUT;
IF @CloseButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_clear;
    RETURN;
END

EXEC sp_api_modal_text   @text = N'Sales dashboard', @class = N'h3';
EXEC sp_api_modal_choose @list = N'30 days,90 days,12 months', @name = N'@Period', @value = @Period OUT;

SET @FromDate = CASE @Period
                    WHEN N'30 days' THEN DATEADD(DAY, -30, CAST(GETDATE() AS DATE))
                    WHEN N'90 days' THEN DATEADD(DAY, -90, CAST(GETDATE() AS DATE))
                    ELSE DATEADD(MONTH, -12, CAST(GETDATE() AS DATE))
                END;

-- Headline figures
SELECT @Revenue = SUM(amount), @Orders = COUNT(*), @AvgOrder = AVG(amount)
FROM #Orders WHERE order_date >= @FromDate;

SET @Kpi = CONCAT(N'Revenue ', FORMAT(ISNULL(@Revenue, 0), N'N0'),
                  N'  ·  Orders ', @Orders,
                  N'  ·  Average order ', FORMAT(ISNULL(@AvgOrder, 0), N'N0'));
EXEC sp_api_modal_text @text = @Kpi, @class = N'h5 text-primary';

-- Chart: revenue per region, one bar per region
SELECT dataset = N'Revenue', label = region, number = SUM(amount)
INTO #ByRegion
FROM #Orders WHERE order_date >= @FromDate
GROUP BY region;

EXEC sp_api_modal_chart @tmptable = N'#ByRegion', @type = N'bar', @title = N'Revenue by region', @height = N'300px';

-- Table: grouped by region, totalled
SELECT [Region*] = region, [Product] = product, [Orders@] = COUNT(*), [Revenue@] = SUM(amount)
INTO #Detail
FROM #Orders WHERE order_date >= @FromDate
GROUP BY region, product;

EXEC sp_api_modal_table @tmptable = N'#Detail', @orderby = N'ORDER BY [Region*], [Revenue@] DESC',
     @excel = 1, @excel_filename = N'sales-dashboard';

EXEC sp_api_modal_button @name = N'@CloseButton', @value = N'Close',
     @valueout = @CloseButton OUT, @class = N'btn-outline-secondary';

How it works

The filter is just a control. sp_api_modal_choose draws the three period buttons and returns the one that is selected. Clicking another one posts the page back, the script runs again with the new @Period, and every figure, the chart and the table are recalculated. Nothing needs to "refresh" anything.

Close is handled first. The Close button is drawn last, but checked before anything else is computed, so closing the dashboard does not run every query one more time.

Column suffixes do the report formatting. In the table, [Region*] groups the rows by region, and the @ on [Orders@] and [Revenue@] adds totals. The data stays an ordinary SELECT; the presentation comes from the column names.

One source, three views. The figures, the chart and the table all read the same filtered rows, so they always agree. With your own data, that is one WHERE clause over your orders table.