locked
IIF statement not working !!!! RRS feed

  • Question

  • hello !

    I ve a table with 3 differente numbers (1,2,150) , i want from my IIF statment to return 1 if the value in the box is 150 and zero if else. so i wrote this :

    IIF (fields!tipo.value=150,1,0) and it doesn t work it returns false everywhere ... i ve tried in other ways but with no hope.

    does anyone have an idea for that ? 

    thanks

    Monday, January 13, 2014 11:03 PM

Answers

  • Hi,

    DATA file and let us know if it works .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Tuesday, January 14, 2014 3:12 PM
  • Hello,

    Thank you for you reply. You can send me following data:Report .rdl file, DataSet with sample data (sql file) or database .bak file. And what is the edition of your SSRS? Could you please elaborate the steps about your report design?

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, January 16, 2014 1:52 AM
  • hello all. hi alisa 

    Sorry for being late ... i appreciate your help ! But i  found out where was the problem... it is because every textbox of my matrix can have MULTIPLE values  so more than one value contemporary.... So , I ve inserted a child group in the column main group and everything is fine.

    thanks everyone .

    • Marked as answer by Alisa Tang Sunday, January 19, 2014 12:20 PM
    Friday, January 17, 2014 9:53 PM
  • You can use matrix container for this. Add the common fields (ie Institute,answertype etc) on row group and question number on column group.Add your value field on the data section. then you'll get values as below

    InstituteName  AnswerType  Question1 Question2 ...
    ---------------------------------------------------
    Institute1      AT1         Answer1    Answer2 ...
    Institute1      AT2         Answer1    Answer2 ...
    Institute2      AT3         Answer1    Answer2 ...
    ..


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, February 28, 2014 5:48 AM

All replies

  • Try this

    =IIf(fields!tipo.value = 150, "1", "0")


    Please mark as Answer if this answers your question ...Thanks, Sary Awwad

    Tuesday, January 14, 2014 5:58 AM
  • IIF (fields!tipo.value=150,Cint(1),Cint(0))



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, January 14, 2014 6:59 AM
  • i tried both ways that you gave me , but nothing ! very strange problem .. it seems much more hard than i thought.
    Tuesday, January 14, 2014 2:30 PM
  • thanks anyways sary 
    Tuesday, January 14, 2014 2:30 PM
  • thanks anyways visakh

    Tuesday, January 14, 2014 2:30 PM
  • Hmm..what happened when you tried the suggestion? are you trying to set this value to some other parameter/variable?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, January 14, 2014 2:34 PM
  • it returns 0 all the way ... the thing is when i try to put any integer instead of 150 it works fine, even it gives me this warning ( input string is not in the correct format) but it runs,,, why only with 150 ?!!!
    Tuesday, January 14, 2014 2:39 PM
  • Hi,

    With this dataset :

    DECLARE @Tmp TABLE (tipo CHAR(5))
    INSERT @Tmp SELECT 1
    INSERT @Tmp SELECT 2
    INSERT @Tmp SELECT 150
    SELECT * FROM @Tmp

    with your expression in OP :

    =IIF(fields!tipo.value=150,1,0)

    I get this , so can post with more details :


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Tuesday, January 14, 2014 2:46 PM
  • hi . yes that what i need , it works for you ... do you think i have to change the tipo datatype ?!!!
    Tuesday, January 14, 2014 2:51 PM
  • Hi ,

    I tried with INT , VARCHAR & CHAR , it is working for me .

    Change the expression  and delete the .data file in the report path - C:\Users\Username\Documents\Visual Studio 2010\Projects\

    Again click on Preview and check the result .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Tuesday, January 14, 2014 3:01 PM
  • i can t delete the .data file in the report path because i ve a lot to stuff already done in my report , so i gonna loose everything !... i m going to delete this table and try to do everything from the beginning..
    Tuesday, January 14, 2014 3:08 PM
  • Hi,

    DATA file and let us know if it works .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Tuesday, January 14, 2014 3:12 PM
  • yeah sorry data file .... if i use ( fields!tipo.value ) alone it returns where the values are 1 and 2 , and evrything else is empty !!!.  Oh my God 
    Tuesday, January 14, 2014 3:23 PM
  • Hi all.

    I did it by modifying my query and  using instead of the numbers their meaning ( so string ) in the database ( in my case : 1 is interno,2 esterno , and 150 is a pagamento ) ... but i still can t understand why this simple problem is being that hard to fix !!..

    anyways thanks a lot because you ve tried to help me.... have a good times all

    Wednesday, January 15, 2014 12:58 AM
  • Hello,

    Superficially, the expression your post above is correctly. I have create a simple report tried to reproduce the same issue, however, I cannot get the same effect.

    In order to solve your issue more effective, could you please send the .rdl file and the dataset with sample data to us by the following E-mail address.
    E-mail: sqltnsp@microsoft.com

    Best regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Wednesday, January 15, 2014 1:36 AM
  • thanks for your post ..alisa tang

    Wednesday, January 15, 2014 11:31 AM
  • Hello,

    Thank you for you reply. You can send me following data:Report .rdl file, DataSet with sample data (sql file) or database .bak file. And what is the edition of your SSRS? Could you please elaborate the steps about your report design?

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, January 16, 2014 1:52 AM
  • hello all. hi alisa 

    Sorry for being late ... i appreciate your help ! But i  found out where was the problem... it is because every textbox of my matrix can have MULTIPLE values  so more than one value contemporary.... So , I ve inserted a child group in the column main group and everything is fine.

    thanks everyone .

    • Marked as answer by Alisa Tang Sunday, January 19, 2014 12:20 PM
    Friday, January 17, 2014 9:53 PM
  • Hello again guys ! how is everybody ?

    I ve another question, hope to hear your suggestions!. In the database i'm working on, i ve 21 institutes, that have to answer a questionnaire of 200 questions, where these questions are divided into 11 sections. 

    so consider that an institute is  answering the questions number 122 and 123, which have the same answer type ( number of medicines and number of production blocks), obviously what i see in the results of my query are two different rows, one for the answer of the question number 122 and the other of 123.  

    what i need is that i'd like to put these two answers of this institute, in the same row of my table in the report !? . i can do what i ve always done by creating two tables but for this excat information i d like to put them on the same row.

    I'm using the SSRS. 

    Thanks in advance. 

    Thursday, February 27, 2014 5:56 PM
  • You can use matrix container for this. Add the common fields (ie Institute,answertype etc) on row group and question number on column group.Add your value field on the data section. then you'll get values as below

    InstituteName  AnswerType  Question1 Question2 ...
    ---------------------------------------------------
    Institute1      AT1         Answer1    Answer2 ...
    Institute1      AT2         Answer1    Answer2 ...
    Institute2      AT3         Answer1    Answer2 ...
    ..


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, February 28, 2014 5:48 AM
  • Hi ,

    Kindly post sample DDL & expected output format .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Friday, February 28, 2014 5:52 AM
  • hi visakh

    THANKS A LOT FOR REPLYING

    IT WORKS WELL, BUT I CAN T HIDE THE ROWS WHICH I DON T NEED, BECAUSE ONLY 7 INSTITUTES GIVE THE ANSWERS OF THOSE QUESTIONS. I VE TRIED SOME IIF STATEMENTS BUT ALL THE ROWS WERE DIASPPEARING.

    ANY IDEA ?



    Friday, February 28, 2014 11:41 AM