Monthly Data Rollup Trigger

Overview

On the 1st of each month, this flow executes a stored procedure that aggregates the prior month's transactional data into a summary table, then verifies the rollup and sends a styled HTML notification email with the results. Demonstrates calling pre-built SQL logic from Power Automate — separation of concerns between database logic and orchestration.

Use Case

Finance and IT teams often need to aggregate transactional data at month-end for reporting, dashboards, and compliance. Rather than embedding complex SQL aggregation logic directly into a Power Automate flow, this pattern calls a stored procedure that encapsulates all the rollup logic. The flow handles orchestration (scheduling, verification, notification) while the database handles computation. This keeps the flow portable across environments — only the stored procedure name and connection details need to change.

The flow is ideal for teams that:

• Finance teams running month-end aggregation jobs for reporting and compliance
• IT admins who want to keep heavy aggregation logic inside SQL while Power Automate handles orchestration
• Teams that need a portable rollup pattern that moves cleanly between environments by swapping env vars

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Create the stored procedure in your target SQL Server database

The flow expects a stored procedure that accepts a @ReportMonth parameter in YYYY-MM format. Example: ``sql CREATE PROCEDURE [dbo].[usp_MonthlyDataRollup] @ReportMonth NVARCHAR(7) AS BEGIN INSERT INTO [dbo].[MonthlySummary] (RollupDate, MetricName, MetricValue) SELECT CAST(@ReportMonth + '-01' AS DATE), Category, SUM(Amount) FROM [dbo].[Transactions] WHERE FORMAT(TransactionDate, 'yyyy-MM') = @ReportMonth GROUP BY Category; END

Update environment variables

Set your server name, database, stored procedure name, summary table, and notification email in the five flowlibs_* environment variables that ship with the solution.

Authorize connections

Open the flow in the designer and complete the SQL Server and Office 365 Outlook connection prompts to sign in with the accounts that should run the queries and send the digest.

Turn on the flow

Once env vars and connections are set, switch the flow on — it will execute on the 1st of the next month at 2:00 AM UTC.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

formatDateTime(addToTime(utcNow(), -1, 'Month'), 'yyyy-MM')

concat('EXEC ', variables('varStoredProcName'), ' @ReportMonth = ''', formatDateTime(addToTime(utcNow(), -1, 'Month'), 'yyyy-MM'), '''')

outputs('Verify_Summary_Table_Count')?['body']?['resultsets']?['Table1']?[0]?['RowCount']

coalesce(outputs('...')?['body']?['resultsets']?['Table1']?[0]?['LatestRollup'], 'N/A')