SSRS Schedule Repo T5

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

User Story: Create the SSRS report with student name, DOB, Date of joining, parent details and schedule this report for every day evening 6pm it need to run and share to manager by using power automate.

Entities:

Students

Account

Fields:

Name

Date of Birth

Date of joining

Parent details (Lookup of account entity)

Now create a SSRS report for that go to visual studio

Step1: Open Visual studio Click on create project and search for the Reports
Step2: Select Report Server Project in second Option and Click on Next

Step3: Rename and click on create Then you see your SSRS reports screen

Step4: Right side we can see Reports Right click on Reports and then we can see some options in that click on Add ==> New Item. Then we can see Add Items Window in That we Have Three Options 1. Report 2. Data Source 3. Data Set .Now select Report because we are Doing for Reports.

Change the Report name and Add it. By this we are getting a .rdl File (Report Definition Language)

Step5: Then we can see in Left Navigation some Options Like 1.Built in Fields, 2.Parameters, 3.Images, 4.Data Source, 5.Data Sets.

Now Right click on Data Source and click On Add Data Source. Then Data Source Properties page will be Visible.

Step6: In Data Source Properties we have two table 1. General 2. Credentials

1. General

Select General and Give Name, Click on Embedded Connection

Select Type as MD365 Fetch and provide the dynamics URL in Connection string

Step7: Next Click on Credentials and click on the option use this user name and password in that provide our Dynamics CRM 365 Credentials and click on ok Button.

Step8: Now for Data Set Right Click on Datasets and click on add Datasets

Step9: Then Data Set Properties in that we have many options like

1. Query 2. Fields 3. Options 4. Filters 5. Parameters

Ø Now Select Query and Name the Dataset after that click on option Use a Data Set Embedded in my report.

Ø Then give Data Source which you created.

Ø Next the query Type select as Text.

Ø Now In query paste your Fetch XML code and click on Query Designer.

Go to Advance find and download the Fetch XML

Advance Find for Fetch XML:

Step10: Create a datasets and paste the Fetch XML code.

Click on preview and build the report then use it in CRM by creating a report in CRM.

Step11: Now Create Report in CRM For that Open CRM, go to advance settings

click on setting savron ==> click on Customization area ==> click on Customization sub area

Inside the Customization click on Customize the System ==> click on Components ==> click on Reports

Create a New Report

Step12: Now Select The Report Type as Existing File and Name the report in name text box.

Click on choose file, go to path of report and select .rdl file (Task5 ScheduleRepo.rdl)

Step13: Now in Categorization we have 4 option

1. Categorize 2.Related Record Type 3.Display in 4.Languages

In Categorize Select modules Marketing, Sales, Service reports

Related Record Type select Record as Accounts

For Display In select Forms for related records, List For Related Record, Report Area

For Language select English

Step14: Now Open CRM, Go to Students Record and click on Run Report.

There you can find the given report, click on Schedule Repo In Students report and check for the result.

Result in Students record:

Power Automate:

Now open power apps to send this report to manager every day at 6pm.

Click on flow > Select Instant cloud flow.

Step1: Create a schedule for Recurrence

Step2: Then add a list row from Data Verse connector and configure it to lookup the report by the Reports Table in table name. In Filter Rows (name eq 'Schedule Repo') and Row Count as 1

Step3: Setup the report viewer – We do this using the HTTP with Azure AD connector, specifically the Invoke an HTTP request 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: To Download pdf