Building a Report

1. To begin building your custom report, the first step will be to create a brand new report within the Admin Reporting tool.

To get started, log in to your Admin Reporting tool, and locate the outermost “Reports” folder. It is denoted by the black computer screen icon, and it holds all existing custom reports. Right-click on this “Reports” folder, and choose “Create Report” from the options that appear.

When you choose “Create Report”, a window will appear that requires you to enter a name for your new report. Choose a name and select “Save & Close”.

Your new report will appear at the bottom of the list of existing reports. Navigate to the report and select it to drop down its components. When a new report has been created, it does not contain any datasets, columns, or views to choose from – You must add these components.

(Note: There will be a red exclamation icon next to your report or an element of your report whenever something is not computing correctly; it will always show on a report that does not yet have tables or columns to generate data from, as seen in this example.)

2. To start adding data and information that you can manipulate and utilize in your report, right-click on your report’s name, and choose “Add Data Set” from the options that appear.

In the “Add/Edit Dataset” window that appears, choose a name for your dataset - It will be helpful to give it a name that relates to the data type you plan to extract. This name is for your organizational purposes only, as a user will not see this name on the front end of the report.

Additionally, this first dataset will be sort of like a  “lens” your report will view all other information through; therefore, this will be our Base dataset. Check the “This is the base dataset” option, and “Save & Close”.

Base Datasets:

As described above, base datasets are the primary datasets that we extract our information from. We reference these base datasets constantly as we choose and join tables to extract the information that we require in our custom reports. However, there is only one base dataset; we may have other datasets to help us in our information gathering, but all other datasets will join their tables to those of the base dataset.

Aggregate Datasets:

Another type of dataset that you may need for your custom reports is the aggregate dataset. An aggregate dataset is a dataset where you can add and join new tables, and apply conditions separately from the base dataset. This is helpful in a case where you need certain calculations performed or information extracted that could otherwise affect the base dataset’s report. You can then join the aggregate dataset to the base dataset by any of the “Join” methods that will be discussed in the next section.

If you already have a base dataset and would like to create an additional aggregate dataset, right-click on the report name, choose “Add Data Set”, name your dataset, and check the “This is an aggregate dataset” option. “Save & Close”.

3. Once you have created your base dataset, you must select the model that will make up that dataset. A model, or “table”, can be thought of as a storage container for data. There are several different tables to choose from, and each table stores its unique list of data points. 

Simple/Smart Columns:

Once you have chosen a base table to extract information from, you can choose any of the data points that are contained in this table to use as building blocks for your report. These data points are displayed as Columns, and each report begins with an empty “Columns” list that will store the specific columns you want to use.

To choose a column to be used in the report, right-click on the table that the column resides in, and choose “Add Column”. This will bring up the “Add / Edit Column” window. Here, you can view all of the available columns of the table; choose one, and “Save & Close”.

You can see that the Column Type we have used is the Simple Column. (As discussed in a previous section, Simple Columns are denoted by three green dots in the Columns list of the report). A Simple Column is selected directly from the table. You can perform some functions with this type of column in the Expression area of the “Add / Edit Column” window.

We also have the option of creating a Smart Column by right-clicking on any table in your report, and choosing “Add Smart Column”. Smart columns are not columns directly chosen from a specific table - Instead, you can choose multiple columns from the tables that you have specified for your report. This is helpful when performing more complex calculations because you can pull in multiple columns to perform functions on. (Smart columns are denoted in the Columns list of the report by six green dots)

Joining Tables:

Oftentimes, you will need more than one table to achieve a report that meets your needs. In such a case, “join” a new table to your base table to acquire access to more information that you can utilize.

For example, if we choose the “Person” table as our base table, that “Person” table provides columns to choose from that include a CRM record’s “First Name” and “Last Name”. But, that table does not include the person’s “Default Address”; so, in order to access related data like this, we will need to join another table to our base table.

Right-click on the table to which you want to add another table, and choose “Add Model”. The menu that appears will show all of the tables that can be connected to the main model (In this case, we see that “Person Address (Default Address)” is available to be joined). Next to each table’s name on the dropdown list, the type of relationship that the table has with the main table is shown in bold and parentheses (i.e. a many-to-one relationship, or a one-to-one relationship).

Choose one of these tables, and “Save & Close”. Now you can see the “Person Address (Default Address)” table nested below the Person table it is joined to.

Joining Datasets:

If you have an aggregate dataset that you need to join to your base dataset, there are a couple of extra steps to take. Right-click on the base dataset and choose “Add Data Set Join”. A new window will pop up that will give you a list of your existing datasets. From this list, choose the dataset that you want to join to the base dataset.

To join datasets together, each dataset must have a common column that they can share. On the left side of the “Add / Edit Dataset Join” window, you will see the “Master Data Set Columns''. From this menu, choose the column from the base dataset that you want to use for joining the new dataset. Double-click this column to select it and to make it appear in the “ON” text area, type “=”, and then choose the same column from the right side: “Join Data Set Columns”. The columns in this right menu represent the columns in the new aggregate dataset.

With this expression that you have made (“Person Id = Person Id”), you have now set the common column that your datasets can be joined with.

You can see that the “Join Type” chosen here is the ”Left” join type. While this might be the most common type of Join you will use, there are a few that you can choose from:

• Left Join Type (most common)

