Basically the dollars are stored so that positive numbers increase the balance of the account and negative numbers decrease it. So you will have to add logic to swap the sign for debits in credit accounts.

Depending on your SQL version and how much access you have to the statement to make changes, you can add a CASE statement that will switch it for any account that is a credit account. Of course you will have to swap the sign on credits in credit accounts as well if you show the negative signs. If you don't have that kind of access to the SQL level, you will have to handle it in the post-SQL processing stage.

Are you able to provide any example code?

Are you able to provide any example code?

Sorry, I don't give away SQL code but if you need help fixing your's, I might be able to point you in the right direction.

In accounting Asset and Expense accounts are debit accounts, the rest are credit accounts.

CASE
WHEN tactrang.nAcctClass in (2,3,4) then (tJEntLYA.dAmount * - 1)
ELSE
tJEntLYA.dAmount
END AS 'Amount'

... This is the case I built, look about right?

Mike

What I see in this statement is that you wish to turn any debit into a positive number and any credit into a negative number for any liability, equity or revenue account in last year's journal entries.

If that is your goal, it should work just fine.

Hi Richard - Using the info you provided, I was able to create a proper export.

Thank you!!

Hey Richard - 4 years ago we chatted about this!  Hoping you can provide some additional insight.

For the project I was working on previously, we only really used the P&L piece for the BI Tool. We're now diving into the Balance Sheet items - which in some cases, isn't lining up how we'd like based on the code I wrote below:

CASE
WHEN tactrang.nAcctClass in (2,3,4) then (tJEntLYA.dAmount * - 1)
ELSE
tJEntLYA.dAmount
END AS 'Amount'

Essentially, the code above is always flipping the sign when the amount is from a Credit Account.

Using this logic on one of our transactions is shown below:

Out of Sage/50/Can:

Result Set out of MySQL

** [OUT] is the result of my calculation above.

When we import this transaction into our BI tool - the balances go out of wack.

The assumption when going into the BI tool is:

Assets - Debits positives, Credits Negative.
Liabilites – Debits negatives, Credits positives.
Revenues – Debits negatives, Credits positives
Expenses – Debits positives, credits negatives.
Equity – Debits negatives, credits positives

It seems the sign for 12020 is good... its an asset account, performing a debit - should be positive.

The sign for the 49000 seems off - its a revenue account, performing a credit - should be positive - but the logic is telling us to flip the sign...

We're performing other imports related to revenue, and it appears OK - but I want to start back at the source-system and really be sure I'm thinking of this correctly.

Unfortunately, I don't have a "great" accounting brain.

At a quick glance, do you see what I may be doing wrong?

Very much appreciate your contribution to the community!

Thank you.

M.

Sage 50 stores all credit balances in accounts in nAcctClass 2, 3 and 4 as positive numbers.

Your SQL statement turns them into negatives.

Majestix said:

Revenues – Debits negatives, Credits positives

If your BI Tool is expecting this, then why did you write the SQL statement to convert all credits to negatives?  I think that is where you need to start looking at your logic.  nAcctClass 1, 2 and 3 are the Balance sheet Assets, Liabilities and Equities respectively regardless of account number.

BTW you don't have to link to other websites to upload images, you can just upload them to this site using Insert and select Upload when it is available (it's not always there, like today but usually it is).