FetchXML Field Types in SSRS

Submitted by feridun on Thu, 05/15/2014 - 12:38

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 TypeCRM Field FormatfieldValue returnsSSRS Data Typefield returns (without value)SSRS Data Type
Whole NumberLanguageWhole numberInt32Formatted as per the user's format String
Whole NumberNoneWhole numberInt32Formatted as per the user's format String
Whole NumberTimezoneWhole numberInt32Formatted as per the user's format String
Floating Point Floating point numberDoubleFormatted as per the user's format String
Decimal Decimal numberDecimalFormatted as per the user's format String
Option Set Integer value of the option set valueInt32Text for the option set valueString
Two Options Boolean value of the option set valueBooleanText for the option set valueString
Date and TimeDate and Time

Date and time in UTC

 

DateTimeDate and time formatted as per the user's time zoneString
Date and TimeDate only

Date with the time set to 12:00AM

 

DateTimeDate only formatted as per the user's time zoneString
Lookup ID of the record referenced in the lookupGuidText of the primary field of the record referenced in the lookupString
Lookup 

A lookup also returns a field with "EntityName" appended, fieldEntityName.

This contains the name (not the display name) of the entity, e,g. systemuser.

String  
Status Integer value of the Status (the field is named statecode)Int32Text for the Status (the field is named statecodeValue)String
Status Reason Integer value of the Status Reason (the field is named statuscode)Int32Text for the Status Reason (the field is named statuscodeValue)String

 

Categories