Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL forum / Dynamics SL - SQL Extr...
Microsoft Dynamics SL forum
Suggested answer

Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

(0) ShareShare
ReportReport
Posted on by Magnified 35

Hey there.

We're working towards extracting the transaction data out of Dynamics SL to be used in a Oracle Hyperion.

I've successfully written the query out of Dynamics to give me a list of all the Transactions - no problem. This data matches the transactional data coming out of Dynamics/Trial Balance - win.

Hyperion would prefer to have the "Net" value of each transaction, not separate columns for Debit/Credit.

Is there a way to know if an account is normally a debit or credit account such that I can calculate the "Net" value of the transaction?

Do you have any guidance in this area? - Or have some sample SQL code to help?

Would appreciate any direction you may have.

Thank you.

Mike

  • MARC_ Profile Picture
    MARC_ 620 on at
    RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

    An easier way to write the case may be like this:

    select t.acct, a.accttype, t.perpost, t.dramt, t.cramt,
    CASE WHEN a.AcctType LIKE '_[AE]' THEN t.DrAmt - t.CrAMt ELSE t.CrAmt - t.DrAmt END as 'Net'
    from gltran t
    join account a on a.acct = t.acct
    cross join glsetup s
    where t.LedgerID = s.LedgerID AND Posted = 'P'

     

    use SUM() and group by if you don't need all the detail lines, but just the net number by account, period, etc. 

  • Suggested answer
    John_Joyce Profile Picture
    John_Joyce 1,040 on at
    RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

    Easy script:

    CASE WHEN GLTran.CrAmt <> '0' THEN (GLTran.CrAmt *-1)

    WHEN GLTran.DrAmt <> '0' THEN (GLTran.DrAmt)

    ELSE '0' END AS [Natural Amt DR(CR)]

    More detailed based on Marc D's reply:

    A. = Account table

    G. = GLTran table

    CASE WHEN A.AcctType in ('1A','4E') and G.DrAmt > '0' then (G.DrAmt)

    WHEN A.AcctType in ('1A','4E') and G.DrAmt < '0' then (G.DrAmt)

    WHEN A.AcctType in ('2L','3I') and G.DrAmt > '0' then (G.DrAmt)

    WHEN A.AcctType in ('2L','3I') and G.DrAmt < '0' then (G.DrAmt)

    WHEN A.AcctType in ('1A','4E') and G.CrAmt > '0' then (G.CrAmt * -1)

    WHEN A.AcctType in ('1A','4E') and G.CrAmt < '0' then (G.CrAmt * -1)

    WHEN A.AcctType in ('2L','3I') and G.CrAmt > '0' then (G.CrAmt * -1)

    WHEN A.AcctType in ('2L','3I') and G.CrAmt < '0' then (G.CrAmt * -1)

    ELSE '0' END AS [Natural Amount DR(CR)],

  • MARC_ Profile Picture
    MARC_ 620 on at
    RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

    join to account table to use accttype in case statement 

  • Magnified Profile Picture
    Magnified 35 on at
    RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

    Essentially, this is the expected output:

    1) Assets - Debits positives, Credits Negative

    2) Liabilites – Debits negatives, Credits positives

    3) Revenues – Debits negatives, Credits positives

    4) Expenses – Debits positives, credits negatives

    5) Equity – Debits negatives, credits positives

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,493 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans