ChatGPT
Perplexity
Claude
GrokTL;DR: Learn how to integrate AI-powered forecasting into your Blazor Pivot Table using Azure OpenAI. This guide walks through building a predictive dashboard that visualizes future trends based on historical data.
In today’s data-driven world, developers are expected to build dashboards that do more than just visualize, they must predict. This blog walks you through integrating AI-powered forecasting into a Syncfusion Blazor Pivot Table, enabling your app to anticipate future trends using Azure OpenAI.
Whether you’re building sales dashboards or inventory trackers, this guide helps you turn historical data into actionable insights.
Prerequisites
Before diving in, make sure you have:
- Visual Studio 2022
- AI Service Provider (we’ll use Azure OpenAI for this demo)
Step 1: Set up your Blazor Web App and Azure OpenAI
First, integrate the Syncfusion Blazor Pivot Table component in a Blazor Web App by following the steps in the documentation guide. To ensure seamless functionality for predicting future data, integrate additional Syncfusion Blazor components, such as Dialog and Dropdown, by installing the following dependencies via NuGet Package Manager or the Package Manager Console:
- Syncfusion.Blazor.DropDowns
- Syncfusion.Blazor.Inputs
- Syncfusion.Blazor.Navigations
- Syncfusion.Blazor.Popups
- Syncfusion.Blazor.Spinner
Next, deploy an Azure OpenAI resource by following the official setup guide and generating your OpenAI API key using the documentation. You’ll need this to connect your app to the AI service. After obtaining the key and endpoint, integrate them into the Blazor application.
Step 2: Install Microsoft’s AI extension packages
To integrate AI capabilities, install the Microsoft.Extensions.AI.OpenAI and Azure.AI.OpenAI packages via NuGet. Alternatively, run these commands in Package Manager Console:
Install-Package Microsoft.Extensions.AI.OpenAI -Version 9.6.0-preview.1.25310.2
Install-Package Azure.AI.OpenAI -Version 2.1.0
Step 3: Create the OpenAI service class
The OpenAI service class handles communication with Azure OpenAI. This class sends a prompt containing your Pivot Table’s data and retrieves the AI-generated forecast results.
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
namespace BlazorAI.Services
{
public class OpenAIService
{
private readonly IHttpClientFactory _httpClientFactory;
private readonly string _apiKey;
public OpenAIService(IHttpClientFactory httpClientFactory)
{
_httpClientFactory = httpClientFactory;
}
public async Task<string> GetResponseFromAzureOpenAI(string prompt)
{
var requestContent = new
{
messages = new[]
{
new { role = "system", content = "You are a helpful assistant." },
new { role = "user", content = prompt }
}
};
var httpClient = _httpClientFactory.CreateClient();
var requestBody = new StringContent(JsonSerializer.Serialize(requestContent), Encoding.UTF8, "application/json");
// Azure OpenAI endpoint and API key
var azureEndpoint = "your-azure-endpoint-url";
var azureApiKey = " your-api-key";
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", azureApiKey);
httpClient.DefaultRequestHeaders.Add("api-key", azureApiKey); // Adding the API key in headers
httpClient.DefaultRequestHeaders.Add("User-Agent", "azure-openai-client"); // Optional user agent header
var response = await httpClient.PostAsync(azureEndpoint, requestBody);
response.EnsureSuccessStatusCode();
var responseContent = await response.Content.ReadAsStringAsync();
var jsonResponse = JsonDocument.Parse(responseContent);
return jsonResponse.RootElement.GetProperty("choices")[0].GetProperty("message").GetProperty("content").GetString();
}
}
} The OpenAI service method performs the following steps:
- Constructs a request with a system message (You are a helpful assistant) and the user prompt.
- Authenticates using your Azure OpenAI API key and endpoint.
- Sends the request and parses the response to extract the forecasted data.
Note: Replace endpoint URL and API key with your actual Azure OpenAI credentials.
Step 4: Register services
We need to register the OpenAI and Syncfusion Blazor services in Program.cs file.
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddTransient<OpenAIService>(sp =>
{
var httpClientFactory = sp.GetRequiredService<IHttpClientFactory>();
return new OpenAIService(httpClientFactory);
}); Step 5: Prepare the data source
Before integrating AI forecasting, prepare the data for the Pivot Table. Create a C# file to store bike sales data and reference it in the Home page for a seamless setup. This will serve as the data source for your Pivot Table.
namespace PivotTableAI.Components.Data
{
public class PivotProductDetails
{
public int Sold { get; set; }
public double Amount { get; set; }
public string Country { get; set; }
public string Product_Categories { get; set; }
public string Products { get; set; }
public string Year { get; set; }
public static List<PivotProductDetails> GetProductData()
{
List<PivotProductDetails> productData = new List<PivotProductDetails>();
productData.Add(new PivotProductDetails { Sold = 413, Amount = 3493.5, Country = "Germany", Product_Categories = "Accessories", Products = "Bottles and Cages", Year = "FY 2022" });
productData.Add(new PivotProductDetails { Sold = 160, Amount = 2604.8, Country = "Germany", Product_Categories = "Accessories", Products = "Helmets", Year = "FY 2023" });
// Add more data as needed
return productData;
}
}
}
Reference it in Home.razor using:
@using PivotTableAI.Components.Data Step 6: Integrating AI forecasting into the Pivot Table
This is where everything comes together. The UI allows users to select a future year, trigger forecasting, and view results directly in the Pivot Table. Forecasted data is highlighted to distinguish it from historical data, facilitating trend analysis. The Home page orchestrates the Pivot Table, user input Dialog, and forecasting logic.
Here’s the code for Home.razor:
@rendermode InteractiveServer
@using Syncfusion.Blazor.PivotView
@using Syncfusion.Blazor.DropDowns
@using Syncfusion.Blazor.Inputs
@using Syncfusion.Blazor.Navigations
@using Syncfusion.Blazor.Popups
@using Syncfusion.Blazor.Spinner
@using Newtonsoft.Json
@inject OpenAIService ChatGptService
@using PivotTableAI.Components.Data
<SfDialog Target="#PivotView" @ref="Dialog" CssClass="AI-dialog" MinHeight="200px" Height="300px" ShowCloseIcon="true" @bind-Visible="@Visibility">
<DialogTemplates>
<Header> AI Assist </Header>
<Content>
<p class="category-title">Predictive Analytics Query:</p>
<div class="inline">
<span id="contentText" class="dropdown-size">
Provide future data points up to the year
<SfDropDownList TValue="string" TItem="Data" CssClass="inlinecss" Placeholder="Select a Year" DataSource="@InlineYears" @bind-Value="@TextValue" Width="45px" PopupHeight="200px" PopupWidth="140px">
<DropDownListFieldSettings Value="Name"></DropDownListFieldSettings>
</SfDropDownList>
along with the existing data.
</span>
</div>
</Content>
</DialogTemplates>
<DialogButtons>
<DialogButton IsPrimary="true" Content="Submit" OnClick="@OnBtnClick" />
</DialogButtons>
</SfDialog>
<SfPivotView ID="PivotView" @ref="pivotRef" TValue="PivotProductDetails" Width="1200" Height="500" ShowFieldList="true" ShowToolbar="true" Toolbar="@toolbar">
<PivotViewTemplates>
<CellTemplate>
@{
var data = (context as AxisSet);
if (data != null)
{
if ((data.Axis == "value" || (data.Axis == "value" && data.ColumnHeaders.ToString() == "Grand Total")) && !(data.RowHeaders as string).Contains('.') && predictivePoints.Contains(data.ColumnHeaders.ToString()))
{
pivotRef.PivotValues[data.RowIndex][data.ColIndex].CssClass = "e-custom-class";
@data.FormattedText
}
else
{
@data.FormattedText
}
}
}
</CellTemplate>
</PivotViewTemplates>
<PivotViewDataSourceSettings DataSource="@data">
<PivotViewColumns>
<PivotViewColumn Name="Year"></PivotViewColumn>
</PivotViewColumns>
<PivotViewRows>
<PivotViewRow Name="Products"></PivotViewRow>
</PivotViewRows>
<PivotViewValues>
<PivotViewValue Name="Sold"></PivotViewValue>
<PivotViewValue Name="Amount"></PivotViewValue>
</PivotViewValues>
</PivotViewDataSourceSettings>
<PivotViewEvents TValue="PivotProductDetails" ToolbarRendered="ToolbarRender"></PivotViewEvents>
</SfPivotView>
<SfSpinner @ref="@spinnerObj"></SfSpinner>
@code {
private List<string> predictivePoints = new List<string>()
{
"FY 2025", "FY 2026", "FY 2027", "FY 2028", "FY 2029"
};
public string TextValue { get; set; } = "2025";
public class Data
{
public string Name { get; set; }
public string ID { get; set; }
}
List<Data> InlineYears = new List<Data>
{
new Data() { Name = "2025", ID = "1" },
new Data() { Name = "2026", ID = "2" },
new Data() { Name = "2027", ID = "3" },
new Data() { Name = "2028", ID = "4" },
new Data() { Name = "2029", ID = "5" },
};
private SfSpinner spinnerObj;
private string Description = string.Empty;
private SfDialog Dialog { get; set; }
private bool Visibility { get; set; } = false;
private SfPivotView<PivotProductDetails> pivotRef;
public List<PivotProductDetails> data { get; set; }
public List<PivotProductDetails> cloneDataSource { get; set; }
public class PivotReport
{
public List<PivotProductDetails> DataSource { get; set; }
public List<PivotViewColumn> Columns { get; set; }
public List<PivotViewRow> Rows { get; set; }
public List<PivotViewValue> Values { get; set; }
}
public List<Syncfusion.Blazor.PivotView.ToolbarItems> toolbar = new List<Syncfusion.Blazor.PivotView.ToolbarItems> {
Syncfusion.Blazor.PivotView.ToolbarItems.FieldList,
};
protected override void OnInitialized()
{
this.cloneDataSource = PivotProductDetails.GetProductData().ToList();
this.data = new List<PivotProductDetails>(cloneDataSource);
}
public void ToolbarRender(ToolbarArgs args)
{
args.CustomToolbar.Add(new ItemModel
{
Text = "AI Assist",
TooltipText = "AI Assist",
Click = EventCallback.Factory.Create<ClickEventArgs>(this, OpenDialog),
});
}
public async void OpenDialog(ClickEventArgs args)
{
await Dialog.ShowAsync();
}
private async Task OnBtnClick()
{
await Dialog.HideAsync();
Description = $"Provide future data points up to the year {TextValue} along with the existing data from the provided data source";
if (!string.IsNullOrEmpty(Description))
{
await spinnerObj.ShowAsync();
PivotReport pivot = new PivotReport()
{
DataSource = data,
Columns = pivotRef.DataSourceSettings.Columns,
Rows = pivotRef.DataSourceSettings.Rows,
Values = pivotRef.DataSourceSettings.Values,
};
var pivotReportJson = GetSerializedPivotReport(pivot);
// Refined prompt for incremental forecasting
string prompt = $"Given the following datasource and settings (such as rows, columns, values, and filters) bound in the pivot table:\n\n{pivotReportJson}\n\n" +
$"The datasource contains historical data and may include previously forecasted data for future years (e.g., FY 2025, FY 2026). Your task is to:\n" +
$"1. Preserve all existing data, including historical data (e.g., FY 2023, FY 2024) and any previously forecasted data for years up to {TextValue}.\n" +
$"2. Forecast data only for the year {TextValue} if it does not already exist in the datasource. Do not modify existing years.\n" +
$"3. If {TextValue} already exists, do not forecast for that year or any later years.\n" +
$"4. Generate meaningful forecasted values based on trends in the historical data (e.g., FY 2023, FY 2024). Do not return zeros unless the trend justifies it.\n" +
$"5. Ensure the forecasted 'Sold' and 'Amount' values are realistic and follow the patterns in the historical data (e.g., growth or decline trends).\n" +
$"Return the updated datasource and settings in JSON format only, without any additional information or content in the response.";
var result = await ChatGptService.GetResponseFromAzureOpenAI(prompt);
if (result != null)
{
PivotReport deserializeResult = DeserializeResult(result);
this.data = deserializeResult.DataSource ?? data;
pivotRef.DataSourceSettings.Rows = deserializeResult.Rows;
pivotRef.DataSourceSettings.Columns = deserializeResult.Columns;
pivotRef.DataSourceSettings.Values = deserializeResult.Values;
}
await spinnerObj.HideAsync();
}
else
{
this.data = cloneDataSource;
}
}
private string GetSerializedPivotReport(PivotReport report)
{
return JsonConvert.SerializeObject(report);
}
private PivotReport DeserializeResult(string result)
{
result = result.Replace("```json", "").Replace("```", "").Trim();
return JsonConvert.DeserializeObject<PivotReport>(result);
}
} Let’s break it down: How the code works
The Home.razor file is the heart of this implementation, combining the UI and logic to create a seamless forecasting experience. Let’s explore its key components step by step.
1. Crafting the user interface
- A dialog titled AI Assist lets users choose a year (2025–2029) via a dropdown.
- Clicking Submit triggers the forecasting logic.
- Custom CSS classes like AI-dialog and inline CSS are used for a polished look. You can find these styles on GitHub.
2. Setting up the Pivot Table
The SfPivotView component is where the data comes to life:
- Data source: It binds to a
List<PivotProductDetails>, which holds historical and forecasted bike sales data. - Layout: The table is structured with Year as columns, Products (e.g., Helmets, Tires) as rows, and Sold and Amount as values.
- Custom toolbar: The toolbar render event adds a handy AI Assist button to the toolbar. Clicking it opens the Dialog, making it easy for users to initiate forecasting.
- Highlighting forecasts: Using a cell template, forecasted years (like FY 2025) are highlighted in yellow with the e-custom-class. This visual cue helps users quickly distinguish AI-generated predictions from historical data. You can find the e-custom-class CSS class in the GitHub sample.
3. Forecasting logic
When the user selects a year and clicks the Submit button, it will initiate the forecasting process:
- Serialize the data: It captures the current Pivot Table data and report settings and serializes them into a PivotReport object.
- Craft a prompt for Azure OpenAI with clear instructions:
- Keep all existing data (historical and previously forecasted) intact.
- Forecast only for the selected year if it’s missing.
- Generate realistic values based on historical trends (e.g., FY 2023–2024 data).
- Send the prompt to Azure OpenAI via the OpenAI Service.
- Deserialize the response and update the Pivot Table with new forecasted data.
4. Enhancing the user experience
To ensure a smooth experience, the code includes thoughtful UX touches:
- Loading spinner: Displays while AI processes the request.
- Fallback logic: Reverts to original data if no forecast is generated.
This implementation combines powerful AI forecasting with an intuitive UI, making it easy for users to predict future trends while keeping the experience engaging and reliable.
Visualizing the results
Let’s look at the process in motion to better understand how this AI-powered forecasting works. A GIF illustrated below shows the full workflow, from selecting a year to seeing forecasted data appear in the Pivot Table. Forecasted values are highlighted for clarity.
GitHub reference
For more details, download the complete sample available on GitHub.
Beyond forecasting: Adaptive filtering and smart aggregation
With the Syncfusion Blazor Pivot Table, you’re not limited to predicting future data.
- Adaptive filtering: Quickly filter data based on user queries.
- Smart aggregation: Get intelligent suggestions for data presentation.
These features enhance your ability to explore and analyze data efficiently.
Adaptive Filtering
Smart Aggregation
Note: Explore the GitHub demo for a closer look at the code and implementation.
Conclusion
By combining the Syncfusion Blazor Pivot Table with Azure OpenAI, you unlock powerful forecasting capabilities. This integration empowers developers to build smarter dashboards that not only visualize data but also predict future trends, making your applications truly data-driven.
This approach, using Azure OpenAI and Syncfusion components, enables smarter, data-driven decisions in an intuitive format. Whether you’re forecasting sales, inventory, or resources, this solution empowers you to stay ahead.
If you’re an existing Syncfusion user, you can download the latest update from the license and downloads page. If you are new to Syncfusion, we offer a 30-day free trial so you can explore everything Essential Studio has to offer.
Need assistance? Reach out to us anytime via our support forum, support portal, or feedback portal. We’re here to help!