Creating a Custom Report

Custom reports are useful when you want to display specific columns of data in a report which are not covered by the Basic Reports. You can pick from any of the available fields on your form and arrange them in any order.

Custom Report Setup

Click 'REPORTS' from the top of the left menu and then click on 'Report Manager'.


 From this menu (shown below), you can:
  1. Create a new custom report
  2. Edit an existing custom report
  3. Delete reports
  4. Copy reports in the same event or different event in the same account.


Create a New Custom Report

This procedure describes setting up a report based on data for the main registrant.  For setting up a report that combines data from a main registrant along with those of additional guests, see the Advanced Report Setup section below. 
 
The steps for creating reports are:
  • Name the new report.
  • Add the columns to the report (eg: First Name, Last Name, etc.).
  • Add optional sort columns to the report.
  • Apply optional filters to the report.
  • View your report.
Naming the report
The report details screen lets you adjust the name and description of the report, add/remove columns and change the order of columns.
  1. Create a new report by clicking 'Custom Report Setup', followed by 'Create a New Report' or click Edit on an existing report (and skip to step 5).
  2. Give it a 'Report Name', which will be displayed under in the Report Menu.
  3. Give it a 'Description', which will show at the top of the report when viewed.
  4. If you want your report to also display registrants who have been 'deleted', then select 'Include Deleted Registrations'.
  5. To add new 'columns' to your report, click 'Add New Column' to go to the 'Report Column' screen.
Adding Columns
All of the attributes for a specific column are managed on the Report Column screen. The column name and 'source' for the column are listed on the left.  Optional attributes for the column are shown on the right.
  1. Enter the caption you want for your column, eg: 'First Name'
  2. Choose the 'source' field for the column by picking the field from the 'Primary Fields' drop-down list, eg: Pick 'First Name'.
    1. Note: There are several 'system' fields that appear at the top of the Primary Fields drop-down list.  For a description of those, see 'System Fields' below.
  3. Set any option attributes for the column under the 'Column Options' on the right.  See Optional Column Attributes below for a detailed description of each.
  4. Click 'Save' to save the column and return to the Report Details screen
    or... click 'Save & New' to save the column while staying on the Report Column screen to prepare to add another column.
  5. Repeat steps 1,2,3 and 4 for each column for your report.
 
Add Sorting
Multiple levels of sorting can optionally be added to a report.  For example, the report could first be sorted by Country, and then sorted by Company name within each country. 
 
To add sorting, follow these steps:
  1. Under the 'Sorting' section, click the drop-down to pick the column for your first level of sorting.
  2. Choose 'Ascending' of 'Descending' order.
  3. Click the 'Add Sort By' button.
  4. Repeat steps 1,2,3 for each level of sortting.
To change the order of sorting, click the 'Up' and 'Down' links.
 
To remove a sort level, click the 'Remove' link at the far right of the sort column name.
 
Add Filtering 
Multiple levels of filtering can optionally be added to a report.  For example, a filter can be added to only show registrant from a specific countries.  An additional filter could be added also narrow down that to only specific countries and after a specific date. 
 
Filtering concepts.
  • Filter Groups - A filter group contains one or more Filter Detail definitions.  For most reports, one Filter Group is sufficient.  For more complex filters, the AND or OR function can be used to combine a Filter Group with additional Filter Groups.
  • Filter Detail - After a Filter Group is defined, one or more detailed filters are created specifying the columns to be filtered.
  • Join Type - This is used to define the filters.
    • AND - Records will be returned only when ALL of the conditions for each defined filters are met.
      • eg: Country = UK AND Gender = 'male' only returns records for all Males in the UK.
    • OR - Records will be returned when ANY of the conditions for each defined filters are met.
      • Country = UK OR Country = US returns records for both UK and US.
