Ledger and Financial Dimension Link

Finance and Operations/AX Developer Forum
1

Hey All,

I’m diong a BI project and trying to link the LedgerDimension (Main Account - Financial Dimension 1-Financial Dimension 2) string to a paticular financial dimension value. I’m essentially trying to pull all journal entries that have a paticular financial dimension on it. I’m stuck trying to see how the LedgerDimension field on the GeneralJournalAccountEntry table relates to the DimensionFinancialTag table. There are like 20 “dimension” tables and I havent figured out how they all link together. Has anyone figured this out before?

Also using X++ to parse the string is not an option, I need to figure out the joins through a SQL Query

Thanks,

Kyle

2

Here is a very simple way to do the basic function. As written, it assumes that the value for the financial dimension is not in the first segment of the ledger account display value. A bit more code could make it better.

DECLARE

@FinDimValue NVARCHAR(60) = ‘BAT’

SELECT DAVC.DisplayValue, GJAE.* FROM GeneralJournalAccountEntry GJAE
INNER JOIN DimensionAttributeValueCombination DAVC ON DAVC.RecId = GJAE.LedgerDimension
WHERE DAVC.DisplayValue LIKE ‘%-’ + @FinDimValue + ‘-%’
OR RIGHT(RTRIM(DAVC.DisplayValue), LEN(@FinDimValue) + 1) = ‘-’ + @FinDimValue