Wierd SUM result in nested Row Group

Question

I have three nested ROW groups:-

The first one is a depended on a wether a field is true or false in the dataset, for each case. This is the where the error is worst. The second is nested on the first and is based on a group variable in the cases (1 to many), the third is the ref number of the cases.

The sums don't work for a cloumn that is produced by a join, depending on the ID of the second group. It seems to pull the right value, but multiplies by the number of cases. I can divide by the case numbers here, inside the last nested group(ref#) to get the right value. Tried using "Count" , Blank, Add total after..

If I try to sum the column with "=Sum(ReportItems!Textbox231.Value)" Produces:-

The Value expression for the textrun 'Textbox232.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

The sums work fine for the non joined values..in all three nested row groups. But for the joined values they are out by an order of magnitude. Why is this?

SUM not working for 3rd column

SUM yields wired results

SELECT  DISTINCT

The Sum is in the report boxes. =sum(iif(Fields!BudgetHires.Value > 0, Fields!BudgetHires.Value, 0)) — mabees

JeromeE JeromeE · Accepted Answer · 2018-01-10T16:00:12

Here is a common reason why this kind of problem happen.

The likely reason for the SUM being wrong is the fact that the DISTINCT in your select hides duplicates in the underlying query. Since the SUM is executed before the distinct, it sum the results that you don't see after they're filtered out by the DISTINCT.

Instead of DISTINCT use a GROUP BY query, then you can either make a base query that do not have duplicates (which you don't have to hide with a DISTINCT) or if you can't get rid of the duplicates, aggregate your column before displaying it by doing a MIN, a MAX or an AVG.

I'd be happy to help more but there's not enough information in your question to reproduce the problem on my computer.

There are other reasons why a SUM can return unexpected results: typically implicit cast (SQL server decides on an unexpected datatype and rounds the numbers), and in some situations a CASE clause which is executed either before or after a WHERE condition. But these don't seem to be the problem here.

Example

DECLARE @T TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, NumVal INT) 
DECLARE @i INT 
SET @i = 1
WHILE @i < 1000 
BEGIN 
    INSERT INTO @T (NumVal) VALUES (@i) 
    IF RIGHT (CAST (@i AS VARCHAR(12)),1) = 7 
    BEGIN INSERT INTO @T (NumVal) VALUES (@i) END 
    SET @i = @i +1 
END 
SELECT DISTINCT NumVal, SUM (NumVal) FROM @T GROUP BY NumVal

In the example above, I have inserted 999 distinct entries in a table, but duplicated any number which ends with 7. The select distinct give the impression that there are only 999 entries, while a sum adds the numbers ending with 7. Your situation is probably more complicated, but what I want to show here is that duplicates in the underlying becomes invisible with a DISTINCT and reappear with a SUM:

NumVal  Sum
1   1
2   2
3   3
4   4
5   5
6   6
7   14
8   8
9   9
10  10
11  11
12  12
13  13
14  14
15  15
16  16
17  34
18  18
19  19
20  20
21  21
22  22
23  23
24  24
25  25
26  26

Wierd SUM result in nested Row Group

1 Answers