Green bar for a group header - SSRS

=IIF(RunningValue(Fields!xxx.Value,COUNTDISTINCT,NOTHING) MOD 2 = 1,

"White","PaleGreen")


=iif(RunningValue(Fields!xxx. Value,CountDistinct,"parentgroupname") Mod 2,"WhiteSmoke","White")

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

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

Select All option with multi-valued parameters (Part 2) - SSRS

Following on from my previous post on the shortcomings of the 'Select All' option with multi-valued parameters, another workaround is to assess whether the user actually Selects All. If so, then deal with this in the main query code.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.

The EstabCount parameter is given the following properties:

The below code simply populates the mult-valued parameter list:

Once the two parameters are set up the next stage is to alter the main dataset query. Select the Parameters tab and create an IIF statement that returns a predetermined string , "Select All" if the count of the number of parameters selected equals or is greater than EstabCount parameter value, i.e. if the 'Select All' option is checked in the drop down box.

=iif(Parameters!EstablishmentParameter.Count>=Parameters!EstabCount.Value, "Select All", Parameters!EstablishmentParameter.Value)

Then we need to alter the WHERE clause in the main dataset query:

WHERE (('Select All' IN (@EstablishmentParameter)) OR (View_ActiveEstablishment.EstablishmentName IN (@EstablishmentParameter)))

So, if the user checks 'Select All' in the drop down the IIF statement returns the string 'Select All' and the WHERE clause evaluates to true and returns a full selection thus negating the need for a potentially massive IN clause being used in the query. Likewise, if the user selects multiple parameter values then these are treated as expected. Unfortunately this may still result in a cumbersome IN clause if the user decides to select all but a few of the parameter values!

A quick play with SQL Server Profiler shows the 'Select All' database query before and after the alteration:

Condensing large character string and removing carriage returns - SSRS

Useful formula for condensing a large character string and removing any carriage returns (such as in a comments field).


=iif(len(Replace((Fields!Comments.Value),CHR(10)," "))>25, (left(Replace((Fields!Comments.Value),CHR(10)," "),25) & "....."),Fields!Comments.Value)


...next stage will be to create a function...

http://sqlreportingservicescrystalreports.blogspot.com/2009/10/condensing-large-character-string-and_22.html

Green bar report - SSRS

=iif(RowNumber(Nothing) Mod 2, “LightGreen”, “White”)

IsNothing Function - SSRS

=Iif(IsNothing(Fields!ANYFIELD.Value), "The Field Is Null", Fields!ANYFIELD.Value)