Adding a simple calculated field in SSRS?

Question

I am trying to create a report in SSRS. I have created a simple table report now I need to add a calculated field in that table. Below is that two fields which I need to use to create the calculated field.

type          value
Credit Memo   3463
Invoice       2623
Invoice       3105
Invoice       3664
Invoice       2040
Credit Memo   2929
Credit Memo   2424
Invoice       2549
Invoice       2129
Credit Memo   2957

I need to put a if condition that is:

sum of values that has type Invoice - Sum of values that has type Credit Memo

I have created 2 calculated fields for that, first is:

SumOfInvoice==iif(Fields!new_documenttypeValue.Value="Invoice",(Sum(Fields!invoicedetail1_extendedamountValue.Value)),0)

Second is:

SumOfCreditMemo==iif(Fields!new_documenttypeValue.Value="Credit Memo",(Sum(Fields!invoicedetail1_extendedamountValue.Value)),0)

and then I added a column to the table and write an expression that :

=Sum(Fields!SumOfInvoice.Value)-Sum(Fields!SumOfCreditMemo.Value)

But It is giving me this error:

The expression used for the calculated filed SumOfInvoice includes an aggregate, RowNumber, Running Value, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Can someone please help me with that??

Thanks

You can't put those expressions in the Dataset Calculated Fields, but you can put them in a cell in a table and they should work just fine. Just to clarify the expressions should start with =iif, you don't need to put a name in there. — StevenWhite
I agree that although the expressions are a bit unwieldy, you should just use them instead of trying to create temporary variables. Also please note that when calculating the final value as you attempted to do, you already summed the values when creating the temporary variables, so the Sum() around each temp variable seems redundant to me... — Aron
I am trying this expression in cell directly now it is showing #Error ?? — Mogli
Maybe try isolating the Iif and the Sum to ensure that the components are working. i.e.: Iif(Fields!new_documenttypeValue.Value="Invoice", 1, 0). And, Sum(Fields!invoicedetail1_extendedamountValue.Value) — Aron

niktrs niktrs · Accepted Answer · 2017-07-19T09:10:29

You can nest IIF inside SUM

Sum Of Invoice

=SUM(
Iif(Fields!new_documenttypeValue.Value="Invoice",Fields!invoicedetail1_extendedamountValue.Value,0)
)

The same for credit memos

=SUM(  
Iif(Fields!new_documenttypeValue.Value="Credit Memo",Fields!invoicedetail1_extendedamountValue.Value,0)
)

Invoice - credit expression

= SUM(
Switch(
Fields!new_documenttypeValue.Value="Invoice", Fields!invoicedetail1_extendedamountValue.Value,
Fields!new_documenttypeValue.Value="Credit Memo",-Fields!invoicedetail1_extendedamountValue.Value,
True, 0
)
)

Or a more simple alternative since you only have invoice and credit memos could be

=SUM( 
        IIF(
        Fields!new_documenttypeValue.Value="Credit Memo",
        -Fields!invoicedetail1_extendedamountValue.Value,
         Fields!invoicedetail1_extendedamountValue.Value
            )
        )

Adding a simple calculated field in SSRS?

1 Answers