Microsoft Report Builder - Help Needed

Kinetic ERP
1

I have a field where I want to left trim everything before a statement within a string. I am attempting to handle this within a textbox instead of doing a calculated field.

CODE:

=First(REPLACE(IIf(InStr(Fields!MfgComment.Value, "MyStatement:") > 0, 
     Left(Fields!MfgComment.Value, InStr(Fields!MfgComment.Value, "MyStatement:") - 1), 
     Fields!MfgComment.Value), chr(13), VbCrLf))

My understanding is that the IIF statement says “If Fields!MfgComment.Value contains “MyStatement:” then left trim MfgComment from the beginning to the start of “MyStatement:” else use MfgComment as is”.

The rest of the first wrap and char replacements are just to handle new line statements.

However, if “MyStatement:” isn’t detected then the textbox errors:

image
image957×228 9.89 KB

Am I missing a handler somewhere? Not exactly sure why its erroring.

2

If I’m understanding the question, it is because SSRS processes both true and false conditions regardless of the actual condition… I’ve personally been victim of this so you have to rewrite your iif statement in such a way that both true and false statements will be “valid”.

This link explains a little bit more about the phenomenon and even mentions InStr as a common culprit. This may not be the answer but I think it will direct you to a way around it.
IT Help and Advice: SSRS: Common Issues with #Error and IIF

2 Likes
3

After reading this I realized I could just use IF instead of IIF and then the statement worked as it should!

Thanks a ton!

2 Likes
4

Wow, the thanks is mutual. I didn’t realize that I could use IF instead of IIF. I guess IIF is the same concept as CASE. I can see in report writing why IF usually may not suffice… but as long as you include an ELSE it should be fine.

1 Like
5

And we all learnt something… Of course that SSRS black whole is such a pain…

So while we’re lamenting the time wasting black hole that is SSRS. Please vote

https://epicor.ideas.aha.io/ideas/KIN-I-5057

4 Likes
6

Hey it’s not Friday here yet!

1 Like
7

This isn’t the weekly Frideas post either :slight_smile: