You are here:
< Back

The Report Builder allows you to create rules to pull data from your system. The purpose of this tutorial is to walk you through the basics of building a report.

When the report builder is first opened, two sections are visible: “Filters” and “Display Fields”. The “Filters” section is where search criteria is specified. The “Display Fields” section is where you specify what fields you want to see from the data.

You’ll notice that to the right of each section’s title and caption is a help button. Press it at any time to get a brief explanation of what that section does.

The first filter you see should look like this. The first drop down box is a list of field categories. The second is the actual field list. The third is the conditional operator (equals, greater than, less than). The text box at the end is the search term. For example, if I wanted to search for debtors whose last names start with a “T”, I would look in the “Debtor” category for the field “Last Name”, set the operator to “Starts With”, and enter “T” as the search term.

 

Build a report

 Let’s build a report. First, we’re going to pull the account numbers for all accounts that have been in the system more than five days.

  1. In the “Filters” section, set the category to “Account”.
  2. Set the field to “Age (days)”
  3. Set the operator to “Greater Than”.
  4. Set the search term to “5”.
  5. In the “Display Fields” section, press “Add Item”.
  6. Set the category to “Account”.
  7. Set the field to “Account Number”.
  8. Press “Preview”.

Now you should see a list of account numbers for accounts that have been in the system for more than five days.

 

Conjunctions

When you have more than one item in the “Filters” section, you need to decide which “conjunction” to use: whether you want them all to apply (and), or at least one to apply (or). Let’s add another item. In this example, we’re going to require that at least one of the items (or) apply for a record to show.

  1. In the “Filters” section, press “Add Item”.
  2. Set the category to “Financials”.
  3. Set the field to “Current Balance”.
  4. Set the operator to “Equals”.
  5. Set the search term to “0”.
  6. Press the blue “AND” button at the top-left of the same section to change the conjunction to “OR”.
  7. Press “Preview”.

Now, account numbers for all accounts that either have been in the system more than five days or have a $0.00 balance are displayed.

 

Filter Groups

Because the “AND/OR” button only allows one conjunction to be applied to all items, you will need to use filter groups to get a combination of “and”s and “or”s. A filter group may contain multiple items with a different conjunction, but is treated as one item in the list.  Let’s add a filter group. This time, we will require that both items (and) in the group be met in order for the record to show, while the outer conjunction remains as “OR”.

  1. In the “Filters” section, press “Add Group”.
  2. In the new group, leave the conjunction button set to “AND”.
  3. Set the category to “Debtor”.
  4. Set the field to “Type”.
  5. Set the operator to “Is”.
  6. Set the search term to “Primary”.
  7. Press “Add Item” in the filter group.
  8. Set the category to “Debtor”.
  9. Set the field to “Last Name”.
  10. Set the operator to “Starts With”.
  11. Set the search term to “S”.
  12. Press “Preview”.

 

Now, the results should show account numbers for all accounts that meet one of three conditions: 1) have been in the system more than five days, 2) have a $0.00 balance, or 3) have a primary debtor with a last name that starts with the letter “S”.

Sorting

Let’s sort the results.

  1. In the “Display Fields” section, press “Sort Results”.
  2. In the “Sort” section, set the category to “Account”.
  3. Set the field to “Account Number”.
  4. Press “Preview”.

Now the results should be sorted by account number.

Footers

Let’s add a footer to the results.

  1. In the “Sort” section, press “Add Footer”.
  2. In the “Footer” section, set the category to “Misc.”
  3. Set the field to “Current Date”.
  4. Press “Preview”.
  5. Scroll down to the bottom of the page to find the footer results.

Below the main results are the footer results, where the current date should be displayed.

 

Aggregate Functions

Footers are also useful for performing statistical functions on the data. For example, I could calculate the sum, maximum, or minimum of all account ages in the results. This is done by setting an aggregate function on a field in the “Footer” section. Let’s add a field to the “Footer” section that calculates the sum of all account ages in the results.

  1. In the “Display Fields” section, press “Add Item”.
  2. Set the category to “Account”.
  3. Set the field to “Age (Days)” (ignore the box that says “No aggregate” in this example).
  4. In the “Footer” section, press “Add Item”.
  5. Set the category to “Output”.
  6. Set the field to “Account Age (Days)”
  7. Set the aggregate (the third dropdown box in the same item) to “Sum”.
  8. Press “Preview”.

You should now see the age of each account in the main results and the sum of the ages in the footer.

 

Grouping Results

If you want to condense your results to, say, one record per client, then you will need to “group” your results (not to be confused with filter groups) by client. This is useful for performing aggregate functions on all the results that were condensed into each record. Let’s try grouping the results by client to get the sum of all account ages per client.

  1. In the “Filters” section, press “Group By”.
  2. In the “Group By” section, set the category to “Account”.
  3. Set the field to “Client”.
  4. In the “Display Fields” section, press “Add Item”.
  5. Set the category to “Account”.
  6. Set the field to “Client Short Name”.
  7. On the “Account Age (Days)” item in the same section, change the aggregate to “Sum”.
  8. Delete the “Account Number” field in the “Display Fields” section by pressing the red “X” button on the far right (now we’re looking at clients instead of accounts)
  9. Press “Preview”.

 

Now the results should be condensed to one row per client, and the “Account Age (Days)” column should display a sum of all account ages in each client.

 

Group Filters

You can also add a layer of filters that apply after the results are grouped. This is especially useful if you want to filter based on an aggregated result from a group, like the summed account age we added in the previous example. Let’s add a group filter.

  1. In the “Group By” section, press “Add Group Filter”.
  2. In the “Group Filters” section, set the category to “Account”.
  3. Set the field to “Age (Days)”.
  4. Set the aggregate to “Sum”.
  5. Set the operator to “Less Than”.
  6. Set the search term to “1000”.
  7. Press “Preview”.

Now you should only see clients with a summed account age of less than one-thousand. Note, the filter groups that we used in the “Filters” section may also be used in the “Group Filters” section.

 

Rearranging Display Fields

Let’s rearrange the display fields. There is a dotted handle on the left side of each item in the “Display Fields” section that is used for rearranging.

  1. In the “Display Fields” section, press and hold the handle on the “Client Short Name” field.
  2. Drag upward until it swaps places with the “Age (Days)” field.
  3. Press Preview.

Now the order of the columns in the results should be “Account Number”, “Account Age (Days)”, and finally “Account Client Short Name”. Fields in the “Sort” and “Footer” sections may also be rearranged this way.

 

Exporting

You may have noticed that the results preview shows only the first thousand records. If you want all of the results, you can export the results as a spreadsheet file (.csv).

  1. Press “Export”.
  2. Use the “Save As” dialog to save the file to the directory of your choice.

Now the results should be saved to your hard drive as a spreadsheet file. There are more options to the right of the “Export” button for advanced users to customize the export. However, changing these settings is not recommended for most users.

 

Saving a Configuration

Let’s save the configuration so that we don’t lose it.

  1. In the textbox on the top-right of the Report Builder, type a name of your choice.
  2. Press “Add”.

You should see a popup dialog that says, “Configuration added successfully.” You should also see the configuration’s name in the dropdown box to the left.

The three buttons in the middle are used to manage existing saved configurations. The “Load” button loads the configuration that is selected in the dropdown box. “Update” saves to the configuration selected in the dropdown box. “Delete” removes the selected configuration from the dropdown box.

 

Conclusion

Hopefully, this tutorial helped you to get started with using the Report Builder. If you have questions, please contact support.

 

See anything missing or out of date on this page? Please contact Simplicity Support at helpdesk@simplicitycollect.com.