February 6, 2015 at 6:58 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply