As a review: In AX 4.0 and 2009, a business was limited to the number of financial dimensions they wanted, I believe the default amount of dimensions was 3, but additional dimensions could be purchased and was regulated by the license file provided by Microsoft when AX is purchased. The various tables that did utilize these financial dimensions simply had a column for each dimension, namely Dimension[1], Dimension[2], Dimension[3], etc... (in SQL: Dimension, Dimension2_, Dimension3_, etc...) depending on how many dimensions your business purchased, of course. Each one of these fields stored the actual value of the dimension right there in that table. If you wanted to reference the name or description of that Dimension, it was easy. AX had a Dimensions table where you could just do a Dimensions::find() and provide the dimension value stored in the above table and your DimensionCode enum type (to specify which dimension you are supplying). Presto, upon returning the Dimensions record, you reference the Description field and you have the dimension description/name.
AX 2012 handles this quite differently...
Instead of there being a limit on the number of financial dimensions, the team at Microsoft found a way to lift that restriction and now allow for infinite dimensions. Yahoo! But it doesn't come for free (don't think monetarily). The price you pay is the complexity of it. Instead of storing each of the dimensions on a given table, their solution is to store a reference (to the related table's RecId) of a record in a centralized dimension table in a column called DefaultDimension. This is the value that will eventually get you to your dimensions.
If you don't care about a technical data structure oversight and just want a simple solution, you can skip down to the solution below, otherwise continue reading (this is starting to feel like a Goosebumps "Give Yourself" book).
The centralized dimesion table I was referring to is DimensionAttributeValueSetItem. The DimensionAttributeValueSet field in this table is going to be the field that DefaultDimension relates to. For each financial dimension that is specified for a record, there will be that many records in the DimensionAttributeValueSetItem table. There is a field called DisplayValue in this table, this is the value of the dimension! Yahoo! Don't get too excited yet, there are no values in this table that will tell us which financial dimensions are which. We will need to join to DimensionAttributeValue and again to DimensionAttribute first. From here it is tricky if you're looking to find the human-readable description in addition, depending on what kind of dimension it is, it will link to a different table. I suggest only linking to the tables you need to. If you have custom dimensions, you will outer join to DimensionAttributeDirCategory and DimensionFinancialTag (as shown below). Any other types of dimensions link to various Views with a "DimAttribute" prefix but also must be linked to the current company that is selected. I've included an example of linking to Customer and Project dimensions. Below you will find a SQL query which will define the join criteria.
SELECT davsi.DisplayValue, da.Name, dft.Description, dact.Name, dapt.NameThe interesting part to note about the above query is the da.Name field. This is the AX 2012 equivalent to the DimensionCode Enum, only AX 2012 doesn't use Enums for dimensions (because Enums cannot (and shouldn't) be created dynamically by your end users, but financial dimensions now can be and are meant to be defined by your end users). So, when coding, you will either need to hard code these dimension names, or create your own Enum or Macro that you must manage separately. I don't like it either but it is what it is.
FROM DimensionAttributeValueSetItem davsi
JOIN DimensionAttributeValue dav
ON dav.RecId = davsi.DimensionAttributeValue
JOIN DimensionAttribute da
ON da.RecId = dav.DimensionAttribute
--Custom dimensions
LEFT JOIN DimensionAttributeDirCategory dadc
ON dadc.DimensionAttribute = da.RecId
LEFT JOIN DimensionFinancialTag dft
ON dft.FinancialTagCategory = dadc.DirCategory
AND dft.RecId = dav.EntityInstance
--Customer dimension
LEFT JOIN DimAttributeCustTable dact
ON dact.Value = davsi.DisplayValue
AND dact.RecId = dav.EntityInstance
AND dact.DataAreaId = 'dat'
--Project dimension
LEFT JOIN DimAttributeProjTable dapt
ON dapt.Value = davsi.DisplayValue
AND dapt.RecId = dav.EntityInstance
AND dapt.DataAreaId = 'dat'
WHERE davsi.DimensionAttributeValueSet = '5637144584'
--where '5637144584' is the DefaultDimension reference value
SOLUTION
I have developed a couple of helper methods that simply the retrieval of dimension data. These can be included in a helper class and referenced to as static methods are normally, but I recommend including these in your Global class, then you can just call the method directly. I have 2 methods: dimValue() and dimDesc(), they return the value and description, respectively. Code and usage is below:
//Usage for dimValue() and dimDesc()
static void JobDimensionUsage(Args _args)
{
SalesLine sl;
DimensionValue value;
Description desc;
;
select firstonly sl;
value = dimValue(sl.DefaultDimension, 'Department');
desc = dimDesc(sl.DefaultDimension, 'Department');
info(strfmt("%1: %2", value, desc));
}
static DimensionValue dimValue(RefRecId _defaultDimension, Name _name)
{
DimensionAttributeValueSetItemView davsi;
DimensionAttribute da;
;
select DisplayValue from davsi
where davsi.DimensionAttributeValueSet == _defaultDimension
join RecId from da
where da.RecId == davsi.DimensionAttribute
&& da.Name == _name;
return davsi.DisplayValue;
}
static Description dimDesc(RefRecId _defaultDimension, Name _name)
{
DimensionAttributeValueSetItemView davsi;
DimensionAttribute da;
DimensionFinancialTag dft;
DimensionAttributeDirCategory dadc;
DimAttributeCustTable dact;
DimAttributeProjTable dapt;
;
select DimensionAttributeValueSet from davsi
where davsi.DimensionAttributeValueSet == _defaultDimension
join RecId from da
where da.RecId == davsi.DimensionAttribute
&& da.Name == _name
outer join Name from dadc
where dadc.DimensionAttribute == da.RecId;
outer join Description from dft
where dft.RecId == dav.EntityInstance
&& dft.FinancialTagCategory == dadc.DirCategory
outer join Name from dact
where dact.RecId == dav.EntityInstance
&& dact.Value == davsi.DisplayValue
outer join Name from dapt
where dapt.RecId == dav.EntityInstance
&& dapt.Value == davsi.DisplayValue;
return (dft.Description ? dft.Description : (dact.Name ? dact.Name : (dapt.Name ? dapt.Name : "")));
}
