troubleshooting Question

Dynamics AX 2012 Balance Report - Need Account Name

Avatar of Zack
ZackFlag for Australia asked on
Microsoft DynamicsSQL* Microsoft Dynamics 365
1 Comment1 Solution8 ViewsLast Modified:
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

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

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros