SUM of column

Finance and Operations/AX Developer Forum
1

Hello All,

I have a calculative field in my SSRS report body and for which i want to sum all the values for that column.

As we cannot use SUM function in report body, i tried using custom code but i think AX does not support it.

So how can i sum up all the values ?

2

Why can’t you use an expression with SUM function? That’s how it’s normally done.

3

I have used the SUM function in expression only but while deploying it gives error as We cannot use the SUM function against the Report items and it is must for me to use the Report items as it is a calculative one.

I do not have dataset fields to use in SUM function.

So is there any way Martin ?

4

Could you explain to me what do you mean by “calculative field” if it’s not a dataset field? Is it a data method? How is the value calculated?

5

Actually the value to that column is calculated by several dataset fields value and using some of the functions.

So for that particular all column values,i want to SUM up all those.

6

Just use a similar calculation in your total summary (or maybe even the same calculation with a different scope). I can’t be more specific since I don’t know your code.

7

Ok Let me share you the code.

=abs(DateDiff(“d”,DateSerial(Year(Fields!AccountingDate.Value), Month(fields!AccountingDate.Value), “1”).AddMonths(1).AddDays(-1),

Fields!AccountingDate.Value))

The whole above expression is written in text box.

So basically the column calculates the total number of days left in the month based on the Accounting date for each record appearing in the table.

So now as we got the remaining days for each records, i want to sum up those remaining days value .

Hope if now you can get my point by the code or else i need to think some other way round.

8

Actually, it’s easier than I thought.

I simplified your expression a little bit to make it more compact:

=-DateDiff(
"d",
DateSerial(Year(Fields!AccountingDate.Value), Month(Fields!AccountingDate.Value) + 1, 0),
Fields!AccountingDate.Value)

Then I added a row to the tablix (Outside Group - Below). There I simply summed up the expression:

=SUM(-DateDiff(
"d",
DateSerial(Year(Fields!AccountingDate.Value), Month(Fields!AccountingDate.Value) + 1, 0),
Fields!AccountingDate.Value))

It works very well.

9

Hello Martin,

Thanks for the way you suggested. It did not actually resolved my issue but i have implemented another way of achieving the values.

Hope this solution may work for several other users.

:slight_smile: