Tim Windsor on 11 Aug 2015 19:03:44
The CRM Online OData feed doesn't dynamically provide access to user defined Option Sets. When adding an Option Set to CRM it should be provided in the PickListMappingSet table
- Comments (8)
RE: CRM Online Dynamic Options sets PickListMappingSet
OK - after some more digging and manipulating I found a partial solution. This is still too much effort for Business Line users, but a start. Kudos to CloudFront for their post here - https://www.cloudfronts.com/crm-option-set-in-power-bi/
The post doesn't work exactly as stated as it's a bit dated, but it can be manipulated to work. I had to forgo the Metadata browser portion of their solution as I couldn't find a way to get the OptionSets to appear. That said an easier way was to simply do an OData query in PowerBI for the entire Global Option Set list using my OData API -
Example: https://contoso.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions
Once I had that I could get the MetadataID for each option set and then copy the table to excel and append the MetaDataID to the above query similar to CloudFronts answer.
Example: https://contoso.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(ae2a3b9d-395a-4dbd-9abc-c32aeb10888b)
This allows you to then query specific optionsets. After you Query each OptionSet you will get a list table. Towards the bottom you will see an Item labeled "Options". Click on "Table" next to it and that will expand the query and you can expand the fields and choose columns.
After the above I basically created a new share point list of all option set query strings for users. Still a pain, but they can at least find and access the values now. Hope that helps, but REALLY hope the Dynamics team can work with PowerBI to get this done.
RE: CRM Online Dynamic Options sets PickListMappingSet
We recently moved to Dynamics. This is a huge issue for us (lots of option sets in data base) and had I known we would likely not have made the switch. My bad. These workarounds are impossible for most of our users. Very disappointed in Dynamics. If you promote Dynamics or CDS with Power BI capability for business line users, but this doesn't work, you are going to have some upset customers. Can someone please address this so a standard business user can pick the option set values easily, not just someone who can write JSON....
RE: CRM Online Dynamic Options sets PickListMappingSet
I'm a bit disappointed that issue has not been resolved yet. This issue was posted in Aug 2015, its Aug 2018 now and this is still a problem. As a D365 cloud user, I'm unable to create reports that use user defined option sets and the workarounds are inelegant solutions.
RE: CRM Online Dynamic Options sets PickListMappingSet
Hi everyone,
You can get the Option Sets value list by using the following Dynamics 365 API endpoint:
https://.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions().
The trick here is instead of using a normal OData call you put the API call inside a Json.Document(Web.Contents()), then convert the result in a table and keep the Options.
Here a sample M Query:
let
Source = Json.Document(Web.Contents("https://.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions()")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{2}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing () , null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Value", "Label", "Description", "Color", "IsManaged", "MetadataId", "HasChanged"}, {"Value", "Label", "Description", "Color", "IsManaged", "MetadataId", "HasChanged"}),
#"Expanded Label" = Table.ExpandRecordColumn(#"Expanded Column1", "Label", {"LocalizedLabels", "UserLocalizedLabel"}, {"Label.LocalizedLabels", "Label.UserLocalizedLabel"}),
#"Expanded Label.UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expanded Label", "Label.UserLocalizedLabel", {"Label", "LanguageCode", "IsManaged", "MetadataId", "HasChanged"}, {"Label.UserLocalizedLabel.Label", "Label.UserLocalizedLabel.LanguageCode", "Label.UserLocalizedLabel.IsManaged", "Label.UserLocalizedLabel.MetadataId", "Label.UserLocalizedLabel.HasChanged"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Label.UserLocalizedLabel",{"Value", "Label.UserLocalizedLabel.Label"})
in
#"Removed Other Columns"
This can be optimized, but it's just for your reference.
Best regards
RE: CRM Online Dynamic Options sets PickListMappingSet
would be a welcome addition
RE: CRM Online Dynamic Options sets PickListMappingSet
As well as Look up, OData feed does not support loading the Option sets or look ups
RE: CRM Online Dynamic Options sets PickListMappingSet
Agreed!!
RE: CRM Online Dynamic Options sets PickListMappingSet
Very important for the acceptance of PowerBI within MS Dynamics CRM projects