Building a Tabular Data Report

This guide will cover how to build out a report in a tabular layout. If you haven't first created a dataset that will provide the information shown in your report, first follow our guide on building a dataset.

Start by adding a new Report

Click "Dynamic Reporting" under the Reports tab
At the top of the listing of available reports, click the table icon that shows a green plus sign

Step 1: "Report Information"

Report Name: Set the name of the report
1. This name must be unique to the other reports
2. Name the report something descriptive so you have an idea of what kind of information is included just by looking at the name
Description: Add a description of the report
1. This description is required
2. The description allows you to give more detail about the report than you are able to give in the shorter title
Report Type: Choose the report type (This guide covers the tabular data option)
1. Tabular data: a table style report with columns and rows
2. Calendar data: a calendar style report for viewing dates in a report
Data Source: Choose the data source you want to use in the report
Created By: The "Created By" field displays your username to show who is the creator of the report
Distinct: The "Distinct" check box will remove duplicate records from the report if checked
1. This option is useful if you are trying to build a report that has duplicate records and you need only one record for each value
  1. Example: a listing of years where applications were filled out
Share Level: The Share level option will allow you to choose who is able to view this report
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 report
3. Other Users: Everyone with a valid account
4. Public: Everyone with access

Step 2: "Columns"

On the Columns step, you can choose which fields from the dataset you want to include in the report and what order you want those columns to display in. By default, all columns are selected to be included in the report. To remove a column from the report, remove the check mark to the left of the column name by clicking on it. To re-arrange the order of the columns in the report, click and hold the dots to the left of the check box and drag it to the position you would like it to be in.

Step 3: "Sorting"

On the Sorting step, you can choose how you would like the report to be sorted. To select a column to sort the report on, click the check box to the left of the column name. Once the check box is selected, two options will appear to the right of the column name: Sort Order and Ascending/Descending. The sort order comes into play when you want to sort on multiple fields and need to specify the order in which those sorts come into play (Ex, Sorting by "Year" first, then "Month", and finally "Day"). The Ascending/Descending option allows you to choose if you want to sort from A-Z and 1-9 (Ascending), or Z-A and 9-1 (Descending).

Step 4: "Links"

On the Links step, you can add various links to the report. There are three sections on the links step: "Columns", "Manual Entry", and the link builder. The Columns section contains the fields within the dataset that are available to use in the report. The Manual Entry section is used to add custom text to the link builder. Within the link building section, there is a spot to determine the text of the link (marked as "Display") and a spot to build the link itself (marked as "URL").

Practical Example

In this example, I will be adding links to specific applications in my report. The imaginary dataset that I am using in this example has 4 fields (appid, Permit Type, Permit Status, and Date Completed) and looks like this:

appid	Permit Type	Permit Status	Date Completed
123	Land Use	Approved	01/05/18
124	Building Permit	Incomplete
125	Land Use	Pending Review	02/27/18
126	Oversize Move	Incomplete
127	Variance Application	Canceled	04/13/18

Note: the appid is used as a way to keep the records in the OneGov database unique and is also used for links. While the appid is not necessary to understand the information contained within the application it is still a very useful and important piece of data.

I don't want to include the appid in the final report because it is not relevant to my dataset but I do want to use it to create a link to the application. On the link step I'll start by choosing what to name the link. This could either be a static phrase like "View Application" or "Click Here to View" or it could be dynamic based on the record like "View App #{appid}" where "{appid}" is the appid for that specific record. To add the link text I have to click the three dots to the right of the "Display" label. When I do this, a gray box will appear in the spot of the three dots and the dots will move to the right of the box. The gray box signifies that if I add some sort of input, it will appear in the spot of the gray box. For this example, I want the link to display "View App #{appid}" so I need to add the text in two steps: Once to add the texts that stays the same for every record and one to add the appid. To start I will type "View App #" in the Manual Entry text box and click the green plus to the left of that text box. Once the plus is clicked, the text will move from the text box to the gray box in the link builder. Now I need to add the appid and since I want the report to use the appid for each record, I need to add the appid from the Columns section. To do this I need to click the three dots to the right of the newly added text to add a new gray box to the end of that text. Now I can click the appid column from the listing above and the link builder will add the new input to the end of the text I added. Now that the display portion of the link is created, I need to create the URL.

When creating links, the tool provides the base of the URL (https://[countyname].rtvision.com) and the report creator needs to provide the rest of the URL using the Manual Entry tool and the columns. It is important to remember that if you want to include a link to and application or an invoice, there are fields in that database that need to be included in the dataset (appid for links to an application, appid and invoiceid for links to an invoice). You also need to know the pattern of the URL to build out a link. These patterns can be found by navigating to the page and looking at how the URL is built out. For the link to an application, that URL looks like this: https://[countyname]rtvision.com/view.php?id=12345. We already know that the "https://[countyname]rtvision.com/" portion is already provided by the link builder and we can see that the number is the appid so that leaves the "view.php?id=" part of the link to add in through the Manual Entry.

Now that I know the layout of the link, I will click on the three dots within the URL portion of the link builder and enter "view.php?id=" using the Manual Entry. Then, I will add a new input by clicking the three dots again and click on appid in the Columns list. At this point in the process, the link builder should look like this:

At this point, my link is now built and I can click the green plus sign on the left side of the link builder to add the completed link to the report. When I view the created report, it will look like this:

	Permit Type	Permit Status	Date Completed
View App #123	Land Use	Approved	01/05/18
View App #124	Building Permit	Incomplete
View App #125	Land Use	Pending Review	02/27/18
View App #126	Oversize Move	Incomplete
View App #127	Variance Application	Canceled	04/13/18

Step 5: "Totals"

The Totals step allows you to add a running total of certain columns at the bottom report. If a column contains numeric data (whole numbers, decimals, etc.) it will be available to select on this step. To include a total of a column, click the check box to the left of the column.

Step 6: "Filters"

The Filters step allows you to filter out records from the dataset. This is helpful for when the dataset that is being used in the report contains very general data and the report needs to be more specific. You can create a filter on one of the columns by choosing a column from the listing of columns available in the dataset. Once you click on one of the column names, a filter builder will appear in the space below the listing of columns. A drop down next to the field name will allow you to choose from several different ways to filter the report 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 report. 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.

Step 7: "Queries"

The queries step allows you to add ways for the report to be filtered without having to edit the report. A query is set up by clicking the check box next to the column name and then choosing a query type from the drop down that appears. There are four types of queries available: Advanced Search, Generic Search, Select from Listing, and Select Multiple from Listing.You can select multiple columns to be able to be queried in the report.

Advanced Search: This option allows the user to make a more dynamic query on the report. The query options on the Advanced Search depends on the data type of the column. For example, if the column contains dates, the query will provide options to search for records on a specific date, between two dates, and before or after a specific date.

Generic Search: This option allows the user to query the report based on a value entered into a search box.

Select from Listing: This option provides the user with a drop down list of all of the unique values in that column. This option is not recommended for columns with numerous unique values such as dates or text box values.

Select Multiple from Listing: This option provides the user with a multiple selection list of all of the unique values in that column. This option is not recommended for columns with numerous unique values such as dates or text box values.

If a report has queries set up, the person running that report can use the queries by clicking on the icon with a magnifying glass on it on the reports page. If a report doesn't have queries set up, that icon will not be available on that report.

Step 8: "Preview"

This step allows you to save and exit the report builder. Once the report creation has been finished, it will show in the list of reports.