AX / Dynamics 365 For Finance and Operations blog

Enums are everywhere in Microsoft Dynamics 365 Finance and Operations (D365FO). They make code readable, enforce consistency, and keep logic clean. But when it comes to reporting or data exports—especially via SQL views—they can be frustrating. After all, SQL only sees the integer values behind the enum. The human-readable labels that make sense to us (“Invoiced”, “Delivered”, “Canceled”) are trapped in the application layer.

So how do we bring that text into SQL without resorting to custom lookup tables or awkward joins? The answer lies in computed columns.

The problem: enums live in the application, not the database

In D365FO, an enum is an application construct. When a record is written to the database, only the numeric value is stored. That’s fine for the system, but not for a report or Power BI dataset that expects meaningful text.

If you build a standard SQL view, you’ll see something like:

Not very informative.

The computed column trick

A computed column allows you to inject SQL logic at build time from within X++. You can write a static method that generates a SQL expression—essentially a chunk of code that becomes part of the database view definition.

Here’s a practical example that converts a SalesStatus enum to its text equivalent inside a SQL view:

public static str salesLine_SalesStatus()
{
    str val = SysComputedColumn::returnField(identifierStr(OTS__DWH_VEN_OrdinatoView), identifierStr(SalesLine), identifierStr(SalesStatus));

    return strFmt(
        "case when %11 = %1 then '%2' " +
        "when %11 = %3 then '%4' " +
        "when %11 = %5 then '%6' " +
        "when %11 = %7 then '%8' " +
        "when %11 = %9 then '%10' " +
        "else 'Unknown' end",
        enum2Int(SalesStatus::None), enum2Str(SalesStatus::None),
        enum2Int(SalesStatus::Backorder), enum2Str(SalesStatus::Backorder),
        enum2Int(SalesStatus::Delivered), enum2Str(SalesStatus::Delivered),
        enum2Int(SalesStatus::Invoiced), enum2Str(SalesStatus::Invoiced),
        enum2Int(SalesStatus::Canceled), enum2Str(SalesStatus::Canceled),
        val
    );
}

This generates SQL that looks like:

CASE SalesLine.SalesStatus
    WHEN 0 THEN 'None'
    WHEN 1 THEN 'Backorder'
    WHEN 2 THEN 'Delivered'
    WHEN 3 THEN 'Invoiced'
    WHEN 4 THEN 'Canceled'
    ELSE 'Unknown'
END

That’s real SQL embedded directly into your D365 view.

Why it works

When the view is built, X++ executes the method and injects the SQL expression into the database definition. The system replaces enum2Int() and enum2Str() with their literal values—so the output is pure SQL, not runtime X++ code.

That means you can use it in Power BI, data entities, or any external reporting tool without needing any extra logic in your reports.

A few pro tips

• Always include an ELSE clause. It protects you from unexpected enum extensions or nulls.
• Be mindful of localization: the strings come from the language of the environment where the model is compiled, not from the user’s language at runtime.
• Keep it maintainable: if your enum might grow, consider generating the CASE dynamically using DictEnum in a build-time job.