Solved

SSRS error from switch expression

Posted on 2014-02-12
4
1,378 Views
Last Modified: 2014-02-18
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.
0
Comment
Question by:pensiongenius
  • 2
4 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 334 total points
ID: 39855411
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 166 total points
ID: 39856117
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 334 total points
ID: 39856181
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
 

Author Closing Comment

by:pensiongenius
ID: 39867888
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question