• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1600
  • Last Modified:

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.

=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")

Open in new window

0
jdharm66
Asked:
jdharm66
  • 5
  • 3
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
I do not see a logic flaw in what you are trying to do, I can get the switch to work with 3+ value sets for me but I did not have your same conditions.  I have found some references to a Bug with Me.Value though, see http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/e59ea21b-7114-4910-8439-cc067787e99b and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124017.
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?
0
 
jdharm66Author Commented:
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.
0
 
jdharm66Author Commented:
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,
=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")))

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
jdharm66Author Commented:
Yes, I continue to experience the problem.  I've noticed that I get this message in the Output window when I preview the report.

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox LOD_Approved___A contains an error: Argument 'Date2' cannot be converted to type 'Date'.

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")))

Open in new window

0
 
jdharm66Author Commented:
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?
0
 
Chris LuttrellSenior Database ArchitectCommented:
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")))

Open in new window

0
 
jdharm66Author Commented:
I was never able to get Switch to work with all three.  I had to use a more complicated query to return a single number that I then used to set the color.
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.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now