Display a CRM 4.0 PickList Display-Value in Reports and Queries

Microsoft CRM 4.0 stores picklist display values within the MetadataSchema in the StringMap table.  When creating custom reports or quieries a custom Scaler-Function is needed to show the picklist display vale.  To do this I wrote a simple function shown below called MapPickList that can be called in a SQL query to return a picklist displayvalue.

This example shows the CRM Contact table with a custom attribute called ShoeSize

SELECT     LastName, FirstName, dbo.MapPickList(‘contact’, ‘ShoeSize’, ShoeSize) AS DisplayValue

FROM  Contact

The output would look like this:

LastName            FirstName           DisplayValue
—————————————————————-
Smith                    Mark                     12.5
Estes                     Lowell                   10
Sue                        Jones                    7

And here is the SQL function.

CREATE FUNCTION MapPickList

(

/******Name of the CRM Entity we are querying******/

@entity varchar(50),

/******Name of the CRM Attribute we are querying******/

@attribute varchar(50),

/******The picklist int value stored by CRM******/

@value int

)

RETURNS varchar(500)

BEGIN

DECLARE  @return varchar(500)

SET @return = (SELECT StringMap.Value AS DisplayValue

FROM StringMap INNER JOIN MetadataSchema.Entity ON StringMap.ObjectTypeCode = MetadataSchema.Entity.ObjectTypeCode

WHERE (StringMap.AttributeName Like @attribute) AND (StringMap.AttributeValue = @value) AND (MetadataSchema.Entity.Name Like @entity))

return @return

END

Advertisements

9 thoughts on “Display a CRM 4.0 PickList Display-Value in Reports and Queries

    • Hi Ibrahim, I’m glad you commented because it reminds me to mention why I didn’t use a filtered view which is the Microsoft recommendation for reporting. Filtered views in complex custom CRM application with large numbers of custom entity’s have very high overhead and are not efficient at all. This function offers a way to get a sleek very efficient resultset.

  1. Hi
    I think i’m having this issue.
    I created a new entity, and when i use excel to query the table, MS Query, it shows the integer value of the picklist and not the description. how can i get around this?
    thanks
    mark

  2. Thanks, this helped a lot!

    A somewhat related question – when you have a duration field in CRM, CRM stores this value in minutes, whereas, from the front end it displays it as “15 minutes”, “30 minutes”…”1 day”, “2 days”, etc.

    I’m using SQL Reporting Services to develop a report. Is there a way I can convert the duration field in CRM (in minutes) to “15 minutes”, “30 minutes”, “1 day”, “2 days” etc.?

  3. …Well I’m a little late to the party here, but I just wanted to say that I agree with your method, and I came to the same conclusion; that a function like this is need – on my own. I call mine “dbo.f_GetPicklistValue” – but the parameters and method are the same. I also created the reverse function;
    dbo.f_GetPicklistAttributeValue
    …which takes a string as the last parm and returns the integer stored in the database for the picklist, allowing me to do something like…

    SELECT LastName, FirstName
    FROM Contact
    WHERE ShoeSize = dbo.f_GetPicklistAttributeValue(‘contact’, ‘shoesize’, ‘9.5’)

    Cheers,
    Chris List

  4. This does not work on Account sadly
    I have a custom field named new_claimsystem
    SELECT
    dbo.MapPickList(‘account’, ‘new_claimsystem’, New_ClaimSystem) AS ClaimSystem

    FROM Account

    Gives error:
    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s