SWITCH Expression Error
I need to display the seniority of the employees working in a company. If the employer is working for more
than 15 yrs, he is considered as SME, if he is working more than 8 yrs, he is considered Senior and greater than 4 yrs is considered as Mid Level and Less than 4 as Junior. How
do I put that in SSRS expression.
I wrote this but it gives me an error as it is unable to compare a datetime output with an integer.. any suggestions would be greatly appreciated. Thanks in Advance.
=SWITCH
(
(Globals!ExecutionTime - Fields!HireDate.Value) > 15, "SME",
(Globals!ExecutionTime - Fields!HireDate.Value) > 8, "Senior",
(Globals!ExecutionTime - Fields!HireDate.Value) > 4, "Mid Level"
(Globals!ExecutionTime - Fields!HireDate.Value) < 4, "Junior"
)
Error:
[rsRuntimeErrorInExpression] The Value expression for the textrun HireDate.Paragraphs[0].TextRuns[0] contains an error: Overload resolution failed because no Public '>' can be called with these arguments:
'Public Shared Operator >(t1 As System.TimeSpan, t2 As System.TimeSpan) As Boolean':
Argument matching parameter 't2' cannot convert from 'Integer' to 'TimeSpan'.
Preview complete -- 0 errors, 1 warnings
September 30th, 2012 10:59am
Hi,
try with
=SWITCH
(
(cdate(Globals!ExecutionTime) - Fields!HireDate.Value) > 15, "SME",
(cdate(Globals!ExecutionTime) - Fields!HireDate.Value) > 8, "Senior",
(cdate(Globals!ExecutionTime) - Fields!HireDate.Value) > 4, "Mid Level"
(cdate(Globals!ExecutionTime) - Fields!HireDate.Value) < 4, "Junior"
)
Hope this will help you !!!
Sanjeewan
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 11:04am
Thanks Sanjeewan for a quick response. After using your expression, I get the following error:
[rsCompilerErrorInExpression] The Value expression for the textrun HireDate.Paragraphs[0].TextRuns[0] contains an error: [BC30452] Operator '-' is not defined
for types 'Date' and 'Object'.
I have also added comma at the end of (Globals!ExecutionTime - Fields!HireDate.Value) > 4, "Mid Level"
September 30th, 2012 11:08am
try with
=SWITCH
(
Datediff(d,cdate(Globals!ExecutionTime) , Fields!HireDate.Value) > 15, "SME",
Datediff(d,cdate(Globals!ExecutionTime) , Fields!HireDate.Value) > 8, "Senior",
Datediff(d,cdate(Globals!ExecutionTime) , Fields!HireDate.Value) > 4, "Mid Level"
Datediff(d,cdate(Globals!ExecutionTime) , Fields!HireDate.Value) < 4, "Junior"
)
http://www.bi-rootdata.com/
Hope this will help you !!!
Sanjeewan
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 11:14am
Worked but gave me a wrong output... If the employer hire date is 7/31/1996; it should be 09/29/2012 - 7/31/1996 which is greater than 16 years and he should be displayed as SME .. but now it shows all as Junior..
Don't you think we should be doing some thing like
Datediff(d,cdate(Globals!ExecutionTime) , Fields!HireDate.Value) > 15 * 365, "SME" ? or * 366
or may be use a year difference function?
September 30th, 2012 11:30am
You guys are missing a comma after "Mid Level" and moreover, you'll need to use DATEDIFF. Please use the code below:
=SWITCH
(
DateDiff("yyyy",Globals!ExecutionTime, Fields!HireDate.Value) > 15, "SME",
DateDiff("yyyy",Globals!ExecutionTime, Fields!HireDate.Value) > 8, "Senior",
DateDiff("yyyy",Globals!ExecutionTime, Fields!HireDate.Value) > 4, "Mid Level",
DateDiff("yyyy",Globals!ExecutionTime, Fields!HireDate.Value)< 4, "Junior"
)
Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 11:41am
Try this - format the cell as Number if it is formatted as date. Right-click on the cell, go to properties....Let us know how it goes.
Also, if this suggestion doesn't work, put the below below expression and see if you are getting correct values.
DateDiff("yyyy",Globals!ExecutionTime, Fields!HireDate.Value)
Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com
September 30th, 2012 12:16pm
The below expression gives the correct value but the entire switch statement is not returning what I needDateDiff("yyyy",Globals!ExecutionTime, Fields!HireDate.Value)
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 4:22pm