Link to home
Start Free TrialLog in
Avatar of pensiongenius
pensiongeniusFlag for United States of America

asked on

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.
SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pensiongenius

ASKER

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.