This Join Type causes the report to display all records from the main table that is being joined to (the “left” table), as well as records from the joining table (the “right” table). The records from the joining table must have matching values to the records of the main table on their common column, for that record to be included in the report.

• Inner Join Type

The Inner Join Type causes the report to display only values that are common to both the main table (the “left” table) and the joining table (the “right” table). The common column that is used for the join determines the values that must match on both tables.

• Union (advanced function)

Based on the common column between the two tables that are being joined, the Union statement will cause the report to show only records that have unique values from either table (i.e. records from each table that happen to have the same value will not both be displayed - Only one of those records will be used on the report).

• Union All (advanced function)

The Union All statement will pull in all of the records from the two joining tables based on their common column, including records that have duplicate values for this common column.

Once you have joined your datasets, you will have access to the Simple and Smart columns that you created on the aggregate dataset.

4. The previous steps are used for building your master report, which is shown on the right-hand side of the Reporting tool. This report consists of the columns that you have chosen from your dataset tables. This main report is a large conglomerate of this data; now, in order to make this data more easily digestible, and to be able to share it in Site Planner, the next step is to create a View for your report.

Views:

As indicated by its name, a View is another “lens” which you can view your data through. Each view that you create has its own set of modifiers and conditions that you can apply to your data. For each view, you will also pick and choose the View Columns that you need, from your main list of report columns.

The individual nature of the views makes it easy for you to generate multiple “reports” from one main master report, and to manipulate the data in varying ways in each view. It is a report’s views, also, that are available to the Site Planner’s Report module; using this module, these views are able to be published to your Dashboard pages, for easy access and use.

As discussed in previous sections, the list of views for your report is denoted by the blue eye icon, and each individual view is denoted by the white eye icon.

To add a new view, right-click on Views, and choose “Add View”. Choose a name for your view - this will be displayed on the front end of this view on any Dashboard pages that you publish this view on. Once you have chosen a name, “Save & Close”.

When you open up the view you have created, there will be an empty view columns list. You can right-click on this list and add a Simple column or create a new Smart column for the view. Either type of column that you choose to include in the view will pull from the columns that you chose for your main master report.

Previewing Reports:

To preview your reports, you will generate the master report within the Reporting tool. You can do this by clicking the gray arrow icon that is to the right of the report’s name. You can also click the “Regenerate Report” text that is in the top-right of the report display window.

(Note: You may need to regenerate the master report periodically while you are building, to refresh your master report’s results.)

To preview your views, you can click on the white eye icon that is to the right of the view’s name. This will open the view in a new tab, where you can see it live. While you are working on your report, you can refresh the tab that is previewing the view to update it.

5. There are a few more ways we can customize the results of our reports and views, which we will briefly discuss here. These modifiers give you even more flexibility in how you manipulate the report data to fit your needs.

Modifiers:

As mentioned in previous sections, each dataset and each view that is added to your table is given its own list of modifiers that can place specific parameters on the data of your report. These modifiers can be set on your master report and/or on each individual view, for different report results. These modifiers include the following:

Conditions

Conditions are modifiers that are helpful to restrict the type of information that your report returns. You can choose to add Conditions, Smart Conditions, and Logical Operators.

You may choose to limit your report to show only results that meet a specific criteria. One way to achieve this is to add a simple condition by right-clicking Conditions and choosing “Add Condition”. A window will appear that will allow you to choose the model (table) that you want to reference, select the column from that table that you want to place the condition on, and choose the type of condition that you want to place on the column.

In the example shown here, this condition will be placed on the “Lastname” column from the Person table. This condition will limit the results of the report to show only those records whose last name is equal to “Smith”.

You may also choose to add a Smart Condition to your report, by right-clicking Conditions and choosing “Add Smart Condition”. Smart Conditions, much like the Smart Columns that were discussed in earlier sections, allow you to choose multiple columns from the tables of your report and perform more complex functions on them.

By right-clicking on Conditions, you are also given the option to “Add Logical Operator”. Selecting this option gives you the choice of adding an “AND” statement or an “OR” statement to act between your other conditional statements.

 EG: c_462  >='2019-07-01' and c_470 = 'true'

Group By

The Group By statement will group all of the entries of a report by a specified column. This is helpful when, for example, your report generates multiple entries of the same person, just because that person’s record might have multiple values for specific columns. (These values cannot be listed in a single entry on their own, and so the report generates an entry for each value.) By grouping these entries by a specific column, all of the entries will be condensed into a single line.

Note that it is important to use aggregate functions when you are using a Group By statement, as compressing these entries into a single line will cause the report to randomly choose a single value for the columns that previously had multiple values. The required aggregate functions are discussed in the next section.

In this example, we have chosen to Group By the “Person Id” column:

Having

Having is a specific method of filtering that is only useful when you use it with the `Group By` clause.

Order By

You can choose to order by a specific column, or by multiple columns, by using an Order By statement. Double-click on Order By and choose your desired column from the list to order your report. By selecting this column, the report will default to sorting in ascending order, but you can clearly define the sort direction by using the “ASC” statement for ascending, and the “DESC” statement for descending. Declare this statement directly after the column that you select.

If you would like to sort by multiple columns, separate your statements by commas. The report will be sorted by the first column first, etc.

Limit

You may narrow down the number of your report’s results by setting a Limit. Double-click on Limit and choose the number of entries that you want to be generated on the report. For example, setting the Limit to “10” will limit the number of entries on the report to the first 10 that are generated: