Option Sets in MS CRM

If you use Microsoft CRM and want to explore the data from the database you will eventually run into data from Option Sets.  These are pre-defined values you can store for a field.  When you create an Option Set CRM stores the values in a special table called StringMapBase (you can also reference the view StringMap for these values.)


Because ALL Option Set values are stored in this table you need to determine which ones relate to your data.  This is not always easy but the following query can be helpful.

select a.AttributeName AS OptionSet, b.Name AS ObjectName, a.ObjectTypeCode

from StringMapBase a

inner join MetadataSchema.Entity b

on a.ObjectTypeCode = b.ObjectTypeCode

What this query does is join the Option Sets to the Entity table and shows the name of the CRM Entity that the Option Set relates to.


Save that query to a view and you can easily find option values for whatever reports you need them for.  When you find the right option set create a view in your reporting database to encapsulate those values for reference purposes.