How to Build a Serverless Report Server with Azure Functions and SendGrid

Automated reports are critical for keeping stakeholders informed on key business metrics and KPIs. Traditionally, companies have provisioned and managed report servers on-premises or in the cloud to schedule and distribute these reports. However, with the rise of serverless computing, it‘s now possible to build highly scalable reporting solutions without having to worry about server infrastructure.

In this guide, we‘ll walk through how to build a modern, serverless report server using Azure Functions and SendGrid. Azure Functions is an event-driven serverless compute platform that allows you to execute code without managing servers. SendGrid is a cloud-based email service that provides reliable email delivery and flexible templating options. Together, they enable you to schedule data-driven reports and deliver them to stakeholders via email in a fully managed, serverless way.

Why Go Serverless for Reporting?

Before we dive into the implementation, let‘s discuss some of the key benefits of using a serverless approach for reporting:

1. Cost-effective – With serverless, you only pay for the compute resources you consume on a per-execution basis. For scheduled reports that may only run a few times a day, this can lead to significant cost savings compared to running a dedicated report server 24/7.

2. Scalable – Serverless platforms like Azure Functions can instantly scale out to handle high-volume reporting workloads. You don‘t need to provision servers ahead of time for peak demands.

3. Less administrative overhead – Since there are no servers to manage, you can spend less time on infrastructure tasks like patching, updates, and monitoring. The serverless platform takes care of these responsibilities for you.

4. Flexible scheduling – Serverless functions can be triggered on a schedule, allowing you to generate reports at any desired frequency without needing a continuously running server process.

5. Focused development – Serverless lets developers focus their efforts on writing the report generation logic, rather than server plumbing and configuration.

Azure Functions Overview

Azure Functions is Microsoft‘s event-driven serverless compute offering. It supports a variety of programming languages including C#, Java, JavaScript, Python, and PowerShell.

With Azure Functions, you write small pieces of code called "functions" that execute in response to events or on a schedule. The platform dynamically allocates compute resources when a function is triggered and scales the number of function instances based on demand. You are billed only for the resources consumed during function execution.

Azure Functions integrates with many other Azure services and third-party systems. It provides a declarative binding model that allows you to easily read from and write to databases, message queues, storage services, and more. You can also expose functions as HTTP endpoints, making them accessible from web and mobile apps.

For our serverless reporting scenario, we‘ll use a Timer trigger to execute a function on a schedule and retrieve data from a SQL database. We‘ll then format the data into an HTML email template and send it out via SendGrid.

SendGrid Email Templates

SendGrid offers a powerful email template engine that allows you to store your email layouts in the cloud and merge them with dynamic data at send time. Templates support substitution variables using Handlebars syntax.

With SendGrid templates, you can:

1. Manage email layouts separately from your application code
2. Reuse templates across multiple email sends
3. Modify templates without redeploying code
4. Dynamically inject values into templates at run-time

SendGrid templates are a great fit for report emails, as they provide a way to decouple your report layout from the data. You can design pixel-perfect report templates using HTML/CSS and use Handlebars to insert your data variables.

Implementation Steps

Now let‘s walk through the steps to build a serverless report server using Azure Functions and SendGrid.

1. Create an Azure Functions App

First, create a new Functions App in the Azure Portal. Choose a unique name, select a resource group, and specify the desired region. For the hosting plan, you can select either a Consumption plan (billed per-execution) or an App Service plan (billed per server) based on your needs.

2. Install the Azure Functions Extension for VS Code

We‘ll be using Visual Studio Code to develop our report function locally. Install the Azure Functions extension for VS Code from the marketplace. This extension provides tooling to create, debug, and deploy functions right from VS Code.

3. Create a Timer Trigger Function

In VS Code, create a new Azure Functions project and add a Timer Trigger function. Timer triggers use NCronTab expressions to specify a schedule. For example, the following expression would run a function every night at midnight:

0 0 0 * * * 

4. Retrieve Report Data

Next, we need to retrieve the data for our report. In this example, we‘ll query a SQL database using the Node.js tedious library. First, install the library:

npm install tedious

Then, add the following code to your function to query the database and retrieve the SKU data:

