How do I total the results of a row group in a column?

Question

My table looks something like:

 Customer         Error1         Error2         Error3         Error4       TotalErrors      TotalOrders
 CustomerName [SUM(Error1)]   [SUM(Error2)]  [SUM(Error3)] [SUM(Error3) [SUM(TotalErrors)]  [TotalOrders]
 Total

The middle row CustomerName is part of a Row Group ordered on CustomerWithErrors which simply tells if a given Customer name has any of the 4 errors. If not, it doesn't show up in the table.

In the last total row, I simply right click and add a total for each column in the row group. This outputs a sum of each column exactly as I want it, except for the last column. When I add a total it gives me the sum of the TotalErrors fields for all customer names, not just the ones with errors (the ones actually shown in the table). Consequently, the number is massive. How can I just get a sum of the numbers displayed in the total errors column?

EDIT: Here are some screenshots of design and preview mode:

Design: https://i.sstatic.net/N8AH5.png

Preview: https://i.sstatic.net/Dy3qm.png

EDIT 2: SQL FOR THE TOP TABLIX:

Some of this was new to me so it probably doesn't look very well done. Let me know what I can explain. Thanks.

 SELECT 
         PKEY
        ,Customer
        ,isnull(CustomerName, 'Unknown') AS CustomerName
        ,CustomerWithErrors
        ,Warehouse
        ,ErrorMessage
        ,PROCESSDATE
        ,CARTONERRORMESSAGE
        ,Division
        ,BOLNO
        ,BOLSHIPDATE
        ,CARTONDETAILERRMSG
        ,ErrorCategory
        ,CASE WHEN ErrorCategory='Success'
        THEN 0
        ELSE    
        COUNT(NotLate) 
        END AS OnTimeCount
        ,COUNT(Invalid) AS InvalidCount
        ,COUNT(WrongData) AS WrongDataCount
        ,COUNT(Duplicate) AS DuplicateCount
        ,COUNT(MissingData) AS MissingDataCount
        ,(COUNT(Invalid)+COUNT(WrongData)+COUNT(Duplicate)+COUNT(MissingData)) AS TotalErrors
        ,Total945s AS TotalReport945s
        --,COUNT(*) OVER(PARTITION BY [CustomerWithErrors]) Total945s 
        , (SELECT (Total945s) WHERE CustomerWithErrors>=0) AS TotalErrors

        FROM(

    SELECT
         PKEY
        ,Customer
        ,CASE WHEN CustomerName = '' OR CustomerName IS NULL
        THEN 'Unknown'
        ELSE CustomerName
        END AS CustomerName
        ,CASE WHEN CustomerName='' OR CustomerName IS NULL OR CustomerName='Unknown'
        THEN 0
        ELSE
        (SELECT DISTINCT Customer WHERE ErrorCategory <> 'Success') END
         AS CustomerWithErrors
        ,Warehouse
        ,ErrorMessage
        ,PROCESSDATE
        ,CARTONERRORMESSAGE
        ,Division
        ,BOLNO
        ,BOLSHIPDATE
        ,CARTONDETAILERRMSG
        ,ErrorCategory
        ,CASE WHEN ErrorCategory='Success'
        THEN 'N'
        ELSE OnTime         
        END AS OnTime
        ,(SELECT Customer WHERE OnTime = 'Y') AS NotLate
        ,(SELECT Customer WHERE ErrorCategory = 'Invalid') AS Invalid
        ,(SELECT Customer WHERE ErrorCategory = 'Wrong Data') AS WrongData
        ,(SELECT Customer WHERE ErrorCategory = 'Duplicate') AS Duplicate
        ,(SELECT Customer WHERE ErrorCategory = 'Missing Data') AS MissingData
        , COUNT(*) OVER(PARTITION BY [Customer]) Total945s



    FROM EDI945UTIErrors


    ) S


        GROUP BY
         PKEY
        ,Customer
        ,CustomerName
        ,CustomerWithErrors
        ,Warehouse
        ,ErrorMessage
        ,PROCESSDATE
        ,CARTONERRORMESSAGE
        ,Division
        ,BOLNO
        ,BOLSHIPDATE
        ,CARTONDETAILERRMSG
        ,ErrorCategory
        ,OnTime
        ,Total945s

EDIT 3: SQL WITH PIVOT: Does this look like what I should do with aggregates in the report? Thanks.

 SELECT 
         S.PKEY 
        ,Customer
        ,isnull(S.CustomerName, 'Unknown') AS CustomerName
        ,CustomerWithErrors
        ,Warehouse
        ,ErrorMessage
        ,PROCESSDATE
        ,CARTONERRORMESSAGE
        ,Division
        ,BOLNO
        ,BOLSHIPDATE
        ,CARTONDETAILERRMSG
        ,ErrorCategory
        ,CASE WHEN ErrorCategory='Success'
        THEN 0
        ELSE    
        COUNT(NotLate) 
        END AS OnTimeCount
        ,Invalid
        ,WrongData
        ,Duplicate
        ,MissingData
        --,COUNT(Invalid) AS InvalidCount
        --,COUNT(WrongData) AS WrongDataCount
        --,COUNT(Duplicate) AS DuplicateCount
        --,COUNT(MissingData) AS MissingDataCount
        --,(COUNT(Invalid)+COUNT(WrongData)+COUNT(Duplicate)+COUNT(MissingData)) AS TotalErrors
        ,Total945s AS TotalReport945s

        --,COUNT(*) OVER(PARTITION BY [CustomerWithErrors]) Total945s 
        --, (SELECT (Total945s) WHERE CustomerWithErrors>=0) AS TotalErrors

        FROM(

    SELECT
         PKEY 
        ,Customer
        ,CASE WHEN CustomerName = '' OR CustomerName IS NULL
        THEN 'Unknown'
        ELSE CustomerName
        END AS CustomerName
        ,CASE WHEN CustomerName='' OR CustomerName IS NULL OR CustomerName='Unknown'
        THEN 0
        ELSE
        (SELECT DISTINCT Customer WHERE ErrorCategory <> 'Success') END
         AS CustomerWithErrors
        ,Warehouse
        ,ErrorMessage
        ,PROCESSDATE
        ,CARTONERRORMESSAGE
        ,Division
        ,BOLNO
        ,BOLSHIPDATE
        ,CARTONDETAILERRMSG
        ,ErrorCategory
        ,CASE WHEN ErrorCategory='Success'
        THEN 'N'
        ELSE OnTime         
        END AS OnTime
        ,(SELECT Customer WHERE OnTime = 'Y') AS NotLate
        --,(SELECT Customer WHERE ErrorCategory = 'Invalid') AS Invalid
        --,(SELECT Customer WHERE ErrorCategory = 'Wrong Data') AS WrongData
        --,(SELECT Customer WHERE ErrorCategory = 'Duplicate') AS Duplicate
        --,(SELECT Customer WHERE ErrorCategory = 'Missing Data') AS MissingData
        , COUNT(*) OVER(PARTITION BY [Customer]) Total945s



    FROM EDI945UTIErrors
    --WHERE Customer ='104646' and OnTime = 'Y'
    --WHERE CUSTOMER='107834'
    --AND ErrorCategory = 'Wrong Data'
    --AND ERRORMESSAGE LIKE '%Overshipment Not Allowed%'

    ) S
    LEFT JOIN
    (
    SELECT PKEY, ISNULL(CustomerName, 'Unknown') AS CustomerName, [Invalid], [WrongData], [Duplicate], [MissingData], [Success]
 FROM EDI945UTIErrors
 PIVOT (Count(Customer) FOR ErrorCategory in ([Invalid], [WrongData], [Duplicate], [MissingData], [Success]))
 AS P) AS PT ON S.PKEY = PT.PKEY

        GROUP BY
         S.PKEY
        ,Customer
        ,S.CustomerName
        ,CustomerWithErrors
        ,Warehouse
        ,ErrorMessage
        ,PROCESSDATE
        ,CARTONERRORMESSAGE
        ,Division
        ,BOLNO
        ,BOLSHIPDATE
        ,CARTONDETAILERRMSG
        ,ErrorCategory
        ,OnTime
        ,Total945s
        ,Invalid
        ,WrongData
        ,MissingData
        ,Duplicate

Thank you very much for your time.

Answer 1

From the pictures you have posted, it looks like your dataset may be pulling in all orders for a customer, and not just those with errors. A good way to check this is to grab the SQL from your dataset and run it in Management Studio. If you're getting rows that you don't want to count, you could use a filter on your tablix.

To apply a filter on your tablix, right click on it and go to Tablix Properties:

Click on Filters and you could chose something like only orders that have an error flag:

If this doesn't solve your problem, please let me know and add the SQL for your dataset to your question and we'll go back to the drawing board. :)

