Get List of Dimension Segments and Values

Question

For a piece of integration, it has been decided to provide a view that can retrieve a list of values for a particular dimension segment.

Validation doesn't matter at this point, there is already some fairly robust validation down the line, but I'm struggling with how to get this information in SQL.

Ideally I would end up with a table structure that would have only the value (Ie 1010 for BusinessUnit) and the corresponding name (ie BU1).

I can pull the display value and the segment name from the DefaultDimensionView, but, not the name associated with the particular segment value.

Am I overlooking a simple way to expose this information? (Given financial dimensions, probably not, but one can hope)

Answer 1

There is no easy way to get the description for a dimension in SQL, because it requires knowledge of the backing entity table that stores the description.

In AX it is easy, just use the DefaultDimensionView.dimensionDiscription display method:

display String50 dimensionDiscription()
{
    Common common;
    DictTable d;    
    if (this.BackingEntityType)
    {
        d = new DictTable(this.BackingEntityType);
        common = d.makeRecord();    
        select common where common.(this.KeyAttribute) == this.EntityInstance;    
        return DimensionAttribute::getLocalizedNameByEntityAndInstance(this.DimensionAttributeId, this.EntityInstance, common.(this.NameAttribute));
    }    
    return '';
}

It simply makes a lookup on the backing table and field (stored in the DimensionAttriube table).