Defined expression in SSRS report is displaying #ERROR

1
votes

I have defined an expression in one of my cells from my dataset1. My Design window and I have to repeat for each month's cells but I'm getting an #ERROR when I click on the PREVIEW tab in SSRS.

My thought is if the ActivityMonth value = 1 and the Type value = "PIF" then display the value in the Data column.

=IIF(Fields!Activity_Month.Value = 1 AND Fields!Type.Value = "PIF", Fields!Data.Value, 0)

I got this WARNING from SSRS:

[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1471.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format.

But it ran successfully.

1
sql-serverreporting-servicesexpression
he logical And operator ist written And. The & operator is the one for concatenating strings, so Fields!Activity_Month.Value = 1 & Fields!Type.Value = "PIF" will most likely result in something like "TrueFalse" which cannot be evaluated as a boolean expression. – Wolfgang Kais
Thanks for the quick reply. I'll try that. I appreciate your help. – Melinda
When I changed the & to "AND" then I got this WARNING from SSRS: [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1471.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format. But it ran successfully. Thank you. – Melinda

1 Answers

1
votes

From the comments and the edit history, it looks like you have used & mark which is used to concatenate strings instead of AND keyword. After editing the expression - for me - the following expression looks great:

 =IIF(Fields!Activity_Month.Value = 1 AND Fields!Type.Value = "PIF", Fields!Data.Value, 0)

But i have two remarks:

It may cause an error due to the different data types returned by the expression (0 is integer, Data.Value has another data type:

If Fields!Data.Value is of type string then use the following expression:

 =IIF(Fields!Activity_Month.Value = 1 AND Fields!Type.Value = "PIF", Fields!Data.Value, "0")

Another thing to mention is that if value contains null it may throw an exception, so you have to check if field contains null: