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.

Open

Two Tables Joined

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.

Open

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.	Open	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.	Open	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.	Open	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	Open	appid Parcel number 123 321 123456789 456 567856765 098897678