📉 TSQL.APP Sales Dashboard Example 📊

Sales Dashboard with complete functionality and correct parameter usage using best practises

-- TSQL.APP Sales Dashboard with complete functionality and correct parameter usage using best practises
DECLARE @Title NVARCHAR(100) = N'Sales Analysis Dashboard';
DECLARE @DateFromStr NVARCHAR(MAX);
DECLARE @DateToStr NVARCHAR(MAX);
DECLARE @RefreshButton NVARCHAR(MAX);
DECLARE @ExportButton NVARCHAR(MAX);
DECLARE @QuitButton NVARCHAR(MAX);

-- Get values from modal state
EXEC sp_api_modal_get_value @name='@DateFromStr', @value=@DateFromStr OUT;
EXEC sp_api_modal_get_value @name='@DateToStr', @value=@DateToStr OUT;
EXEC sp_api_modal_get_value @name='@RefreshButton', @value=@RefreshButton OUT;
EXEC sp_api_modal_get_value @name='@ExportButton', @value=@ExportButton OUT;
EXEC sp_api_modal_get_value @name='@QuitButton', @value=@QuitButton OUT;

-- Create and populate sales data
IF OBJECT_ID('tempdb..#SalesData') IS NOT NULL DROP TABLE #SalesData;
CREATE TABLE #SalesData (
    SaleMonth DATE,
    Revenue DECIMAL(18,2),
    Costs DECIMAL(18,2),
    Profit DECIMAL(18,2)
);

INSERT INTO #SalesData (SaleMonth, Revenue, Costs, Profit)
VALUES 
    ('2024-01-01', 50000, 30000, 20000),
    ('2024-02-01', 55000, 32000, 23000),
    ('2024-03-01', 60000, 35000, 25000),
    ('2024-04-01', 52000, 31000, 21000),
    ('2024-05-01', 58000, 34000, 24000),
    ('2024-06-01', 65000, 38000, 27000);

-- Create chart data
IF OBJECT_ID('tempdb..#ChartData') IS NOT NULL DROP TABLE #ChartData;
CREATE TABLE #ChartData (
    dataset NVARCHAR(50),
    label NVARCHAR(50),
    number DECIMAL(18,2),
    [order] INT,
    color NVARCHAR(50)
);

INSERT INTO #ChartData
SELECT 
    dataset = metric,
    label = FORMAT(SaleMonth, 'MMMM yyyy'),
    number = value,
    [order] = MONTH(SaleMonth),
    color = CASE metric 
        WHEN 'Revenue' THEN 'rgba(54, 162, 235, 0.8)'
        WHEN 'Profit' THEN 'rgba(75, 192, 192, 0.8)'
    END
FROM #SalesData
CROSS APPLY (VALUES 
    ('Revenue', Revenue),
    ('Profit', Profit)
) v(metric, value);

-- Display title
DECLARE @TitleText NVARCHAR(100) = @Title;
EXEC sp_api_modal_text @text=@TitleText, @class='h2';

-- Add date range selector
EXEC sp_api_modal_date_from_to @from=@DateFromStr OUT, @to=@DateToStr OUT;

-- Display chart
DECLARE @ChartOptions NVARCHAR(MAX) = '{
    "responsive": true,
    "maintainAspectRatio": false,
    "scales": {
        "y": { "beginAtZero": true }
    }
}';

EXEC sp_api_modal_chart
    @name='sales_chart',
    @tmptable='#ChartData',
    @type='bar',
    @height='400px',
    @options=@ChartOptions;

-- Display data table
EXEC sp_api_modal_table 
    @tmptable='#SalesData',
    @orderby='ORDER BY SaleMonth',
    @excel=1,
    @print=1;

-- Add buttons
DECLARE @ButtonValue NVARCHAR(100) = 'Refresh';
EXEC sp_api_modal_button 
    @name='@RefreshButton',
    @value=@ButtonValue,
    @valueout=@RefreshButton OUT,
    @class='btn-primary',
    @key='F5';

SET @ButtonValue = 'Export';
EXEC sp_api_modal_button 
    @name='@ExportButton',
    @value=@ButtonValue,
    @valueout=@ExportButton OUT,
    @class='btn-success';

SET @ButtonValue = 'Close';
EXEC sp_api_modal_button 
    @name='@QuitButton',
    @value=@ButtonValue,
    @valueout=@QuitButton OUT,
    @class='btn-secondary';

-- Handle button actions
IF @RefreshButton IS NOT NULL
BEGIN
    DECLARE @RefreshMessage NVARCHAR(100) = 'Dashboard refreshed';
    EXEC sp_api_toast @text=@RefreshMessage, @class='success', @seconds=3;
    RETURN;
END

IF @ExportButton IS NOT NULL
BEGIN
    DECLARE @FileName NVARCHAR(100) = 'Sales_Report_' + FORMAT(GETDATE(), 'yyyyMMdd') + '.xlsx';
    DECLARE @ExportSQL NVARCHAR(MAX) = 'SELECT * FROM #SalesData';
    
    EXEC sp_api_excel
        @sql = @ExportSQL,
        @filename = @FileName,
        @stay_in_modal = 1,
        @order_column = 1;
        
    DECLARE @SuccessMessage NVARCHAR(100) = CONCAT('Report exported as ', @FileName);
    EXEC sp_api_toast 
        @text = @SuccessMessage, 
        @class = 'success',
        @seconds = 3;
END

IF @QuitButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_clear;
    DECLARE @CloseMessage NVARCHAR(100) = 'Dashboard closed';
    EXEC sp_api_toast @text=@CloseMessage, @class='info', @seconds=3;
    RETURN;
END

I've created a comprehensive demo that showcases several key capabilities of TSQL.APP. Here's what the demo includes:

Interactive Dashboard

• Title section with professional styling
• Date range selector for filtering data
• Dynamic chart visualization
• Interactive data table
• Export functionality
• Toast notifications for user feedback

Key Features Demonstrated

• Data visualization using sp_api_modal_chart
• Interactive tables with sp_api_modal_table
• Excel export functionality
• Date range selection
• Toast notifications
• Modal dialog management
• Button handling
• Keyboard shortcuts

Best Practices

• Proper variable declaration and synchronization
• Clean temporary table management
• Structured UI layout
• Error handling
• Proper parameter passing to stored procedures
• Consistent styling using Bootstrap classes

To run this demo in TSQL.APP:

• Copy the entire script
• Paste it into your TSQL.APP action script editor
• Execute the script

You'll see a fully functional dashboard with:

• A bar chart showing Revenue vs Profit
• A detailed data table
• Export to Excel capability
• Date range selection
• Interactive buttons with keyboard shortcuts
• Toast notifications for user feedback

The demo shows how TSQL.APP can create sophisticated business applications using just T-SQL, without needing separate frontend development.