...
Step 1: "Dataset Information"
- Set the name of the reportdataset
- This name must be unique to the other datasets
- Name the dataset something descriptive so you have an idea of what kind of information is included just by looking at the name
- Add a description of the dataset
- This description is required
- The description allows you to give more detail about the dataset than you are able to give in the shorter title
- The "Created By" field displays your username to show who is the creator of the dataset
- The "Distinct" check box will remove duplicate records from the dataset if checked
- 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
- Example: a listing of years where applications were filled out
- 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
- The Share level option will allow you to choose who is able to view this dataset
- Private: Only the creator
- Group: Shows a multi-select check box to allow you to choose which employee groups are able to view the dataset
- Other Users: Everyone with a valid account
- Public: Everyone with access
- The "Overwrite" option will allow you to overwrite an existing dataset with the version that you are going to create
- 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
...
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. |  |
| ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 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. |
|
| ||||||||||
| 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. |  |
| ||||||||||
| All records | If "All records" is chosen, the dataset will display all applications and all parcels |  |
|
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.
...
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".
| appid | Status Name | Yes or No |
|---|---|---|
| 123 | Approved | Yes |
| 321 | Canceled | No |
| 456 | Pending Review | No |
| 789 | Approved | Yes |
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.
...
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".
| appid | Datetime Completed | Date On/Before/After |
|---|---|---|
| 123 | 2018-01-01 09:38:00 | Before |
| 321 | 2018-04-20 15:01:00 | On |
| 456 | 2018-09-30 07:19:00 | After |
| 789 | N/A |
Step 7: "Formatting"
| Operation | Use | Requirements | Operation Format |
|---|---|---|---|
| Coalesce | Returns the value of the first field in the listing that isn't null/empty | Two or more fields |
|
| Concatenate | Returns two or more fields formatted together as one field | One or more fields |
|
| Currency | Returns a number formatted as a dollar value | A field containing a numeric value |
|
| Date Format | Returns the date provided in a format specified in the formula | A field containing date data |
|
| Date Only | Returns only the date portion of a datetime field | A field containing a date |
|
| Date Part | Returns a portion of the provided date as specified in the formula | A field containing a date |
|
| Epoch to Datetime | Returns a datetime value converted from an epoch value(the number of seconds since January 1, 1970 not counting leap seconds) | A field containing a numeric value (Must be an epoch value to function correctly) |
|
| Join | Returns a listing of values associated with a single value (Explained below) | A field and a separator value (space, comma, semi-colon, etc.) |
|
| Leftmost | Returns a number of the leftmost digits of a field as specified by the second value of the formula | Any field |
|
| Number | Returns a number field converted from a text datatype | A text field containing a number |
|
| Rightmost | Returns a number of the rightmost digits of a field as specified by the second value of the formula | Any field |
|
| Text to Date | Returns a date field converted from a text datatype | A text field containing a date |
|
| Text to Datetime | Returns a datetime field converted from a text datatype | A text field containing a date |
|
...
| Info | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||
The Join operation is different that the original join step in the dataset creation and is a tricky one to fully master. It requires a decent understanding of the data that you are trying to manipulate. The example below will try to explain the intricacies that come with using the Join operation using a real-life situation within OneGov. Example: Multiple Parcels per Application One often requested report is one that will show certain important pieces of information associated to an application. This may include the Applicant's name, the location of the work being done (parcels), and certain custom fields in the application. In some applications, the application is able to select multiple parcels on a single application. If a dataset was created to show the appid and the parcel number for that application by choosing the appid and parcel columns on the field step, there would be multiple rows that have the same appid but different parcels. This could possibly throw off the report if you were counting the number of rows to see how many applications there are in the report.
This problem can be solved by using the join function. Instead of choosing both the appid and parcel number on the field step, only the appid would be chosen. On the "Formatting" step, a join operation would be added by naming the field appropriately (in this case "Parcel Number") and then choosing the parcel number column and adding that to the first box in the formula and inserting a comma with a space after (so the parcels are separated a bit) in the second box using the manual input. This operation is then added to the dataset by clicking the green plus on the left side of the field builder.
By setting up the dataset in this manner, the results now show that there were only two applications and not three.
|
Step 8: "Filter"
This step allows you to filter out more information from the dataset. You can create a filter by choosing a field from the tables that you included in the dataset in step 2. Once you click on one of the field names, it will appear in the space below the listing of tables. A drop down next to the field name will allow you to choose from several different ways to filter the dataset based on the data type of the field you chose. Once you choose how to filter the field, you can type a value into the text box to filter on that value. Once the filter is built, you can click the green plus in the top left of the filter box to add the filter to the dataset. If you accidentally click the wrong field to filter on, you can click the broom icon in the top left to clear the selection. The filter step allows you to include multiple fields in a single filter as well.
...