Edit - 11/12/2014

Thanks for the SQL! If CustomerWithErrors only returns the customer number, then the filter won't help. You're still pulling in every record (even those where ErrorCategory='Success'). Did you know you have two columns aliased as TotalErrors? There's

(COUNT(Invalid)+COUNT(WrongData)+COUNT(Duplicate)+COUNT(MissingData)) AS TotalErrors

and

(SELECT (Total945s) WHERE CustomerWithErrors>=0) AS TotalErrors

The first looks like it might work, but I don't know which you're using in your table. Another thing to think about is that you're calculating aggregates of aggregates. You could end up multiplying them if you run into a situation where you have this in your result set:

Customer    |   ErrorCategory   |   TotalErrors
1234        |   Invalid         |   2
1234        |   Duplicate       |   2

Results you'll see in your report:

Customer    |   TotalErrors
1234        |   4

If you want to avoid that, you could let SSRS do the aggregating. Have you considered doing a PIVOT? You could do something like this:

Select Customer, ISNULL(CustomerName, 'Unknown'), [Invalid], [Wrong Data], [Duplicate], [Missing Data], [Success]
FROM EDITable
PIVOT (Count(Customer) FOR ErrorCategory in ([Invalid], [Wrong Data], [Duplicate], [Missing Data], [Success]))

Then in your report for total errors, use an expression that says Sum(invalid)+Sum(wrong data)+Etc. As long as you have one record per customer, your data shouldn't be doubled and you can still get your total count of non-error records, too.

I hope that helps. Let me know if you have questions!