Hi Everyone. I am planning to use the generaljournalaccountentry table to get the ledger transactions for the ledger accounts. I see that there is a ledger dimension field in that table holds the dimension for the ledger (using the dimensionvaluecombination table you can get this) but how I do get individual dimensions per ledger line?
For instance, ledger account combination : 1100 - Sales _site
I need to get sales and site through defaultdimension field.
Here is what I use to get the default dimensions information in SSMS. I have the stored procedure bring back a table that has all of the possible defaultdimension combinations. Then, wherever I need to get the default dimension I execute the stored procedure into a variable table (this code is commented out at the top) and simply join that table to whatever table as the defaultdimenions field. Note that I put in my dimensions of RnD, department, Product_Line, Territoy, and Type_Size. You would need to change these to get the dimensions that you set up in AX.
ALTER PROC [dbo].[AX_spr_defaultDimension]
AS
/*
DECLARE @defaultDimension TABLE
(
defaultDimension VARCHAR(100)
,department VARCHAR(100)
,location VARCHAR(100)
,productLine VARCHAR(100)
,rnd VARCHAR(100)
,territory VARCHAR(100)
,typeSize VARCHAR(100)
)
INSERT INTO @defaultDimension EXEC AX_spr_defaultDimension
*/
IF OBJECT_ID(‘tempdb…#financialDimensionStaging’) IS NOT NULL DROP TABLE #financialDimensionStaging
SELECT DISTINCT
davs.recId ‘DefaultDimension’
,da.NAME
,COALESCE(dft.DESCRIPTION, daomd.name, daomcc.name, ‘’) dimensionValue
INTO #financialDimensionStaging
FROM
AX2012_PROD…DIMENSIONATTRIBUTEVALUESET davs
INNER JOIN AX2012_PROD…DIMENSIONATTRIBUTEVALUESETITEM davsi (NOLOCK)
ON davs.RECID = davsi.DIMENSIONATTRIBUTEVALUESET
INNER JOIN AX2012_PROD…DIMENSIONATTRIBUTEVALUE dav (NOLOCK)
ON davsi.DIMENSIONATTRIBUTEVALUE = dav.RECID
INNER JOIN AX2012_PROD…DIMENSIONATTRIBUTE da (NOLOCK)
ON dav.DIMENSIONATTRIBUTE = da.RECID
LEFT JOIN AX2012_PROD…DIMENSIONFINANCIALTAG dft (NOLOCK)
ON dft.RecId = dav.EntityInstance
AND da.VIEWNAME = ‘DimensionFinancialTag’
LEFT JOIN AX2012_PROD…DimAttributeOMDepartment daomd (NOLOCK)
ON daomd.RECID = dav.ENTITYINSTANCE
AND da.VIEWNAME = ‘DimAttributeOMDepartment’
LEFT JOIN AX2012_PROD…DimAttributeOMCostCenter daomcc (NOLOCK)
ON daomcc.RECID = dav.ENTITYINSTANCE
AND da.VIEWNAME = ‘DimAttributeOMCostCenter’
SELECT
DefaultDimension
,ISNULL(Department, ‘’) department
,ISNULL(Location, ‘’) location
,ISNULL(product_Line, ‘’) productLine
,ISNULL(RnD, ‘’) rnd
,ISNULL(Territory, ‘’) territory
,ISNULL(Type_Size, ‘’) typeSize
FROM
#financialDimensionStaging fd
PIVOT (MAX(dimensionValue) FOR NAME IN
(
[RnD]
,[Department]
,[Product_Line]
,[Territory]
,[Type_Size]
,[Location]
)) AS pvt
Hi Neill, that makes sense but I realized that the defaultdimension field does not exist in the generaljournalaccountentry table. There is a field called displayvalue and it shows me the combination of the financial dimension but how do I get the individual financial dimensions per a ledger transaction.
I would have used your idea if I had the defaultdimension field available. Thanks
I must have missed that part of the question. Not sure where to get to that field for this transaction. Hopefully someone will be able to provide that information. Also, I was not sure if this would be helpful or not: http://sumitsaxfactor.wordpress.com/2011/12/16/dimension-provider-class-and-run-time-dimension-ranges-ax-2012/
Hi Johan87,
It is fairly easy to extrapolate actual dimension values from dimension. An example below -
static void FinDimensionValues(Args _args)
{
CustTable custTable;
DimensionAttribute dimensionAttribute;
DimensionAttributeValueSetStorage davsStorage = new DimensionAttributeValueSetStorage();
DimensionAttributeValue dav;
Map dimensionSpecifiers;
MapEnumerator enumerator;
RefRecId recId;
str myStr;
;
custTable = custTable::find('C100029ST01');
dimensionSpecifiers = DimensionDefaultingEngine::getDefaultDimensionSpecifiers(custtable.DefaultDimension);
enumerator = dimensionSpecifiers.getEnumerator();
while (enumerator.moveNext())
{
recId = enumerator.currentKey();
myStr = enumerator.currentValue();
dimensionAttribute = dimensionAttribute::find(recId);
info (strFmt("%1 -> %2 (%3)", dimensionAttribute.Name, myStr, recId));
}
}
Hi Harish. There is no defualtdimension field in the generaljournalaccountentry table. I need to use the same logic that the lengertransbase form uses. Look at the financial dimension tab. I can see each financial dimension seperately…