0

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?!

Rick
  • 1,392
  • 1
  • 21
  • 52
  • 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.# – Thom A May 30 '19 at 15:42
  • 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 May 30 '19 at 15:43
  • @Larnu I'm using 2008. 2012 tag removed – Rick May 30 '19 at 15:44
  • An asterisk `*`should be used for LIKE in SSRS. – Hannover Fist May 30 '19 at 16:57

1 Answers1

1

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.

Steve-o169
  • 2,066
  • 1
  • 12
  • 21