Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

Dynamics AX 2012 Balance Report - Need Account Name

Hi EE,

For you Axers out there:

SELECT A.ACCOUNT, SUM (REPORTINGCURRENCYAMOUNT) MST, SUM (TRANSACTIONCURRENCYAMOUNT) CUR, TRANSACTIONCURRENCYCODE, NAME
, SUM (CREDITMST) CREDITMST, SUM (BALANCECUR) BALANCECUR, SUM (BALANCEMST) BALANCEMST
, SUM (CREDITCUR) CREDITCUR, SUM (DEBITCUR) DEBITCUR
FROM (
SELECT DISTINCT LEFT (D.DISPLAYVALUE, 9) ACCOUNT, B.RECID, A.SUBLEDGERVOUCHER, B.LEDGERDIMENSION, A.ACCOUNTINGDATE, B.LEDGERACCOUNT
, UPPER (C.NAME) NAME, B.TRANSACTIONCURRENCYCODE, B.TRANSACTIONCURRENCYAMOUNT--, E.CURRENCYCODE
, CASE WHEN B.REPORTINGCURRENCYAMOUNT <0 AND A.ACCOUNTINGDATE <= '2019-01-01' AND A.ACCOUNTINGDATE> = '2019-01-01' THEN -B.REPORTINGCURRENCYAMOUNT ELSE 0 END CREDITMST
, CASE WHEN B.TRANSACTIONCURRENCYAMOUNT <0 AND A.ACCOUNTINGDATE <= '2019-01-01' AND A.ACCOUNTINGDATE> = '2019-01-01' THEN -B.TRANSACTIONCURRENCYAMOUNT ELSE 0 END CREDITCUR
, CASE WHEN B.TRANSACTIONCURRENCYAMOUNT> 0 AND A.ACCOUNTINGDATE <= '2019-01-01' AND A.ACCOUNTINGDATE> = '2019-01-01' THEN B. TRANSACTIONCURRENCYAMOUNT ELSE 0 END DEBITCUR
, CASE WHEN A.ACCOUNTINGDATE <'2019-01-01' THEN B.TRANSACTIONCURRENCYAMOUNT ELSE 0 END BALANCECUR
, CASE WHEN A.ACCOUNTINGDATE <'2019-01-01' THEN B.REPORTINGCURRENCYAMOUNT ELSE 0 END BALANCEMST
, B.REPORTINGCURRENCYAMOUNT
, D.DISPLAYVALUE, A. LEDGER, D. MAINACCOUNT--, B.REPORTINGCURRENCYAMOUNT
FROM GENERALJOURNALENTRY AS A LEFT OUTER JOIN GENERALJOURNALACCOUNTENTRY AS B ON A. RECID = B. GENERALJOURNALENTRY
LEFT OUTER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION AS D ON D. RECID = B.LEDGERDIMENSION
LEFT OUTER JOIN MAINACCOUNT AS C ON LEFT (D.DISPLAYVALUE, 9) = C. MAINACCOUNTID
WHERE ACCOUNTINGDATE <= '2019-01-01' -- AND LEFT (D.DISPLAYVALUE, 9) = '20010.001'
) A --WHERE ACCOUNT BETWEEN '10100.016' AND '10112.001' OR ACCOUNT BETWEEN'20010.001 'AND' 21006.002 '
GROUP BY A.MAINACCOUNT, ACCOUNT, NAME, TRANSACTIONCURRENCYCODE ORDER BY ACCOUNT

Open in new window


How would I incorporate the Mainaccount 'name' into this query?

Example of a query with Main account Name:

select
dh.name
from DIMENSIONHIERARCHY dh
left join DIMENSIONHIERARCHYLEVEL dhl on dhl.DIMENSIONHIERARCHY = dh.RECID
left join DIMENSIONCONSTRAINTNODE dcn on dcn.DIMENSIONHIERARCHYLEVEL = dhl.RECID
left join DIMENSIONCONSTRAINTNODECRITERIA dcnc on dcnc.DIMENSIONCONSTRAINTNODE = dcn.RECID
left join
(
select ma.mainaccountid, ma.name
from MAINACCOUNT ma
left join LedgerChartOfAccounts lcoa on lcoa.RECID = ma.LEDGERCHARTOFACCOUNTS
--where lcoa.name = 'COA'
) ma on ma.MAINACCOUNTID >= dcnc.RANGEFROM and ma.MAINACCOUNTID <= dcnc.RANGETO
where level_ = 1
and ma.mainaccountid is not null

Open in new window


Any assistance is appreciated .

Thank you

ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial