Report Writer - Data Source Glossary

Report Writer Data Source Gloassary

The Data Source Glossary is a handy reference page that lists a wide range of useful Data Sources available within Report Writer. Each data source represents a specific table or view from the accessplanit database, and this glossary makes it easier to understand what information each one contains and how it can be used in your reports.

Whether you’re building a finance report, tracking Delegate activity, or analysing Course performance, the glossary helps you identify the most relevant data sources to get started. Think of it as your go-to guide—a quick way to find the right building blocks for your reports without having to dig through the entire database. By using the glossary, you can save time, avoid trial and error, and ensure that your reports are based on the most appropriate and reliable sources.

Users

ap_users_view - This Data Source contains data about Users.

users - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the User module.

userscompanies -This Data Source contains employment data for Users, such as Employment Start Date, whether this is their Main Account, etc. (companyid is the Account that a User has an employment record with).

ap_users_usertypes_view - This Data Source contains Job Roles linked to Users.

ap_users_specialrequirements_view - This Data Source contains details of additional requirements linked to Users.

Delegates

ap_users_coursescalendar_view - This Data Source contains data about both Delegates & Trainers (Users attached to Courses), where companyid is the Account that was the booking was made under.

userscoursescalendarattributes - This Data Source contains data regarding any Attributes a Delegate has.

userselearningpackages - This Data Source contains data about eLearning Delegates.

usersdocumentpackages - This Data Source contains data about Knowledge Document Delegates.

Top Tip!

You can link both userselearningpackages and usersdocumentpackages mappedid field to the ap_coursescalendar_view table and its coursecalid field to add in information about the Course, and then link ap_coursescalendar_view to ap_users_coursescalendar_view using the coursecalid fields for information about the Delegate (Status, etc.).

Courses

courses - This Data Source contains data about Course Templates.

coursescompanies - This is the Main Data Source for Course Templates as well as any Custom Fields in the Course Template module.

ap_courses_categories_view - This Data Source contains data to link Categories to Course Templates.

ap_coursescalendar_view - This Data Source contains data about Course Dates (excluding any Courses with no Trainers/Delegates).

coursescalendar - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Course Date module, and the 'sessional(bit)' field to filter out any parent or child courses.

Please Note

Contains the field durationinhours - which can display Course Date Duration, regardless of Duration Type, in Hours - making reporting this a lot easier where there are multiple duration types in use.

coursescalendarbookings - This Data Source contains data about Bookings. The notable fields it contains are as follows:

The User who created the basket (userid)
The User marked as the Booker for the basket (bookuserid)
The Opportunity that was used to make the booking (opportunityid)
The Account that was the booking was made by (companyid)

Top tip!

You can link a Delegate’s booking record to their Delegate record by linking ap_users_coursescalendar_view’s ccbookid field to coursescalendarbookings view’s field of the same name!

coursedate_statusonly_view - This Data Source contains data about Course Date Status, and can be linked to your coursescalendar data source using 'coursescalid'.

coursescalendarcompanies - This Data Source is used to link Course Dates and their Exclusive Accounts, with coursescalendarcompanygroups being the table used for Exclusive Account Groups.

Top Tip!

In the database and therefore Report Writer, coursescalendar is a Course Date table and courses is for Course Templates – you will need to know the difference!

Accounts

ap_companies_view - This Data Source contains data about Accounts (only with the status of 'Enabled').

companies - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Account module.

ap_companygroupcompanies_view - This Data Source contains data to link Accounts to Account Groups.

Resources

ap_resourcerequirements_view - This Data Source contains data about Resource Requirements on a Course Date.

resourcerequirements - This Data Source contains data about Resource Requirements on Course Dates or Templates (mappedid is the ID of the Course Date or Template).

ap_resources_view - This Data Source contains data about Resources.

ap_venues_view - This Data Source contains data about Venues.

Finance

ap_invoices_view - This Data Source contains data about Invoices (excluding any Invoices with a custom Reference Prefix that isn't 'INV').

invoices - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Invoices module and also includes Credit Notes.

ap_invoiceitems_view - This Data Source contains data about Invoice line items (excluding any Purchases/Products).

invoiceitems - You would use this Data Source if you need all of the possible Invoice line items.

ap_transactions_view - This Data Source contains data about Transactions on Invoices.

Please Note

For Credit Note transactions, the exttransid field in ap_transactions_view can be used to link which Credit Notes have been used as payment against which Invoices.

ap_discounts_view - This Data Source contains data about Discounts.

discountmappings - This Data Source contains data about each use of a Discount.

Please Note

The mappedid field will be the ID of the Booking, Delegate or Invoice, depending on whether your Discount has the type of “Course Booking”, “Delegate” or “Invoice”. Therefore, this can be linked to the coursescalendarbookings (ccbookid), userscoursescalendar (usercoursecalid) or invoices (invoiceid) tables.

ap_vouchers_view - This Data Source contains data about Vouchers.

ap_vouchers_mappings_view - This Data Source contains data about each Voucher issued to a User/Account, such as the Status, Expiry Date, Value, etc.

Awards

ap_awards_view - This Data Source contains data about Awards.

userawards - This Data Source contains data about each Award a User had, such as Expiry Date, Renewed, etc., as well as any Custom Fields in the Opportunity module.

ap_courses_awards_view - This Data Source contains data about Awards that are linked to Courses.

Opportunities

ap_opportunities_view - This Data Source contains data about Opportunities, such as Sales Stage, Source, Linked To Account, etc.

opportunities - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Opportunity module.

opportunityitems - This Data Source contains data about Opportunity Items, where you can add columns regarding Quantity, Unit Cost, etc.

opportunityitemmappings - This Data Source contains data to link Opportunity Items to the Courses they are mapped to. The mappedid field is the ID of the Course Date that the Opportunity Item is for, therefore can be linked to ap_coursescalendar_view using the coursecalid_2 field.

Notes

ap_notes_view - This Data Source contains data about Notes (mappedid is the ID of the User, Course, Opportunity, etc. that the Note has been added to).

Products & Services

products - This Data Source contains data about Products & Services.

productmappings - This Data Source contains data to link Products & Services to Course Dates or Templates.

purchases - This Data Source contains data about Purchases.

Custom Fields

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

Please Note

Data sources that are formatted like ap_[e.g. Users]_view are known as views, not database tables, these are pre filtered to only show certain records.

For example, ap_coursescalendar_view is not the same as coursescalendar, ap_invoices_view is not the same as invoices, etc.

Because of this pre-filtering, views and tables will occasionally bring very different sets of results.