none
Expression Help: IIF with lookups and data set references outside a data region

    Question

  • What I would like to do is:

    IF

    x > low threshold

    AND

    x < high threshold

    return: 'Great Job'

    ELSE

    'Please see payroll'

    I am close.. i think..

    =IIF(
    	
    	((Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo"))
    	>= lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds"))
    	
    	,IIF (	
    			((Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo"))
    			<= lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds"))
    			,"Great Job, You're In Range"
    			,"Please Contact Payroll"
    		 )
    		,"Please Contact Payroll"
    	)
    I also tried..

    =switch( ( Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo") > lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds") AND Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo") < lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds") ) ,"Great Job You're in Range",

      (
    Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo")
    < lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds")
    OR
    Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo")
    > lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds")
    )

    ,"Please Contact Payroll" )

    Can anyone help?







    Friday, June 28, 2013 9:59 PM

Answers

  • Hi Deter,

    What's the issue when you using this expression? Please try the expression below:
    =IIF((Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo")) >= lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds") AND (Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo")) <= lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds"),"Great Job, You're In Range","Please Contact Payroll")

    If the issue persists, Please post the result of the expression below.

    1. =Sum(Fields!BreakerHours.Value, "spDemo")
    2. =Sum(Fields!DemoHours.Value, "spDemo")
    3. =lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds")
    4. =lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds")

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by determinism Sunday, June 30, 2013 6:12 PM
    Sunday, June 30, 2013 3:19 AM
    Moderator

All replies

  • Hi Deter,

    What's the issue when you using this expression? Please try the expression below:
    =IIF((Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo")) >= lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds") AND (Sum(Fields!BreakerHours.Value, "spDemo")/Sum(Fields!DemoHours.Value, "spDemo")) <= lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds"),"Great Job, You're In Range","Please Contact Payroll")

    If the issue persists, Please post the result of the expression below.

    1. =Sum(Fields!BreakerHours.Value, "spDemo")
    2. =Sum(Fields!DemoHours.Value, "spDemo")
    3. =lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!LowValue.Value, "spThresholds")
    4. =lookup(ReportItems!Textbox121.Value,Fields!LongName.Value,Fields!HighValue.Value, "spThresholds")

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by determinism Sunday, June 30, 2013 6:12 PM
    Sunday, June 30, 2013 3:19 AM
    Moderator
  • Hey Charlie,

    Thanks for the reply, after doing what you asked I see that when I divide my two values

    Sum(Fields!BreakerHours.Value, "spDemo") / Sum(Fields!DemoHours.Value, "spDemo")

    I am getting back a decimal .143435 

    But when I compare to my thresholds using the lookups, the values stored in the table are in the form of 14.1 and 14.9, etc. I was trying to compare .143 to 14.3, which will not work :-)

    Thanks Charlie!

    • Edited by determinism Sunday, June 30, 2013 6:10 PM fixed a typo
    Sunday, June 30, 2013 6:10 PM