Search code examples
sqldynamics-crmnvarcharfetchxml

FetchXML nvarchar returns nothing - Custom Report for CRM

I am trying to get data from Dynamics CRM 2013 for a custom report using FetchXML.

I "converted" the following SQL Query to FetchXML using this page: http://www.sql2fetchxml.com/

SQL:

SELECT
accountidname,
billto_line1,
billto_postalcode,
billto_city,
invoicenumber,
name,
description
FROM FilteredInvoice 
WHERE invoiceid = @invoiceid

FetchXML:

<fetch mapping="logical">
  <entity name="invoice">
    <attribute name="accountidname" />
    <attribute name="billto_line1" />
    <attribute name="billto_postalcode" />
    <attribute name="billto_city" />
    <attribute name="invoicenumber" />
    <attribute name="name" />
    <attribute name="description" />
    <filter>
      <condition attribute="invoiceid" operator="eq" value="@invoiceid" />
    </filter>
  </entity>
</fetch>

Both, "accountidname" and (e.g.) "billto_line1" are NVARCHAR(4000) and both contain data, but if I try to execute the query (e.g. in the "Query Designer" in Visual Studio) only the data of "billto_line1" is shown, not the data of "accountidname".

Since both fields "accountidname" and "billto_line1" are of the same type (nvarchar(4000)) and contain information in the database (I checked it with the "Microsoft SQL Server Management Studio") I am wondering why I can only receive the information from one of those two.

This has happend with many different fields of the type NVARCHAR - some are displayed correctly, while some are not - and I have no idea why.

I would be very pleased if somebody could give me a hint :)

Thank you

Solution

  • accountidname is on the Views, not the actual tables, in SQL. It is not a field on the invoice entity and therefore will not return any information in a FetchXml request.

    You should be able to retrieve the Account's name by using accountid - I don't remember in SSRS how the Name property of an EntityReference is surfaced but it should be available.

    The FetchXml converter is good at converting SQL SELECT's to FetchXml format and converting joins. It will not validate that the SQL fields are available in FetchXml - that could only be done by accessing the metadata of the CRM Org.