Summing an SSRS column set

rigamonk

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

Jeffrey Van Laethem

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Summing Only Visible Rows in SSRS

From Dev

Summing Only Visible Rows in SSRS

From Dev

summing column by column in java

From Dev

using a group by and summing a column

From Dev

Summing a filtered column in DataTables

From Dev

Not summing whole column

From Dev

Summing a column in Pandas Groupby

From Dev

Summing based on another column

From Dev

bash summing and grouping a column

From Dev

How to set intervals in integers under column, then summing corresponding values fall under the interval?

From Dev

Pandas DataFrame merge summing column

From Dev

SUMIF dynamically change summing column

From Dev

how to increase performance of summing a column

From Dev

Summing column values based on multiindex

From Dev

Summing a column by all transactions in a day

From Dev

how to increase performance of summing a column

From Dev

summing up bigint values in a column

From Dev

Summing 2 rows of a column in Pandas

From Dev

Summing values in a column and grouping by another column in R

From Dev

Summing a column in the same column without a circular reference

From Dev

Excel: Summing a column according to values in another column

From Dev

Pandas, summing values in a row to form a "totals" column

From Dev

Summing overlapping rows from a single column in R

From Dev

pandas: summing number of column values with groupby

From Dev

Summing a column value in a table including duplicates

From Dev

Numeric Grid column Says NaN when Summing

From Dev

MYSQL Summing Fields within a column for given Timestamp

From Dev

Pandas grouping and summing just a certain column

From Dev

Group by / Summing values with the same column value

Related Related

HotTag

Archive