report-distributor

What this skill does

# Report Distributor

Produce a complete automated report distribution specification. Every report that must be delivered to stakeholders who do not access Power BI directly gets an explicit delivery mechanism, schedule, recipient list, and archive process.

## Delivery Channel Selection

Choose the delivery channel based on the recipient and the report's purpose:

| Scenario | Recommended Channel | Why |
|----------|--------------------|----|
| Executive wants PDF snapshot in email inbox | Power BI subscription (email with PDF attachment) | Simple, no technical setup, recipient needs no Power BI license |
| Team reviews report in weekly standup | Teams channel post (via Power Automate + export API) | Report appears in the channel where discussion happens |
| Report posted to SharePoint for reference | Power Automate → export PDF → save to SharePoint library | Creates an auditable archive alongside other business documents |
| Alert only when a threshold is breached | Power Automate conditional distribution | Prevents report fatigue — only sends when action is needed |
| External parties (carriers, regulators) | Export PDF via Power Automate → send from shared mailbox | Keeps a copy in the mailbox, uses firm email identity |

## Power BI Subscriptions (Email Delivery)

Native Power BI subscriptions are the simplest delivery mechanism. No code required.

**Configuration** (in Power BI Service > Report > Subscribe to report):

| Field | Value |
|-------|-------|
| Subject | [Report Name] — [Frequency] Report — {Report Page} |
| To | Recipient email addresses (comma-separated, max 50) |
| Frequency | Daily / Weekly / After data refresh |
| Time | Specify time in the report's timezone |
| Include attachment | PDF (recommended for archival) or PowerPoint |
| Link to report | Yes — include the clickable link |
| Start date | [Go-live date] |
| End date | Leave blank (ongoing) |

**Subscription matrix** (one row per subscription):

| Report | Page | Recipients | Frequency | Time (ET) | Attachment |
|--------|------|-----------|-----------|-----------|------------|
| Agency Production Dashboard | Executive Summary | Principal, VP Operations | Weekly | Monday 7:00 AM | PDF |
| Agency Production Dashboard | Producer Detail | Sales Manager | Weekly | Monday 7:00 AM | PDF |
| Claims Operations | Claims Summary | Claims Manager, Operations Director | Daily | 7:30 AM | PDF |
| Monthly Board Report | All pages | Board members DL | Monthly | 1st of month 7:00 AM | PDF |
| Renewal Pipeline | My Renewals | All producers (individual subscription per producer) | Daily | 8:00 AM | Link only (RLS personalizes the view) |

**Limitation of built-in subscriptions**: All recipients of a single subscription see the same data snapshot, regardless of their RLS role. For role-personalized content (each producer sees only their data), use one subscription per producer OR use the Power Automate export API with per-user embed tokens.

**License requirement**: Subscription sender must have Power BI Pro. Recipients do not need Power BI licenses to receive the email and PDF attachment.

## Power Automate Distribution Flow

Use Power Automate when: you need to export and send with conditional logic, attach the PDF to a shared mailbox thread, post to Teams, or trigger based on a threshold rather than a schedule.

**Flow specification — Weekly Operations Report**:

