Multiple Datasets in one Table OR show distinct Value in Table (SSRS)

Question

I am fairly new to SSRS and currently I'm working on building my first reports. I encounter some problems with a report that in my opinion should be fairly easy, but so far I couln't find the right answer.

I have a table similar to this:

Material - - Date - - Amount

MatA - - 02/12/15 - - 10

MatB - - 02/12/15 - - 12

MatA - - 02/15/15 - - 5

MatA - - 02/16/15 - - 10

...

What I want to do is sum all the amounts for one material in a table. The result should look like this:

Material - - Sum

MatA - - 25

MAtB - - 12

...

My table features approximately 200.000 rows with more than 150 different materials, so manual selection is definitely not an option ;)

My approaches so far:

• Create a dataset with only the distinct material names (through a query) and use them as the dimension in the table. But I cannot get another dataset (with all the information) into the same table to use the SUM()-function to calculate the second field.

• Show only distinct values of the field Material in a table from a dataset with all information, but I couldn't find out how to do this.

Your help would be greatly appreciated! Thanks and all the best.

Answer 1

On the face of it the solution to what you're asking is very simple, just set the query in your dataset to be something like:

SELECT 
  Material,
  SUM(Amount) AS Sum
FROM 
  <table>
GROUP BY 
  Material
ORDER BY 
  Material

That should create a dataset with two fields, Material and Sum, and you can add these to a table for display.