Building a Dataset

This guide will cover how to build out a dataset and the different Functions and Formatting options available during the process.

Start by adding a new Dataset

1. Click "Dynamic Reporting" under the Reports tab
2. Click "Manage Datasets" in the "Manage Datasets" box on the left side of the page
3. At the top of the listing of available datasets, click the table icon that shows a green plus sign

Step 1: "Dataset Information"

1. Set the name of the dataset
   1. This name must be unique to the other datasets
   2. Name the dataset something descriptive so you have an idea of what kind of information is included just by looking at the name
2. Add a description of the dataset
   1. This description is required
   2. The description allows you to give more detail about the dataset than you are able to give in the shorter title
3. The "Created By" field displays your username to show who is the creator of the dataset
4. The "Distinct" check box will remove duplicate records from the dataset if checked
   1. This option is useful if you are trying to build a dataset that has duplicate records and you need only one record for each value
      1. Example: a listing of years where applications were filled out
5. The Share level option will allow you to choose who is able to view this dataset
   1. Private: Only the creator
   2. Group: Shows a multi-select check box to allow you to choose which employee groups are able to view the dataset
   3. Other Users: Everyone with a valid account
   4. Public: Everyone with access
6. The "Overwrite" option will allow you to overwrite an existing dataset with the version that you are going to create
   1. When a dataset is included in a report or another dataset, this option will be unavailable since changing the dataset may break the report or dataset that depends on it

Step 2: "Data Tables"

This step allows you to choose which tables you want to bring into the dataset. This includes the other datasets that show on the "Manage Dataset" page. You can click on the name of the table or dataset to add it to the listing of tables you want to use. Once it is added to the list, the table will appear on the right side of the page and you will have several options available to manage that table:

• Remove: If you added the table by accident, you can remove it by clicking the red "X" to the left of the table name.
• View: To view the contents to the table before you move to the next step, you can click the table icon to the left of the table name. This will load the contents of that table into a new tab in your browser.
• Rename: In the text box to the right of the table name, you can choose a different name for that table to appear by in the dataset. This is helpful if the original name of the table is longer and you want to simplify it. This is also helpful if the table name is vague and you want to give more detail.

Step 3: "Join Tables"

This step is where you link up the tables and specify what fields to join the tables on. By joining the tables together on a common field, the dataset is able to tell which information belongs to which record so that when you choose fields to display, the dataset will create a row for information that belongs together. This is the step where knowing what the Primary and Foreign keys of the table will be necessary in order to build an accurate and functional dataset.

On the Join step, the tables that you selected on the previous step will all be shown in the top part of the page. When joining multiple tables, it is typically a good idea to start with a "base" table. This would be a table that contains the most general information that you want to include in the dataset. For example, if I want to build a dataset that includes information about all the permits in the site, I would include the PermitApps table and use that as my "base" table since that table includes all permits no matter which type or what status it is in. You can always trim down what information is included in a dataset but can sometimes be difficult to add more information in to a more focused dataset.

