SSRS Transaction T2
Task 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).
User story: Generate a report for individual student, when you click on the
payment details report in a specific Student Record then it need to show all
the transactions done in the current year along with sum of transaction amount.
(It should show for each student separately).
Entities:
Students
Transaction
Fields:
Transaction
Name
Amount (currency data type)
Student Details (lookup of students’ entity)
Transaction Date. ( date data type)
Students
Name
Date of Birth.
Fetch XML:
Go to Advance find and get the fetch XML then use in SSRS report.
1.
Transaction
entity Fetch XML
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="effizz_transaction">
<attribute name="effizz_transactionid" />
<attribute name="effizz_name" />
<attribute name="createdon" />
<attribute name="effizz_studentdetails" />
<attribute name="effizz_transactiondate" />
<attribute name="effizz_amount" />
<order attribute="effizz_name" descending="false" />
<filter type="and">
<condition attribute="effizz_studentdetails" operator="eq" uiname="Ram" uitype="effizz_students" value="{8559C283-7B08-EF11-9F8A-002248D654EE}" />
<condition attribute="effizz_transactiondate" operator="this-year" />
</filter>
</entity>
</fetch>
Note: Pass the parameter in value to perform dynamically and remove
uiname=”Ram”
Click on Edit
Columns and add the columns by clicking on Add Columns
2. Students entity Fetch XML
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="effizz_students">
<attribute name="effizz_studentsid" />
<attribute name="effizz_name" />
<attribute name="createdon" />
<order attribute="effizz_name" descending="false" />
</entity>
</fetch>
Give enableprefilter1 in students entity fetch XML i.e. name = “effizz_students” then with in the tag give enableprefilter="1" [<entity name="effizz_students" enableprefilter="1" >]
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.
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 past your Fetch XML code and click on Query Designer.
Step10: Create two datasets and paste.
DataSet1:
Create
a parameter and pass the @Student a parameter
in place of id as dynamic
data.
Creating a parameter:
Click on Parameter and click on Add Parameter
Name the Parameter.
Click
on Available value, Select student id in Value Field and Label field
In label field provide name
Click
on Default values and in value field provide the student id
Click on advanced, check the refresh data is automatic and Report notification enable or not
DataSet2:
Step10: Now Query Designer page Will Be
Open Click on (!) Run and check the Result.
Now in Result you Can See the students
record Information.
Step11: Right click in then add an Items
In the report i.e, which is visible in center a small empty box. Add Table the
drag and drop the Data Set Fields which was required.
Step12: To get the transactions done in
current year by the sum right click on the amount data set and click on Add
Total.
Step13: Now
click preview and check the result.
Step14: Now
Build the Solution
Step15: 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
Step16: 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 (Task1DrillRepo.rdl)
Step17: 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
Step18: Now Open CRM, Go to Students Record and click on Run
Report.
There
you can find the given report, click on Payment Details report and check the
result.
Output:
I
have create two students those are Ram and Bheem
Result
for Ram:
Result for Bheem:
.png)
Comments
Post a Comment