To add filtering, follow these steps:
  1. Add a Filter Group 
    • entering a name for the filter (example: 'Date Filter')
    • Select AND or OR from the Join Type drop-down.  For a simple one-level filter, it does not matter what you chose here.  But if you know you will want a 2nd group of filters, you will want to consider the correct join type.
    • click 'Create Filter Group'
  2. The newly created Filter Group will now be displayed.  Click on the named filter (example: click on 'Date Filter').
  3. Add Filter Details. 
    • From the Report Column drop-down, select the name of the column you want to filter on.
      • only columns defined in the report definition for your report will be displayed in the list
      • Note that you can define a column, but make it not 'Visible' (see optional Column Attributes below)
    • Choose the expression that matches what you want to do.  Selections are:
      • Is Equal To
      • Is Equal or Less Than
      • Is Equal or Greather Than
      • Is Greater Than
      • Is Less Than
      • Contains
      • Beings With
      • Ends With
      • Does Not Contain
      • Is Not Equal To
    • Set the 'Comparison Value'.   
    • Set the 'Join Type'.  Required only if the report requires multiple filters.
    • Click 'Add Filter Expression'
  4. Optionally, repeat Step 3 for each additional Filter Detail within a Filter Group
  5. Optionally, repeat Steps 1-4 for each additional Filter Group.
Filtering Examples:
  1. Simple filter.
    eg: Show me registrants only from the UK.
    • Filter Group setup
      • Name = 'Country'
      • Join Type = AND or OR (does not matter)
      • Filter Detail setup for the 'Country' filter group
        • Report Column: 'Country'
        • Filter Expression: 'Is Equal To'
        • Comparison Value: 'UK'  (do not enter the quotation marks)
        • Join Type = AND or OR (does not matter)
  2. Simple filter
    eg: Show me registrants who registered on or after February 14, 2011:
    • NOTE: for this to work, you must have a column defined using the Registration Date field.  And the Column Format for the column must be set to 'Date' (see optional column attributes below).
    • Filter Group setup
      • Name = 'Date Filter'
      • Join Type = and or OR (does not matter)
      • Filter Detail setup for the 'Date Filter' filter group
        • Report Column: 'Registration Date'
        • Filter Expression: Is Equal or Greater Than
        • Comparison Value: '2/14/2011' (do not enter the quotation marks)
        • Join Type = AND or OR (does not matter)
  3. Filter with 2 filters
    eg: Show me registrants from the UK who registered on or after February 14, 2011:
    • NOTE: for this to work, you must have a column defined using the Registration Date field.  And the Column Format for the column must be set to 'Date' (see optional column attributes below).
    • Filter Group setup
      • Name = 'UK after Valentines'
      • Join Type = and or OR (does not matter)
      • Filter Detail 1 setup for the 'Date Filter' filter group
        • Report Column: 'Registration Date'
        • Filter Expression: Is Equal or Greater Than
        • Comparison Value: '2/14/2011' (do not enter the quotation marks)
        • Join Type = AND
      • Filter Detail 2 setup for the 'Country' filter group
        • Report Column: 'Country'
        • Filter Expression: 'Is Equal To'
        • Comparison Value: 'UK'  (do not enter the quotation marks)
        • Join Type = AND or OR (does not matter)
  4. Advanced filter - 2 Filter groups, with multiple details.
    eg: Show me registrants from the UK who registered on or after February 14, 2011.  But also show me registrants from the US who registered on after January 1, 2011:
    • Filter Group 1 setup
      • Name = 'UK after Valentines'
      • Join Type = OR
      • Filter Detail 1 setup for the 'Date Filter' filter group
        • Report Column: 'Registration Date'
        • Filter Expression: Is Equal or Greater Than
        • Comparison Value: '2/14/2011' (do not enter the quotation marks)
        • Join Type = AND
      • Filter Detail 2 setup for the 'Country' filter group
        • Report Column: 'Country'
        • Filter Expression: 'Is Equal To'
        • Comparison Value: 'UK'  (do not enter the quotation marks)
        • Join Type = AND or OR (does not matter)
    • Filter Group 2 setup
      • Name = 'US after New Years'
      • Join Type = and or OR (does not matter)
      • Filter Detail 1 setup for the 'Date Filter' filter group
        • Report Column: 'Registration Date'
        • Filter Expression: Is Equal or Greater Than
        • Comparison Value: '1/1/2011' (do not enter the quotation marks)
        • Join Type = AND
      • Filter Detail 2 setup for the 'Country' filter group
        • Report Column: 'Country'
        • Filter Expression: 'Is Equal To'
        • Comparison Value: 'US'  (do not enter the quotation marks)
        • Join Type = AND or OR (does not matter)

