Total for preceding rows in report builder 3.0

Question

I've built a sales report that shows the salesman, client and then shows last year's sales, this year's sales, this year's pending sales, this year's lost sales and then a column at the end called "In Play". The In Play formula should be last year's sales - SUM(this year's sales,this year's pending, this year's lost). If the result of the formula is less than 0 then I want it to show 0.

On the client level I can get it to work with the following: =Iif(SUM(Fields!InPlay.Value)<0,0,SUM(Fields!InPlay.Value)). I created a calculated field to come up with InPlay with the following: =Fields!LastSeasonRevenue.Value-Fields!CurrentSeasonContractedRevenue.Value-Fields!CurrentSeasonPendingRevenue.Value-Fields!CurrentSeasonLostBusiness.Value.

My struggle comes when I try to put this into the total row for the salesman or even the grand total. My data is structured such that it has multiple rows for each contract and so the calculated field obviously calculates on a line by line basis. This seems to work fine when I total everything for the client level in the "In Play" column, but won't work for the totals. Part of it comes because of the Iif formula I have, but I can't figure out a better way to do it.

I've tried various methods to total it by salesman and grand total with either #error as the result or the wrong result.

I tried to add an image, but stackoverflow won't let me.

EDIT: This is a text representation of my issue and is just one of the salesman on my list.

     LastYearRev,  ThisYearRev,  Pending,  Lost,  InPlay   

Client1 0, 0, 4972, 0, 0

Client2 0, 16800, 0, 0, 0

Client3 4800, 0, 0, 0, 4800

Client4 6375, 0, 0, 0, 6375

Client5 26754, 0, 0, 0, 26754

Client6 0, 1200, 0, 0, 0

Client7 5300, 0, 0, 0, 5300

Totals 43229, 18000, 4972, 0, 43229

In the InPlay column and total row I want the report to show 43,229, but the way the formula would work for that row the total would be 20,257.

This should give you the data that I'm working with: CREATE TABLE #SampleData ([Salesman] Varchar(255), Season varchar(255), Company varchar(255), Client varchar(255), ContractNo int, LastSeasonRevenue Decimal, CurrentSeasonNewContractedRevenue Decimal, CurrentSeasonContractedRevenue Decimal, CurrentSeasonPendingRevenue Decimal, CurrentSeasonLostBusiness Decimal, BusinessClosedLastWeek Decimal) ; INSERT INTO #SampleData (Salesman,Season,Company,Client,ContractNO,LastSeasonRevenue,CurrentSeasonNewContractedRevenue,CurrentSeasonContractedRevenue,CurrentSeasonPendingRevenue,CurrentSeasonLostBusiness,BusinessClosedLastWeek) Values('Salesman1','2014-2015','Company5','Client6',1157,0,1200,1200,0,0,0), ('Salesman1','2014-2015','Company1','Client1',1267,0,0,0,4972,0,0), ('Salesman1','2013-2014','Company4','Client7',298,1600,0,0,0,0,0), ('Salesman1','2013-2014','Company4','Client7',298,3400,0,0,0,0,0), ('Salesman1','2013-2014','Company4','Client7',298,300,0,0,0,0,0), ('Salesman1','2013-2014','Company2','Client5',442,4653.75,0,0,0,0,0), ('Salesman1','2013-2014','Company9','Client3',491,4800,0,0,0,0,0), ('Salesman1','2013-2014','Company1','Client4',519,2975,0,0,0,0,0), ('Salesman1','2013-2014','Company1','Client4',519,680,0,0,0,0,0), ('Salesman1','2013-2014','Company1','Client5',674,22100,0,0,0,0,0), ('Salesman1','2013-2014','Company1','Client4',676,2720,0,0,0,0,0), ('Salesman1','2014-2015','Company1','Client2',868,0,16800,16800,0,0,0)

Answer 1

Jared, I would actually handle this in the SQL dataset itself and add a calculated column called "InPlay":

SELECT Client
, LastSeasonRevenue
, CurrentSeasonContractedRevenue
, CurrentSeasonPendingRevenue
, CurrentSeasonLostBusiness
, CASE WHEN LastSeasonRevenue - CurrentSeasonContractedRevenue
- CurrentSeasonPendingRevenue
- CurrentSeasonLostBusiness <0 THEN  0 ELSE 
LastSeasonRevenue - CurrentSeasonContractedRevenue
- CurrentSeasonPendingRevenue
- CurrentSeasonLostBusiness
END InPlay
FROM #SampleData

While this logic is doable in ReportBuilder, it is a best practice to push this calculation back so that it may be referenced in other pieces of the report (also, I think that SQL looks better than SSRS formula syntax).

If you wanted to do it in SSRS (or have to for some reason like using a shared dataset), the formula would be:

=Sum(iif(Fields!LastSeasonRevenue.Value - Fields!CurrentSeasonContractedRevenue.Value - Fields!CurrentSeasonPendingRevenue.Value - Fields!CurrentSeasonLostBusiness.Value< 0 , 0, CDBL(Fields!LastSeasonRevenue.Value) - CDBL(Fields!CurrentSeasonContractedRevenue.Value) - CDBL(Fields!CurrentSeasonPendingRevenue.Value) - CDBL(Fields!CurrentSeasonLostBusiness.Value)))

Screenshot of application:

The important factor missing in your original equation is that you SUM() inside the IIF() statement. In SSRS, SUM() takes a collection and individual evaluates each item in that collection. So in this example, we are passing SUM() a series IIF() statements instead of passing the return of a SUM() statement to an IIF() statement.