In this blog post Rohan Perera, HSD’s Dynamics 365 Technical Consultant outlines how to create a Global Option set as a parameter in FetchXML reports.
In Dynamics 365 and Common Data Model, fields of type “Option Set” has a value and label property against each list item. When creating reports we could create report parameters with static values and labels for any option set. However with time there might be changes to these option set values/labels due to various business requirements.
If we want to make sure that reports refer to the latest values of option sets, we have to acquire them programmatically at run time. The following steps will demonstrate how to create a dynamic option set parameter:
1. Create a FetchXML data set for the global Optionset
<fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0"> <entity name="stringmap" > <attribute name="attributevalue" /> <attribute name="value" /> <filter type="and" > <condition attribute="attributename" operator="eq" value="new_state" /> <condition attribute="objecttypecode" operator="eq" value="1" /> </filter> </entity> </fetch>
Please substitute the ‘attributename’ and the ‘objecttypecode’ values.
1.1 Finding the ‘objecttypecode’ value for your entity. (Source)
Use the following meta data call (Web API) as it is and replacing only the [org Url].[orgUrl]/api/data/v9.0/EntityDefinitions$select=LogicalName,ObjectTypeCode
Copy the required ObjectTypeCode value from the resulting list.
In addition, you can use XrmToolBox – Meta data Browser to get the ObjectTypeCode.
2. Create a new calculated field for this new data set. (Source)
The issue is that resulting ‘attributevalue’ is not numeric and contains comma characters.
Add new calculated field called ‘attributevaluecleaned’.
The <<Expr>> for this field is: =Replace(Fields!attributevalue.Value,”,”,””)