SQLTeam.com | Weblogs | Forums

SQL sub total


Ok. I have a tablix the pulls data from a sproc. It also has a total of all columns, Now they want a secondary total that subtracts one certain record(bob) in the tablix from that total. But I never know where bob will show up. I do know that bob will always be in the records that come back. Can this be done

So I got

bob 4
mary 3
Tim 6

Total 13
total without bob 9


Can you create another dataset and apply a filter for Bob only to that set., or you could alter the SPROC although that's overkill for one filter, or you could add direct SQL to a new dataset for Bob in SSRS.


If you look at the Expression for your existing SUM cell, it should have something like this:


For sum excluding bob, the expression should be something like this:

=SUM( IIF( Field!YouNameCol.Value = "bob", 0, Field!YourQtyCol.Value) )


JamesK is correct without too much work,

To calc within a dataset expression:

=Sum(IIf(Fields!Emp_Name.Value <> "bob", Fields!Sales_Calls.Value, 0)) 

To calc within a text box expression, calling the dataset use:

=Sum(IIf(Fields!Emp_Name.Value <> "bob", Fields!Sales_Calls.Value, 0), "CallStats")