Conditional formatting not working as expected in SSRS report
I am trying to display conditional fill on a report in SSRS on SQL Server 2008. Looking at Fields!MS_Communicator.Value, desired fill to be SeaGreen if version build is >= 83, Tomato if < or [Not Installed]. Query provides either [Not Installed]
or the version number (i.e. 3.5.6907.83).
=(IIF((Split(Fields!MS_Communicator.Value, ".")).GetValue(3) >= 83, "SeaGreen", "Tomato")) works as expected, leaving cells with [Not Installed] transparent.
=IIF(Fields!MS_Communicator.Value = "[Not Installed]", "Tomato", "Transparent") works as expected, leaving cells with version transparent.
My problem:
=IIF(Fields!MS_Communicator.Value = "[Not Installed]", "Tomato", (IIF((Split(Fields!MS_Communicator.Value, ".")).GetValue(3) >= 83, "SeaGreen", "Tomato"))) works if there is a version number, but leaves cells with [Not Installed] transparent.
November 18th, 2010 12:28pm
Hi,
The IIF evaluates both the true part as well as the false part. Therefore the Split function is failing which is in the false expression as the word NotInstalled does not contain the 4th element separated by a dot "."
The fix for this is to manually add a string that a value for the 4th element like ".0.0.0.0" as shown below.
=IIF(Fields!MS_Communicator.Value = "[Not Installed]", "Tomato", (IIF((Split(Fields!MS_Communicator.Value & ".0.0.0.0", ".")).GetValue(3) >= 83, "SeaGreen", "Tomato")))
Please check. Hope this helps.
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
BH
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2010 12:44am
Thanks very much Bilal! This works perfectly.
November 19th, 2010 10:15am