Totaling an expression in SSRS

Question

I have an expression that calculates a value in a textbox. I would like, at the end of the report, to have a total of all the fields in that textbox. However, the expression uses named fields from the query, which are totaled also.

=SUM(Fields!Total_Defective__.Value) / SUM(Fields!sales_Dollars.Value)

Total_Defective__ and sales_Dollars are totaled at the end of the report. So when I put that expression in the total field for that column, it performs the calculation on the totals for the other columns. This produces a incorrect result, since its based on the totals of the other fields. what I'm trying to do is sum the column. I tried Use a summary field in an expression in SSRS reports but this only gives me the row above the total, and you cannot aggregate by it.

So the report looks like this:

The COQ total colummn is taking the total of Defective $ / sales. I would like it to add the column (COQ) to be $0.39

Here is what the query looks like:

    --Prod Qty
SELECT Division as 'division', SUM(prodtable.qtysched)as 'qty',rtrim(Ltrim(itemgroupid)) as 'itemGroup'
into #SCHED
FROM MiscReportTables.dbo.PlantDivisions 
inner join prodtable on prodtable.dimension2_ = MiscReportTables.dbo.PlantDivisions.Division  
WHERE PlantID IN (@plantid)
and SCHEDDATE between @start and @end   
Group by itemgroupid,MiscReportTables.dbo.PlantDivisions.Division

--sales qty
Select Division as 'Division', SUM(SALESQTY) as 'salesQTY',rtrim(Ltrim(salesline.itemgroupid)) as 'itemGroup'
into #salesQty
FROM MiscReportTables.dbo.PlantDivisions 
inner join prodtable on prodtable.dimension2_ = MiscReportTables.dbo.PlantDivisions.Division
inner join SalesLine on SalesLine.InventrefId = ProdTable.ProdiD
WHERE PlantID IN (@plantid)
and SCHEDDATE between @start and @end
Group By Division,salesLine.itemgroupid

--SALES Dollars
Select Division as 'Division',
 (SUM(SALESQTY) - REMAINSALESFINANCIAL) * (SalesPrice / PriceUnit) as 'sales$',
 rtrim(Ltrim(salesline.itemgroupid)) as 'itemGroup'
into #salesDollars
FROM MiscReportTables.dbo.PlantDivisions 
inner join prodtable on prodtable.dimension2_ = MiscReportTables.dbo.PlantDivisions.Division
inner join SalesLine on SalesLine.InventrefId = ProdTable.ProdiD
WHERE PlantID IN (@plantid)
and SCHEDDATE between @start and @end
Group By Division,salesLine.itemgroupid,REMAINSALESFINANCIAL,SalesPrice,PriceUnit


