Conditional totals for groups of rows, based on the value of a given column, in SQL Server Reporting Services

SQL Server Reporting Services seems like a bit of a dark art at times (trying to look at the markup behind an RDL file definitely is!), for 90% of report based requirements it can be used to quickly build a report structure with aggregates, counts and a selection of truly garish default themes.

More of a memo to self thank anything else, but, if you want to display something like this:

Grouping Item Status Value
Group 1 Item 1 Alive 1
Item 2 Alive 3
Item 3 Dead 2
Dead: 1 Alive: 2 Total: 6
Group 2 Item 1 Alive 1
Item 2 Dead 2
Dead: 1 Alive: 1 Total: 3

i.e. Have totals for the values present in the Status column, and you've got a closed list of values to build the summary line from, use this:

=SUM(IIF(Fields!Status.Value = "Alive", 1, 0))

Substituting "Dead" for the counter for dead items.

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment