Data Sources
Selecting Data Sources and Building Relationships
When designing a Report from scratch, you'll first need to choose your Data Sources.
The Data Sources are the tables and views within accessplanit's database which hold all of your data.
The first Data Source in a brand new report must:
Always exist for the results that you want.
For example: you won’t be able to use a delegate table such as ap_users_coursescalendar_view for a finance report if you want to show placeholders in your report as well, as a delegate record won’t always exist and therefore there’ll be no data in the table.Be appropriate for what the report is centred around.
For example: if the report is mainly invoice-based, you can use ap_invoices_view or invoices as the first data source, or if the report is delegate-based, you can use ap_users_coursescalendar_view.
Tip!
Most reports work best with the ‘Distinct’ option ticked in the top left corner, and this will reduce the chance of duplicated results.
Simple reports may only require one Data Source such as a list of Users. In this case, select the appropriate view (ap_users_view) and move onto the Fields tab.
When more than one Data Source is needed the Data Sources need to be link together with a relationship.
For a list of common Data Sources and what they contain, please use our Data Source Glossary.
Data Source Joins
Most Reports require more than one Data Source, so it is important to understand how to build relationships between them.
Relationships are built using a shared Field which is present in the tables of both Data Sources.
For example, the ap_users_view and the ap_userscoursescalendar_view above both contain the field userid.
The userid is a unique value for each User, so when you join the two Data Sources tables together using this field we know that the ap_users_view and the ap_userscoursescalendar_view will be referencing the same User for each record in the Report.
Every record on accessplanit has an ID, a unique way of identifying the record that sets it apart from everything else. Using IDs is the best way to join tables, and also the most common, so this should always be your first try.
Here, we're telling the Report to show us User information and then we're linking to the Delegates Data Source, so the report can show us which courses these Users they have been added to.
An incorrect relationship can lead to incorrect results.
For example, if we were to instead join the two Data Sources above on the datecreated field, it is almost certain that our results would be incorrect as the date that the User was created, and the date that they were added to the course will not be a match.
The Report Writer will aim to determine the relationship automatically based on whether both Data Sources contain a field with the same name, however you may need to adjust this, as two Data Sources may contain the same field, but in the context of the table, the field means something different.
For example, you could join ap_coursescalendar_view to ap_users_coursescalendar_view using the companyid(varchar) field, but this would be incorrect because:
- companyid (ap_coursescalendar_view) - This is the Account ID for the Training Provider that the course is under.
- companyid (ap_users_coursescalendar_view) - This is the Account ID that the delegate's booking was made under, most likely the User's Main Account, but this would be empty if the delegate booked the course privately.
We recommend that you start your Report by adding one or two Data Sources then return to this stage once you have started to add your Fields and Filters. This can make the process much more logical and allows you to check that each relationship you add works by using the Preview tab.
Field types
When joining tables, the relationship must be between two fields of the same type.
For example, you can’t join a ‘datetime’ field to an ‘int’ field, because they aren’t the same type and will never have an equal value because the format dd/MM/yyyy can never equal a whole integer.
Field types:
- (int) is an Integer value, meaning positive or negative whole numbers.
- (varchar) is a Variable Length Character String, meaning the field stores alphanumeric combinations and text.
- (nvarchar) is similar to (varchar), but it can store more characters and it can also store non-alphanumeric characters from supported languages, e.g. % or *.
- (bit) is a Boolean value, meaning that is can only be True (1), False (0) or null.
- (smalldatetime) is a Date field, which is stored in local format such as dd/mm/yyyy.
- (float) is a Decimal value, which is a number that isn't whole, e.g. 1.3333.
Joining the same table twice
It is possible within Report Writer to add the same table multiple times to yield different results.
This can be particularly useful when to pull multiple attributes through to the same report, for example.
As the attributes table is a single table listing different attributes separated by different attribute names, in order to pull through different attributes, the table needs to be referenced multiple times and each one filtered to show the attribute needed.
In the example below, the Delegate has two different attributes, one for result (attname: 10007), the other for score (attname: 10004).
Firstly, adding the same Data Source twice linked to the same table and using the same join field will allow us to pull two different values.
Once these have been added, it is important to filter each field to show a certain value.
Navigate to the Filters tab and drop down the first filter field.
Notice that there are two userscoursescalendarattributes fields. The first shows as expected. The second shows with a '2' at the end, differentiating the second Data Source.
Two filters will need to be added, one for userscoursescalendarattributes.attname, the second for userscoursescalendarattributes - usersattributes2.attname.
Custom Field Tables
The correct Data Sources to use for the different types of Custom Fields are below:
User fields - users
- Account fields - companies
Course Date fields - coursescalendar
Course Template fields - coursescompanies
Delegate fields - userscoursescalendar
Invoice fields - invoices
- Opportunity fields - opportunities
- User Award fields - userawards