asked on

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

DBAduck - Ben Miller

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.

ochness

ASKER

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.

ASKER CERTIFIED SOLUTION

DBAduck - Ben Miller

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial