How to set visibility expression in SSRS?

Question

I am using SSRS 2008 with a tablix. I want to make one of my rows visible, when field value = 1 and invisible when this value = 0. How do I configure this? Right now I right-clicked teh row and entered the field value for the expression. Then I said "= 1" for visible. I've also tried: ="1" (text), true, "true".

But all of these result in the row always appearing, regardless if it is 1 or 0. This is an integer field value. How to rewrite the expression to make this row visible or invisible depending on the value?

I think the problem now is the T-SQL and not SSMS. Currently, it returns these records on separate rows. Instead I think I need all of these values on just one row so that all of these values can appear in the table at one time. How could I rewrite this snippet to return the same information on just one line?

CASE WHEN [test_details].[test_setup_details_caption] LIKE '%?%' 
      THEN LEFT([test_details].[test_setup_details_caption],CHARINDEX('?',[test_details].[test_setup_details_caption]))
      ELSE [test_details].[test_setup_details_caption] END [test_setup_details_caption],
      [test_details_answers_expanded_view].[test_setup_answers_value],
      CASE WHEN [test_details].[test_setup_details_caption] in ( 'Self-Harm', 'Suicidal Ideations / Attempts')
            AND [test_details_answers_expanded_view].[test_setup_answers_value] >0 THEN 1 ELSE 0 END AS [Self_Harm_Flag],
      CASE WHEN [test_details].[test_setup_details_caption] in ( 'Substance Abuse / Drug Use')
            AND [test_details_answers_expanded_view].[test_setup_answers_value] >0 THEN 1 ELSE 0 END AS [Drug_Use_Flag],
      CASE WHEN [test_details].[test_setup_details_caption] in ( 'Homicidal Ideations / Attempts')
            AND [test_details_answers_expanded_view].[test_setup_answers_value] >0 THEN 1 ELSE 0 END AS [Homicidal_Falg],
      CASE WHEN [test_details].[test_setup_details_caption] in ( 'Runaway Behavior')
            AND [test_details_answers_expanded_view].[test_setup_answers_value] >0 THEN 1 ELSE 0 END AS [Runaway_Flag],
      CASE WHEN [test_details].[test_setup_details_caption] in ( 'Problem Sexual Behavior', 'Inappropriate Sexual Behavior')
            AND [test_details_answers_expanded_view].[test_setup_answers_value] >0 THEN 1 ELSE 0 END AS [Sexual_Bx_Flag],
      CASE WHEN [test_details].[test_setup_details_caption] in ( 'Gang Involvement')
            AND [test_details_answers_expanded_view].[test_setup_answers_value] >0 THEN 1 ELSE 0 END AS [Gang_Flag]

Steven Schroeder · Accepted Answer · 2012-02-14T16:47:30.073

5

You are setting the expression for: Hidden, correct? Try:

=IIf(Parameters!value.Value = 1, False, True) ' Note: Setting Hidden to False will make the row visible

You may want some other logic since you refer to "one of my rows" not all of them

=IIf((Parameters!value.Value = 1) And (RowNumber(Nothing) = 5), False, True) ' Only applies to row # 5

edited Feb 14 '12 at 16:47

answered Feb 14 '12 at 15:43

Steven Schroeder

5,904
2
21
15

Yes I did try that. Can u look at my revised description please? – salvationishere Feb 14 '12 at 16:44
It looks to me like the SQL snippet you provided does provide values on a single row. It is returning the fields test_setup_details_caption, test_setup_answers_value, Self_Harm_Flag, Drug_Use_Flag, Homicidal_Falg, Runaway_Flag, Sexual_Bx_Flag, Gang_Flag all on one row does it not? – Steven Schroeder Feb 14 '12 at 17:28
No. These values all appear on separate rows; a total of 8 rows. – salvationishere Feb 14 '12 at 17:32

How to set visibility expression in SSRS?

1 Answers1