D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Wednesday, 8 August 2012

Useful SSRS expression syntax

=IIF(Fields!a.value>100, True, False)

--------------------------------------------------------
=IIF(Fields!a.value >=10, "Green", IIF(Fields!a.value >=1, "Blue", "Red"))

values >=10 are green, between 1 & 9 are blue, less than 1 are red

--------------------------------------------------------

=SWITCH(
    Fields!a.value >=10, "green",
    Fields!a.value >=1, "blue",
    Fields!a.value = 1, "yellow",
    Fields!a.value <=0, "Red")

>=10 are green, between 1 & 9 blue, =1 yellow, <=0 red

--------------------------------------------------------

SWITCH function finds the first expression that's true. It will not catch errors further along.
IIF function evaluates all parts of the expression.

--------------------------------------------------------


=IIF(Rownumber("scope") Mod 2 = 0, "Khaki", "White")

set "scope" to reset row colours for every group

--------------------------------------------------------