If you had a chance to read my other posts, we are working towards enabling reports development in AX using SQL, no X++ thing. On of the issue we faced was getting Base Enum Labels. Dynamics AX transactional DB only keeps Enum values (0,1,2,3…). All labels and names are stored in the Model DB. However, you cannot access it through TSQL query as data is stored in the binary format.

I have found a post, where one smart guy actually did a good reverse engineering to parse that binary field. His script provides a function with name of the Enum + you need to specify a value of Enum for which you would like to get a Label.

I have changed it a bit to get all available Labels for specific Enum. Now, you just need to loop through all available Enums to get your table populated for reporting.

It takes around 2-3 minutes to get into table all Base Enum Labels.

Orinial Script can be found here + explanation on how to parse binary field: Link

Download:  GetAllEnumLabels

Tagged on:     

7 thoughts on “Retrieving Label from Enum Value in Dynamics AX 2012 SQL DB

  • April 13, 2016 at 3:26 pm
    Permalink

    You are returning both an [EnumItemValue] and an [EnumValueIndex] column. In my (very limited) testing, they always equal for each row. I want to only have one numeric value to use as an index to get the Label. Which would you recommend that I use?

    Reply
    • April 13, 2016 at 11:21 pm
      Permalink

      Hi Mark, only [EnumItemValue]
      Index is used internally, you can ignore that field.

      Label A B C
      Value 0 2 3
      Index 1 2 3

      Thanks
      Nick

      Reply
  • May 11, 2016 at 9:49 am
    Permalink

    Hi Nick,

    I created based on your Procedure a Table Valuated Function. It works like a charm.
    Thanks four your Inspiration.

    Pro: Can be used in Join or Apply without generating Table with all Enum’s
    Con: Hardcoded Model Database Name

    Here is the Code:

    — select * from MyOGetEnumLabel(‘ReqRefType’, ‘de_at’) as t

    Create Function [dbo].[MyOGetEnumLabel]
    (
    @EnumName nvarchar(200)
    , @LanguageId VarChar(10) = ‘de_at’
    )
    Returns @Enum TABLE
    (
    [EnumItemName] nvarchar(200) NULL
    , [EnumItemLabel] nvarchar(max) NULL
    , [EnumItemValue] int NULL

    )
    as
    BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
    — /*Not allowed within a Function*/ SET NOCOUNT ON;

    DECLARE @bin AS varbinary(MAX);
    SET @bin = (SELECT TOP 1 Properties
    FROM ax6r2_live_model.dbo.ModelElement me — Change Model DB Name
    JOIN ax6r2_live_model.dbo.ModelElementData med — Change Model DB Name
    ON med.ElementHandle = me.ElementHandle
    WHERE me.Name = @EnumName
    AND me.ElementType = 40
    ORDER BY med.LayerId DESC);
    DECLARE @pos AS int;
    DECLARE @flags AS int;
    DECLARE @count AS int;
    DECLARE @idx AS int;
    DECLARE @off AS int;
    DECLARE @ret AS varchar(255);
    DECLARE @ValueToReturn AS int;

    DECLARE @value as INT
    DECLARE @IndexToFind as int

    DECLARE @MyTempTable TABLE
    (
    [EnumItemName] nvarchar(200) NULL,
    [EnumItemLabel] nvarchar(max) NULL,
    [EnumItemValue] int NULL,
    [EnumValueIndex] int NULL
    )

    SET @pos = 3;
    SET @off = CAST(SUBSTRING(@bin, @pos, 1) AS int) – 1;
    SET @pos = @pos + 1;
    WHILE @off > 0 –skip BaseEnum Label/Help/CountryRegionCode
    BEGIN
    WHILE SUBSTRING(@bin, @pos, 2) 0x0000
    SET @pos = @pos + 2;
    SET @pos = @pos + 2;
    SET @off = @off – 1;
    END
    SET @flags = CAST(SUBSTRING(@bin, @pos, 3) AS int);
    SET @pos = @pos + 3;
    IF @flags & 0x008000 = 0x008000 –skip BaseEnum ConfigurationKey
    BEGIN
    WHILE SUBSTRING(@bin, @pos, 2) 0x0000
    SET @pos = @pos + 2;
    SET @pos = @pos + 2;
    END
    IF @flags & 0x000002 = 0x000002 –skip BaseEnum ConfigurationKey
    SET @pos = @pos + 1;
    SET @pos = @pos + 1; –skip DisplayLength
    SET @count = CAST(SUBSTRING(@bin, @pos, 1) AS int);
    IF @count > 0
    BEGIN

    SET @pos = @pos + 1;

    IF @flags & 0x000200 = 0x000200 –UseEnumValue property
    SET @idx = @count-1;
    ELSE
    BEGIN
    SET @idx = 0;
    SET @off = 2 + CAST(CAST(REVERSE(SUBSTRING(@bin, @pos, 2)) AS binary(2)) AS int) * 2;
    SET @off = @off + 2 + CAST(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS binary(2)) AS int) * 2;
    WHILE @idx = 0
    BEGIN
    SET @pos = @pos + 2;
    SET @IndexToFind=0;
    WHILE 1 = 1
    BEGIN
    SET @off = 0;
    SET @ret = ”;
    WHILE SUBSTRING(@bin, @pos + @off, 2) 0x0000
    BEGIN
    SET @ret = @ret + CHAR(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS binary(2)));
    SET @off = @off + 2;
    END

    IF SUBSTRING(@ret, 1, 1) = ‘@’ –label file
    BEGIN
    DECLARE @module AS varchar(3);
    DECLARE @label AS int;
    SET @module = SUBSTRING(@ret, 2, 3);
    SET @label = CAST(SUBSTRING(@ret, 5, DATALENGTH(@ret) – 4) AS int);
    SET @ret = (SELECT TOP 1 Text FROM ax6r2_live_model.dbo.ModelElementLabel — Change Model DB Name
    WHERE LabelId = @label
    AND Module = @module
    AND Language = @LanguageId
    ORDER BY LayerId DESC);
    END

    IF EXISTS(SELECT top 1 * from @MyTempTable where [EnumItemName]=@EnumName and [EnumValueIndex]=@IndexToFind)
    BEGIN
    UPDATE @MyTempTable SET [EnumItemLabel]=@ret
    where [EnumItemName]=@EnumName and [EnumValueIndex]=@IndexToFind
    END
    ELSE
    BEGIN
    INSERT INTO @MyTempTable VALUES (@EnumName,@ret,@IndexToFind,@IndexToFind);
    END

    SET @pos = @pos + @off + 2;
    IF @idx <= 0
    BREAK;
    SET @idx = @idx – 1;
    SET @IndexToFind=@IndexToFind+1
    END
    END
    ELSE
    SET @ret = '<NOT FOUND';
    END
    ELSE
    SET @ret = '';

    insert into @Enum
    (
    [EnumItemName]
    , [EnumItemLabel]
    , [EnumItemValue])
    select
    [EnumItemName]
    , [EnumItemLabel]
    , [EnumItemValue]
    from @MyTempTable

    Return
    END

    Reply
    • May 11, 2016 at 11:48 pm
      Permalink

      Hi Robert!
      This is good, will give a try.
      Thanks a lot

      Nick

      Reply
  • Pingback: Cıvata

  • August 29, 2016 at 9:23 pm
    Permalink

    Nick,

    I’m having a consistent issue with the procedure and I’m hoping you can help. For most of our enumerations, the procedure is returning a Value of 0 for the Label with the lowest non-zero Value.

    A simple example is AccountType_IN. The procedure return All, 0, Customer, 0. Customer should actually be 1.

    AccountingEventType is a more complex case that illustrates it better. The procedure returns (in this order) Correction, 13, Period close, 14, Period open, 15, None, 0, Finalize, 16, Cancel, 17, Original, 0. While None should be 0, Original should be 12. If I run the function upon which you based your procedure and pass in the enum name and the value of 12, it returns Original.

    For both of these examples, the code works fine through line 87, looping through and inserting the correct data for each Enum it finds. However, it needs to get the last one from the next block of code. Line 93 sets @IndexToFind=0. That value is never changed the rest of the code. Although it correctly finds the @ret (EnumItemLabel) for that last Enum, it inserts a 0 for the Value no matter what the correct value should be.

    I don’t know how to calculate the correct value for @IndexToFind in that block of code. Can you help with this?

    Reply
    • September 1, 2016 at 1:50 pm
      Permalink

      We actually start getting similar issue with some of the Enums as well. Will have to look at this Mark. Will let you know once I figure this out… Thanks
      Nick

      Reply

Leave a Reply to nicksav Cancel reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">