Let’s make the report at little more readable and interactive.
We are going to do a drill down on the names in our report. This mean when the report first opens, you will only see one row for each name. If you want to see the details of that person, you can click on a + by their name and the details for that person will pop down like an accordion.
Below your report design window, you should see another window called Row Groups
Go to the area marked Details (green arrow) and right click – Group Properties…
Click Visibility > Hide > Display can be toggled.
(Note I am selecting Name1 not Name. If you remember Name was the column we hid. Name1 is the column created when we created the Parent Group)
Now when you open your report, you will see it is in drill down format.
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 headerand 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.
Next, let’s set up a running total for Hours spent on each job. To do so, right click on the Hourstext box and selectAdd Total
Now when we go back to preview, we will see at total in the group footer for each person
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 toCommon 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.