When choosing tables to join, a visual aid is used to help show what table is currently selected. When hovering over the available tables, they will turn a soft green. When you click on a table to select it for joining, it will initially turn red. When you move the mouse off of the table, it will change from red to a bright green. The red indicates that you cannot join the table to the one you have currently selected (you can't join a table to itself) and the bright green indicates which table you have currently selected. When you hover over another un-joined table, it will turn a soft green to indicate that you can select that table to join. NOTE: It is important to note that just because the dataset creation tool allows you to select two tables to join it doesn't mean that those two tables will have a common field to join on. Once you have two tables joined together a box will appear around those two tables and an icon of a chain will appear between those two tables to indicate that they are linked.

Once the tables have been joined, the fields to join those tables on will need to be selected. This is done using the section that appears below the joined tables.

There are four different options available to choose from in the drop down. For this example, we will assume that I am joining the permitappfieldcustomoptions table (which is used to join the PermitApps and parcel records table) and the parcel records table. Depending on the option that is chosen in the drop down, there will be different information pulled into the dataset

Only matching records	If "Only matching records" is chosen, the dataset will only display applications that have a parcel record associated with it.		appid Parcel number 123 134234564 321 123456789 456 567856765 890 098897678
All from left	If "All from left" is chosen, the dataset will display all applications whether or not they have a parcel number associated with it.		appid Parcel number 123 134234564 321 123456789 456 890
All from right	If "All from right" is chosen, the dataset will display all parcels whether or not they have an application associated with it.		appid Parcel number 123 134234564 321 123456789 567856765 098897678
All records	If "All records" is chosen, the dataset will display all applications and all parcels		appid Parcel number 123 321 123456789 456 567856765 098897678

Once you have chosen one of the options from the drop down, you can choose which fields from each table to make the join on. When you first click the green plus symbol, it will default to joining the tables where a certain value in the table on the left of the join is not blank.

There are a number of different options for joining tables and the most commonly used one is "Value is equal". By choosing this option, you can choose columns in the tables that are a primary and foreign key. For example, in the PermitApps table, there is a column called statusid. This column is also in the appstatus table and follows the id naming convention so it can be inferred that this is a Primary/Foreign key pairing. To set up this join on those two fields, click on one of the red boxes with a question mark inside to specify which side of the join to start on. In this case, I am starting with the left side. 

Because I chose to set the left side first, my choices for which fields I am able to put in this position are restricted to the table on the left.

Once I have both of the fields filled in the join, my join is complete.

Most datasets that are created involve more than two tables. To join a third table to the other two, the process is the same with the only difference being that the already joined tables effectively act as a single table. For example, when you click on the two joined tables to start the join, they both change to the bright green color to indicate that they are currently selected. 

Once you click the third table, the tables will display that they are now linked and another joining section will appear below the first one.

Another difference is now when you choose a field to join on the left side, all of the tables to left of the new table are available to be used for the join. This is helpful because the PermitAppFieldValues table doesn't have a field that we can join on but PermitApps does with the appid field.

You can join as many tables as you need in a dataset but keep in mind that the more fields you include in a single dataset, the longer it will take to load and if it is large enough, it won't load at all.

Step 4: "Fields"

This step is where you start to add fields to the dataset. On the top half of the page, you will see all of the tables (and their respective fields) that you have chosen to include in the dataset on step 2. You can add fields one-by-one by clicking on the name of the field from the list of field names. You can also choose all of the fields from a table by clicking the icon to the left of the table name. The fields added on this step will be displayed exactly as they are in the original table, without formatting or other alterations.

Once the fields are added there are two options available for that field:

• Remove: If you added the field by accident, you can remove it by clicking the red "X" to the left of the field name.
• Rename: In the text box to the right of the field name, you can choose a different name for that table to appear by in the dataset. This is helpful if the original name of the field is long or vague.

Step 5: "Formulas"

This step allows you to add fields to the dataset and format them to display differently than they do in the original table. This step includes mathematical operations, and date formatting.

Step 6: "Conditional Fields"

The Conditional Fields step allows you to build out logic statements to customize the data that displays in the dataset. There are two ways to build a conditional field: Simple and Advanced.

Simple

The Simple conditional field is used to check the value of a single field and output a value that is determined by the contents of the value of that field. For example, I could set up a conditional field to display a field in the dataset that either displays "Yes" or "No" based on whether or not the application is approved. I could set it up so that if the "statusname" field is equal to "Approved", it will output "Yes" for that record and if it is equal to anything else, it will output to "No".

The Simple option is used by selecting the "Simple" option to the right of the "Alias" text box. Once you make the selection, the template for the conditional field will appear.

Advanced

The Advanced option gives you more flexibility when building the conditional field. Instead of only being able to check if a field is equal to a specific value, you are able to make a variety of other comparisons. These include comparisons specific to fields with numeric data and datetime data. For example, if I wanted to create a conditional field that outputs a specific value depending on whether or not a date is before or after a specific date, this is how I could set that field up.

With this setup, any applications that were completed before 2018-04-20 will show "Before", any that were completed after 2018-04-20 will show "After", any that were completed on 2018-04-20 will show "On", and all other applications will return "N/A".

Step 7: "Formatting"