Zack
asked on
Dynamics AX 2012 Balance Report - Need Account Name
Hi EE,
For you Axers out there:
How would I incorporate the Mainaccount 'name' into this query?
Example of a query with Main account Name:
Any assistance is appreciated .
Thank you
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
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
Any assistance is appreciated .
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.