Solved

SSRS Expression

Posted on 2014-04-10
6
385 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
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.   …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 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

19 Experts available now in Live!

Get 1:1 Help Now