Understanding Tables, Views, and Table Value Functions

This section is for advanced Query Builder users.

A relational database is made up of many tables, with each table containing information (for example: associations, pledges, students, classes). 360 is made up of numerous tables which link to one another to store all the data entered.

To make is easier to query and access data, 360 contains Smart Tables which combine information from many tables (called a View).

Further, 360 also utilizes a Table Valued Function (TVF). A TVF is similar to a view in that it often joins many tables together. However, a TVF includes Parameters which allows the users to filter the data even before it is returned. This allows the user to control what data is included on the report even before the report runs. There are two main benefits to using a TVF:

1. Instead of bringing in all the data and then filtering through criteria, the data is filtered up front, making the report run significantly faster.

2. Certain calculations can be done on the raw data and the result can include summarized data based on the parameters that are passed in.

Due to these advantages, many 360 reports use TVFs.

As example of a TVF that appears on many reports in the Address TVF. The Address TVF has 3 Parameters: Show address, number of addresses to show and address types. By putting in these parameters, the user filters the data upfront instead of bringing in all addresses and only filtering it later. This also allows the user greater control over what appears on the report.

You will notice that TVF parameter prompts are NOT on the edit criteria screen. This is because a TVF parameter is not like criteria. Whereas a criterion filters the data that is returned, a TVF parameter filters the data before it is returned.

TVF prompts can be seen when you preview the report. On the second page, there is tab called Report Options which includes all TVF prompts.

In this documentation, a ‘table', refers to a simple table, a smart table and a TVF.