View the report

As you build your report, you may click the newly created report listed under the Reports menu on the left.

Advanced Report Setup

System Fields
Several system fields are available at the top of the Primary Fields drop-down list when setting the field source for the column.  These are:
  • Registration Balance - The current balance owed by that registrant.  Negative amount means registant owes money.
  • Registration Date - Date of registration
  • Registration Status - Status of registration: 
    • Green square - Normal live registration
    • Yellow circle - demo registration (not a live registration)
    • Red triangle - deleted registration (only shows when 'Deleted Registrations' are selected on the Report Details setup screen above).
  • Billed Amount - Total amount of all charges for the registrant.
  • Paid Amount - Total amount paid by the registrant to date
  • Registration Note - Registration Notes entered for the reigstrant.
  • Transaction Note - All transaction notes entered against the registrant.
  • Last Changed By - The person who last changed the registration
    • detail
  • Last Changed Date - Date of last change to the registration
  • Login Password - displays the password set for a registrant.  This is used when the login feature is enabled.

Optional Column Attributes
You may set up additional attributes for each column.  The following summarizes those options:
  • Column Format:
    • Text (default) - the best choice for most columns.
    • Integer- If you have a field that collects numbers only (1,2,3, etc) and you want to add those up on your report, you must select 'Integer' before the total will display at the top of the report.
    • Decimal - If you have a field that collects numbers only (1.1,2.1,3.7, etc) and you want to add those up on your report, you must select 'Decimal' before the total will display at the top of the report.
    • Currency - Display as currency.
    • Display as date - allows date fields to sort correctly as date.
  • Hyperlink Type:
    • Registration Details - Creates a Hyperlink on the displayed report, which when clicked takes you to the  Registration Details screen for that registrant.
    • Payment Details - Creates a Hyperlink on the displayed report, which when clicked takes you to the  Payment Summary screen for that registrant.
    • Summary Report - Creates a Hyperlink on the displayed report, which when clicked takes you to the  Summary Report
    • Email - Creates a Hyperlink on the displayed report, which when clicked opens up your default email program to send an email to that person (only to be used on an email field).
  • Visible - indicates whether a field is visible on your report.  Sometimes it is convenient to define a field on your report which can be used for filtering or sorting purposes, but is not required to be displayed on the report.
  • Downloadable - indicates whether a field is should be downloaded when the Download link is clicked.  Sometimes it is convenient to define a field on your report which can be used for filtering or sorting purposes, but is not required to be downloaded.
  • Use Price - When defining fields in the ePly system, a 'price' can be set as well as the 'value'.  The 'value' field is used by default for all data, except when you select 'Use Price'.  In that case the price of the item will be displayed on the report.
  • Width - the width of a column can be set.  The default of -1 choses auto-width.
  • Calculated Equation - see help on setting up a calculated column.
  • Show Total - Adds an optional Total at the top of the report, for that column.  The following options are available:
    • Count - Counts the number of items in that column that display any value at all.  Items showing nothing in that column will not be counted.
    • Sum - Adds up the items in that column.  The values in that column MUST be numbers, and must have the Column Format set to Integer, Decimal, or Currency.
    • Average, Minimum, Maximum - the average, minmum or maximum of all items in that column.  The values in that column MUST be numbers, and must have the Column Format set to Integer, Decimal, or Currency.
  • Total Align - Alignment for the 'Total Caption' (see next option)
  • Total Caption - An optional heading displayed alongside or above the Total at the top of the report.
  • Repeat data for Group Member(s) - This is an advanced option to be used when combining data from additional guests or other Groups.  It is discussed in detail under the Advanced Form Setup option.
    • This option should only be selected for the primary registrant.  When selected it treats any data item entered for the main guest as if it was also entered for all additional guests.
      • For example, if you collect Company, FirstName, LastName for the primary registrant, but only collect FirstName and LastName for the additional guest, your report could not normally show CompanyName for any of the additional guests.  But, clicking this option is effectively saying 'all of the additional guests also have the field name "Company"' (even though the form did not collect that information), and therefore cascades that company name into all sub-guests.

 
Comments