SELECT
    dbo.TQMNCR.NCRID,
    dbo.TQMPlantTable.PlantName AS 'Division',
    RTRIM(LTRIM(dbo.INVENTTABLE.ITEMGROUPID)) AS 'Item Process/Group',
    dbo.TQMNCRDEFECTTYPECODES.QTY AS 'Defective Qty',
    CASE CATYPE
        WHEN 0 THEN 
            (CASE WHEN dbo.SALESLINE.SALESID = ''
                THEN ISNULL((PRICE * (PERCENTEXT / 100))  / NULLIF(dbo.INVENTTABLEMODULE.PRICEUNIT, 0), 0) * dbo.TQMNCRDEFECTTYPECODES.QTY
                ELSE ISNULL((SALESPRICE * (PERCENTEXT / 100))  / NULLIF(dbo.SALESLINE.PRICEUNIT, 0), 0) * dbo.TQMNCRDEFECTTYPECODES.QTY END)
        WHEN 2 THEN 
            (CASE WHEN dbo.TQMNCR.SALESID = ''
                THEN ISNULL((PRICE * (PERCENTINT / 100))  / NULLIF(dbo.INVENTTABLEMODULE.PRICEUNIT, 0), 0) * dbo.TQMNCRDEFECTTYPECODES.QTY
                ELSE ISNULL((SALESPRICE * (PERCENTINT / 100))  / NULLIF(dbo.SALESLINE.PRICEUNIT, 0), 0) * dbo.TQMNCRDEFECTTYPECODES.QTY END)
        ELSE 0 END AS 'Total Defective $',
    dbo.PRODTABLE.PRODPOOLID
    ,#SCHED.qty,
    (#SCHED.qty - dbo.TQMNCRDEFECTTYPECODES.QTY) / #SCHED.qty as 'First Pass Yield',
    #salesQty.salesQty as 'Sales Quantity',
    #salesDollars.sales$ as 'sales Dollars'
FROM
    dbo.TQMNCR LEFT OUTER JOIN
    dbo.TQMDISPOSITION ON dbo.TQMNCR.DISPOSITIONID = dbo.TQMDISPOSITION.DISPOSITIONID LEFT OUTER JOIN 
    dbo.TQMCA_TABLE ON dbo.TQMCA_TABLE.NCRID = dbo.TQMNCR.NCRID LEFT OUTER JOIN 
    dbo.TQMNCRDEFECTTYPECODES ON dbo.TQMNCR.NCRID = dbo.TQMNCRDEFECTTYPECODES.NCRID LEFT OUTER JOIN
    dbo.TQMPlantTable ON TQMPlantTable.PlantID  = dbo.TQMNCR.PlantID LEFT OUTER JOIN 
    dbo.INVENTTABLE ON dbo.TQMNCR.ITEMID = dbo.INVENTTABLE.ITEMID LEFT OUTER JOIN 
    dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID AND MODULETYPE = 2 LEFT OUTER JOIN 
    dbo.SALESLINE ON dbo.SALESLINE.SALESID = dbo.TQMNCR.SALESID AND dbo.SALESLINE.ITEMID = dbo.TQMNCR.ITEMID LEFT OUTER JOIN 
    dbo.PRODTABLE ON dbo.TQMNCR.PRODID = dbo.PRODTABLE.PRODID
    inner join #sched on #sched.itemGroup = INVENTTABLE.itemgroupid
    inner join #salesQty on #salesQty.itemGroup = INVENTTABLE.itemgroupid
    inner join #salesDollars on  #salesDollars.itemgroup = INVENTTABLE.itemgroupid
WHERE
    SCHEDDATE between @start and @end
    AND
    dbo.TQMNCR.PlantID IN (@plantid)
ORDER BY dbo.INVENTTABLE.ITEMGROUPID, dbo.TQMPlantTable.PlantName


drop table #sched
drop table #SalesQty
drop table  #salesDollars

and here's what the result set looks like:

The report is grouped by Item_Process,Division,Total_Defective

Answer 1

You could try adding your COQ field as a new Calculated Field in your dataset. Doing this will perform the calculation at dataset level, rather than Tablix grouping level.

Lets say you've called this new field "COQ".

Then in your total row, have the expression Sum(Fields!COQ.Value) as your total.

This should perform the calculation for each row's COQ first, then sum the result in the total row.

EDIT: The above will work if your dataset were grouped exactly as your table will be. When this is not the case (as you've indicated) and your table in SSRS is actually grouped from a more granular dataset, you can total your groups using the expression below:

=Sum(IIF(Sum(Fields!sales_Dollars.Value,"YOURGROUPNAME") > 0,   
 Sum(Fields!Total_Defective__.Value,"YOURGROUPNAME") / 
 Sum(Fields!sales_Dollars.Value,"YOURGROUPNAME"), 0))

This should evaluate the calculation within each member of the GROUP BY, and then SUM the result of the calculation across the entire table/dataset.

Answer 2

I beleive your requirement is beyond the capabilities of SSRS alone. I would move the grouping to the SQL dataset and (after grouping to the 9 "detail" rows in your example) I would calculate COQ in that dataset e.g.

SELECT *
, Total_Defective / Sales AS COQ
FROM (
  SELECT 
    MyGroupColumns
    , SUM ( Total_Defective ) AS Total_Defective 
    , SUM ( Sales ) AS Sales 
   FROM  MyTable
  GROUP BY
    MyGroupColumns
  ) D1

Then SSRS can just present the detail COQ for the 9 rows output from the dataset, and you can use an SSRS Sum() function to get COQ for your Total row.

Answer 3

You should be able to do this with

=SUM(Fields!Total_Defective__.Value/Fields!sales_Dollars.Value)

So that it divides each value, then sums rather than summing the values before dividing.

EDIT: if you get divide by zero errors you can add the following to Report - Report Properties - code:

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal
    If denominator = 0 Then
        Return 0
    Else
        Return numerator / denominator
    End If
End Function

(Ref: http://williameduardo.com/development/ssrs/ssrs-divide-by-zero-error/)

The enter the formula as

=SUM(code.Quotient(Fields!Total_Defective__.Value,Fields!sales_Dollars.Value)

Then you should get the desired result with relatively short code in your expression.