Sometimes when pulling service data, which often results in multiple rows for one individual person (if someone received a service 8x, you will see 8 rows of data for that one person). Often, you might need to see a unique list of clients instead of a long list of duplicate IDs. To do this, you will use the group feature in the Report Builder.
The group feature lives in the filter selection at the top of the column for Unique ID. So the first thing you need to do is bring the UID into the grid. Once you've done that, click on the filter icon in the Unique ID column.
Check "Group by this field", then "Apply".
You will see a unique count of IDs in that column (see the column to the right of the ID's). This tells you how many occurrences of that ID are in this data set.
In the example above, we can see that there are 9 unique participants in this data set (you can find that number by looking at the Details tab at the top)
Why are all the other fields displayed as numbers? What do these mean?
When you group by a certain field, the Report Builder will automatically show you the field you are grouping by in its original form (if you have 5 people with the same name, it will show you the name, but only once), but it will turn all the other fields into counts. What that means, is that it will show the number of responses present for that particular person, for that particular field.
For example: looking at the data above, you can see that Tim has an "8" in the second column (Unique ID) - this means that his unique ID appeared 8x in that data set. In the "reason for coming" column, you see a "5". This means that there are 5 responses from Tim related to that answer.
How do I get the counts to show data?
When you are looking at counts for grouped data, you have the option to show the most recent value or the first value, based on a set of criteria (usually date it was recorded).
For example, you have a consent form that is completed every year by your clients. If you'd like to see the data from the most recent year's form, you can:
Pull your UID's into the grid
Pull the fields you need from the Consent Form
Group by UID (to show each person only 1x)
In the Consent Form column(s), click on the filter icon and click "Show last by" and "Date of Activity"
At this point, the actual data will show in that column. If you want to see the very first form filled out, you can check the "Show first by" option and select "Date of Activity"