SSRS User Story 2: Generate a report on individual student, if I click on the payment details report in student record. It need to show all the transactions done in current year along with sum. (it should show for each student separately)

 

Entities:

·         Student

·         Transaction

Fields:

            Transaction:

o   Student Name (Lookup of student entity)

o   Amount

 

Report Creation: Right click on ProjectàAddàNew item

Here enter the Report Name and click ok Add

Right click on the Data Sources

choose Type as Microsoft Dynamics 365 Fetch

Connection String: Enter your CRM URL and click on ok

Click on the Credentials

Enter User name and Password click on Ok

Then Data Source is created

Right click on the Data Source and choose Add Data Set  

 

Fetch xml for all Transactions in current from linked from student:  

 

Double click on data set name (Transaction)

Then below window will be opened.

choose Data Source.

Choose Query type as Text.

Here Enter the Fetch xml code in query area and do some changes.

Click on Ok.

 

Right click on Design area àInsertàTable

Then drag and drop the fields schema names to design window.

Then below window will be opened.

Total amount Row creation:

Sum of Amount:

After clicking on Expression below window will be opened.

Here choose Aggregate and choose sum

Create expression and click on Ok

After creation of Report Design:

Click on preview then below output will come

Output in CRM:

Click on Reports in sitemapà New

Open the student record and click on Run Report àclick on Report Name

Then below output will come