none
IF Function in Formula Dialog Box (Report Builder 3.0)

Answers

  • There are some expressions that allow you to pull data from another dataset. Those are the Lookup expressions. Otherwise, you are right. An expression requires all field references to be from the same dataset.

    Please mark the appropriate post(s) as answer(s) so others can find the solution easier.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, December 05, 2013 10:21 PM
  • Thank you, that's what I ended up doing.
    Here's the data the query returns:

    Work Assignment ID Work ID Work Queue Staff ID Reason Date Created Date Last Updated
    1 1234 Queue 1   Caseload 11/1/2013 11/1/2013
    2 1234   2494 Forward 12/1/2013 12/1/2013
    3 1234   1344 Complete 12/5/2013 12/5/2013

    I was hoping I could just filter the query further to load the report quicker to only return this data:

    Work Assignment ID Work ID Work Queue Staff ID Reason Date Created Date Completed
    1 1234 Queue 1 1344 Complete 11/1/2013 12/5/2013

    But since I couldnt get the formula right in the query, I just used lookup expression in my tablix.


    Friday, December 06, 2013 7:36 PM

All replies

  • Maybe I missed it, but there should be an = to start the expression (for example: =iif(Fields!WorkItemAssignmenteason.Value = "Complete", Last Assigned To ID, "n/a")

    HTH

    Wednesday, November 27, 2013 8:16 PM
  • Maybe I missed it, but there should be an = to start the expression (for example: =iif(Fields!WorkItemAssignmenteason.Value = "Complete", Last Assigned To ID, "n/a")

    HTH

    Hi HTH,

    I also tried added an = sign in front of the formula, and it gives this error message:
    The following syntax error occurred: Unexpected =.

    When I click the ok button, the = in front of the formula is selected.

    Also, adding the Fields!.....Value in the formula gives this error message:
    The following character is not valide: !

    I'm adding a formula in the Define Formula window.

    Thank you,

    Wednesday, November 27, 2013 8:20 PM
  • I cant seem to upload an image so i'll try to paste the table directly.

    Here's the data source I have:

    Queue Name User ID Created Date Status Due Date Completion Date Work ID
    123 FO [blank] 10/2/2013 Created 10/12/2013 10/3/2013 1234
    [blank] 10721 10/3/2013 Complete 10/12/2013 10/3/2013 1234

    But I only need 1 row per work ID on the report which should be like this:

    Queue Name User ID Created Date Status Due Date Completion Date Work ID
    123 FO 10721 10/2/2013 Complete 10/12/2013 10/3/2013 1234

    It seems, in the formula, the TRUE or FALSE value cannot be one of the fields.

    I tried this formula and it worked:
    IF (Work Item Assignment Reason = "Complete", "A", "B")

    But if I replaced "A" with a field name (with or without ""), I get the error message. :(


    Anyway, my thought process is completely way off....but if anyone know how or direct me to some other instructions on how to build a report displaying only one row just like the bottom table above, I greatly appreciate it. Thank you so much!
    • Edited by AzuDaioh Wednesday, November 27, 2013 9:48 PM
    Wednesday, November 27, 2013 8:40 PM
  • Hi AzuDaioh,

    As per my understanding, in SQL Server Reporting Services (SSRS), we can use IIF() function to return a specify value based on specify condition.  Please refer to the expression below:
    =IIF(Fields!WorkItemAssignmenteason.Value = "Complete", Fields!LastAssignedToID.Value, "n/a"))

    There is an article about SSRS expression, you can refer to it.
    http://technet.microsoft.com/en-us/library/ms157328(v=sql.105).aspx

    If the issue persist, could you please post your dataset with sample data?

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, November 28, 2013 9:25 AM
  • Hi AzuDaioh,

    As per my understanding, in SQL Server Reporting Services (SSRS), we can use IIF() function to return a specify value based on specify condition.  Please refer to the expression below:
    =IIF(Fields!WorkItemAssignmenteason.Value = "Complete", Fields!LastAssignedToID.Value, "n/a"))

    There is an article about SSRS expression, you can refer to it.
    http://technet.microsoft.com/en-us/library/ms157328(v=sql.105).aspx

    If the issue persist, could you please post your dataset with sample data?

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Sorry, I'm unmarking this as the right answer because it did not work.  I am aware of the above expression but it does not work when I entered it in the formula box.  It gives an error message "The following character is not valid: !"

    The link below provides the 'define formula dialog box' I'm referring to:
    http://technet.microsoft.com/en-us/library/ee240820(v=sql.105).aspx

    Thanks anyway. I was just wondering if its possible to filter my query an returning only 1 row of data just as I provided above. 

    Thanks all for the help.

    Monday, December 02, 2013 5:37 PM
  • The expression provided by Alisa is a proper SSRS function except for the single open parenthesis and 2 close parantheses. If you are getting an error about ! not being valid then you somehow are not entering the formula from the correct context.

    You state that you are trying to create this in query designer. The formula provided in an SSRS expression and is not valid in query designer. If you are designing a T-SQL query you would use:

    CASE WHEN [Work Item Assignment Reason] = "Complete" THEN [Last Assigned To ID] ELSE "n/a" END AS LastAssignedToID

    This assumes that the info you provided in your post is accurate and the columns you reference actually have the spaces in their names. The values I have in the square brackets are intended to be the actual name of the fields. Whenever a field name has spaces in T-SQL, you need to enclose them in brackets.

    If you are simply adding an expression to a cell in a tablix or a textbox then the provided expression should work (minus the extra close parenthesis) as long as the field names are correct. Your example has spaces in the field names while Alisa's does not. To be certain, enter Alisa's formula (without the extra close parenthesis) into expression builder (not query designer). Delete the first field name in the expression, Fields!WorkItemAssignmenteason.Value. Now, with the cursor active at the place where you deleted field, select the Fields (dataset name) category from the lower left pane. In the Values pane, double click the "Work Item Assignment Reason" field. Doing this ensures all the correct formatting and syntax. Now repeat the same process for the "Last Assigned To ID" field. That should resolve it.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, December 02, 2013 8:30 PM
  • Thanks TIM.  I dont think I have T-SQL.  I cant upload an image for some reason but all I know is that I'm using Report Builder 3.0, Report Model Query Designer, using SemanticQuery(?). In my Dataset Properties > Query option, this is displayed in the Query box after I selected the columns I want:

    <SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign">

    But I think I figured it out.  The IF statement is only valid if the fields all belong in one table.  For example:
    IF(Work Item Assignment Reason = "Complete", Last Assigned To ID, "n/a")

    After I created a new field to store the value of Last Assigned To ID in the same table as Work Item Assignment Reason, the formula worked. Well, at least it worked...not sure if it really is the right answer.

    Thank you all.

    Thursday, December 05, 2013 10:10 PM
  • There are some expressions that allow you to pull data from another dataset. Those are the Lookup expressions. Otherwise, you are right. An expression requires all field references to be from the same dataset.

    Please mark the appropriate post(s) as answer(s) so others can find the solution easier.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, December 05, 2013 10:21 PM
  • Thank you, that's what I ended up doing.
    Here's the data the query returns:

    Work Assignment ID Work ID Work Queue Staff ID Reason Date Created Date Last Updated
    1 1234 Queue 1   Caseload 11/1/2013 11/1/2013
    2 1234   2494 Forward 12/1/2013 12/1/2013
    3 1234   1344 Complete 12/5/2013 12/5/2013

    I was hoping I could just filter the query further to load the report quicker to only return this data:

    Work Assignment ID Work ID Work Queue Staff ID Reason Date Created Date Completed
    1 1234 Queue 1 1344 Complete 11/1/2013 12/5/2013

    But since I couldnt get the formula right in the query, I just used lookup expression in my tablix.


    Friday, December 06, 2013 7:36 PM
  • Glad you found a resolution. Please mark the post or posts that the solution you chose as answer(s) so others can find it more easily.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, December 09, 2013 2:15 PM