```
Flow name: Distribute Weekly Operations Report
Flow type: Scheduled cloud flow
Frequency: Weekly — every Monday at 6:30 AM ET

Step 1: Get report embed token
  Connector: HTTP
  Method: POST
  URI: https://api.powerbi.com/v1.0/myorg/groups/{env:WorkspaceId}/reports/{env:ReportId}/GenerateToken
  Authentication: Service principal (client credentials)
  Body: { "accessLevel": "View" }
  Output: embedToken (from response body)

Step 2: Export report to PDF
  Connector: HTTP
  Method: POST
  URI: https://api.powerbi.com/v1.0/myorg/groups/{env:WorkspaceId}/reports/{env:ReportId}/ExportTo
  Body: {
    "format": "PDF",
    "powerBIReportConfiguration": {
      "pages": [{ "pageName": "ExecutiveSummary" }, { "pageName": "ProducerDetail" }]
    }
  }
  Output: exportId (from response)

Step 3: Poll until export complete
  Do until exportStatus = "Succeeded"
    Delay: 10 seconds
    GET export status: /reports/{reportId}/exports/{exportId}
  Output: fileContent URL

Step 4: Get PDF file content
  Connector: HTTP
  Method: GET
  URI: [fileContent URL from Step 3]
  Output: pdfBytes

Step 5: Send email with PDF attachment
  Connector: Office 365 Outlook
  From: operations@[firm].com (shared mailbox — Send As)
  To: [environment variable: WeeklyReportDL]
  Subject: Weekly Operations Report — @{formatDateTime(utcNow(), 'MMMM d, yyyy')}
  Body: [Template — see below]
  Attachment name: Weekly-Operations-Report-@{formatDateTime(utcNow(), 'yyyy-MM-dd')}.pdf
  Attachment content: pdfBytes

Step 6: Save copy to SharePoint
  Connector: SharePoint
  Action: Create file
  Site: [operations site URL]
  Folder: /Reports/Weekly-Operations/[Year]
  File name: Weekly-Operations-@{formatDateTime(utcNow(), 'yyyy-MM-dd')}.pdf
  File content: pdfBytes

Step 7: Post to Teams channel
  Connector: Microsoft Teams
  Team: Operations
  Channel: Management-Reports
  Message: "Weekly Operations Report for week ending @{formatDateTime(utcNow(), 'MMM d')} is ready. PDF attached to this week's email distribution. [View live report]({env:ReportURL})"
```

**Email body template**:
```
Subject: Weekly Operations Report — [Date]

[Firm Name] — Weekly Operations Summary

Report period: [Start Date] through [End Date]

This week's report is attached as a PDF. Key sections:
• Executive Summary — Production vs. target, retention snapshot
• Producer Detail — Individual producer performance
• Open Items — Renewals due next 30 days, claims requiring attention

To view the live interactive report, click here: [Report URL]

Questions? Contact [Operations Manager name] or reply to this email.

This report is generated automatically every Monday morning. 
Report data last refreshed: [Last refresh time from dataset]
```

## Conditional Distribution

Send the report only when a specific condition is met (e.g., a KPI falls below threshold):

**Flow specification — Claims Alert**:

```
Flow name: Send Claims Alert When Threshold Exceeded
Flow type: Scheduled — daily at 8:00 AM

Step 1: Get KPI value from dataset
  Connector: Power BI
  Action: Run a query against a dataset (DAX)
  Workspace: [workspace]
  Dataset: Claims Operations
  DAX query: 
    EVALUATE
    ROW(
      "OpenClaimsOver60Days", CALCULATE([Open Claim Count], Fact_Claims[DaysOpen] > 60)
    )
  Output: OpenClaimsOver60Days value

Step 2: Condition
  If OpenClaimsOver60Days > 10:
    Step 2a: Export report page to PDF (claims aging page only)
    Step 2b: Send email to Claims Manager and Director
      Subject: [ATTENTION] @{OpenClaimsOver60Days} claims over 60 days — review required
      Body: As of today, @{OpenClaimsOver60Days} claims have been open for more than 60 days. 
            This exceeds the threshold of 10. The aging report is attached.
      Attachment: Claims aging PDF

  Else:
    Step 2c: No action (do not send)
    [Optional: Log "no alert needed" to SharePoint list for audit trail]
```

## Recipient Management

Maintain distribution lists in a SharePoint list rather than hardcoding email addresses in flows:

**SharePoint list: Report Distribution List**

| Column | Type | Example |
|--------|------|---------|
| ReportName | Text | Weekly Operations Report |
| RecipientEmail | Text | [email protected] |
| RecipientRole | Text | Principal |
| Active | Boolean | Yes |
| StartDate | Date | 2026-01-01 |
| EndDate | Date | (blank = ongoing) |

**Flow modification**: In Step 5 (send email), instead of a hardcoded "To" address, use:
```
Get items from SharePoint: ReportDistributionList
Filter: ReportName = "Weekly Operations Report" AND Active = "Yes" AND StartDate <= Today AND (EndDate = blank OR