Summing an SSRS column set

Question

I have a query which gathers Items from a database and generates a number of rows:

    SELECT 
    ITEMID,
    SUM(dbo.CUSTINVOICETRANS.QTY) AS 'Quanity',
    SUM(LINEAMOUNTMST) AS 'Sales',
    COUNT(DISTINCT dbo.CUSTINVOICEJOUR.SALESID) AS 'Total Orders'
INTO
    #tempItemRevenue
FROM
    dbo.CUSTINVOICEJOUR INNER JOIN 
    dbo.CUSTINVOICETRANS ON dbo.CUSTINVOICEJOUR.INVOICEID = dbo.CUSTINVOICETRANS.INVOICEID
WHERE
    dbo.CUSTINVOICETRANS.DIMENSION2_ IN (@division)
    AND
    CONVERT(DATETIME, dbo.CUSTINVOICETRANS.INVOICEDATE, 101) BETWEEN @start AND @end
GROUP BY
    ITEMID

SELECT
    ITEMGROUPID AS 'Process',
    [DESCRIPTION] AS 'Division',
    ISNULL(PRICE / CASE WHEN PRICEUNIT = 0 THEN NULL ELSE PRICEUNIT END, 0) AS 'Unit Price',
    SUM(LOADQTY) AS 'Load Size',
    SUM(LOADQTY * ISNULL(PRICE / CASE WHEN PRICEUNIT = 0 THEN NULL ELSE PRICEUNIT END, 0)) AS 'Load Value',
    SUM(Sales) as 'Sales',
    SUM(Quanity) AS 'Quantity'
FROM
    dbo.INVENTTABLE INNER JOIN 
    dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID 
    INNER JOIN #tempItemRevenue ON dbo.INVENTTABLE.ITEMID = #tempItemRevenue.ITEMID 
    INNER JOIN dbo.DIMENSIONS ON NUM = dbo.INVENTTABLE.DIMENSION2_ 
    --INNER JOIN dbo.CUSTTABLE cu ON ACCOUNTNUM = CUSTACCOUNT
WHERE
    MODULETYPE = 2
    AND
    ITEMGROUPID IN (@group)
    AND
    dbo.INVENTTABLE.DIMENSION2_ IN (@division)
    and LOADQTY * ISNULL(PRICE / CASE WHEN PRICEUNIT = 0 THEN NULL ELSE PRICEUNIT END, 0) > 0
Group BY 
ITEMGROUPID,[DESCRIPTION],PRICE,PRICEUNIT

DROP TABLE #tempItemRevenue

This produces results like this:

Process Division    Unit Price  Load Size   Load Value  Sales   Quantity
Anodize Green Bay   0.132916        1050        139.5618    26      200
Anodize Green Bay   0.15375         2000        307.5       447.45  2983
Anodize Green Bay   0.156           5000        780         848     5300
Anodize Green Bay   0.1751          17040       2983.704    278.64  1548
Anodize Green Bay   0.187138516     13520       2530.112741 3147.35 16565

I put this into an SSRS table and it sums everything, but it sums each line...i need everything with the same Process to be summed ALL TOGETHER in a total. But I can't get report builder 3.0 to give me an option to sum all the lines. In essence, I want a single line per process and division (there are many combinations). not each line item. What am I doing wrong? I can't seem to get the totals only. The designer The current results

Each row has the SUM function already on it. For some reason its only doing it for that particular field in a single row. I does not sum up the totals from all of the rows. I'll add a couple of pictures. — rigamonk
Add group on Process and Devision as you want a single line per process and division. And then add total row and then delete the details row. — Mahesh
Its not letting me add a totals row (or i am doing it wrong). It just says "add a row" when i do that then put a sum, it just shows the same total as the line....for each line, not a total for all. I do have it grouped by process and division, though — rigamonk
I notice that you don't include UnitPrice in the rdl. Is it possible that you are grouping on it though? Can you include a picture of your tablix so we can see the grouping? — Tab Alleman

Jeffrey Van Laethem Jeffrey Van Laethem · Accepted Answer · 2015-02-09T15:20:00

So, I'm able to add a totals row at the bottom of a Tablix field like this:

Click on the row header (so the whole row is highlighted). Right where the first text box in the Tablix says "Process", you'll see a bracket (signifying the row grouping. Right-click on that, then go down to Add Row, then Add Row outside group. That should give you a single row outside all the groups, in which you can sum everything above.

Summing an SSRS column set

1 Answers