CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
rigamonk
rigamonk

Reputation: 1181

Summing an SSRS column set

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

Upvotes: 1

Views: 124

Answers (1)

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

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.

Upvotes: 1

Related Questions