Search code examples
sqlsql-serverssrs-2012reportbuilder3.0

Sum of a column of expressions

I am trying to take the value from a group of figures calculated by

=sum((Code.QRSupport.StockAtDate(Fields!PluNo.Value, Fields!SiteNo.Value, Parameters!SessionDateFrom.Value))+(Fields!DeliveryQty.Value)+(Fields!TransferInQty.Value)+(Fields!TransferOutQty.Value)+(Fields!WasteQty.Value)+(Fields!ReturnsQty.Value)+(Fields!CorrectionQty.Value)+(Fields!StockCheckQty.Value)+(Fields!SaleQty.Value))*(Sum(Fields!OuterCost.Value/Fields!OuterSize.Value))

This will then be totalled at the end of the group like below. enter image description here

No matter what i try i do not get the total of them, it seems to take the total of the group price and the group calculated close.

Any help will be appreciated. Im about to throw my laptop out the window!! enter image description here

Solution

  • Without sample data (that I don't have to retype) and you custom code function it's hard to test but this should be along the right lines.

    This assumes you have a rowgroup (it looks like you group on description?). You ned to replace the two myRowGroup references with the name of your rowgroup, it is case sensitive and must be enclosed in quotes.

    =
SUM(
    sum(
        (
            Code.QRSupport.StockAtDate(Fields!PluNo.Value, Fields!SiteNo.Value, Parameters!SessionDateFrom.Value)
        )
        +(Fields!DeliveryQty.Value)
        +(Fields!TransferInQty.Value)
        +(Fields!TransferOutQty.Value)
        +(Fields!WasteQty.Value)
        +(Fields!ReturnsQty.Value)
        +(Fields!CorrectionQty.Value)
        +(Fields!StockCheckQty.Value)
        +(Fields!SaleQty.Value)
        , "myRowGroup")
        *
        (Sum(Fields!OuterCost.Value/Fields!OuterSize.Value, "myRowGroup"))
)

    All we are doing here is taking you group level expression, specifying the scope to be the group level and then sum the results.