What is Custom Report Builder?
Deputy has a number of default reports you can run but if you would like to build your own report you can use the Custom Report Builder.
Using the Custom Report Builder, you can call any piece of information within Deputy you would like to generate a custom report for.
How to access Custom Report Builder
You must be a Location Manager or System Administrator to access Reports in Deputy.
To access the Custom Report Builder, click on the Reports page and scroll to the Report Builder section. Click on one of your previously saved reports or Create New Report to begin a new one.
How to create a Custom Report
1. Load template
After clicking on Create New Report a pop-up box will appear with the templates available to build reports.
You can select from:
- Leave Balance
- Sales Data
- Timesheet Pay Return
- Approved Leave
- Shift Questions Response Report
For this example, we'll select Schedule to begin.
Click Load Template.
The following is an example of a report that will be generated, click Edit Report to customise your report.
The following screen will appear. Here, you can edit the details of your report.
2. Adding and removing fields
On the left side of the Custom Reporter dashboard, you can see a list of categories from which you can pull data from.
If you know the name of the field you need you can type it in the search bar. Alternatively, you can expand to field categories to select the field you need.
In this example, we'll add the Area Name field into the report
To add this, click the Area drop-down and drag Area Name field into the list of fields as shown:
To remove fields that you don't need from the report, simply click the 'X' icon on the item you would like to remove.
Note: You can't rearrange the order of the fields once they are added but you can remove them and re-add them in your preferred order.
3. Add filters
Once you have all the fields you'd like and in the preferred order, you can add in some filters.
The report builder is limited to 20,000 rows at a time. This means you'll want to add in some filters to narrow down the data to just what you're interested in.
Click on the Filters.
Click on Select Column and choose what column you'd like to use to filter your results by
Depending on what column you choose to use as a filter, you'll have different options for the remaining fields.
Verify that the filters are configured as required and click Save.
Your data will refresh to reflect the new filter that's been applied.
Next, click on Conﬁguration. This will allow you to group your data into meaningful pieces. Click on Group By from the Configuration dropdown menu.
Click on "Select" and choose what field you'd like to group your data by.
Note: should you choose to export this data in the CSV format, the groupings will not export. This grouping view is exclusively available within the Deputy.
4. Add calculation fields
Calculation fields allow you to create a custom column to create your own calculations using the data from the report.
Click Add Calculation Field in your Fields section.
A pop up will appear and you can select from our pre-built calculation fields such as:
- Cost difference
- Late Arrival
- Meal break exceeds
or use formulas to create your own calculations from the fields available.
For more information about resource calls, check out our API doc.
Some example formulas:
To search for a keyword in a shift note:
IF(ISERROR(SEARCH("Permit",[RosterObject.Comment])), 0, IF(SEARCH("Permit",[RosterObject.Comment]), 1, 0)
Split Timesheet Mealbreak - (Scheduled) into individual fields:
=IF([Timesheet.strMealBreak] && [Timesheet.Mealbreak] <> "0:00",LEFT([Timesheet.strMealBreak], SEARCH("-",[Timesheet.strMealBreak]) - 1),"")
=IF([Timesheet.strMealBreak] && [Timesheet.Mealbreak] <> "0:00",RIGHT([Timesheet.strMealBreak], LEN([Timesheet.strMealBreak]) - SEARCH("-",[Timesheet.strMealBreak])),"")
To generate a report that shows team members who are Early(-) Late(+) to their shift:
IF([RosterObject.StartTimeLocalized] && [Timesheet.StartTimeLocalized],((ROUND([Timesheet.Date],0) * 24 * 3600 + VALUE(LEFT([Timesheet.StartTimeLocalized],LEN([Timesheet.StartTimeLocalized])-3)*3600) + MID(IF(LEN([Timesheet.StartTimeLocalized]) = 4,CONCATENATE(0,[Timesheet.StartTimeLocalized]),[Timesheet.StartTimeLocalized]), 4, 2)*60)-(ROUND([RosterObject.Date],0) * 24 * 3600 + VALUE(LEFT([RosterObject.StartTimeLocalized],LEN([RosterObject.StartTimeLocalized])-3)*3600) + MID(IF(LEN([RosterObject.StartTimeLocalized]) = 4,CONCATENATE(0,[RosterObject.StartTimeLocalized]),[RosterObject.StartTimeLocalized]), 4, 2)*60))/60,"")
5. Save Reports
After you have configured and compiled your report, you can use it for future use. Click Save at the top of the page.
You will be asked to create a name and a description for this report. When you are done, click Save.
6. Load Reports
To load a previously saved report, click Load, next to the Save button.
In the Load Report pop up, click the report you would like to load, then click Load Template.
You can also delete previously created and saved reports by clicking on the trash can icon.
7. Exporting your Custom Report
The Custom Report Builder exports reports in CSV format.
Click 'Export CSV' at the top of the page.
Read more articles about the reports you can generate with Deputy in our Help Centre Collection or watch our training webinar on Reports from October 2021.