const { Connection, Request } = require(‘tedious‘);
const config = {
userName: process.env.SQL_USERNAME,
password: process.env.SQL_PASSWORD,
server: process.env.SQL_SERVER,
options: {
encrypt: true,
database: process.env.SQL_DATABASE
}
};
function getChangedSkus() {
return new Promise((resolve, reject) => {
const connection = new Connection(config);
const query =  SELECT Sku, Quantity, CONVERT(varchar, Modified, 0) as Modified FROM Inventory WHERE Modified >= dateadd(day, -1, getdate()) ;
connection.on(‘connect‘, err => {
  if (err) reject(err);
  let request = new Request(query, err => {
    if (err) reject(err);
  });

  const results = [];

  request.on(‘row‘, columns => {
    let result = {};
    columns.forEach(column => {
      result[column.metadata.colName] = column.value;
    });
    results.push(result);
  });

  request.on(‘doneProc‘, () => {
    resolve(results);
  });

  connection.execSql(request);
});
});

}
Make sure to store your database connection settings as environment variables for security.
5. Create a SendGrid Template
Log into the SendGrid portal and create a new dynamic template. Design your report layout using HTML/CSS. Use Handlebars syntax to specify where you want your dynamic data to be inserted. For example:
<table>
  <thead>
    <tr>
      <th>SKU</th>
      <th>Quantity</th> 
      <th>Last Modified</th>
    </tr>
  </thead>
  <tbody>
  {{#each skus}}
    <tr>
      <td>{{Sku}}</td>
      <td>{{Quantity}}</td>
      <td>{{Modified}}</td>
    </tr>
  {{/each}}
  </tbody>
</table>

Save your template and make note of the template ID.
6. Send the Report Email
Lastly, we need to send our SKU data to the SendGrid template to generate and send the email. Add the following code to your function:
const sg = require(‘@sendgrid/mail‘);
function sendEmail(skus) {
sg.setApiKey(process.env.SENDGRID_API_KEY);
const msg = {
to: ‘[email protected]‘,
from: ‘[email protected]‘,
subject: ‘Daily SKU Report‘,
templateId: process.env.SENDGRID_TEMPLATE_ID,
dynamicTemplateData: {
skus: skus
}
};
return sg.send(msg);
}

Here we use the SendGrid Node.js library to send an email using our template. We specify the recipient, sender, subject, template ID, and the dynamic SKU data.
Your final function code should look something like:
const { Connection, Request } = require(‘tedious‘);
const sg = require(‘@sendgrid/mail‘);
module.exports = async function (context, myTimer) {
const skus = await getChangedSkus();
await sendEmail(skus);
}
// DB query function omitted for brevity
function sendEmail(skus) {
// SendGrid code omitted for brevity
}

Testing and Deployment
To test your function locally, you can use the func start command to run the Functions runtime. Trigger your function manually in the portal to verify that emails are being sent successfully. 
Once you are ready to deploy to Azure, use the "Publish" option in the Functions extension. You‘ll need to configure your app settings in Azure to store your database connection string and SendGrid API key securely.
Monitoring and Troubleshooting
Azure Functions provides built-in logging and monitoring capabilities. You can view function executions and logs in the Azure Portal. Be sure to configure an Application Insights instance for advanced logging and analytics.
Some common issues you may encounter:


Emails not being sent – Verify your SendGrid API key is valid and that your template syntax is correct. Check the SendGrid portal for any rejected emails.


Function not triggering on schedule – Double-check that your cron expression is valid and matches your desired schedule. 


Database connection errors – Ensure your connection string is correct and that your SQL server firewall rules allow access from Azure


Advanced Scenarios
This guide covered a basic example of generating a daily SKU report email using Azure Functions and SendGrid. Here are some ideas for more advanced scenarios:

Attach an Excel file with the raw data to the email
Embed charts or graphs in the email to visualize trends
Implement user subscription preferences to let recipients customize their report frequency and contents
Add retry logic and dead-letter queues to handle transient email failures

Summary
In this guide, we saw how to build a serverless report server using Azure Functions and SendGrid. Serverless computing provides a scalable, cost-effective way to generate and deliver reports without managing server infrastructure.
Azure Functions lets you run reporting logic on a schedule, while SendGrid enables pixel-perfect email templates that can be populated with dynamic data. Together, they offer a powerful platform for automating all kinds of data-driven reports.
Some key takeaways:

Serverless is ideal for scheduled workloads like reports, as you only pay for the compute you use
Use Timer triggers to execute reporting functions on a schedule
Query databases directly from your function code to retrieve report data
Use SendGrid dynamic templates to design report layouts that can be populated with data at send time
Monitor and troubleshoot using Azure portal tools and Application Insights

I hope this guide has been helpful in demonstrating the potential of serverless for modernizing your reporting systems. For more information, check out the Azure Functions and SendGrid documentation. Happy reporting!


	
Related