Error in Expression

I am writing a report in SSRS and getting the following error for an expression I'm trying to write: [rsRuntimeErrorInExpression] The Value expression for the textbox OEPeriodOne contains an error: Input string was not in a correct format.

What I'm trying to do is provide sales dollars by period so there are periods on the report that don't have data yet. I have included the code that I'm using. When there is no data for the period the code is fine, but when there is data for the period I get the above error.

Any help would be great. Thanks.
=iif(Fields!OEPeriodFive.Value = "", FormatCurrency(0, 2), FormatCurrency(Fields!OEPeriodFive.Value, 2))

Open in new window

Who is Participating?
DBAduck - Ben MillerPrincipal ConsultantCommented:
Then you would just use COALESCE(FieldName, 0) AS OEPeriodFive in the query.  Then all the NULLs will be 0.

DBAduck - Ben MillerPrincipal ConsultantCommented:
To me this would mean that the value in the OEPeriodFive.Value is not a numeric value and could not be formatted in Currency.  But without seeing the data, I am guessing.

But you could do the query and check for that field to be ISNUMERIC(field) = 1 in SQL and see if you get the right number of rows back as opposed to a regular query.
ochnessAuthor Commented:
Thank you for your response.

The field we are working with is a numeric field...the database returned all "1". Maybe I should handle this in my query not in SSRS. I'm just not sure how to handle when the subquery that makes up OEPeriodFive returns a NULL. I would like it if it returned a NULL to make it a 0 then I shouldn't have to worry about it in SSRS.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.