Solved

SSRS error from switch expression

Posted on 2014-02-12
4
1,452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 69
Clone table from one server.database to another server.database 24 49
SSRS Expression syntax with Iif Else 1 36
SQL server client app 3 32
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…

733 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