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.
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:
That should create a dataset with two fields, Material and Sum, and you can add these to a table for display.