SSRS report expression with where clause

• var05

  Hall of Fame

  Points: 3011

  More actions

  November 27, 2012 at 2:01 am

  #285443

  Hi All,

  I have to develop a SSRS report with two columns say:

  City_Name Emp_Name

  Both feilds city_name and emp_name are fetched from SQL database.... But instead pulling city_name from database, I wanted city_name as static lables in the report

  City_name Emp_Name

  City A want to fetch emp_name where city_name="City A"

  City B same as above

  City C

  City D

  All the city_names are in database,but I wanted those city_names to be hardcoded...How to achieve this way of filtering in SSRS...

  I am opting this way cause there are some cities where there are no records for it in the database....I wanted the entire report to use single dataset and in the same single report...I don wanna create multiple reports and give filters for every report

  Any help on this?

  Thanks!

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  November 27, 2012 at 7:01 am

  #1562664

  Why not just do a row group on city name?

• var05

  Hall of Fame

  Points: 3011

  More actions

  November 28, 2012 at 4:38 am

  #1563026

  Thanks 4ur reply!

  The below expression works fine

  =Sum(IIF(Fields!Cityname.Value = "CityA", Fields!empname.Value, 'Noname'))

  the above code works.

  Similarly I have another feild AvgRate

  city emp avgrate

  CityA Emp1 7.5

  CityB Emp2 3.3

  but the same expression doest work with decimal values.

  =Sum(IIF(Fields!Cityname.Value = "CityA", Fields!avgrate.Value,0))

  it gives #error....Any help on this?

  Thanks