Sum of grouped individual values

Question

It is a little difficult to explain in words ... I'm trying to calculate the sum of the grouped different values ​​in a matrix. Let me say that I have the following data returned by the SQL query:

------------------------------------------------ | Group | ParentID | ChildID | ParentProdCount | | A | 1 | 1 | 2 | | A | 1 | 2 | 2 | | A | 1 | 3 | 2 | | A | 1 | 4 | 2 | | A | 2 | 5 | 3 | | A | 2 | 6 | 3 | | A | 2 | 7 | 3 | | A | 2 | 8 | 3 | | B | 3 | 9 | 1 | | B | 3 | 10 | 1 | | B | 3 | 11 | 1 | ------------------------------------------------ 

The request has other data, but it does not matter. ParentProdCount is specific to ParentID.

Now I have a matrix in MS Report Designer, in which I am trying to calculate the sum for ParentProdCount (grouped by group). If I just add an expression

 =Sum(Fields!ParentProdCount.Value) 

I get a result of 20 for group A and 3 for group B, which is wrong. The correct values ​​should be 5 for groups A and 1 for group B. This would not have happened if ChildID had not been involved, but I should use some other child data in the same matrix.

I tried nesting the aggregated functions FIRST () and SUM (), but it seems impossible to have nested aggregation functions, even if they have certain areas.

I am sure there is a way to calculate a grouped different amount without having to create another SQL query. Has anyone figured out how to do this?

Answer 1

Ok, I figured this out by adding the ROW_NUMBER () function to my SQL query:

 SELECT Group, ParentID, ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY ChildID ASC) AS Position, ChildID, ParentProdCount FROM Table 

and then I replaced the SSRS SUM function with

 =SUM(IIF(Position = 1, ParentProdCount.Value, 0)) 

Answer 2

Place the grouping over the ParentID and use the summation over this group,

eg:

 if group over ParentID = "ParentIDGroup" then column sum of ParentPrdCount = SUM(Fields!ParentProdCount.Value,"ParentIDGroup")