Solved

SSRS Expression

Posted on 2014-04-10
6
387 Views
Last Modified: 2014-04-16
Hi,

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 :)
0
Comment
Question by:hackman122
  • 2
  • 2
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39993258
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".
0
 

Author Comment

by:hackman122
ID: 39993324
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.

Thanks.
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 250 total points
ID: 39993339
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?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 27

Expert Comment

by:planocz
ID: 39999831
Are these fields all in the same textbox or are you having 3 different textboxes?
KronosEmprContribution, KronosEmpDeduction, TotalEmployeePremium
0
 
LVL 27

Accepted Solution

by:
planocz earned 250 total points
ID: 40000681
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")))
0
 

Author Closing Comment

by:hackman122
ID: 40005017
The error was somewhere else and I figured it out. Thanks for your help though guys!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now