SSRS Order Product T3
Task3: In order entity create a report look like mentioned below, it should show the address of selected customer and products dynamically.
User Story: In Order
entity we need to create a report looks like above and it should show the
address of the customer.
Entity:
Order entity
Order Product entity
Contact
Fields:
Order:
Order Id
Name
Bill to
Ship to
Created on
Requested Delivery
Contact:
Full Name
Designation
Phone Number
Order Product:
Price Per Unit
Quantity
Amount
Manual Discount
Extended Amount
Sales Person
The Above
Fields from those entities are helpful to create a report that you required.
Fetch XML:
Go to Advance find and get the fetch XML then use
in SSRS report.
1. Order entity Fetch XML to add in data set:
Add all the columns and use which field you want to use
according to the requirement.
Fetch XML Code:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="salesorderdetail">
<attribute name="productid" />
<attribute name="productdescription" />
<attribute name="priceperunit" />
<attribute name="quantity" />
<attribute name="extendedamount" />
<attribute name="salesorderdetailid" />
<attribute name="volumediscountamount_base" />
<attribute name="volumediscountamount" />
<attribute name="uomid" />
<attribute name="tax_base" />
<attribute name="tax" />
<attribute name="shipto_postalcode" />
<attribute name="shipto_line3" />
<attribute name="shipto_line2" />
<attribute name="shipto_line1" />
<attribute name="shipto_stateorprovince" />
<attribute name="shipto_telephone" />
<attribute name="shipto_name" />
<attribute name="shipto_fax" />
<attribute name="shipto_country" />
<attribute name="shipto_contactname" />
<attribute name="shipto_city" />
<attribute name="willcall" />
<attribute name="sequencenumber" />
<attribute name="isproductoverridden" />
<attribute name="salesrepid" />
<attribute name="requestdeliveryby" />
<attribute name="overriddencreatedon" />
<attribute name="quotedetailid" />
<attribute name="quantityshipped" />
<attribute name="quantitycancelled" />
<attribute name="quantitybackordered" />
<attribute name="propertyconfigurationstatus" />
<attribute name="producttypecode" />
<attribute name="productname" />
<attribute name="ispriceoverridden" />
<attribute name="priceperunit_base" />
<attribute name="parentbundleidref" />
<attribute name="parentbundleid" />
<attribute name="salesorderid" />
<attribute name="salesorderdetailname" />
<attribute name="modifiedon" />
<attribute name="modifiedonbehalfby" />
<attribute name="modifiedby" />
<attribute name="manualdiscountamount_base" />
<attribute name="manualdiscountamount" />
<attribute name="lineitemnumber" />
<attribute name="shipto_freighttermscode" />
<attribute name="extendedamount_base" />
<attribute name="exchangerate" />
<attribute name="description" />
<attribute name="transactioncurrencyid" />
<attribute name="ordercreationmethod" />
<attribute name="createdon" />
<attribute name="createdonbehalfby" />
<attribute name="createdby" />
<attribute name="iscopied" />
<attribute name="productassociationid" />
<attribute name="baseamount_base" />
<attribute name="baseamount" />
<order attribute="productid" descending="false" />
</entity>
</fetch>
enableprefitering
=”1”
When Pre filtering is created then Parameter is created automatically.
2. Order Product entity Fetch XML to add in data set:
Fetch XML Code:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="salesorderdetail">
<attribute name="productid" />
<attribute name="productdescription" />
<attribute name="priceperunit" />
<attribute name="quantity" />
<attribute name="extendedamount" />
<attribute name="salesorderdetailid" />
<attribute name="volumediscountamount_base" />
<attribute name="volumediscountamount" />
<attribute name="uomid" />
<attribute name="tax_base" />
<attribute name="tax" />
<attribute name="shipto_postalcode" />
<attribute name="shipto_line3" />
<attribute name="shipto_line2" />
<attribute name="shipto_line1" />
<attribute name="shipto_stateorprovince" />
<attribute name="shipto_telephone" />
<attribute name="shipto_name" />
<attribute name="shipto_fax" />
<attribute name="shipto_country" />
<attribute name="shipto_contactname" />
<attribute name="shipto_city" />
<attribute name="willcall" />
<attribute name="sequencenumber" />
<attribute name="isproductoverridden" />
<attribute name="salesrepid" />
<attribute name="requestdeliveryby" />
<attribute name="overriddencreatedon" />
<attribute name="quotedetailid" />
<attribute name="quantityshipped" />
<attribute name="quantitycancelled" />
<attribute name="quantitybackordered" />
<attribute name="propertyconfigurationstatus" />
<attribute name="producttypecode" />
<attribute name="productname" />
<attribute name="ispriceoverridden" />
<attribute name="priceperunit_base" />
<attribute name="parentbundleidref" />
<attribute name="parentbundleid" />
<attribute name="salesorderid" />
<attribute name="salesorderdetailname" />
<attribute name="modifiedon" />
<attribute name="modifiedonbehalfby" />
<attribute name="modifiedby" />
<attribute name="manualdiscountamount_base" />
<attribute name="manualdiscountamount" />
<attribute name="lineitemnumber" />
<attribute name="shipto_freighttermscode" />
<attribute name="extendedamount_base" />
<attribute name="exchangerate" />
<attribute name="description" />
<attribute name="transactioncurrencyid" />
<attribute name="ordercreationmethod" />
<attribute name="createdon" />
<attribute name="createdonbehalfby" />
<attribute name="createdby" />
<attribute name="iscopied" />
<attribute name="productassociationid" />
<attribute name="baseamount_base" />
<attribute name="baseamount" />
<order attribute="productid" descending="false" />
<filter type="and">
<condition attribute="salesorderid" operator="eq" uiname="Mobile" uitype="salesorder" value="{F07DB107-5309-EF11-9F8A-002248D654EE}" />
</filter>
</entity>
</fetch>
3.Contact
entity Fetch XML to add in data set
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.
Step10: Create three datasets and paste.
Order
Data Set:
Product data set:
Contact Data set:
Creating a parameter:
Click on Parameter and click on Add Parameters
Name the Parameter.
Click on Available value, Select salesorderid in Value Field and Label field.
In label field provide name
Click on Default values and in value field provide the salesorderid
Click on advanced, check the refresh data is automatic and Report notification enable or not
Store the
image:
Drag the image and insert in report body.
Drag the Fields into report body and also create a product table.
Then design the report according to the requirement.
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 (Task3ProductDetails.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 Order Record and click on Run Report.
There
you can find the given report, click on Product Details In Order report and
check the result.
Output: Final report in Order entity:
.png)
Comments
Post a Comment