AX2012 Extracting Ledger Accounts and Dimensions in SQL

To be able to extract ledger information in SQL can be useful for analysis and data extraction. Below is a scrip that will allow you to extract the main account and dimension of the ledger transactions.

select SUBLEDGERVOUCHERDATAAREAID, VOUCHER, ACCOUNTINGDATE, LEDGERACCOUNT, MAINACCOUNTID, [AccountName], postingtype,
coalesce(<Dimension1>,”) as Dim1,
coalesce(<Dimension2>,”) as Dim2,
coalesce(<Dimension3>,”) as Dim3

from
(
select
GeneralJournalEntry.SUBLEDGERVOUCHERDATAAREAID,
SubledgerVoucherGeneralJournalEntry.VOUCHER,
SubledgerVoucherGeneralJournalEntry.ACCOUNTINGDATE,
GeneralJournalAccountEntry.LEDGERACCOUNT,
GeneralJournalAccountEntry.LEDGERDIMENSION,
MAINACCOUNT.MAINACCOUNTID,
MAINACCOUNT.NAME as [AccountName],
GeneralJournalAccountEntry.postingtype,
DimensionAttribute.NAME,
DimensionAttributeLevelValueAllView.DisplayValue
from
GeneralJournalAccountEntry join
GeneralJournalEntry on (GeneralJournalEntry.RECID = GeneralJournalAccountEntry.GENERALJOURNALENTRY) join
SubledgerVoucherGeneralJournalEntry on (SubledgerVoucherGeneralJournalEntry.VOUCHERDATAAREAID = GeneralJournalEntry.SUBLEDGERVOUCHERDATAAREAID and SubledgerVoucherGeneralJournalEntry.GENERALJOURNALENTRY = GeneralJournalEntry.RECID) left join MAINACCOUNT on (MAINACCOUNT.[PARTITION] = GeneralJournalAccountEntry.[PARTITION] and MAINACCOUNT.recid = GeneralJournalAccountEntry.MAINACCOUNT) left join
DimensionAttributeLevelValueAllView on (DimensionAttributeLevelValueAllView.[PARTITION] = GeneralJournalAccountEntry.[PARTITION] and DimensionAttributeLevelValueAllView.VALUECOMBINATIONRECID = GeneralJournalAccountEntry.LEDGERDIMENSION) left join
DimensionAttribute on DIMENSIONATTRIBUTE.RecId = DimensionAttributeLevelValueAllView.DIMENSIONATTRIBUTE
where
GeneralJournalAccountEntry.[PARTITION] = 1234) d
PIVOT
(
MAX(DisplayValue)
FOR Name
IN (<Dimension1>, <Dimension2>, <Dimension3>…)
)
AS PivotTable

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s