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.

Sample Report - "WOSummary"

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:

1

Trigger WO

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

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

2

Variable: Report Filter

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

Report filter - sample report

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>

3

List rows: Reports table

• List rows: get a List of report records on the table "Reports"

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

4

Step: WOSummary View

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

Authenticate for HTTP with Microsoft Entra ID (preauthorized)

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

5

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)

6

Download PDF Lenght

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"])

7

Download PDF URL

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

8

HTTP Request - GET - Download PDF Report file

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

9

Send email to customer

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

Final Testing - Complete WO - Check Power Automate - Check Email

Thank you & Hoping well.

[NTD]yns.Asia ...invite me a cup. ☕ Thank you. ❤️