Hi
I am creating an SSRS report for Dynamics Field service, for the invoice with the Fetch XML query.
The query has multiple linked entities, it is repeating tablix values with the number of products we have in the Invoice.
I am not sure what is wrong in the query, or is there something extra I have to do to stop those redundant values.
here is my Fetch Xml query
<fetch distinct="false" no-lock="true" mapping="logical">
<entity name="invoice" enableprefiltering="1">
<attribute name="invoiceid" />
<attribute name="shipto_line2" />
<attribute name="shipto_city" />
<attribute name="billto_line2" />
<attribute name="sg_referencenumber" />
<attribute name="shipto_stateorprovince" />
<attribute name="billto_stateorprovince" />
<attribute name="shipto_postalcode" />
<attribute name="billto_city" />
<attribute name="billto_line1" />
<attribute name="billto_line3" />
<attribute name="invoicenumber" />
<attribute name="createdon" />
<attribute name="billto_postalcode" />
<attribute name="shipto_line3" />
<attribute name="shipto_line1" />
<attribute name="msdyn_invoicedate" />
<attribute name="paymenttermscode" />
<attribute name="sg_workorder" />
<attribute name="freightamount" />
<attribute name="totalamount" />
<attribute name="totallineitemamount" />
<attribute name="totaltax" />
<attribute name="sg_salesperson" />
<attribute name="billto_telephone" />
<attribute name="shipto_telephone" />
<link-entity name="invoicedetail" from="invoiceid" to="invoiceid" alias="invoiceproduct">
<attribute name="productname" />
<attribute name="invoiceid" />
<attribute name="baseamount" />
<attribute name="description" />
<attribute name="priceperunit" />
<attribute name="invoicedetailid" />
<attribute name="quantity" />
</link-entity>
<link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" alias="owner">
<attribute name="address1_line3" />
<attribute name="address1_line1" />
<attribute name="address1_city" />
<attribute name="address1_stateorprovince" />
<attribute name="address1_fax" />
<attribute name="address1_postalcode" />
<attribute name="address1_telephone1" />
</link-entity>
</entity>
</fetch>
and here is the report how it looks
If you can see, the WO table values are repeated, and it is repeating the number of items we have in the Invoice( here only one Item is visible because there is a visibility condition attached to the row to hide rows with $0 price)
Please suggest what can be the fix for the problem
