Using the Reporting Engine

Entity

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

Field

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
• ID
• Is Staff
• etc.

See Relation for an illustrated example of Fields and Relations.

Group

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.

Adding Groups from the top level: clicking any other +Group will result in nested Groups

Ordering

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 prioritize 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).

Create an ordering priority with your Output Columns to control the display of your Report

Output Column

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.

The Output Columns here are Student Name , Contact - Email , Contact - ID , D.O.B. , etc.

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 behavior 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.

Relabel your Output Columns to whatever you wish to be displayed

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

You can view numerical Output Columns in statistical form.

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.

Add Formulas to perform arithmetic on your numerical Output Columns.

Relation

Relations are the connections that different Entities have with each other. Using Relations, you can find related Fields to the Entity you’ve based your report on, in a logical manner.

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

Example of Relations and Fields

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.

Tick the Advanced checkbox to reveal the Add Relation button

Section Grouping

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:

Section Group your results to achieve easier to read visibility.

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.

Export to Excel or CSV

You may wish to export to Excel or CSV:

1. When viewing the results of a Report, click
2. Select Excel or CSV as your desired file format.
3. 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.
4. Click

Export to DMS

When you export to the DMS , you can recall the Reports for use with other parts of the system, e.g. attaching them to Communication Triggers .

1. When viewing the results of a Report, click
2. Select the folder in the DMS (Default: Reports).
3. Name your Report.
4. Click

Scenario

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.

Entity & Filter Conditions

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.

1. Click and select Delegates.
2. Ensure the top left text says: Delegate matching ALL the following: this means that all the Conditions must be satisfied for this query*.
3. Add the first Event: click and select Event → Course → Title.
4. In the drop-down for this Condition, select is , and in the text box enter: First Aid at Work (or Event X).
5. Add the second Event: click and select Event → Course → Title.
6. In the drop-down for this Condition, select is , and in the text box enter: First Aid Basics (or Event Y).
7. Add the criteria for including only non-cancelled students: click and select Cancelled Date.
8. In the drop-down for this Condition, select does not exist: this will exclude any results where the Student has cancelled.
9. Add the criteria for including only students who have an email address: click and select Contact → Email.
10. In the drop-down for this Condition, select exists: this specifies this field must be populated to return the results.
11. 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.

Your Filter Conditions should look like this for this scenario.

Now that we have the correct search criteria, let’s add some Output Columns .

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.

1. Click on the Output Columns tab (if you haven’t already).
2. Click
3. Add the student’s name as an Output Column: select Contact → Name.
4. Relabel this field (the text box under the Label column) to whatever you like, in our example: Student’s Name.
5. Click
6. Add the student’s email address as an Output Column: select Contact → Email.
7. Relabel this field (the text box under the Label column) to whatever you like, in our example: Email Address.

Your Output Columns should look something like this for this scenario.

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.

Ordering

1. Click on the Ordering tab.
2. Click on green Contact → Name to select this as your main sorting priority.
3. 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.

Save

When you’re satisfied with the Report, you should save it:

1. Click on then Save As.
2. Name your Report.
3. Select the folder in your DMS
4. Click

Export

To Export a Report, you must view it first.

1. Click
2. Select CSV as your desired file format.
3. Select Full: this will download the CSV results as you’ve viewed the Report (a summary will only provide statistics).
4. Click

Your CSV file will be downloaded and ready for import into your mailshot program.

A user may encounter a page with this error message if they are attempting to export or download a report that contains unsaved edits. The "414 Request-URI Too Large" error message explains that the reporting engine cannot process the requested export because it contains too many unsaved filter conditions and/or output column settings.

To avoid this error, please click the ‘Save Changes’ button first (it should be highlighted green) and then try to export your report.

If this error message persists even after saving the report template then please submit a support ticket with these details:

• The URL for the report template, which should look like this: “...administrateapp.com/App#/reports/1"

• A screenshot of the error message including the address bar of the web page