Expression Issue

• rcr69er

  Ten Centuries

  Points: 1010

  More actions

  February 6, 2015 at 6:58 am

  #295191

  Hi guys,

  I am having an issue where I have one column in a table, which I wish to switch between fields dependant on what parameter group is selected. I.e. if group is 'App Support" then use fieldA if group is 'Infrastructure' then use FieldB.

  I have tried to create an expression which always returns FieldB, even when only 'Application Support' is selected from the parameter list.

  The functionality desired is below:

  @group in ('Application Supprt')

  Column to use FieldA

  @group in ('infrastructure')

  Column to use FieldB

  @group in ('infrstructure', 'Application Support')

  Column to use FieldB

  I have tried many different expression variations including:

  =IIF(InScope(Parameters!group.Value("Application Support")),Fields!No_Of_Incidents.Value,Fields!Num_Of_Incidents.Value)

  or

  =IIF(InScope(Fields!Assignment_Group.Value="Application Support"),Fields!No_Of_Incidents.Value,Fields!No_Of_Incidents2.Value)

  I have also used the IIF statement exclusively to no avail such as

  =IIF(Parameters!group.Value("Application Support"), Fields!No_Of_Incidents.Value,Fields!Num_Of_Incidents.Value)

  but regardless of what @group parameter I select FieldB is always populated.

  Any advice of how to switc the field would be greatly appreciated.

  Query attached ("No. Of Incidents" is FieldA, "Num OF Incidents" is FieldB:

  select top 6 (ap.description),

  p.number,

  p.short_description,

  sum(case when i.incident_state =8 then 1 else 0 end) [No. Of Incidents],

  sum(case when i.incident_state<>8 then 1 else 0 end) [Num Of Incidents],

  s.name [Assignment Group]

  From Incidents i (nolock)

  inner join problem p (nolock)

  on p.sys_id=i.parent

  left join app_services ap (nolock)

  on p.ServiceID=ap.ServiceID

  inner join sys_user_group s (nolock)

  on s.sys_id = i.assignment_group

  where s.name in (@group)

  and p.problem_state not in (5)

  group by ap.description,

  p.number,

  p.short_description,

  s.name

  order by sum(case when i.incident_state =8 then 1 else 0 end) desc, sum(case when i.incident_state<>8 then 1 else 0 end) desc

  Thanks