Administrate’s Report Engine lets you build more advanced reports that links most fields in your Administrate database together. By utilising these links, and creating your own criteria as to how this information is queried, the Reports cater for a wide variety of situations and eventualities. The Reports feature was built so you can:
- build Reports that are relevant and crucial to your organisation and workflow
- choose exactly what information you need to query or display
- choose exactly how to display your results
- export information to an Excel spreadsheet or CSV , and by extension, an emailing or marketing list
- automatically schedule your Reports to be sent by Communication Triggers
You can also use Custom Fields to record organisation-specific information for your Accounts and Contacts , which help build more powerful and streamlined Reports for your organisation. Whereas previously you’d use the CRM Filtering system to create your Reports and emailing lists, the Reports Engine will now accomodate the same functionality but with more fields available to offer you wider flexibility in building more meaningful Reports.
As the Reports can access and display information across your Administrate database, there’s no limit for the kinds of Reports you wish to produce, who they’re intended for, and for what purpose.
Here are some examples of use cases for Reports:
|Who?||What kind of Report?|
|Administrators||Report on the Attendance of every Event in a given month, and students who did not make certification, so that changes can be made to target improvement in these areas|
|Sales||Report on current open Opportunities so that realistic targets can be projected for any given time period|
|Marketers||Report on past students who had attended Event X and Event Y, and therefore might be interested in Event Z: using these results a new marketing list can be created to promote Event Z to these specific ex-students|
|Managers||Report on Event statistics for a given month to see how the business is doing|
Please visit the section you wish to learn more about:
- Create a Report
- Edit a Report
- Delete a Report
- Export a Report
- Example Report
Please familiarise yourself with the following terminology used for understanding the Reports Engine:
You’ll be able to create conditions when in the Filter Conditions tab, using the button, and to remove them. When searching for the appropriate Field to query, you can use Relations to find related information.
There are different operators for different kinds of Conditions to help you build your Reports: Text, Numerical, and Date. When you select a Field then it automatically presents you with the operator. For example, if you select the field:
- Event Title: text operators will be available
- Event ID: numerical operators will be available
- Event Start Date: date operators will be available
The operators for each are:
|ends with||does not exist||is in day|
|is||less than||is in the future|
|does not exist||not equals||is in month|
|does not contain||exists||is in the past|
|not equals||is in week**|
is in year
does not exist
is on day of week
For example, to query Events, you can set operators to search only:
- Events that contain (or partially-contain) a Course Code
- Events that started in the month of February last year*
*For ranges, you should set 2 conditions: one for the start of the range (using the operator after), and the other for the end of the range (using the operator before).
You can create more powerful reports by grouping Conditions together so that they are treated as one Condition by using Groups .
**The is in week operator uses MySQL's default mode of defining a week, which is that Sunday is the first day of each week and that week 0 (the first week of the year) begins on the first Sunday of the new calendar year. MySQL has eight different modes for how a "week" is defined (external link: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week). Using is in week can produce a date range you're not expecting in your report and is because the definition of a "week" can be completely relative. If you encounter this issue, we recommend using a group of conditions for the is in day or is in month operators because the definitions of "days" and "months" would produce consistent results.
When you create a new Report, the first step is to choose your Entity, which essentially determines which Fields you can choose to base your database query on, and which Relations you can access. This is the most important step, as this determines the information you can Report on, and how the Report will be displayed. Bear the following in mind when selecting your base Entity when building your Report:
- What do you want to report on?
- What do you want the results to be displayed in relation to? For example: do you want to see the results in terms of an Account, or a Contact?
For example, if you’re looking to create a Report on:
- the students who’ve been registered on Event X and Event Y, then you’d select Delegate
- Events that started between certain dates, then you’d select Events
The Field is what information in your Administrate database you wish to query on, using Conditions , or display, using Output Columns. These are named as they are seen in your database. Fields available depend on the Entity selected, and Relations , e.g. If you wanted to query the Account Name of a Contact, then the Account information would be a Relation to the Contact.
For example, the Fields from a Contact Entity include many of those seen on the Contact’s screen:
- Name (note these can be further separated by First Name, Middle Name, and Last Name)
- Job Title
- Address Town
- Is Staff
See Relation for an illustrated example of Fields and Relations.
To create powerful reports, you can Group a set of Conditions together so that they count as one Condition.
To create a Group, when you’re in the Filter Conditions tab, click to add, and to remove them. Be careful where you click : creating Groups from the top level of the Report is generally the rule of thumb, unless you wish to create Nested Groups (Groups within Groups). Think of Groups like the folders on your computer, where you would create folders from the root directory, unless you want to create sub-directories.
Ordering takes your specified Output Columns and lets you sort them in ascending or descending order so you can order the display of your Report. You can prioritise the sorting, too. For example, if you had a Report displaying all your students and the Events they have attended, you may wish to have the student name as your main sorting method, followed by the date of when they attended an Event.
To create an order, go to the Ordering tab and click on the Output Column you wish to have as the main sorting priority, then the Output Column you wish to have as the second priority, etc.
In the example below, we’ve created a sorting priority of the number of booked students in descending order (so higher numbers appear towards the top), then the second sorting priority is the number of maximum amount of students the Event can hold, in ascending order (so lower numbers appear towards the top).
Output Columns are the fields you wish to display in your Report. If you export your Report into Excel , these Output Columns form the top row of your results.
For example, if you are creating a marketing or emailing list, then you’d want to have the Contact’s email address as an Output Column.
In the Output Columns tab, you can add Output Columns by clicking , and to remove unwanted Output Columns. Selecting these Output Columns is similar in behaviour to selecting your Conditions , where understanding of Entities , Relations , and Fields will be useful.
Once you’ve added an Output Column, you can relabel any of them to suit your needs.
For numerical Output Columns, you can also get the statistical values to return:
- Sum (Total): the addition of all the values
- Average: the average number of the range of values returned
- Maximum: the maximum value within the range of values returned
- Minimum: the minimum value within the range of values returned
- Count Results: the number of results returned
In addition, you can also create basic formulas using the Advanced checkbox in the bottom-left corner. Ticking this will reveal:
- A button
- Alphabet titling of the Output Columns (1st row = A, 2nd row = B, 3rd row = C, etc.)
You’ll then be able to use these to perform arithmetic in your Output Columns.
For example, if your Entity is Delegate , then you can query, with a Condition , or display, through an Output Column, the Event Location of their Event through:
Event → Location → Name
Note that Event and Location are Relations, and the Name is the Field . Event is a Relation of Delegate (the Entity in this example), and Location is a Relation of Event .
You can also add a Relation as a Condition to create more powerful and streamlined reports. In the Filter Conditions tab, in the bottom-left of the screen there is an advanced checkbox, which once ticked will reveal a button.
When you have a big list of results, you may find it easier to view when you can group an Output Column . For example, when you have a lot of different dates, it may be easier to Section Group your results by the Month and Year, in ascending or descending order. This will display your results per Month and Year, e.g. all the results for January 2014, then February 2014, then March 2014 (in the case of ascending order) as illustrated below:
Here you can see at the beginning there’s a Start Date Output Column with randomly ordered dates. After adding the Section Grouping, we can clearly see the results clustered by Month and Year: January 2014, February 2014, etc.
If at any stage you create an unwanted criteria, please use or to remove the criteria you wish.
To create a basic report:
- Select the Entity you want to report on, this will create a new blank Report with that Entity.
- In the Filter Conditions tab, click to select the Conditions , or filters, you want to apply to your Report, using Relations and Fields as appropriate.
- Select the Output Columns tab to select, edit, and order what to display in your Report
- [Optional] Select the Section Grouping tab to specify how to group your results
- [Optional] Select the Ordering tab to specify the order priority of your results
- Click to run and view the report.
- Click to save the report.
- On the Reports screen, click against the Report you wish to edit.
- Select Edit.
- Make your desired changes.
- Click on the name of the Report to run and view it.
- Make your desired changes.
- On the Reports screen, click against the Report you wish to delete.
- Select Delete.
- Confirm or Cancel the deletion.
If you confirm the deletion your Report will now be deleted.
Every time you load a saved Report, it pulls the latest information from your database. When you export a Report, it essentially takes a snapshot of the current data from when you ran the Report. You can export Reports as an Excel or CSV , or to the DMS (Document Management System) .
You may wish to export to Excel or CSV:
- When viewing the results of a Report, click
- Select Excel or CSV as your desired file format.
- Select Full or Summary: when you view your Report, there are tabs for Full and Overview, which corresponds to the information you wish to export.
You can set User Role permissions for the Reports system so you can specify who can view or edit Reports. There is a section in the Permissions tab for User Roles under the section of Reporting.
You’re a marketer who wishes to target a new course, Advanced First Aid , to students who’ve previously attended both your First Aid at Work (Event X) and First Aid Basic (Event Y). You’ll then export these results to a CSV, which you can use to import into a mailshot program so you can send an email blast for your new course.
Because we want to base our Report on students who’ve attended past Events, we’ll choose the base Entity type as Delegates : this will start a new Report for the Entity type, Delegate .
Sometimes when you create a Report, you may find that you’re seeing results you wouldn’t have thought of. In this case, you might find your search results includes students who have cancelled in any of the specified Events. Cancelled students shouldn’t be counted as having attended your Events, so these results should be excluded. In addition, you only want to include Students who have an email address because you want to use this to send them an email blast.
- Click and select Delegates.
- Ensure the top left text says: Delegate matching ALL the following: this means that all the Conditions must be satisfied for this query*.
- Add the first Event: click and select Event → Course → Title.
- In the drop-down for this Condition, select is , and in the text box enter: First Aid at Work (or Event X).
- Add the second Event: click and select Event → Course → Title.
- In the drop-down for this Condition, select is , and in the text box enter: First Aid Basics (or Event Y).
- Add the criteria for including only non-cancelled students: click and select Cancelled Date.
- In the drop-down for this Condition, select does not exist: this will exclude any results where the Student has cancelled.
- Add the criteria for including only students who have an email address: click and select Contact → Email.
- In the drop-down for this Condition, select exists: this specifies this field must be populated to return the results.
- As a last check, ensure the title of these conditions and conditions reads: Delegate matching ALL of the following:**
* This ensures all the Conditions listed are matched for the Report. If you selected ANY then the Report would return results for students who went on Event X or Event Y or had never cancelled any Event: which would not produce the desired results.
Now that we have the correct search criteria, let’s add some Output Columns .
You won’t be able to view the Report until you’ve added Output Columns .
As we’re looking to have this as a marketing email list, we’ll need at least an email address. It would be useful to have the student’s name as well. So this is going to be a very basic output: just name and email address.
- Click on the Output Columns tab (if you haven’t already).
- Add the student’s name as an Output Column: select Contact → Name.
- Relabel this field (the text box under the Label column) to whatever you like, in our example: Student’s Name.
- Add the student’s email address as an Output Column: select Contact → Email.
- Relabel this field (the text box under the Label column) to whatever you like, in our example: Email Address.
Click to view the results.
You may notice that your results will be somewhat random and unordered. It may not matter if you’re never going to browse through the information, but it might be useful when you need to quickly refer to a student. Thus, having the Report sorted by Student Name will be beneficial here.
- Click on the Ordering tab.
- Click on green Contact → Name to select this as your main sorting priority.
- As we want our results to be shown in alphabetical order, we’ll leave the Sort Direction as Ascending.
Click to view the results.
Once you’re satisfied with the Report, you can save it, then Export it.
When you’re satisfied with the Report, you should save it:
- Click on then Save As.
- Name your Report.
- Select the folder in your DMS
To Export a Report, you must view it first.
- Select CSV as your desired file format.
- Select Full: this will download the CSV results as you’ve viewed the Report (a summary will only provide statistics).
Your CSV file will be downloaded and ready for import into your mailshot program.