SSRS error from switch expression

I have a field in an SSRS report that changes what it displays based on the dataset results.  I return a VarChar field that has either an A,I or an empty string.  I use a switch statement to determine the value displayed.

If it is either A or I that is returned it displays a value from a different textbox on the report.  If an empty string is returned it displays the empty string.  It works when an A or an I is returned but it displays #error if an empty string is returned.   Here is the expression in the report:

=Switch(Fields!FOOTNOTE.Value = "A", LTrim(RTrim(ReportItems!Textbox4.Value.ToString)),Fields!FOOTNOTE.Value = "I", LTrim(RTrim(ReportItems!Textbox5.Value.ToString)),LTrim(RTrim(Fields!FOOTNOTE.Value))="","")

Open in new window

I double checked the dataset directly in SQL server and verified that it is actually an empty string being returned and not a NULL value.

Any ideas or even a workaround would be helpful in this situation.
pensiongeniusAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Forgot to mention: whenever you get #Error in your report you can find details about that error in the Output window (CTRL+ALT+O to open the window if needed).
0
 
ValentinoVBI ConsultantCommented:
It appears to me that you've got three situations: A, I and all the rest.  There's no need to test for empty string, conditions in the Switch are processed in the order in which they are given to the function.  So the following should give you the expected outcome:

=Switch(Fields!FOOTNOTE.Value = "A", LTrim(RTrim(ReportItems!Textbox4.Value.ToString))
	,Fields!FOOTNOTE.Value = "I", LTrim(RTrim(ReportItems!Textbox5.Value.ToString))
	, true, Nothing)

Open in new window

0
 
mlmccCommented:
Another consideration is that the field rather than being an empty string is actually NULL which may be causing the issue.

If the above doesn't work you may have to test for NULL as the first test in the switch.

=Switch(IsNull(Fields!FOOTNOTE.Value ),""
        ,Fields!FOOTNOTE.Value = "A", LTrim(RTrim(ReportItems!Textbox4.Value.ToString))
	,Fields!FOOTNOTE.Value = "I", LTrim(RTrim(ReportItems!Textbox5.Value.ToString))
	, true, Nothing)
 

Open in new window


mlmcc
0
 
pensiongeniusAuthor Commented:
Thanks guys, all these suggestions helped, I had already tried essentially the same solutions as you both proposed but the error persists.  Your answers helped me to realize I wasn't crazy.  The issue isn't in the report expression but in the dataset coming back from the SQL stored procedure I am using.  Not completely solved but your answers really sped up the process of eliminating potential causes.  I know now that my report is right, the data is wrong.  If I need more help I will open a new question in the SQL forum.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.