# Dowload SSRS Report and Send email

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:

<table><thead><tr><th width="400">Components</th><th>Flow summary</th></tr></thead><tbody><tr><td><ul><li><em><strong>Trigger</strong></em>: WO completed</li><li><em><strong>Initialize Variables</strong></em>: create a <strong>Variable</strong> for Report pre-filtering<br><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FMEIIJvcHsyvD03AvNGu7%2Fimage.png?alt=media&#x26;token=e4419148-1f25-4537-8daf-f9d1de4ceb01" alt="" data-size="line"></li><li><em><strong>List rows</strong></em>: Table <strong>Report</strong> - filter Report Name "<strong>WOSummary</strong>"<br><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FKUDS4J9phBC15tYOUJ5i%2Fimage.png?alt=media&#x26;token=39875c77-c41b-4cf0-948b-5b50d7bc6b17" alt="" data-size="line"></li><li><em><strong>Invoke an HTTP request:</strong></em> using to configure step: <em>Report Viewer (POST)</em> and <em>Download report file (GET)</em> <br><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FRdp38I2u0gI6BlKMa425%2Fimage.png?alt=media&#x26;token=a9caef8a-e167-4573-802f-58dabf2b84c5" alt="" data-size="line"></li><li><em><strong>Compose (JSON)</strong></em>: using to configure step: Download PDF Start - Length - URL<br><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2Fpy1vOPrMP8lPagKRjbsc%2Fimage.png?alt=media&#x26;token=1f90e8b1-9d16-495e-8739-b6be4727ffec" alt="" data-size="line"></li><li><em><strong>Send an email (V2)</strong></em>: using to send the attached report to the Customer<br><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2Fkm2i2WLnhKcIKIbUNw7M%2Fimage.png?alt=media&#x26;token=95a2ab11-cd52-4cb4-8206-07b6eb51ea32" alt="" data-size="line"></li></ul></td><td><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FtbqVu1GdxctWVgzi82y1%2FFlow_All_Step_Configuration.png?alt=media&#x26;token=eecbfa8d-307f-4470-abf6-af1137c8d274" alt="" data-size="original"></td></tr></tbody></table>

## Details of configuration.

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

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FPEbafjDG6v6L9ep7Gmwj%2Fimage.png?alt=media&#x26;token=db006f50-8d1a-4c39-a432-a99ae751e51c" alt=""><figcaption><p>Sample Report - "WOSummary"</p></figcaption></figure>

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:

{% tabs %}
{% tab title="1" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2Fz6gVbhnRjtJQU6eoHxna%2Fimage.png?alt=media&#x26;token=0ca2b6cd-87b7-41fc-9213-94fdeb409803" alt="" width="563"><figcaption><p>Trigger WO</p></figcaption></figure>

* Trigger: Work Order **Modified** on field *"Status Reason"*
* Filter: Status Reason = Completed  <--> statuscode eq 2
  {% endtab %}

{% tab title="2" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FbhPAG4e9LMxBFoIBvnuB%2Fimage.png?alt=media&#x26;token=d00117a8-2cb0-4abc-b962-610ee1206ab2" alt="" width="375"><figcaption><p>Variable: Report Filter</p></figcaption></figure>

* 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

<mark style="background-color:green;">How to find the report filter code?</mark>

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2Ft2n1cPTkSX0SYCCz65oR%2Fimage.png?alt=media&#x26;token=0f559f4d-fe51-4346-b8de-06e4774cd432" alt=""><figcaption><p>Report filter - sample report</p></figcaption></figure>

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).**&#x20;

* **uiname**: <mark style="color:red;">\[\[WO Number]]</mark>
* **guid**: <mark style="color:red;">\[\[Work Order]] guid</mark>

{% code lineNumbers="true" %}

```xml
// 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>
```

{% endcode %}
{% endtab %}

{% tab title="3" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2F8hBqZJJXRb1g0glEN6zP%2Fimage.png?alt=media&#x26;token=fd26a294-242b-48be-b459-8d6f77ee4d62" alt="" width="563"><figcaption><p>List rows: Reports table</p></figcaption></figure>

* List rows: get a List of report records on the table **"Reports"**
* Filter rows: Report Name = "**WOSummary**"    <-->    name eq "WOSummary"
  {% endtab %}

{% tab title="4" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FRMuCbvYuQa8eMcHpqbcc%2FHTTP_Request_ReportViewer.png?alt=media&#x26;token=8e22413a-004f-4dae-8563-1724ec88f212" alt="" width="563"><figcaption><p>Step: WOSummary View</p></figcaption></figure>

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*: <mark style="color:purple;">`POST`</mark>
* *Url of the request*: <mark style="color:purple;">`/CRMReports/rsviewer/reportviewer.aspx`</mark>
* *Header*: `Content-Type:`<mark style="color:purple;">`application/x-www-form-urlencoded`</mark>
* *Body of the request*: \
  `id =`` `<mark style="color:green;">`[[Report]]`</mark>`&CRM_Filter=`<mark style="color:red;">`encodeUriComponent(`</mark><mark style="color:purple;">`[Report Filter XML]`</mark><mark style="color:red;">`)`</mark>\
  **with:**\
  &#x20;  \+ <mark style="color:green;">\[\[Report]]</mark>: this is Report GUID in **Step (3)**\
  &#x20;  \+ Expression: <mark style="color:red;">`encodeUriComponent(`</mark><mark style="color:purple;">`[[Report Fitler XML]]`</mark><mark style="color:red;">`)`</mark> \
  &#x20;                 with <mark style="color:purple;">\[Report Filter XML]</mark> at **Step(2)**

***Note:** We must authenticate first*

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FnnKVtKGIjiODnQQlo5Ea%2Fimage.png?alt=media&#x26;token=f0fe8d75-a349-45a5-b19b-ba8597490fc5" alt="" width="563"><figcaption><p>Authenticate for HTTP with Microsoft Entra ID (preauthorized)</p></figcaption></figure>

* *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)
  {% endtab %}

{% tab title="5" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FgVgt5DBFO8Yxh7e99pWY%2Fimage.png?alt=media&#x26;token=2c51d466-7f99-4739-8a04-0da0e08e7cd4" alt="" width="563"><figcaption></figcaption></figure>

Using code block:

* Using expression with **Dynamics Value**: <mark style="color:blue;">**Body**</mark> of step <mark style="color:blue;">**WOSummary Viewer**</mark>**&#x20;step(4)**

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

{% endtab %}

{% tab title="6" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2F03w8THHLJR0k6eORmG6e%2Fimage.png?alt=media&#x26;token=1b09d6ce-896c-4c91-bb55-ad4a11802184" alt="" width="563"><figcaption><p>Download PDF Lenght</p></figcaption></figure>

Using code block:

* Using expression with **Dynamics Value**: <mark style="color:blue;">**Body**</mark> of step <mark style="color:blue;">**WOSummary Viewer**</mark>**&#x20;step(4)**
* Using expression with **Dynamics Value**: <mark style="color:blue;">**Output**</mark> of step <mark style="color:blue;">**Download PDF Start**</mark>**&#x20;step(5)**

{% code overflow="wrap" %}

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

```

{% endcode %}
{% endtab %}

{% tab title="7" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FcWnEwIvOppIPvxwvIoK2%2Fimage.png?alt=media&#x26;token=ab4097ea-7915-490d-80d1-451992b667ee" alt="" width="563"><figcaption><p>Download PDF URL</p></figcaption></figure>

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**: <mark style="color:blue;">**Body**</mark> of step <mark style="color:blue;">**WOSummary Viewer**</mark>**&#x20;step(4)**
* Using expression with **Dynamics Value**: <mark style="color:blue;">**Output**</mark> of step <mark style="color:blue;">**Download PDF Start**</mark>**&#x20;step(5)**
* Using expression with **Dynamics Value**: <mark style="color:blue;">**Output**</mark> of step <mark style="color:blue;">**Download PDF Length**</mark>**&#x20;step(6)**

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

{% endtab %}

{% tab title="8" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FemwMDLSHJA3uTGvJN7bT%2Fimage.png?alt=media&#x26;token=b090f4a1-6471-4ab5-b09d-d1a3f4fb1e88" alt="" width="563"><figcaption><p>HTTP Request - GET - Download PDF Report file</p></figcaption></figure>

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

* Method: <mark style="color:purple;">`GET`</mark>
* Url of the request: <mark style="color:purple;">`[Output-"Download PDF URL"]`</mark>
  {% endtab %}

{% tab title="9" %}

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2Fs2ezIwrkqdaSlveC0eLW%2Fimage.png?alt=media&#x26;token=bdbc02d9-ed47-4c03-9431-d25ef252017f" alt="" width="563"><figcaption><p>Send email to customer</p></figcaption></figure>

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:* <mark style="color:purple;">`[Body-"Download file PDF WOSummary"]`</mark> at **Step(8)**
  {% endtab %}
  {% endtabs %}

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

Now, I will run the testing.

## Testing...

{% embed url="<https://youtu.be/XvUfOc0eDnk>" %}
Final Testing - Complete WO - Check Power Automate - Check Email
{% endembed %}

Thank you & Hoping well.

**\[NTD]yns.Asia**\ <mark style="color:red;">...</mark>[<mark style="color:red;">invite me a cup.</mark>](https://ko-fi.com/ntdyns/?ref=qr\&amp;v=2) :coffee: Thank you. :heart: