SSRS Expression


I am trying to build an expression in SSRS to do the following:

I have 5 fields that are involved. BenefitPlanCode, KronosEmprContribution, KronosEmpDeduction, TotalEmployeePremium and Amount (plus the expression field)

If BenefitPlanCode = "LIFETX" and (KronosEmprContribution<> Amount) then expression field = "LIFETX MISMATCH"
If BenefitPlanCode = "MEDICAL COPAY" and (KronosEmpDeduction<> Amount) then expression field = "MEDICAL COPAY MISMATCH"
If BenefitPlanCode = ("MEDICAL") and (TotalEmployeePremium<>Amount) then expression field = "MEDICAL MISMATCH"

I tried the following switch function but it doesn't work properly even though it doesn't generate an error:

=Switch(Fields!BenefitPlanCode.value = "LIFETX" and
Fields!KronosEmprContribution.Value<>Fields!Amount.Value,"LIFETX MISMATCH",Fields!BenefitPlanCode.value = "MEDICAL COPAY" and
Fields!KronosEmpDeduction.Value<>Fields!Amount.Value,"MEDICAL COPAY MISMATCH",Fields!BenefitPlanCode.value = "MEDICAL" and
Fields!TotalEmployeePremium.Value<>Fields!Amount.Value,"MEDICAL MISMATCH")

Can someone help please?

Thank you :)
Who is Participating?
planoczConnect With a Mentor Commented:
If the 3 different values are all in the same textbox try this.....

IIf(Trim((Fields!BenefitPlanCode.Value) = "LIFETX" And (Fields!KronosEmprContribution.Value<> (Fields!Amount.Value), "LIFETX MISMATCH", _
        IIf(Trim((Fields!BenefitPlanCode.Value) = "MEDICAL COPAY" And (Fields!KronosEmpDeduction.Value <> (Fields!Amount.Value), "MEDICAL COPAY MISMATCH", _
        IIf(Trim((Fields!BenefitPlanCode.Value) = "MEDICAL" And (Fields!TotalEmployeePremium.Value <> (Fields!Amount.Value), "MEDICAL MISMATCH", "0")))
Koen Van WielinkBusiness Intelligence SpecialistCommented:
You say that "it doesn't work properly even though it doesn't throw an error". I guess this implies that the expression is correct (seems ok to me), but that the output is not what you're expecting? Can you clarify? Could it be that you have part of the switch statement evaluating to True before you expect it to happen? Please explain why it's not working "properly".
hackman122Author Commented:
Only the first part returns results ("LIFETX MISMATCH"). I don't get any "MEDICAL COPAY MISMATCH" or "MEDICAL MISMATCH" even though there should have been some.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Koen Van WielinkConnect With a Mentor Business Intelligence SpecialistCommented:
Can you provide some sample data where you would expect to see at least 2 different outcomes? Which version of SSRS are you using by the way?
Is this expression in the details section of the report or part of a group?
Are these fields all in the same textbox or are you having 3 different textboxes?
KronosEmprContribution, KronosEmpDeduction, TotalEmployeePremium
hackman122Author Commented:
The error was somewhere else and I figured it out. Thanks for your help though guys!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.