FetchXML Field Types in SSRS
When creating reports that use FetchXML, some field types are returned with two fields, the name of the field and the name of the field with Value appended, fieldValue.
The table shows the field types that I found that have a Value version. Other CRM field types only return one field and are of type String.
The Value version returns the value of the field as stored in CRM, the "non"-value version returns a formatted version of the field according to the user's settings in Personal Options.
|CRM Field Type||CRM Field Format||fieldValue returns||SSRS Data Type||field returns (without value)||SSRS Data Type|
|Whole Number||Language||Whole number||Int32||Formatted as per the user's format||String|
|Whole Number||None||Whole number||Int32||Formatted as per the user's format||String|
|Whole Number||Timezone||Whole number||Int32||Formatted as per the user's format||String|
|Floating Point||Floating point number||Double||Formatted as per the user's format||String|
|Decimal||Decimal number||Decimal||Formatted as per the user's format||String|
|Option Set||Integer value of the option set value||Int32||Text for the option set value||String|
|Two Options||Boolean value of the option set value||Boolean||Text for the option set value||String|
|Date and Time||Date and Time||
Date and time in UTC
|DateTime||Date and time formatted as per the user's time zone||String|
|Date and Time||Date only||
Date with the time set to 12:00AM
|DateTime||Date only formatted as per the user's time zone||String|
|Lookup||ID of the record referenced in the lookup||Guid||Text of the primary field of the record referenced in the lookup||String|
A lookup also returns a field with "EntityName" appended, fieldEntityName.
This contains the name (not the display name) of the entity, e,g. systemuser.
|Status||Integer value of the Status (the field is named statecode)||Int32||Text for the Status (the field is named statecodeValue)||String|
|Status Reason||Integer value of the Status Reason (the field is named statuscode)||Int32||Text for the Status Reason (the field is named statuscodeValue)||String|