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