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

Hi RishiRauli,

According to your description, you want to add a column to the report which data will depend on parameter @groups, if you select Application Supprt, FieldA will be displayed, if you select infrastructure, FieldB will be displayed, if you select both Application Supprt and infrastructure, FieldB will be displayed. If that is the case, please refer to the following steps:

1. Create multi-value parameter @Groups, select Allow multiple values.
   
2. Click Available Values in left pane, click Add button, then add Application Supprt and infrastructure selections.
   
3. Right-click the dataset you used to retrieve data for the report, then click Dataset Properties.
   
4. Click Filters in left pane, select the field from Expression drop-down list, set Operator to IN, in Value text box, type [@Groups].
   
5. Right-click the column handle you want to add expression, then click Expression.
   
6. In Expression text box, type the expression like below:
   

=Switch(Parameters!Groups.Count=1 and Parameters!Groups.Value(0)="Application Supprt", Fields!FieldA.Value,Parameters!Groups.Count=1 and Parameters!Groups.Value(0)="infrastructure",Fields!FieldB.Value,Parameters!Groups.Count=2 and Join(Parameters!Groups.Value,",")="Application Supprt,infrastructure",Fields!FieldB.Value)

The following screenshots are for your reference:
 

 
If you have any more questions, please feel free to ask.

Thanks,
Wendy Fu

Good afternoon,

 

I will a bit more clear with my instructions:

 

This is part of my drop down list:

 

 

If application Support is selected by itself, then Field Value should be No_Of_Incidents, if anything else is selected including a multiple selection including Application Support then field value should be Num Of Incidents].

Thank you.

=IIF(Parameters!Groups.Value="Application Support",Fields!No_Of_Incidents,Fields!NumOfIncidents)

Does this get you what you need?

Sounds like this to me

=IIF(Len(Replace("," & Join(Parameters!Groups.Value,",") & ",",Application Support,","")) = 0,Fields!No_Of_Incidents.Value,Fields!NumOfIncidents.Value)

Hi RishiRauli,

Please refer to the expression like below:

=IIF(Parameters!Groups.Count=1 and Parameters!Groups.Value(0)="Application Supprt",  Fields!No_Of_Incidents.Value,IIF(Parameters!Groups.Count>1 and InStr(Join(Parameters!Groups.Value,","), "Application Supprt")>0,Fields!Num_Of_Incidents.Value,"Null"))

If you have any more questions, please feel free to ask.

Thanks,
Wendy Fu

Hi Wendy,

The Application support when selected by itself displays No_of_Incidents, however when selecting anything else, NULL appears rather than num_of_incidents.

Hi Visakh,

The no_of_incidents value appears when anything is selected in this instance.

I would suggest you simplify. If there are two different fields, you want to display the first when a certain set of conditions are true and display the second when they are false then add 2 columns, one for each of the 2 fields. Now set the column visibility so that the first column is visible and the second is hidden when the conditions are true, vice versa when false.

Given your initial set of Conditions:

@group in ('Application Supprt')

Column to use FieldA

@group in ('infrastructure')

Column to use FieldB

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

Column to use FieldB

We can set visibility for column with FieldA to "Show or hide based on an expression" with the following expression:

=IIf(Parameters!group.Value = 'Application Supprt', False, True)

The expression determines whether to hide the column so False means show and True means hide.

Set the expression on the alternate column the same but switch the True and False:

=IIf(Parameters!group.Value = 'Application Supprt', True, False)

Hi Tim,

I thought of doing this but it took an absolute age to format the matrix so If I add a column it just goes to the bottom of the page.

Not sure what you mean by absolute age to format the matrix. You should be able to add column > inside group either right or left then set the 2 column's visibility. I guess I am missing something.

Ok.

Almost there:

I did the following and it all works except for when I select all it still displays FieldA not fieldB:

Expression I used is:

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

I think I am missing a count =1 or something on the initial expression for when Application Support is selected by itself to return FieldA, and anything else fieldB.

Hi Tim,

Regardless of myself adding an inside group or not the table is nested in a box and if I add a column the formatting is off hence the requirement to resolve it via an expression.

Thank you,