Introduction: Power BI Paginated Reports are ideal for creating highly formatted, pixel-perfect layouts optimized for printing or PDF generation. By integrating OpenAI with Power BI Paginated Reports using Azure Functions, you can enhance your reports with AI-generated insights and content. This blog post provides a step-by-step guide on how to integrate OpenAI with Power BI Paginated Reports using Azure Functions and an intermediary SQL Server database.
Prerequisites:
- An OpenAI API key
- An Azure account
- Power BI Report Builder
- Basic knowledge of Power BI Paginated Reports, Azure Functions, and C#
Step 1: Create a SQL Server database
- Set up a SQL Server database or use an existing one.
- Create a new table to store the AI-generated content:
CREATE TABLE OpenAI_Responses (
ID INT PRIMARY KEY IDENTITY(1,1),
Prompt NVARCHAR(MAX),
GeneratedText NVARCHAR(MAX),
DateGenerated DATETIME
);
Step 2: Create an Azure Function to call the OpenAI API and store the AI-generated content in the SQL Server database
- Set up an Azure Function App with an HTTP trigger and follow the instructions to create a new function.
- Add the necessary NuGet packages to call the OpenAI API (e.g.,
OpenAI) and connect to SQL Server (e.g.,System.Data.SqlClient). - Modify the Azure Function code to call the OpenAI API, and insert the AI-generated content into the SQL Server table.
using System.Data.SqlClient;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using OpenAI;
public static class OpenAIIntegrationFunction
{
[FunctionName("OpenAIIntegrationFunction")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
// Get the 'prompt' parameter from the query string
string prompt = req.Query["prompt"];
// Use OpenAI API Key
string openaiApiKey = Environment.GetEnvironmentVariable("OPENAI_API_KEY");
// Initialize the OpenAI API client
var apiClient = new OpenAIApiClient(apiKey: openaiApiKey);
// Set up the completion request
var completions = await apiClient.Completions.CreateAsync(
engine: "text-davinci-002",
new CompletionRequest
{
Prompt = prompt,
MaxTokens = 50,
N = 1,
Stop = null,
Temperature = 0.7,
}
);
string generated_text = completions.Choices[0].Text.Trim();
// Replace with your SQL Server connection string
string connectionString = "your_sql_server_connection_string";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("INSERT INTO OpenAI_Responses (Prompt, GeneratedText, DateGenerated) VALUES (@Prompt, @GeneratedText, @DateGenerated)", connection))
{
command.Parameters.AddWithValue("@Prompt", prompt);
command.Parameters.AddWithValue("@GeneratedText", generated_text);
command.Parameters.AddWithValue("@DateGenerated", DateTime.UtcNow);
await command.ExecuteNonQueryAsync();
}
}
return new OkObjectResult("Data saved to the database.");
}
}
Copy and paste this code into your Azure Function App. Replace the your_sql_server_connection_string placeholder with your actual SQL Server connection string. This code assumes you have already set up an OpenAI API key as an environment variable within your Azure Function App.
- Save the function and test it to ensure it inserts the AI-generated content into the SQL Server table.
Step 3: Create a Power BI Paginated Report
- Open Power BI Report Builder.
- Create a new report or open an existing one.
- Add a new “Parameter” to the report:
- In the “Report Data” pane, right-click “Parameters” and click “Add Parameter.”
- Name the parameter “Prompt.”
- Set the data type to “Text.”
- Provide a default value or leave it blank.
- Add a “Textbox” to the report and set its value to the “Prompt” parameter:
=Parameters!Prompt.Value
Step 4: Connect the Power BI Paginated Report to the SQL Server database
- In the “Report Data” pane, right-click “Data Sources” and click “Add Data Source.”
- Choose “Microsoft SQL Server” as the connection type and provide a name for the data source.
- In the “Connection string” field, enter your SQL Server connection string.
- Click “OK” to add the data source.
- In the “Report Data” pane, right-click “Datasets” and click “Add Dataset.”
- Choose the SQL Server data source you just created and click “Query Designer.”
- In the “Query Designer,” enter a SQL query to fetch the latest AI-generated content for the given prompt:
SELECT TOP 1 GeneratedText
FROM OpenAI_Responses
WHERE Prompt = @Prompt
ORDER BY DateGenerated DESC
8. Add the “Prompt” parameter to the query by clicking
“Add Parameter” in the “Query Designer.” 9. Close the “Query Designer” and click “OK” to add the dataset.
- Add a “Textbox” to the report and set its value to the AI-generated text:
=First(Fields!GeneratedText.Value, "Dataset1")
Conclusion: You now have a Power BI Paginated Report that displays AI-generated content based on the prompt parameter. When the report is run, it will retrieve the latest AI-generated content for the given prompt from the SQL Server database and display it in the report. To update the AI-generated content in the SQL Server database, you can manually call the Azure Function with the specified prompt, or you can create a separate application to automate this process. The Azure Function will then call the OpenAI API, generate the text, and insert it into the SQL Server table.
This approach allows you to leverage the Power BI Paginated Report’s native support for SQL Server as a data source while still incorporating AI-generated content from the OpenAI API. It involves additional steps and requires an intermediary database, but it provides a viable solution for integrating OpenAI with Power BI Paginated Reports
This blogpost was created with help from ChatGPT Pro and Paginated Report Bear.
Christopher Finlan 