Report Writer common data sources & relationships

Common Data Sources and Relationships in Report Writer

1 Relationships
2 Commonly used data sources
3 Common relationships
4 Entity relationship diagram

Related Help Guide Pages

Report Writer - Common Data Sources and Relationships

This common data sources and relationships page provides a quick reference to the key tables and views used in Report Writer and how they connect to one another! Understanding these relationships helps you choose the right data sources, link them correctly, and build accurate, reliable reports.

Relationships

Relationships need to be:

Between compatible data source types
i.e. ‘varchar’ to ‘varchar' (text to text) or ‘int’ to ‘int’ (number to number)

Relationships usually are:

Based on a shared field
i.e. ‘userid’ to ‘userid’ or ‘coursecalid’ to ‘coursecalid’
Based on ID fields
i.e. ‘userid’, ‘coursecalid’, ‘rsrcid’, ‘courseid’, ‘companyid’

Commonly used data sources

To report on	Use the Data Source
Course Templates	courses
Courses	coursescalendar
Bookings	coursescalendarbookings
Delegates & Trainers on Courses	userscoursescalendar
Users	users
User’s employment records (their link to their Account)	userscompanies
Accounts	companies
User training history	usersawards
Awards	awards

For Custom Fields on the system, they can be found in the following data sources:

User fields - users
Course Date fields - coursescalendar
Course Template fields - coursescompanies
Delegate fields - userscoursescalendar
Invoice fields - invoices

Common relationships

How to read this section

Where relationships are provided, we show both the table name and the field name in the format: table.field

This matches how fields are displayed in the Report Writer, making it easier to identify and apply the correct relationships, for example, courses.courseid is a match to:

Reporting on Courses & Delegates

To report on Course information and the Delegates that have been registered on your Courses, use the following data sources and relationships

coursecalendar
userscoursescalendar.coursecalid = coursescalendar.coursecalid
(this links your courses to your delegate data based on the unique ID of the course)
users.userid = userscoursescalendar.userid
(this links your delegates to your user data based on the ID of the user)
courses.courseid = coursescalendar.courseid
(this links your courses to your course templates based on the course template’s unique ID)

Reporting on Users, Accounts, and Account Groups

To report on User information, which Accounts they are employed by and which Account Groups they are in, use the following data sources and relationships

users
userscompanies.userid = users.userid
(this links your users to their employment records based on their unique User ID)
companies.companyid = userscompanies.companyid
(this links user employment records to your accounts data based on the unique ID of the account)
companygroupscompanies.companyid = companies.companyid
(this links your accounts to your account groups based on the unique ID of the account, this relationship is required because accounts can belong to multiple account groups)
companygroups.comgroupid = companygroupscompanies.compgroupid
(this links to your account groups database table to access the label of the account group)

Reporting on Delegates and their Invoices

To report on Delegates and their Invoices, use the following data sources and relationships

userscoursescalendar
invoiceitems.sourceid = userscoursescalendar.usercoursecalid
(this identifies the invoice items that your delegates are referenced by)
invoices.invoiceid = invoiceitems.invoiceid
(this links your invoice items to to your invoices based on the unique invoice ID)

Reporting on customer Accounts and their Invoices

To report on the Invoices that have been generated for your customer Accounts, use the following data sources and relationships

companies
invoices.targetid = companies.companyid
(this identifies the invoices that your customers have been the target for, in other words where they are paying the invoice not issuing the invoice)
invoiceitems.invoiceid = invoices.invoiceid
(this links your invoice items to to your invoices based on the unique invoice ID)

Reporting on Training Providers and their Invoices

To report on the Invoices that have been generated by your Training Provider Accounts, use the following data sources and relationships

companies
invoices.companyid = companies.companyid
(this identifies the invoices that your training providers have generated, in other words where they are issuing the invoice not paying for the invoice)
invoiceitems.invoiceid = invoices.invoiceid
(this links your invoice items to to your invoices based on the unique invoice ID)

Reporting on Resources assigned to Courses

To report on venues assigned to classroom courses, use the following data sources and relationship

coursescalendar
venues.venueid = coursescalendar.venueid
(this links each course to it’s assigned venue, if a course has more than one venue then only the first is referenced)

To report on trainers and other resources assigned to courses, use the following data sources and relationships

coursescalendar
resourcerequirements_coursedate_view.coursecalid = coursescalendar.coursecalid
(this links your courses to all of the resource requirements it has based on the unique ID of the course)
resources.rsrcid = resourcerequirements_coursedate_view.rsrcid
(this links each of the resource requirements to the specific resource in use on the course)

Reporting on Course Categories

To report on the categories that your Courses are assigned to, use the following data sources and relationships

courses
coursecategories.courseid = courses.courseid
(this links your courses to the categories it is assigned to, this relationship is required because course templates can belong to multiple categories)
categories.catid = coursescategories.catid
(this links to the categories table so that you can report on the category label)