In our last lesson we made the basic report seen below:
**Note, you can follow the link below to the first lesson:
SSRS: Introdution: 1rst Report
Grouping
Our data is currently in table form, but is otherwise still nothing more than a raw data dump. Let’s make our report a little nicer with some grouping. Right click on the data row (not header) in your table. Mouse over to Add Group and select Parent Group…
In the new window, select [Name] from the drop down.
Click Add group header and Add group footer boxes. Now click OK
Now a group has been added to your report.
If you click on preview, you will now see the table is grouped by Names
But you will notice we now have 2 columns showing the Name, one – our new grouping column and the other – the original column. This is redundant. To get rid of it, go back to Design, right click on the second name column and select Column Visibility…
When the new window opens up, click Hide.
Now when you look at the report now, you will see the second Name column is now hidden.
Running Total:
Next, let’s set up a running total for Hours spent on each job. To do so, right click on the Hours text box and select Add Total
Now when we go back to preview, we will see at total in the group footer for each person
Average:
Now what if we wanted an average instead? Right click on the textbox that says [Sum(Hours)] and select Expression
You can just type = Avg(Fields!Hours.Value) in the expression builder box, but if you don’t know the code, you can use information in the boxes below. As you can see in the example below, if you go to Common Functions > Aggregate you will see the code for lots of functions like Average, count, standard deviation.
Now when you go to preview, you will see an average.
But now we have a new problem. If you are trying to average something like work hours, odds are you will not need to go out to 10 decimal places. So a number like 8.272727272727 is pretty much ridiculous for a report like this.
Now go to Number > Number and set the Decimal Places to 2
So if you look at it again, you will see you only have 2 decimal points now.
