I have th next table in my SRSS report:
Month Amount + Tax PY Amount + Tax CY Amount + Tax PY vs CY %
--------------------------------------------------------------------------------------------------
Gennaio 0 124.687 0%
Febbraio 5.817 169.236 2.809%
Marzo 397 158.143 39.720%
Aprile 1.059 202.928 19.067%
Maggio 18.016 240.273 1.234%
Giugno 79.429 315.122 297%
Luglio 144.766 198.513 37%
Agosto 103.777 158.023 52%
Settembre 225.305 457.579 103%
Ottobre 214.439 453.605 112%
--------------------------------------------------------------------------------------------------
Total 793004,97 2478110,02 [Error]
The column [Amount + Tax PY vs CY %] is a calculated field with the next expression:
=IIf(Fields!Amnt_Tax_PY.Value=0,0,(Fields!Amnt_Tax_CY.Value-Fields!Amnt_Tax_PY.Value)/IIf(Fields!Amnt_Tax_PY.Value=0,1,Abs(Fields!Amnt_Tax_PY.Value)))
I would like to calculate the total of that column using the total of the columns [Amount + Tax PY] and [Amount + Tax CY] using the next expression:
=IIf(sum(Fields!Amnt_Tax_PY.Value)=0,0,(sum(Fields!Amnt_Tax_CY.Value)-sum(Fields!Amnt_Tax_PY.Value))/IIf(sum(Fields!Amnt_Tax_PY.Value)=0,1,Abs(sum(Fields!Amnt_Tax_PY.Value))))
But is giving me an error... the expected result should be : 212%
The error is:
aggregate, rownumber, runningvalue, previous and lookup functions cannot be used in calculated field expression
What I'm missing?
Your error message is telling you the answer:
aggregate ... functions cannot be used in calculated field expression
sum is an aggregate function.
It looks like you are trying to add the overall total as a calculated column within the dataset, which as you can see from the error you are getting is not possible.
If you want to have the Amount + Tax PY vs CY % column in the table you can do so on a row by row basis without the aggregations in the expression, and then to get the overall figure for display in the report, use the expression with the aggregates in.