Get Choice Label / Option Set Label (Dataverse Reporting)

What are Choices in Dataverse?

Also, and formerly, known as Option Sets and Picklists, Choices are effectively a key-value pair object type in Dataverse that allow for a drop-down list of static options. The end user sees a label and the database stores its corresponding integer value. This is wonderful for application performance but poses a problem when reporting.

The Problem

While integers are great for performance in the application, they are not so helpful for reporting. Depending on how the data is extracted from the application, one may encounter only having the value of the integer with no label. Users of reports will not want to see the integer value, but the label that they are familiar with in the application. Thus, it is regularly desired to show the label. But how does one retrieve the label in a repeatable way? To address this problem, a SQL function can be used.

The Function

The function below is an in-line table valued function. This allows for a repeatable, straightforward way to retrieve a label. Not only is it repeatable but using an in-line table valued function instead of a scalar function will yield performant queries over any size of data. Some other solutions using scalar functions in SQL do not scale as well over large data sets. The function assumes using a single language is used. If needing to use multiple languages, need to pass in the language into the function as well. Otherwise leave it hardcoded as 1033 (English)

CREATE FUNCTION [dbo].[GetChoiceLabel]
(
    @EntityName nvarchar(64),
    @ChoiceName nvarchar(64),
    @ChoiceValue int
)
RETURNS TABLE
AS
RETURN (
SELECT 
    StringMap.Value AS Label
FROM dbo.StringMap AS StringMap
WHERE StringMap.AttributeName=@ChoiceName
AND StringMap.ObjectTypeCode=(
                                SELECT TOP 1
Entity.ObjectTypeCode
FROM dbo.Entity AS Entity
WHERE Entity.LogicalName=@EntityName
)
AND StringMap.AttributeValue = @ChoiceValue
AND StringMap.langid=1033
)

Notes

The above function may need to be updated for your exact situation. Using the Dataverse connector in Azure Data Factory does not require the "SELECT TOP 1" to translate the entity name to an objecttypecode.

The Implementation

To implement the above solution, you use the APPLY operator (similar to a join).

APPLY dbo.GetChoiceLabel('Entity', 'Choice Name', Value Field) AS ChoiceLabel

Here is another example using a table called item

SELECT
    I.ItemType,
    ItemType.Label AS ItemTypeLabel
FROM dbo.item as I
    APPLY dbo.GetChoiceLabel('item','itemType',Item.ItemType) AS ItemType

Alternatives

There are some alternatives to using an inline function. See a quick overview below.

Option	Description	Pro	Con
Scalar function	A function that is used in-line to return a single (scalar) value	Inline. More intuitive	Performance degradation over large record sets especially if invoking functional multiple times
Multi-statement table valued function	Similar to an in-line table valued function but allows for multiple statements	Flexibility	Less performant than in-line table valued function
Manual Translation	Manually type out the conversations	No function call.	Not scalable if choices change.