CODEWITHSUNDEEP
reporting-servicesreportexpressionvisual-studio-2019reporting-services-2016
Giuseppe Lolli
Giuseppe Lolli

Reputation: 177

SRSS Complex Calculated Value on a Total

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?

Upvotes: 0

Views: 473

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21703

If you have added the expressions you stated as calculated fields in the dataset then take them out of their and just use those same expressions in the report table and they will work OK.

I recreated your dataset, I understand your data probably has more rows but this will not be a problem.

I then created a simple table .

In the last column I used the expressions you stated

enter image description here

The final output looks like this..

enter image description here

You will probably need to change the expression in columns 2 and 3 to be SUMs but other than that it should be fine.

If this does not help, post you report design.

Upvotes: 1

iamdave
iamdave

Reputation: 12243

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.

Upvotes: 1

Related Questions