Dynamic Reporting in OneGov is a very powerful tool that is primarily used to create custom data sets and reports from data within the site. It is helpful to have an understanding of dataset creation when creating datasets. The purpose of this guide is to give an overview of the core concepts and steps involved in dataset creation so that you can more easily create reports without requiring the aid of an RTVision employee.
Concepts that will be covered in this guide:
- Parts of a database
- Primary and foreign keys
- Data Types
- Difference between a Report and a Dataset
- Dataset Creation
Parts of a Database
A database is a collection of tables that are related to each other through the use of certain fields called Primary Keys and Foreign Keys.
Primary and Foreign Keys
In nearly all cases, a table will contain a primary key as a way of keeping all of the records unique. Without a way to keep records unique, the data inside quickly becomes useless. A Primary Key is usually an incremented number and is unique for every record in that table. This allows tables to use that Primary Key value to reference records in another table. This is useful because it is more efficient to break data up unto specialized tables with Primary and Foreign Keys keeping that data related.
Primary and Foreign Key Example
The Primary Key from the permitapps table is used as a Foreign Key in the contacts table. This means that there can only one application with an appid of 12 but there may be multiple contacts on that application. By writing a request to the database to see all of the records where the appid is 12 we would be able to see that there is one application and potentially several contacts.
Difference Between a Dataset and a Report
There are two parts to the Dynamic Reporting section of OneGov: Reports and Datasets. The Reports page shows all reports that you have access rights to view.
This section of the guide will cover the actual creation of a dataset. Due to the amount of customization available on OneGov, table names and fields are often times different however, there are a number of consistencies which you can view in the Consistent OneGov Tables guide.