Sum of the grouped distinct values

Question

This is a bit hard to explain in words ... I'm trying to calculate a sum of grouped distinct values in a matrix. Let's say I have the following data returned by a 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 |
------------------------------------------------

There's some other data in the query, but it's irrelevant. ParentProdCount is specific to the ParentID.

Now, I have a matrix in the MS Report Designer in which I'm trying to calculate a sum for ParentProdCount (grouped by "Group"). If I just add the expression

=Sum(Fields!ParentProdCount.Value)

I get a result 20 for Group A and 3 for Group B, which is incorrect. The correct values should be 5 for group A and 1 for group B. This wouldn't happen if there wasn't ChildID involved, but I have to use some other child-specific data in the same matrix.

I tried to nest FIRST() and SUM() aggregate functions but apparently it's not possible to have nested aggregation functions, even when they have scopes defined.

I'm pretty sure there is some way to calculate the grouped distinct sum without needing to create another SQL query. Anyone got an idea how to do that?

Solution

Ok I got this sorted out by adding a ROW_NUMBER() function 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))

Comments

[Rono]

For anyone else who has this problem, I tried this summing a decimal value and got this error: "Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type." I got it to work by replacing the 0 with Nothing in the Sum function.

[Bashar Abu Shamaa]

It worked with me, when use Nothing instead of 0 : =SUM(IIF(Position = 1, ParentProdCount.Value, Nothing))