Prev: Financial software firm needs SQL Server DBA - Metro Atlanta.
Next: Question on SSRS
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