From: JohnE on 14 May 2010 11:53 I have a table that displays instrument calibration dates. The users are looking to redo the SSRS report to go out 30 days instead of 7. Not a problem to do. What else they want is a color coding of the different times. Originally, I was using the following; =IIF(Fields!NextCalibrationDate.Value < Today(),"Red","Black") This worked fine. But now they are looking at still using the original, plus less than 7 days, plus 7 to 14 days, over 14 days. The added IIF's are causing me a headache trying to figure it out. I even tried the following SWITCH; =SWITCH(Fields!NextCalibrationDate.Value < Today(), "Red", Fields!NextCalibrationDate.Value < 7, "Yellow", Fields!NextCalibrationDate.Value < 14, "Green", Fields!NextCalibrationDate.Value >= 14, "Black") But ran it error with this. Can someone get me started on how best to accomplish this? Thanks...John
From: Michael C on 20 May 2010 15:58 Hi John, First I'm a little confused how you plan on coding 'the original' plus the new items. The reason i say this is because <7 days is <today, so how you plan on coding those to 2 different colours isn't really clear to me. But lets leave that aside, and just deal with your 3 numbered criteria <7, >7 and <14, and >14. Have you used the datediff function before? I'll give a shot at a sample for you: =IIF(DateDiff(d,Fields!NextCalibrationDate.Value,Today())<=7,"Red",IIF(DateDiff(d,Fields!NextCalibrationDate.Value,Today())>7 and DateDiff(d,Fields!NextCalibrationDate.Value,Today())<=14,"Green",IIF(DateDiff(d,Fields!NextCalibrationDate.Value,Today())<14,"Black","Red"))) 'Red' in this case should never actually show up unless the answer is Null i do beleive, but test that! I haven't really tested the example above for the proper parenthesis, but I think you get the idea. If not just post again and I'll try and make some clarifications. Michael C. "JohnE" wrote: > I have a table that displays instrument calibration dates. The users are > looking to redo the SSRS report to go out 30 days instead of 7. Not a > problem to do. What else they want is a color coding of the different times. > Originally, I was using the following; > > =IIF(Fields!NextCalibrationDate.Value < Today(),"Red","Black") > > This worked fine. But now they are looking at still using the original, > plus less than 7 days, plus 7 to 14 days, over 14 days. The added IIF's are > causing me a headache trying to figure it out. I even tried the following > SWITCH; > > =SWITCH(Fields!NextCalibrationDate.Value < Today(), "Red", > Fields!NextCalibrationDate.Value < 7, "Yellow", > Fields!NextCalibrationDate.Value < 14, "Green", > Fields!NextCalibrationDate.Value >= 14, "Black") > > But ran it error with this. > > Can someone get me started on how best to accomplish this? > > Thanks...John >
From: Michael C on 20 May 2010 16:01 I made an error in the above using 'Red' twice, but it actually could work, as Red would be both <7, and anything else not covered by the other criteria. Michael C. "Michael C" wrote: > Hi John, > > First I'm a little confused how you plan on coding 'the original' plus the > new items. The reason i say this is because <7 days is <today, so how you > plan on coding those to 2 different colours isn't really clear to me. But > lets leave that aside, and just deal with your 3 numbered criteria <7, >7 and > <14, and >14. > > Have you used the datediff function before? I'll give a shot at a sample > for you: > > =IIF(DateDiff(d,Fields!NextCalibrationDate.Value,Today())<=7,"Red",IIF(DateDiff(d,Fields!NextCalibrationDate.Value,Today())>7 > and > DateDiff(d,Fields!NextCalibrationDate.Value,Today())<=14,"Green",IIF(DateDiff(d,Fields!NextCalibrationDate.Value,Today())<14,"Black","Red"))) > > 'Red' in this case should never actually show up unless the answer is Null i > do beleive, but test that! > > I haven't really tested the example above for the proper parenthesis, but I > think you get the idea. If not just post again and I'll try and make some > clarifications. > > Michael C. > > "JohnE" wrote: > > > I have a table that displays instrument calibration dates. The users are > > looking to redo the SSRS report to go out 30 days instead of 7. Not a > > problem to do. What else they want is a color coding of the different times. > > Originally, I was using the following; > > > > =IIF(Fields!NextCalibrationDate.Value < Today(),"Red","Black") > > > > This worked fine. But now they are looking at still using the original, > > plus less than 7 days, plus 7 to 14 days, over 14 days. The added IIF's are > > causing me a headache trying to figure it out. I even tried the following > > SWITCH; > > > > =SWITCH(Fields!NextCalibrationDate.Value < Today(), "Red", > > Fields!NextCalibrationDate.Value < 7, "Yellow", > > Fields!NextCalibrationDate.Value < 14, "Green", > > Fields!NextCalibrationDate.Value >= 14, "Black") > > > > But ran it error with this. > > > > Can someone get me started on how best to accomplish this? > > > > Thanks...John > >
|
Pages: 1 Prev: Financial software firm needs SQL Server DBA - Metro Atlanta. Next: Question on SSRS |