IIF with an OR statement getting #Error
-
Hello all,
I for the life of me cannot figure out why this would receive a #Error when running the report:=iif((ReportItems!Textbox2004.Value="-") or (ReportItems!Textbox2005.Value="-") ,"-",
(
iif(Fields!FISCAL_YEAR.Value=Parameters!CurrentYear.Value,((Sum(Fields!NPS_RESULT.Value) / Sum(Fields!SURVEY_COUNT.Value)) * 100),0)
-
iif(Fields!FISCAL_YEAR.Value=Parameters!PreviousYear.Value,((Sum(Fields!NPS_RESULT.Value) / Sum(Fields!SURVEY_COUNT.Value)) * 100),0)))
If I take out the OR and just have 1 condition, it works fine, as soon as I add the OR with the 2nd condition, I receive the error.
Can anyone shed some light on what I am missing?Thank you!
-
I think I found the error but I am not sure if there is an alternate solution. I have to change reach of the ReportItems! formulas to cstr(ReportItems!....)...kind of annoying. Any other ways to change it?
Thanks!
-
I did not find a solution. I am not sure what happened yesterday but I was working and today, it is back to giving me #Error.
- CPiacente - Friday, September 22, 2017 9:21 AM
What's the error message you're getting? If the report is generating, and showing the value "#Error" then a message will be shown in message pane in your Output in SSDT.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Let me run it that way. I have been running it on the server because its quite a large report and takes a while.
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox2318.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format.
That is the error I get.
-
So this was my formula that gave that error:
iif(cstr(ReportItems!Textbox1.Value)="-" or cstr(ReportItems!Textbox2.Value)="-","-",ReportItems!Textbox1.Value-ReportItems!Textbox2.Value)
-
I assume that there is a textbox named Texbox2005? What is the contents of it when you run the report?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Yes there is...I generalized the formula with textbox 1 and 2 because the report was rerunning when I sent that message...Here is what it is: so text box 2316 is a - and text box 2317 is 9.4...basically my formula is asking, if one of those text boxes is a - then put a - in text box 2318....if not, subtract the 2.
- CPiacente - Friday, September 22, 2017 10:04 AMYes there is...I generalized the formula with textbox 1 and 2 because the report was rerunning when I sent that message...Here is what it is: so text box 2316 is a - and text box 2317 is 9.4...basically my formula is asking, if one of those text boxes is a - then put a - in text box 2318....if not, subtract the 2.
I'm not sure the problem is the "Or", after seeing this. I think the problem is how your treating your data. Is the image you've provided showing on a dataset? If so, why are you referencing the Textboxes instead of the field (i.e. Fields!MyColumnName.Value)?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I am referencing the text boxes because the formulas are not easy to work with really. Was taking the easy way out. They aren't just a field in the dataset.
- CPiacente - Friday, September 22, 2017 10:15 AM
The problem is more to do with the reference to the value. Those textboxes may have a numeric value, but you are comparing them to a character that is not a digit. That could be the source of the grief. Often, text box values of zero can be suppressed, and a dash appears instead, but it's not something you can test for by character comparison. You may also be dealing with a NULL value, which may have to be taken into account.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply