Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS Expression

Posted on 2014-04-10
6
Medium Priority
?
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 13

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 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 1000 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

705 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