jdharm66
asked on
Using Switch in an expression
I'm trying to create an expression that determines the shading of the field based on three factors. I thought using Switch to test various conditions would be the cleanest way to do this. The problem I run into is that when I add the third condition, the second condition stops working. If I remove the third condition, the second condition works just fine. It doesn't seem to matter what I use as the third condition, it causes the second condition to be skipped.
Condition 1: LOI_Signed is either 0 or 1, if it's 0 it means that I have a valid date in my "actual date" field and I want the shading to be Gainsboro.
Condition 2: If LOI_Signed is 1 then the date in LOI_Signed__A is actually a forecasted date and I want to verify that it is a valid date. If it is not then I want Gainsboro shading.
Condition 3: If it makes it to this point then it should be a valid, forecasted date so I want to test how close we are to it. If it's less than 30 days or no more 180 days past it, I want it Yellow.
Any help would be apprecated.
Condition 1: LOI_Signed is either 0 or 1, if it's 0 it means that I have a valid date in my "actual date" field and I want the shading to be Gainsboro.
Condition 2: If LOI_Signed is 1 then the date in LOI_Signed__A is actually a forecasted date and I want to verify that it is a valid date. If it is not then I want Gainsboro shading.
Condition 3: If it makes it to this point then it should be a valid, forecasted date so I want to test how close we are to it. If it's less than 30 days or no more 180 days past it, I want it Yellow.
Any help would be apprecated.
=Switch(Fields!LOI_Signed.Value = 0, "Gainsboro",
IsDate(Fields!LOI_Signed___A.Value) = 0, "Gainsboro",
DATEDIFF("d", Today, Me.Value) > -180 AND DATEDIFF("d", Today, Me.Value) < 30, "Yellow")
ASKER
We're using SQL Reporting Services 2005. It appears that the bug mentioned applies to 2008.
Let me say that I was wrong about one thing I mentioned initially. If I do something simple like 2=2, "Yellow" as the third condition, all three conditions work. Howerver, I tried just testing the Datediff for the -180 and used the field value rather than Me.Value and the second condition doesn't work.
Is there a another way to calculate the difference between the current date and the date value in the respective field?
Thanks for your response.
Let me say that I was wrong about one thing I mentioned initially. If I do something simple like 2=2, "Yellow" as the third condition, all three conditions work. Howerver, I tried just testing the Datediff for the -180 and used the field value rather than Me.Value and the second condition doesn't work.
Is there a another way to calculate the difference between the current date and the date value in the respective field?
Thanks for your response.
ASKER
For the past few minutes, I tried to setup a nested IIf expression and I'm getting exactly the same symptoms. If I only nest one IIf inside of the other, it works fine, but when I add the second nested IIf, the first nested one doesn't work, the exact symptoms I get with my orignial Switch statement. I've attached the code for anyone to critique.
Thank you,
Thank you,
=IIf(Fields!LOD_Approved.Value = 0, "Gainsboro",
IIf(IsDate(Fields!LOD_Approved___A.Value) = 0, "Gainsboro",
IIf((DATEDIFF("d", Today, Me.Value) > -180) AND
(DATEDIFF("d", Today, Me.Value) < 30), "Yellow", "Gainsboro")))
Hi, have you solved your problem here yet? I have tried to duplicate your problem and cannot get it to fail for me. Of course I do not have your exact Data and setup. Is it possibly a data issue somehow? If this is still a problem maybe some more detail or examples of the data would help.
ASKER
Yes, I continue to experience the problem. I've noticed that I get this message in the Output window when I preview the report.
[rsRuntimeErrorInExpressio
I'm not sure what Date2 is. I'm including the exact expression that runs and there's not Date2 in it.
[rsRuntimeErrorInExpressio
I'm not sure what Date2 is. I'm including the exact expression that runs and there's not Date2 in it.
=IIf(Fields!LOD_Approved.Value = 0, "Gainsboro",
IIf(IsDate(Fields!LOD_Approved___A.Value) = 0, "Gainsboro",
IIf((DATEDIFF("d", Today, Fields!LOD_Approved___A.Value) > -180) AND
(DATEDIFF("d", Today, Fields!LOD_Approved___A.Value) < 30), "Yellow", "Gainsboro")))
ASKER
One other thing...It's the second IIf that is failing and should only shade the background "Gainsboro" if the value is 00/00/00. Is there a way to check for this instead of using the IsDate function?
sorry for the delay. Try just checking for the "00/00/00" value like this. I believe it was trying to implicitly convert the "00/00/00" into a date but that is not valid.
=IIf(Fields!LOD_Approved.Value = 0, "Gainsboro",
IIf(Fields!LOD_Approved___A.Value = "00/00/00", "Gainsboro",
IIf((DATEDIFF("d", Today, Fields!LOD_Approved___A.Value) > -180) AND
(DATEDIFF("d", Today, Fields!LOD_Approved___A.Value) < 30), "Yellow", "Gainsboro")))ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If this does not solve things for you I wll try some other test to see if I can see any other issues. I have not set up a test for this Me.Value Bug they refer to.
Oh, also, if you need more help, what version of SSRS are you on?