Error in Expression

Posted on 2009-02-23
Last Modified: 2012-05-06
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

Question by:ochness
    LVL 24

    Expert Comment

    by: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.
    LVL 1

    Author Comment

    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.
    LVL 24

    Accepted Solution

    Then you would just use COALESCE(FieldName, 0) AS OEPeriodFive in the query.  Then all the NULLs will be 0.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    How to increase the row limit in Jasper Server.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now