Reportviewer - Make consecutive repeating rows become one

Question

may I know how to do with the scenario describe below?

Lets say I have a dataset with 4 columns, and I have 3 consecutive repeating criteria of Date, Name and ID but not quantity. How can i make it become one row that sums all the 3 rows before? How can I do this in report viewer?

Date      Name          ID          Quantity
____________________________________________
21/3     John           001         10
         John           001         20
         John           001         10

And I want to make it like:

Date      Name          ID          Quantity
____________________________________________
21/3     John           001         40

Thanks in advance.

Answer 1

I solved this by using GROUP BY query. For instance:

SELECT Date, Name, ID, SUM(Quantity) as Quantity
From EmpTable
GROUP BY Date, Name, ID
ORDER BY Date ASC

Simple though, I just thought that report viewer can do this. Thanks