IIf(Instr) in SSRS reporting error - Switch cases in SSRS Expression

0
votes

My report looks as below:

enter image description here

And my SSRS form of the report is as below:

enter image description here

I want only one line of total and don't want the total percentage line to show up.For this, I would like to add a case/switch statement in the expression:

If Column_grouping contains "percent" then show "%value" 
If column_grouping does not contain "percent" then show "total"

And my SSRS expression is as below:

​​​​​​​=Switch
(Fields!Column_Grouping.Value like "*percent*", Fields!Total_value_Count_pct.Value,
Fields!Column_Grouping.Value not like "%percent%",Sum(Fields!Total_value_Count.Value))

Evidently this expression is wrong. Any help to fix this?!

1
sql-serverreporting-servicesssrs-2008rdlc
One of your string uses % and the other *, is that intended? Also, one return value and the other is an aggregate; does that match the grouping in your report? It seems odd that you would aggregate one column and not the other. Also, you've tagged SSRS 2008 and 2012? Which you really using? There's a big difference between the two.# – Larnu
oops. The statement I added is having all the combinations I tried. It's wrong. I need help to fix this statement. Also pls refresh, question is edited slightly – Rick
@Larnu I'm using 2008. 2012 tag removed – Rick
An asterisk *should be used for LIKE in SSRS. – Hannover Fist

1 Answers

1
votes

I'm thinking you could simplify the expression a bit and just use an IIF statement. The SWITCH is best used with more than 2 expressions to evaluate. Additionally, you should be able to use InStr or Contains as you indicated in the title of the question. I would think the following expression should work.

= IIF(Fields!Column_Grouping.Value.Contains("percent"), 
      Fields!Total_value_Count_pct.Value, Fields!Total_value_Count.Value)

Refer to this answer for a more detailed explanation on how InStr works. Contains will simply check if the string occurs in the field you are using it on.