CRM Picklist Values for Your Report Parameter

It’s pretty common to have a pulldown menu for your report parameter, but what if you wanted to use the same picklist value that’s in a CRM form?  Here’s one way I go about it:

SELECT DISTINCT crm_picklistvaluefield, crm_picklistvaluefieldname FROM filteredcrm_entity

Or

SELECT crm_picklistvaluefield, crm_picklistvaluefieldname FROM filteredcrm_entity GROUP BY crm_picklistvaluename

The net result is that this will give you the picklist values, but there can be a performance cost, especially if either of those SELECT statements are going across a large recordset. So we know that the picklist values are stored in the CRM database somewhere.  The question then becomes, how do we get our report access to them in a way that CRM security allows? The answer is the FilteredStringMap view. 

In lieu of the above, I would instead use the following SQL statement:

SELECT  AttributeValue, Value

FROM FilteredStringMap

WHERE FilteredViewName='filteredcrm_entity’

and

AttributeName=’crm_picklistvaluefield’

Of course, by all means test this against your existing set-up before you start incorporating this into your reports.  As a start, I recommend taking the user security role with the lowest level access to insure that it works for them.