IIf Condition is not working in SSRS Report

(0) Share

Report

Posted on by Krishna potdar

317

Dear All,

I have created one Custom report for Account Entity and wrote an Expression for address1_line1 and address1_line2 Field, its not working(please refer below mentioned Expression). I have tried in Two different instances which are online D365 version, Since i am facing the issue from Yesterday., after deploying in CRM instance i m getting as #Error in that Column.

Expression :=iif(Fields!address1_line1.Value,Fields!address1_line1.Value,Fields!address1_line2.Value)

Please suggest me for the same.

Regards,

Krishna.

*This post is locked for comments

I have the same question (0)

All responses (18)

Answers (0)

Sort by

Jie G Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hello Krishna,

The first parameter of the IFF function to be evaluated should return a Boolean value, the "Fields!address1_line1.Value" in your expression seems to be a string. Conversion from a string type to a 'Boolean' type is not valid. So you need to replace the first parameter of the IFF function according to your business logic so it can return true or false.

Here is detailed introduction of Iff function: docs.microsoft.com/.../iif-function

Was this reply helpful? Yes No
Suggested answer

Alex Fun Wei Jie 33,628 on at

Like (0)

Report
Copy link

Link copied!

Hi,

the problem with your first parameter.

Refer below article , keyword "Decision Functions"

docs.microsoft.com/.../expression-examples-report-builder-and-ssrs

Was this reply helpful? Yes No
Suggested answer

Kishor Kumar 3,710 on at

Like (0)

Report
Copy link

Link copied!

Check it out Krishna

docs.microsoft.com/.../expression-examples-report-builder-and-ssrs

Was this reply helpful? Yes No
Suggested answer

Abby Kong 6 on at

Like (0)

Report
Copy link

Link copied!

Hi Krishna,

Is the logic if line 1 is empty then use line 2?

Try this:

IIf(IsNothing(Fields!address1_line1.Value),Fields!address1_line2.Value,Fields!address1_line1.Value)

Regards,

Abby

Was this reply helpful? Yes No
Krishna potdar 317 on at

Like (0)

Report
Copy link

Link copied!

HI Ada DIng,

Thanks for your response!

The above mentioned query i have pasted for first expression in my scenario i need to add the total 4 iif conditions , but for me first condition only failing,

since i have added many times the same expression in many reports which i have implemented and those are working fine, Now when i wrote the expression recently i am getting the error as # Error in CRM. Please suggest me for the same and please refer the below mentioned Expression query which is causing me issue in my report.

iif(isnothing(Fields!address1_line1.Value) AND (Fields!address1_line2.Value),Fields!address1_line2.Value,Fields!address1_line1.Value)

Regards,

krishna.

Was this reply helpful? Yes No
Krishna potdar 317 on at

Like (0)

Report
Copy link

Link copied!

HI Wei Jie Fun,

Thanks for your response!

I have tried the same with Currency field too , Since its showing the same error, please suggest me the same.

Regards,

Krishna.

Was this reply helpful? Yes No
Krishna potdar 317 on at

Like (0)

Report
Copy link

Link copied!

Hi Kishor,

Thanks for your response!

I have tried the same but same i am getting the same error , Please suggest me for the same.

Regards,

krishna.

Was this reply helpful? Yes No
Krishna potdar 317 on at

Like (0)

Report
Copy link

Link copied!

HI Abby Kong,

Thanks for your response!

The logic which you have mentioned is fine and working fine in the below mentioned Business logic.

=IIf(IsNothing(Fields!address1_line1.Value),Fields!address1_line2.Value,Fields!address1_line1.Value)- if address1_line1.value does't have data print address1_line2, else print adressline1_line1.

Now please refer the below mentioned business Scenario and please let me know where i am wrong.

1) if the address1_line1 doesn't have data and address1_line2 has data, print address1_line2.

2)if the address2_line2 doesn't have dataand address1_line1 has data print address1_line1

3) if address1_line1 and address1_line2 both having data print like this - address1_line1, adress1_line2

4)if address1_line1 and address1_line 2 both are not having data than print as null

Regards,

Krishna.

Was this reply helpful? Yes No
Abby Kong 6 on at

Like (0)

Report
Copy link

Link copied!

Hi Krishna,

Would suggest implementing this in Dynamics to reuse in multiple reports.

----------------------------------------------------------------------------------

You only post the business logic and didn't post the code to check where is wrong?To implement the blow logic in report, try switch function, based on the different conditions, pick a value from line1, line 2, line1&", "&line2, null

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/switch-function

1) if the address1_line1 doesn't have data and address1_line2 has data, print address1_line2.

2)if the address2_line2 doesn't have dataand address1_line1 has data print address1_line1

3) if address1_line1 and address1_line2 both having data print like this - address1_line1, adress1_line2

4)if address1_line1 and address1_line 2 both are not having data than print as null

Regards,

Abby

Was this reply helpful? Yes No
Krishna potdar 317 on at

Like (0)

Report
Copy link

Link copied!

HI Abby Kong,

Thanks for your response !

I will check with that and update you shortly.

Regards,

Krishna.

Was this reply helpful? Yes No