IIF with an OR statement getting #Error

Question

IIF with an OR statement getting #Error

CPiacente

SSC Eights!

Points: 863
More actions
September 21, 2017 at 10:36 am

#328679

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!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

CPiacente SSC Eights! Points: 863 More actions · Answer 1

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!

CPiacente SSC Eights! Points: 863 More actions · Answer 2

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.

Thom A SSC Guru Points: 99130 More actions · Answer 3

CPiacente - Friday, September 22, 2017 9:21 AM
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.

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

CPiacente SSC Eights! Points: 863 More actions · Answer 4

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.

CPiacente SSC Eights! Points: 863 More actions · Answer 5

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)

Thom A SSC Guru Points: 99130 More actions · Answer 6

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

CPiacente SSC Eights! Points: 863 More actions · Answer 7

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.

Thom A SSC Guru Points: 99130 More actions · Answer 8

CPiacente - Friday, September 22, 2017 10:04 AM
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.

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

CPiacente SSC Eights! Points: 863 More actions · Answer 9

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.

sgmunson SSC Guru Points: 110640 More actions · Answer 10

CPiacente - Friday, September 22, 2017 10:15 AM
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.

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)