Summary

Depending on which fields you have selected to include in your report and the type of information you are trying to extract, it may be necessary for you to aggregate some of the fields in your Report Table. Aggregation allows you to get accurate numbers in your crosstabs, pie charts, bar graphs, line graphs, etc. by ‘condensing’ fields that may have multiple values into one single cell in the Report Table. You may need to aggregate fields if you have ever asked yourself, “Why is this case showing up on multiple rows in my Report Table?”


One of the most common scenarios to aggregate is when you wish to answer the question, “How many individuals received services within a timeframe?” In this scenario, you want to gather basic information (How many individuals) based upon fields that can contain multiple values (services performed). There are too many possible combinations of data points to list all scenarios when you would need to aggregate fields. Therefore, in this document, the ‘general rules’ to follow will be discussed.


Look at the fields in your report. For each field, ask yourself, “Can a case have more than one of these?” (Examples: Age At Intake: Can a case have more than one of these? No. ...Service Performed: Can a case have more than one of these? Yes.) If the answer to this question for all of your fields in the report is No, then you will not need to aggregate any fields. If you answered No for some fields and Yes for others, then you might have to aggregate fields. If so, for each field in the report (including hidden fields!) follow these questions & steps:


  • “Can a case have more than one of these?” If no, leave as-is ~ unaggregated. If Yes, go to question #2.
  • “Do I have or want this field as a label in a crosstab (or piechart, etc.)?” If Yes, leave it as-is ~ go to the next column. If no, then aggregate.
    • For Date Fields, select Min to force the field to only show the first date, or Max to force the field to only show the most recent date.
    • For Text Fields, select Text-Join
  • For any fields in question #2 where you answered Yes (you have or need them as a label in a crosstab, etc.), you must have that field somewhere in all crosstab configurations. It can be a column or row header or a sub-header… but it must be in there somewhere to ensure accurate numbers.
  • Once you have answered Yes to question #2 one time, exercise caution any subsequent times you answer Yes here because it could cause you to get inaccurate numbers, especially if the field is not a header somewhere in all crosstabs.
  • Do not aggregate the Row Count field. Instead, leave it as-is ~ unaggregated.


Here is a basic flowchart for the above:

Still not sure? See the Aggregating Columns Video Guide for more information. The Aggregation functionality is a powerful tool which requires some practice and experience to master. We recommend getting some practice with it, when you are not in a time-crunch, to get the hang of it. If you need assistance, contact Collaborate Technical Support by submitting a Support Request.

See Also: