Fetch XML Previews in Visual Studio Reports for CRM

Author by Dan Fink

While working with Visual Studio to build some Dynamics CRM Online Reports recently, an odd issue was encountered which I thought might be worth documenting for others. Let's assume you are creating a Report which shows all the Opportunities in CRM with specific Owners on the related Accounts. Using Visual Studio to create this report requires a Fetch XML to select the specific records you'd like to show in the report. In the event you're not versed in writing fetches from scratch in Notepad, you can do what most of us do and download it directly from Advanced Find in CRM. Below is the query I need for my report, which pulls all Opportunities where the Owner on the related Account is my or in my reporting hierarchy.

 

AdvancedFind.PNG

 

I can use the 'Download Fetch XML' button in the Advanced Find tab to get this fetch in Notepad, but prior to doing that, you should always check the Results. This isn't just to ensure they're accurate, but also refreshes the 'Download Fetch XML' button so any changes you just made will reflect in the downloaded text file.

Here is my Fetch XML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

  <entity name="opportunity">

    <attribute name="name" />

    <attribute name="customerid" />

    <attribute name="estimatedvalue" />

    <attribute name="statuscode" />

    <attribute name="amp_opportunitytype" />

    <attribute name="opportunityid" />

    <order attribute="name" descending="false" />

    <link-entity name="account" from="accountid" to="parentaccountid" alias="aa">

      <filter type="and">

        <condition attribute="ownerid" operator="eq-useroruserhierarchy" />

      </filter>

    </link-entity>

  </entity>

</fetch>

 

Once the fetch is ready, Query Designer of Visual Studio can be used to get the records I need in my Report, but there can be issues with showing fetches that have link-entity components in them which will cause no results to show in the preview or in the report, even though you're sure the fetch is valid and shows results in CRM.

In many cases, the culprit is simply the Connection String. Many people often just use the ServerURL of the CRM site as the string, but as the TechNet documentation shows, there are additional components which can be added, including the OrganizationName, which is the unique ID assigned to your CRM instance, that can be seen in the Settings > Customizations > Developer Resources page.

Simply update your connection string to include the OrganizationName after the ServerURL (delimited by a semicolon) and run the exact same fetch in the query designer. Assuming this was your issue, the results should now show just as they did in CRM.

Author

Dan Fink

Senior System Engineer