• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1603
  • 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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