Dowload SSRS Report and Send email

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

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
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

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','&')