IIF Function in Reporting Services

• paula_sql

  SSCrazy

  Points: 2665

  More actions

  August 2, 2009 at 10:44 pm

  #140350

  I have this expression below in my Group row:

  =iif((Fields!PERIODID.Value=1) and (Fields!Year1.Value = Parameters!Year.Value),sum(Fields!Cost_Code_Actual_Cost.Value),"0")

  The sum output for some reason is coming from all 12 PeriodID's and not just PeriodID=1. Does anyone know how to get the sum of Fields!Cost_Code_Actual_Cost.Value when PeriodID =1 only.

  Thanks in advance!!

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 3, 2009 at 9:33 am

  #1033720

  Sounds like you want something like this:

  =SUM(iif((Fields!PERIODID.Value=1) and (Fields!Year1.Value = Parameters!Year.Value), CDbl(Fields!Cost_Code_Actual_Cost.Value), CDbl(0)))

  you can convert to whatever datatype you need.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question