Custom SQL Dashboard Digest

Overview

FlowLibs - Custom SQL Dashboard Digest is a scheduled Power Automate Cloud Flow that executes a fully customizable aggregate SQL query (COUNT, SUM, AVG, etc.) against a SQL Server database, formats the results into a professionally styled HTML email with a gradient header, data table, and footer, and delivers it as a daily executive digest to a configurable recipient list.

Key Value: Unlike the simpler "Get Rows" action, this flow uses ExecutePassThroughNativeQuery_V2 to run raw SQL — enabling JOINs, UNION ALL, GROUP BY, and multi-table aggregations that are impossible with standard row-based connectors. The entire query is stored in an environment variable, so business users can update KPIs without editing the flow logic.

Use Case

Finance and executive teams need a daily digest of key business metrics (total orders, revenue MTD, average order value, pending orders) delivered to their inbox before business hours. IT maintains the SQL query; leadership gets a clean, branded email every morning at 7 AM ET.

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.

Change the SQL query

Update the flowlibs_DashboardDigestSqlQuery environment variable value. The query must return exactly three columns: MetricName (VARCHAR), MetricValue (VARCHAR), and MetricDetail (VARCHAR). Use UNION ALL to combine multiple metric queries.

Change the schedule

Edit the Recurrence trigger — modify hours, minutes, frequency, or timeZone to adjust delivery time.

Change the recipient

Update flowlibs_DigestRecipientEmail. For multiple recipients, use a semicolon-separated list.

Point to a different database

Update flowlibs_SqlServerName and flowlibs_SqlDatabaseName environment variables.

Customize email styling

Edit the Compose action HTML template — the gradient colors, table styles, and footer text are all inline CSS.

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.

@variables('varSqlQuery')

@outputs('Run_Dashboard_Query')?['body']?['resultsets']?['Table1']

{ "html": "<tr><td>@{item()?['MetricName']}</td><td>@{item()?['MetricValue']}</td><td>@{item()?['MetricDetail']}</td></tr>" }

replace(replace(replace(string(body('Select_HTML_Table_Rows')),'"},{"html":"',''),'[{"html":"',''),'"}]','')

@{concat('Executive Dashboard Digest - ', utcNow('yyyy-MM-dd'))}

utcNow('yyyy-MM-dd') and utcNow('HH:mm')