Skip to main content

Notifications

Microsoft Dynamics 365 | Integration, Dataverse...
Answered

FetchXML - Query multiple tables to display one field in a report

(0) ShareShare
ReportReport
Posted on by 873

Hello everyone,

I built a very simple sub-report that is connected to the Work Order entity.
That sub-report is related to a parent report that will be generated from "Customer Asset" entity to show the related work orders of the asset.

I have a custom entity that is related to the Work order, and the customer asset, and both has 1:N relationship which are the below fields:

Related to asset tableRelated to WO
I would like to add to my fetch XML a link query to my custom entity to fetch only one attribute and display it in the report once it got generated from the Customer Asset entity.

The fetch XML that I tried:
























However, when I tried to import the report it is showing the below error:

ReportError.PNG
If I removed from the above XML line 21 until 23 the report will be uploaded successfully but the problem without that custom field that I want to show.
It seems that because my query logic is wrong on those lines.

I tried to use Advanced find just to query directly from WO entity to my custom entity and it worked fine.
XML query:

















Can please someone advise what should be the updated XML query in order to achieve the above to display that custom field in the report?

Please let me know if you need any further information.
Any help is highly appreciated.

  • EBMRay Profile Picture
    EBMRay 873 on at
    RE: FetchXML - Query multiple tables to display one field in a report

    Hey ,

    Thank you for your reply and for providing the updated XML query.

    Yes, the Asset detail is not related to the bookable resource entity.

    I used the code mentioned, I was able to import it and it is showing that field value successfully in the report.

    Your help is highly appreciated!

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: FetchXML - Query multiple tables to display one field in a report

    Hi EBMRay,

    According to your description, Asset Detail entity is related to Customer Asset entity and Work Order entity, not Bookable Resource Booking entity.

    However, in your XML query your Asset Detail entity is linked to your Bookable Resource Booking entity which causes the error.

    Please change your code like this to have a try(Make  Asset Detail entity link to Work Order entity):

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    I previewed it on my side and it should work:

    pastedimage1639016479896v1.png

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans