In reports, you can group the data together and then automatically calculate aggregate metrics such as a sum, count, or average. For example, you could create a report that shows the median compensation in a department or the total number of new hires you made during the previous quarter.
- If you’ve selected a grid as your layout, you can optionally choose to apply grouping and aggregation using the instructions below.
- If you’re using a pivot table or chart layout, the data is automatically grouped and aggregated as you design your report.
Aggregation methods
Rippling supports the following methods of aggregation:
- Average. Calculates the average value.
- Count All. Counts all of the values in the report. Each row is counted once, including duplicate values. Rows with null values are not counted.
- Count Unique. Counts the number of unique values in the report. Values with duplicates are counted only once.
- Max. Identifies the maximum value in the report.
- Median. Calculates the median value.
- Min. Identifies the minimum value in the report.
- Sample. Displays a representative sample of the data in the report. This is useful for showing what the underlying data looks like without needing to click on a row to drill down to the details.
- Sum. Calculates the sum.
Add grouping and aggregation to a grid report
When you build a report using a grid layout, you'll get a table with rows and columns that looks similar to a spreadsheet. Grouping it can help organize the data; adding aggregation lets you calculate metrics using the raw data.
There are two ways to add grouping and aggregation to a grid report:
- Method 1: Group the report
- Method 2: Aggregate specific fields
Method 1: Group the report
To apply grouping, drag and drop a field from the Columns area into the Grouping area. An aggregate value will be calculated automatically for each column. You can then change or remove the aggregation as desired.
For example, if your report includes annual base compensation and you group it by department, the values will automatically be summed to generate a total annual base compensation value for each department.
- If you want to view the average compensation in each department rather than the total compensation, you can change the aggregation method from sum to average.
- If you don’t want to view aggregated values but instead want to see detailed rows that are grouped together under each department name, you can remove aggregation altogether.
If you add data that has a one-to-many relationship to your report, grouping and aggregation can be particularly helpful. For example, if you add pay run information, there will be a unique row for each employee + pay run combination. You can then group by employee to automatically calculate total pay for each person.
After grouping your report, you can optionally make the following changes:
- Update the aggregation method being used for a given data point. Click the three dots on the field name, select Aggregate, and then select your preferred aggregation method. Only aggregation methods that are supported for the field’s data type will be available. For example, if you’re aggregating a text field, only Count All, Count Unique, and Sample will be available.
- Add multiple groupings to further organize your report. Groups are applied in the order they appear in the Grouping area and are nested together. For example, the report can be grouped first by department, and then within each department, by manager.
Method 2: Aggregate specific fields
You can apply aggregation to specific fields in the Columns area by clicking the three dots and selecting Aggregate, then selecting your preferred aggregation method.
The field will be aggregated, and all of the other columns will automatically be moved to the Grouping area.
Nest or flatten the groupings in your report
When you group a report, you can show the information as nested or flattened. You can toggle between a nested and flattened display by clicking the curved arrow icon next to the Grouping area.
Nest your report if you want to show the groupings in a nested hierarchy with expandable/collapsible sections.
Flatten your report to remove the collapsible sections and show one row for every combination of data in the report.
Remove aggregation
If you’re using a grid report and you don’t want the data to be aggregated, you can remove aggregation while keeping the grouping. The data in the report will appear as a series of rows grouped together below the common data point you’re using to group it.
To remove aggregation, click the Unaggregate all button in the Data panel.
To add aggregation back to your report, click this button again. You can then adjust the aggregation methods as needed.
Remove grouping
To remove grouping from a report, remove the field from the Grouping section by clicking the X. The field will remain in the Selected fields area of the report, but it will no longer be used to group the rows.
Bucket by time interval
When your report is grouped by a date or time field, you also have the option to choose an interval, or bucket, for the grouping, which allows you to create a time series of data. For example, if you group payroll data by check date, you can then bucket your data by year and month to produce a monthly time series.
- Add the date field you want to analyze to the desired area of your report.
- In a grid report, add the date field to the Grouping area.
- In a pivot table, add it to the Columns or Rows area.
- In a chart, add it to the X axis or Split by area.
- Click the three dots next to the field name and select Bucket by.
- Choose how you want to bucket the dates, such as by specific date, day, year, year and week, year and month, year and quarter, fiscal year and quarter, or fiscal year. Selecting the fiscal year and quarter or fiscal year lets you set the start date of the fiscal period.
If you're using a datetime field (a date that includes a timestamp) or a time field, you can also bucket by Date and Time (Increments). This lets you bucket your data into more granular time increments such as 1 minute, 15 minutes, 1 hour, 6 hours, or 12 hours.
Chart types in reports
If you want to create visualizations for your data, you can do so in the Reports and Dashboards app. Charts can help you more easily spot trends and identify outliers.
Different types of charts are useful for analyzing different types of information.
Bar and stacked bar
Bar and stacked bar charts are useful for comparing different categories of data. For example, use a bar chart to:
- Understand how many employees you have in each department.
- See how many people started in each month over the past year.
- Understand average compensation by gender per department.
Line
Line charts are useful for comparing changes over time. For example, use a line chart to:
- See pay run amounts over time.
- Compare overtime by manager during the busy season.
- Spot trends in spending per department month over month.
Area
You can convert a line chart to an area chart using the following steps:
- Add one and only one numeric aggregation on the X axis.
- Add one and only one numeric aggregation on the Y axis.
- Add at least one value in the Split by field.
- Select the Settings tab.
- Select Area or Area % in the Series field.
Combination
Combination bar/line charts are particularly useful if you want to illustrate the correlation between two related metrics. For example, use a combination chart to:
- See the average offer acceptance rate over time, while understanding the overall volume of offers at each point.
- Understand voluntary vs. involuntary termination rates.
- Compare monthly pay to hours worked over time.
- Review course completion rate along with course enrollments per course.
Donut
Donut or pie charts can be useful when you have a small number of values you need to compare. For example, use a donut chart to:
- See the proportion of assigned/unassigned devices.
- Demonstrate gender diversity at the company.
- Visualize the breakdown of responses for a multiple-choice survey question.
Funnel
Funnel charts can be useful to demonstrate how data moves through a process or series of stages. For example, use a funnel chart to see how many applicants are making it through each stage of the recruiting pipeline.
Scatter
Scatter/bubble charts are a great way to plot two numeric attributes to understand correlations and trends. For example, use a scatter chart to:
- See the number of referrals made by function over time.
- Visualize the amount of time off taken by employees across tenure and location.
Single metric
Single metric charts let you view a single “big number” of your choice, then drill down to the specifics that make up the number. Single metric charts are especially useful for tracking key performance indicators (KPIs) in dashboards.
For example:
- Monitor the number of customer support tickets opened each week.
- See what percentage of candidates are making it to each interview stage for an open job requisition.
- See the number of people on your team who are scheduled to be out of the office next week.
- Track the median number of days it takes to screen a candidate.
When you build a single metric chart, it uses the top-most aggregation from the report as the metric (the value in the “All” line).
Gauge
Gauge charts are useful for showing progress towards a goal or monitoring a health or performance metric.
For example:
- Monitor the number of candidate applications you’re receiving per week.
- Track your target offer acceptance rate.
When you build a gauge chart, it uses the topmost aggregation from the report (the value in the “All” line) to set the segments. You can then manually change the values used to divide the segments.
Convert the currencies in a report
If your report contains currency fields, you have the option to convert them all to the same currency type. You may want to do this if you want to aggregate the data and create comparable calculations.
How currencies are handled in reports by default
- Currency values are calculated daily at 12 AM Pacific Time based on rates from Open Exchange Rates. The current day's rates are used in all calculations, regardless of the time frame selected in the report. Note that these rates are used for reporting only, not payments. For more information, see our Help Center article about how Rippling calculates currency conversion for payments.
- When you add currency values to a report, they are displayed in their original currency type. Your report may contain a mix of currency types: USD ($), INR, CAD, etc.
- When you aggregate a report, any aggregated currency values are automatically converted and combined into a single value. The default currency applied to your report will be the currency in which your company is billed.
When you download a report, you can specify how you want to display currency fields. For more information, see Share and download reports.
To convert the currencies in your report, click the three dots in the top right corner and select Currency conversion, then select the desired currency.
When you save the report, the selected currency is saved with the report. If you share it with others, they’ll see it in the currency that you saved.
To remove currency conversion and display all currency fields in their original currencies, click the three dots in the top right corner, select Currency conversion > No currency conversion.
Note that if there is any aggregation applied to the view, you won’t have the option to remove currency conversion. For example, if your report includes an aggregate variable such as Vacation balance in hours or Number of vested and unvested shares, you won’t be able to remove currency conversion.
Aggregate amounts in multiple currencies
The simplest way to aggregate currency values is to first convert them to a common type, as described above. But there may be times when you want your report to aggregate different currency types separately from each other (for example, aggregate all the USD values together and aggregate all the INR values together). There are two ways to accomplish this.
Option 1: Use a Report Formula to change a currency value to a number
Your first option is to create a Report Formula that converts a currency field to a data type of Number. When you convert the currencies in your report, only fields with the data type of Currency will be converted. Those with a data type of Number will not, effectively bypassing currency conversion for those values.
This option works well if the values are all in the same currency. If the values are in multiple currencies, use option 2 below.
- In the Available data panel, hover over Report Formula and click +.
- Give your formula a name.
- In the formula editor, add the currency field that you don’t want to be converted.
- For the data type, select Number.
- Click Continue to save the formula.
- Drag the new report formula field from the Source Fields area into the area of the report where you want it to be displayed.
Option 2: Use Report Formulas to group by currency
Your other option is to use Report Formulas to separate the currency type and amount into different columns, then use the type to group and aggregate the amounts.
To do this, you’ll create two Report Formulas:
- Create a formula using the CURRENCYTYPE function to extract just the currency type from the field.
CURRENCYTYPE(Annual base compensation)
- Create a formula using the CURRENCYAMOUNT function to extract just the amount from the field. Set the data type to Number.
CURRENCYAMOUNT(Annual base compensation)
You can then group the report using the currency type field and aggregate the amounts. Because the amount is a numeric data type, it won’t be converted when you change the currency in the report.