SSRS User Story-5: Create a SSRS report with student name, DOB, Date of Admission, parent details. Schedule this report, every day evening 6 pm it need to run and share with managers. (use power automate)

May 04, 2024

Entities:

· Student

· Contact

Fields:

· Student:

o Student Name

o DOB

o Date of Admission

o Parent Name

o Parent Phone Number

Fetch xml for retrieving student details:

Click on Edit Columns and add required fields

Creating SSRS Report:

Design:

SSRS Report output in visual studio:

Adding SSRS report in to CRM:

Open CRM and choose Reports in sitemap and click on New

Then add above created SSRS report.

Power Automate:

Step1:

Step2: Then, add a List Rows action from the Dataverse connector and configure it to lookup the report by the Report Name.

Step3: Setup the report viewer – We do this using the HTTP with Azure AD connector, specifically the Invoke an HTTP request (Pre Authorized) action.

Step4: We need to authorize our connection to use this action. If you’re using Dynamics 365 online, grab your CRM URL (including https://) and paste it in for both Base Resource URL and Azure AD Resource URL.

Step5: Open the SSRS Report in CRM

After opening the report click on F12 then developer resource will be opened

Here you can check all details of HTTP Request

Extract the PdfDownloadUrl from the Report Viewer page.

Step6: The start of the URL – Add a compose action for the following

add(indexOf(outputs('HTTP__POST_Report_Viewer')?['body'],'"PdfDownloadUrl":"'),18)

Step7: Length of PdfDownloadUrl – Add a compose action for the following

sub(indexOf(outputs('HTTP__POST_Report_Viewer')?['body'],'","PdfPreviewUrl"'),outputs('PDF_Download_Start_-_Index'))

Step8: Extract and convert the PdfDownloadUrl from the body – Add a compose action for the following

Replace(substring(outputs('HTTP__POST_Report_Viewer')?['body'],outputs('PDF_Download_Start_-_Index'),outputs('PDF_Download_String_Length')),'\u0026','&')

Step9: Download pdf