Power Dynamics
  • Power Dynamics
  • 🍕My name
  • D365 CE
    • Copilot
      • 🥐Add Copilot into Rich Text Control
    • Sales
      • Copilot in D365 Sales
      • Export record to PDF
    • Customer Experience
      • Contact Center (Omnichannel)
        • 🐚Internal Live Chat in MDA
        • 📢Add Chat Widget to Power Pages - Proactive Chat
      • Attachment Control
      • Case Routing: Basic Routing rule set
      • Custom SLA for any Entity
      • Customer Insight
        • Journey - Custom Trigger - Call Power Automate
    • Field Service
  • Power Platform
    • Analytics
    • Dataverse
      • Use case for the feature of Record ownership across Business Unit
      • 💡Workaround: Change the OOB "Exchange Rate" of a record
      • Using a Virtual Entity to store historical log (sample)
      • Disable Empty Address record
      • Using Adaptive card
      • Block Unmanaged Customizations
      • 🪃Recycle Bin on Dataverse (Preview)
    • Model Driven App
      • 💡Run SSRS report on MDA mobile
      • 💡Tip: Create a Security Role with "App Opener" minimum privileges
      • Tag @person to notify in MDA
      • Auto search on the Lookup field
      • Activities with multiple related records
      • Embedded Power BI - Dashboard
      • Embedded Power BI - Form
      • "Custom Help" - Is it help?
      • New modern Command Bar
      • Tip: Hide the button "Add Existing Record" on subgrid
      • Custom Page - Why not?
      • Hiding sensitive column data
    • Canvas App
    • Fabric & Power BI
      • New Text slicer
      • Dataverse link to Microsoft Fabric
      • 💡Leveraging Interactive Warehouse Floor Maps in Power BI
      • Calculation Group for Time Intelligence
    • Power Automate
      • ✅Sequential Approval
      • 📤Create an Appointment by Outlook Graph API
      • Dowload SSRS Report and Send email
      • Power Automate: Add a table in Email
      • 💡Tips: Get "DisplayName" of the Lookup field on Power Automate
      • E-sign: Power Automate & DocuSign
  • D365 Finance & Operation
    • General
      • 🇻🇳D365 FnO - Virtual entities: Use case
    • Finance
      • Consolidating Customer & Vendor Balances
    • Supply Chain
      • Landed Cost Series
        • Landed Cost- Essential Configuration
        • Landed Cost Scenarios 1
Powered by GitBook
On this page
  • My scenario
  • My proposal
  • Details of configuration.
  • Testing...

Was this helpful?

  1. Power Platform
  2. Power Automate

Dowload SSRS Report and Send email

#PowerAutomate, #SSRS, #HTTP, #SendEmail, #Attachment

PreviousCreate an Appointment by Outlook Graph APINextPower Automate: Add a table in Email

Last updated 1 year ago

Was this helpful?

Have a nice day, my friends!

Today, I noted how to use Power Auotomate download SSRS Report, and send it via email.

This is a question from my teammate and I feel it's very exciting because I have faced this requirement from many clients before. Last time, my dev team did the C# code to generate the report and attached this report to the email for sending. Now, after researching & learning, I try to use the Power Automate with action HTTP Request to do that.

My scenario

The customer is using the Work Order process:

  • Engineers use a Work Order to log inspection and work results at customer sites.

  • After work completion, the customer signs the Work Order mobile form.

  • The engineer marks the Work Order as complete, triggering an immediate Work Order Summary report (of the current Work Order) to be sent to the customer.

My proposal

I'm using the Power Automate to configure this scenario:

Components
Flow summary
  • Trigger: WO completed

Details of configuration.

For instance, I already created the report called WOSummary report, and runs on the Work Order main form.

When a Work Order is completed, the Power Automate will be triggered -> then download the WOSummary report and send it to the customer.

The configuration details are as below:

  • Trigger: Work Order Modified on field "Status Reason"

  • Filter: Status Reason = Completed <--> statuscode eq 2

  • This step uses the action Initialize Variable to set report pre-filtering when calling HTTP request - Report Viewer at Step(4)

  • We using the XML format

How to find the report filter code?

My sample report "WOSummary" was enabled pre-filtering so we need this step to set the Pre-filtering for a report when running the HTTP Request (apply for current Work Order triggered).

After converting the XML format, then copy it to Power Automate and replace the attribute with Dynamic Values at Step(1).

  • uiname: [[WO Number]]

  • guid: [[Work Order]] guid

// Sample XML - report filter
<ReportFilter>
 <ReportEntity paramname="CRM_Filteredntd_WorkOrder" donotconvert="1" displayname="Work Orders">
  <fetch version="0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name="ntd_workorder">
    <filter type="and">
      <condition attribute="ntd_workorderid" operator="in">
        <value uiname="WO202403-1002" uitype="10327">
          edf341b8-14e0-ee11-904c-0022485a170d
        </value>
      </condition>
     </filter>
     <all-attributes />
   </entity>
  </fetch>
 </ReportEntity>
</ReportFilter>
  • List rows: get a List of report records on the table "Reports"

  • Filter rows: Report Name = "WOSummary" <--> name eq "WOSummary"

This step is to call an HTTP request to view the WOSummary report of the current Work Order. And scope is run on Apply to each (value): The report in Step (2)

  • Method: POST

  • Url of the request: /CRMReports/rsviewer/reportviewer.aspx

  • Header: Content-Type:application/x-www-form-urlencoded

  • Body of the request: id = [[Report]]&CRM_Filter=encodeUriComponent([Report Filter XML]) with: + [[Report]]: this is Report GUID in Step (3) + Expression: encodeUriComponent([[Report Fitler XML]]) with [Report Filter XML] at Step(2)

Note: We must authenticate first

  • Base resource URL: this is your environment URL (Power Platform/ CE environment)

  • Microsoft Entra ID Resource URL (Application ID URI): this is your environment URL (Power Platform/ CE environment)

Using code block:

  • Using expression with Dynamics Value: Body of step WOSummary Viewer step(4)

// add string PdfDownloadUrl starts
add(indexOf([Body-"WOSummary View"],'"PdfDownloadUrl":"'),18)

Using code block:

  • Using expression with Dynamics Value: Body of step WOSummary Viewer step(4)

  • Using expression with Dynamics Value: Output of step Download PDF Start step(5)

// add the length of PdfDownloadUrl 
sub(indexOf([Body-"WOSummary Viewer"],'","PdfPreviewUrl"'),
                                [Output-"Download PDF Start"])

Now, we need to replace the string "\u0026" with "&" to match the final URL format for the HTTP Request.

Using code block:

  • Using expression with Dynamics Value: Body of step WOSummary Viewer step(4)

  • Using expression with Dynamics Value: Output of step Download PDF Start step(5)

  • Using expression with Dynamics Value: Output of step Download PDF Length step(6)

// replace string
replace(substring([Body-"WOSummary Viewer",
                  [Output-"Download PDF Start"],
                  [Output-"Download PDF Length"),'\u0026','&')

This step is to call an HTTP request to download the Report PDF File of the "WOSummary" report.

  • Method: GET

  • Url of the request: [Output-"Download PDF URL"]

Finally, we will configure the action "Send email (V2)" to send the PDF report file to the customer.

I notice in the attached file:

  • Attachments Name: the label name of the attached file

  • Attachments Content: [Body-"Download file PDF WOSummary"] at Step(8)

Yeah... I have finished the Power Automate configuration for my scenarios.

Now, I will run the testing.

Testing...

Thank you & Hoping well.

Initialize Variables: create a Variable for Report pre-filtering

List rows: Table Report - filter Report Name "WOSummary"

Invoke an HTTP request: using to configure step: Report Viewer (POST) and Download report file (GET)

Compose (JSON): using to configure step: Download PDF Start - Length - URL

Send an email (V2): using to send the attached report to the Customer

[NTD]yns.Asia ... Thank you.

☕
❤️
invite me a cup.
Page cover image
Final Testing - Complete WO - Check Power Automate - Check Email
Sample Report - "WOSummary"
Trigger WO
Variable: Report Filter
Report filter - sample report
List rows: Reports table
Step: WOSummary View
Authenticate for HTTP with Microsoft Entra ID (preauthorized)
Download PDF Lenght
Download PDF URL
HTTP Request - GET - Download PDF Report file
Send email to customer