Post reply

If statement problem in SSRS 2005 expression

  • February 1, 2006 at 9:16 am

    #166928

    Ok, this is weird.  When you are in the expressions editor, if your report has more than one dataset defined like mine (because I'm doing some other calcs), then you see this in the left pain:

    Fields(DataSet1)

    Datasets

    What I was doing was adding fields to my IIf statement that were actually in the report already...they were preceeded by SUM (because they're in a group) so this is not going to work obviously.

    So I realized, I had to use the Fields(DataSet1) fields which were my original fields and then changed it to this:

    =IIf(Fields!New_Old_CC.Value = 1, SUM(Fields!PDC.Value),0)

    Ok, the report rendered...some were summed but in some of the fields for that column I got an error:

    [rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox86’ contains an error: Input string was not in a correct format.

  • February 1, 2006 at 9:39 am

    #618144

    Hmm, I'm stumped, the iif appears to be structured properly.

     

  • February 1, 2006 at 9:46 am

    #618148

    sometimes New_Old_CC may be null so I need to ch eck for null somehow in the expression functions...wonder if I should do an IIf inside my IIf to check for IsNothing on New_Old_CC ?

  • February 1, 2006 at 9:51 am

    #618153

    I need to do somethign like this but my syntax is not correct:

    =IIf(IIf(

    Not(Isnothing(Fields!New_Old_CC.Value) AND Fields!New_Old_CC.Value = 1), Fields!PDC.Value,0)

  • February 1, 2006 at 10:51 am

    #618175

    Ok, I am trying now to figure out this final error actually:

    I am still getting this remaining error when that field renders in the report:

    "The Value expression for the textbox ‘textbox86’ contains an error: Input string was not in a correct format."

    so I wonder if it's something else with the field New_Old_CC that it's not liking when evaluating it from my dataset.  I get some fields with values and then the rest #Error in my report when it renders now

  • March 1, 2006 at 11:08 am

    #624071

    I am having the same issue. 

    The error I get is

  • "

  • The Hidden expression used in textbox ‘GrossSales’ returned a data type that is not valid. "

  • And the code that causes it is

    " <Visibility>

           

    <Hidden>=IIf(Fields!GrossSales.Value &lt; 0,"false" , "true")</Hidden>

       </Visibility>

    "

    If you figure out what is going on or see a problem with my code please let me know.

    Another point of interest is that this code works in ssrs 2000 but not in ssrs 2005.

    Thanks in advance for your help.

     

  • March 2, 2006 at 5:57 am

    #624261

    Chris,

    For my visibility i use non-stringed True and False values, as in

    =Iif(CountRows("dsDailyProcessingMonitor") = 0, True, False)

    Steve.

  • March 2, 2006 at 10:14 am

    #624384

    I changed my visibility section to read

    <Visibility>

                            <Hidden>=IIf(Fields!Sales_Vs_COHD.Value &lt; 0, False , True)</Hidden>

                          </Visibility>

    I no longer get an error but all of my boxes are coming up hidden now even though Fields!Sales_Vs_COHD.Value is never less then 0 in this instance.

    What version of SSRS are you running?  2005?

  • March 2, 2006 at 11:51 am

    #624424

    SSRS 2005

  • March 2, 2006 at 11:53 am

    #624425

    I ended up having to do a stupid work around where I placed the field value in a table in SQL Server 2005 then grabed the value from the table...the table only contains one column one row...one value.  When you bring a value in through a table in a dataset, you can then leave out the SUM() portion and reference it just by Fields!

    this is stupid how you can't get around this.

  • March 7, 2006 at 2:43 am

    #625126

    Hello,

    You could first check for null with ISDBNull:

    Your're expression

    =IIf(Fields!New_Old_CC.Value = 1, SUM(Fields!PDC.Value),0)

    should be changed to something like this:

    =IIf(IIF(ISDBNull(Fields!New_Old_CC.Value),0,Fields!New_Old_CC.Value) = 1, SUM(Fields!PDC.Value),0)

    Haven't tested it , but I think it should work!

    Regards,

    Jos Janssen

     

  • November 5, 2007 at 12:24 am

    #748117

    lol?

  • March 12, 2008 at 1:49 pm

    #788777

    Grasshopper and others,

    I believe your solution is as follows:

    You don't need the iif statement for Visibility, you just need the first part if you're doing "True" or "false",

    So your expression is just:

    =Fields!GrossSales.Value < 0

    This will automatically send true or false to SSRS. Using the iff statement will ALWAYS return the third parameter of the iff statment for whatever reason:)

    I hope this works for you.

    Paul

    @Cizer Software

    • Viewing 13 posts - 1 through 12 (of 12 total)

    You must be logged in to reply to this topic. Login to reply