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:
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:
POSTUrl of the request:
/CRMReports/rsviewer/reportviewer.aspxHeader:
Content-Type:application/x-www-form-urlencodedBody 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:
GETUrl 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.
[NTD]yns.Asia ...invite me a cup. ☕ Thank you. ❤️
Last updated
Was this helpful?