web

You’re offline. This is a read only version of the page.

News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics AX (Archived) How to Select SUM(ABS(...

Microsoft Dynamics AX (Archived)

Answered

How to Select SUM(ABS(Data)) in AX

(0) Share

Report

Report

Posted on by Community Member

the Data Amount in my Table have Positive and Negative Values,

I need to SUM it up in my Report with all negative have to be positive to sum it up.

How do i code it?

SELECT SUM(ABS(Amount)) from Table ------*ERROR*
SELECT ABS(SUM(Amount)) from Table ------*ERROR*

any different approach?

thanks.

*This post is locked for comments

I have the same question (0)

All responses Img

All responses (4)

Answers Img

Answers (1)

Verified answer

Mea_ 60,284 on at

Like (1)

Report

Hi RenSonozaki ,

Try this :

select sum(Amount) from table1 where amount < 0;

select sum(Amount) from table2 where amount > 0;

Amount = abs(table1.Amount) + table2.Amount;

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi RenSonozaki !

you try code again:

real sum ;

While select * form Table

{

if ( Table . Amount <0) sum += (Amount * (-1));

else sum += Amount ;

}

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,805 Most Valuable Professional on at

Like (0)

Report

As far as I know, SSRS expression =(sum(abs(Fields!Amount.Value)) should work (but I didn't test it).

If you're looking how to do in a select statement, Ievgen's approach is a nice and efficient workaround. You can't use T-SQL function ABS from X++, unless you use a computed column or a direct T-SQL query.

Fetching all records one by one, as suggested by ThanhNguyen, would be inefficient and if you had many records, it would become a serious problem. By the way, you could use X++ function abs() there, therefore the body of the while loop could be simply sum += abs(table.Amount);.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

i used ievgen Miroshnikov's suggestion, it worked. thanks.

Was this reply helpful? Yes No

Helpful resources

Quick Links

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1

Martin Dráb 4 Most Valuable Professional

#1

#3

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans