web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / How can we implement c...
Microsoft Dynamics CRM (Archived)

How can we implement conditional fetch xml query for ssrs report?

(0) ShareShare
ReportReport
Posted on by Drashti Mehta 264

I have a requirement to fetch records in report such that if a record has child transactions need to consider sum of amount of child transactions of that record else need to consider sum of amounts of parent transactions. How can i implement the same using fetch xml ? Since i am using crm online custom code would also not work out. any help is much appreciated. I have been searching for a long time for any work around even but could find any luck..

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    TylerS.Dev Profile Picture
    TylerS.Dev 729 on at

    If you are developing this report through SSRS in visual studio, you can set the condition within the report instead of doing it through fetchxml.  Have the fetchxml pull everything you need, and set your condition through expression in your report.

    You can also set condition in tablix or charts by going through properties and play around with  visibility and filters setting.

  • Drashti Mehta Profile Picture
    Drashti Mehta 264 on at

    Thanks for the reply Tyler, but how can i build expression even. Say for an example i have a parent transaction 0001 with 2 child transactions t1: 0001-1 and t2: 0001-2. In this case i need to consider amount of t1 and t2 and not the parent .

  • Drashti Mehta Profile Picture
    Drashti Mehta 264 on at

    Also i need to find top 20 so if i do not apply over query i would not get correct output

  • Suggested answer
    TylerS.Dev Profile Picture
    TylerS.Dev 729 on at

    First, you should download Xrmtoolbox and test your fetch query using FetchXML Builder plugin from xrmtoolbox, below is the website to download xrmtoolbox.

    https://www.xrmtoolbox.com/

    In dynamic 365, account and contact has parent child relationship so lets use that as an example

    <fetch top='20' >

     <entity name='account' >

       <attribute name='name' />

       <filter type='or' >

         <condition attribute='accountnumber' operator='eq' value='t1' />

       </filter>

       <link-entity name='contact' from='accountid' to='accountid' >

         <attribute name='fullname' />

       </link-entity>

     </entity>

    </fetch>

    You might want to also add sort statement based on how you define "top 20" (such as by age, by highest sales, etc.) .  You will need to modify t1 as input parameter, in CRM, your user can select both T1 and T2 and use run reports and get the result that you want.  This will give you top 20 child record based on your parent record input.

    Once you have your query, you need to setup your visual studio so that it can make SSRS report for Dynamic 365 (it only works with VS2015 or below for now), below is a guide that can help you with VS.

    meganvwalker.com/.../

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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans