?
Solved

SSRS error from switch expression

Posted on 2014-02-12
4
Medium Priority
?
1,763 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 1002 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 101

Assisted Solution

by:mlmcc
mlmcc earned 498 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 1002 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

850 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