Report Writer Issues

Troubleshooting issues when building a report can be frustrating, especially if you’re unsure where to start. Understanding a structured process can make resolving problems much easier and less stressful. The checklist below outlines key steps to guide you through identifying and fixing report issues.

1. Identify the type of issue
   Begin by determining whether the problem is caused by missing results, incorrect data, or a report writer error. Clearly defining the issue will help you focus your troubleshooting efforts.

2. Investigate missing results
   If your report isn’t showing certain results, ask yourself: which specific results are missing? Tools like datagrids can be very helpful here, allowing you to compare what is returned versus what you expect.

3. Examine incorrect data
   If the report is displaying inaccurate data, identify exactly which fields or records are wrong and how they differ from the expected values. Understanding the discrepancy is key to pinpointing the root cause.

4. Determine the cause of the issue
   Consider where the problem might lie:

   • Is it related to the Data Sources?

   • Could it be caused by filters applied to the report?

   • Or is the issue with the underlying data in accessplanit itself?

5. Decide what needs to change
   Once you’ve identified the source of the problem, determine the specific adjustments required to resolve it. Whether that involves updating a filter, correcting a data source, or fixing a data entry, having a clear plan ensures a quicker and more effective resolution.

Troubleshooting process

Identify the issue

Identify the Issue

The biggest challenge in resolving an issue with a report is to first understand what the issue is and where it is coming from.

The report has an error message

When loading the report, if an error shows like below, clicking the hyperlink will give you further details about the issue.

Open

For further information on the errors, please click here

The report is showing some data correctly but not all / The report is showing completely incorrect information

One of the most common issues with building reports is when the report is not showing expected results.

There can be many reasons for this. The process to identify and resolve this is below:

Identify the missing data  

To start this process, it helps to identify exactly what data is missing in the report or is showing incorrectly.

 

Top Tip!

Cross reference the data in the report with the data in accessplanit to identify differences in results

 

In the example below, the report has been built to count the number of Delegates on Class courses that start this year. 

There are 108 delegates that meet this criteria. This can be found by applying the same filter to the Class Delegates SmartGrid:

Open

The report however is showing a different number of 122

Open

After checking that the Data Sources and the Filters are correct, the cause of the issue is still not clear.

So to help diagnose the issue, changing the report to show a granular list of results can help.

In this case, showing the records helps to highlight that the report is pulling through trainers as well as delegates.

Open

This is because the ap_users_coursescalendar_view lists all user records associated with the course date, not just delegates.

Knowing this now, a filter can be applied to the report which removes the trainers, and gives an accurate number

Open

Are the order of the Data Sources causing data to be filtered?

The order in which Data Sources are linked can have a huge impact on the data showing.

In the following example, the report has been built to show course dates this year along with some additional details such as the course start date, number of delegates booked and the award granted upon completion.

The Data Sources used contain all the information needed

Open

The report shows only 3 results.

Open

There are 39 different courses in accessplanit which can be seen on the course templates page, so why is the report not pulling these results through?

Open

The reason for this issue stems from the order or the data sources. 

The first Data Source used was ap_courses_awards_view.

Although this is a highly relevant Data Source as it will provide the award for the course, the Data Source itself only contains records of courses that have awards associated.

In this case, only 3 courses in accessplanit have awards associated.

The linked Data Sources therefore are only able to link to the records available in that table, which means that the details for any courses that do not have awards are inherently filtered out of the report.

 

Instead, if the starting Data Source is the courses view, this will first provide a list of all courses in accessplanit.

From there the ap_coursescalendar_view can be added to show any course dates for those courses, then the ap_courses_awards_view added to show awards linked to those courses.

This way, the maximum amount of data has been provided without any background filtering

Top Tip

When adding a Data Source, check what data this provides.
Some Data Sources only provide you data that meets certain conditions so starting with any Data Sources like this will instantly cause the report to be filtered.

I.e. ap_courses_awards_view only shows courses that have awards associated

ap_users_awards_view will only show users that have an award

ap_coursescalendar_bookings_view will only show course dates that have at least 1 booking

Are the Data Sources linked correctly?

In the section, check the Data Sources, we see how using different key fields when joining tables can cause drastically different results in the report.

Are the correct fields being used?

This may seem like an obvious note but often a field that appears correctly initially can be showing different results than expected.

In the example below, the report has been created to show delegates on course dates, the account of the delegate and the delegates status:

Open

The Status column shows each delegate status as "Enabled", as this is not a delegate status, the field must be incorrect:

Open

When checking the field used, Status field is being used from the companies table rather thean the ap_users_coursescalendar_view and is therefor showing the status of the company, not the delegate.

Open

Changing this one field to pull from the correct table fixes the report.

Are the filters applied hiding the expected results?

Often overlooked, the reason that data may not be appearing in the report may be due to the filters applied.

Once the missing data has been identified, check that the filters applied do not exclude this from the report.

Is the report schedule not working?

If you have set your report to email automatically, and the email is not sending, please check the following:

• Is the report a private report?
  If your report is not shared with others (e.g. the sharing option is share with: ‘everyone’ rights: ‘none’), then the schedule option will not work, please update your sharing options for the schedule to work!

• Is the scheduled date in the future?
  Make sure that the next schedule date is in the future, and that you have not accidentally set the next scheduled date to ‘yesterday’

Check the Data Sources

Check your Data Sources

The most common reason for reports not working or showing data as expected is due to an issue with how Data Sources have been linked.

The following report has been built to show invoices and invoice details including the company being invoiced:

Open

The company name that shows is the the training provider, not the company being invoiced.

The data sources used appear to be correct and are also in the correct order:

Open

So where does the issue lie?

Data Sources can contain multiple key fields. In this case, the ap_invoices_view table has two fields of interest:

CompanyID and TargetID. CompanyID shows the name of the training provider linked to the invoice and TargetID shows us the name of the company being invoiced.

 

So by changing the relationship between the ap_companies_view table and the ap_invoices_view table using the key field, the ap_companies_view will now pull through company information about the invoice target, not the training provider:

Open

Open

Top Tip

When building the report, if only the ap_invoices_view had been added and all the fields within that view shown in the report, at a glance it could be identified which field contains the information needed and therefor what the key fields should be in the joining of the tables.

Check the filters

Check your filters

Having multiple filters on one report is natural but be aware that filters can also filter each other.

Each filter applied is pre-filtered by all the filters above to show only results that would be available.

In the example below, the report has been built to show delegates who have the status of "No Attend" on the course "First Aid At Work".

The report has been filtered to only show course names that equal "First Aid At Work".

Open

When adding a filter to show Candidate Status, the option to select "No Attend" is not available.

The reason for this is because there are currently no delegates with that status in accessplanit on a "First Aid At Work" course.

Common Issues

Why are there results missing from my Report?

If you are having issues in Reports where certain results are missing from the Report, please check the following:

• Check the Report filters

Is a filter prevent the results from showing?

If a record is not appearing in a report, the most common cause is that a filter has been applied, which is preventing the record from being included in the results.

The first step is to review the intended result in the context of the Report’s filters. Consider whether the record or data point you expect to see, meets all of the filter criteria defined in the report.

• If the intended result does not meet the filter rules, this is likely why it is not appearing in the Report. In this case, you can either adjust the filters to include the desired data or acknowledge that the record will not be displayed under the current filter configuration.

• If the intended result does meet all filter criteria, then you can proceed to the next step in the checklist to continue troubleshooting or reviewing the report.

Open

• Check the Data Sources of the Report

Are your Data Sources joined correctly?

This issue may be related to the joins configured in the Data Sources section of the report. When a field is included as part of a join, it is essential that the field contains valid values within accessplanit.

If the joined field is empty or missing data, the Platform may be unable to complete the join, which can result in the corresponding record not appearing in the report output.

For example, in the below scenario:

Open

For a course date to appear in the report results, it must have a ‘Venue’ assigned. If a Course does not have a Venue, the system cannot complete the join, and the record will be excluded from the results.

For further information on Data Sources, please visit the following help guide: Report Writer - Data SourcesPreview or Report Writer - Data Source GlossaryPreview

If you would like to display course dates even when the Venue field is empty, you can enable this by selecting the “Allow Nulls” checkbox at the top of the page. This allows the report to include records with missing values in the Venue field.

Open

Why is the terminology different in Report Writer to the rest of accessplanit?

The platform’s database still retains some of the legacy terminology, which may differ from the labels you see in Report Writer. This can occasionally make it challenging to locate certain fields.

For example:

• Candidate → Delegate

• Company → Account

• User Type → Job Role

• CourseCalendar → Courses

• CourseCompanies → Course Templates

If you are unable to locate a specific field or are unsure which current term corresponds to a legacy term, please contact the Customer Support Team for further support

What's the difference between an AP view data source & a non AP view data source?

In Report Writer, any ‘ap’ view is a filtered database view, not a traditional database table. This means that while the view draws data from the database, it is filtered to display only specific records and may convert constant values into more readable labels. For example, a ‘Course Status’ will show as ‘16’ in the normal ‘coursecalendar' view, whereas the ‘Course Status’ will show as ‘Booked’ when using the 'ap_coursecalendar_view’

In such cases, it is recommended to use the underlying database tables (e.g., coursecalendar) instead of the filtered views, to ensure that all relevant data, including custom prefixes, is included in the Report.

Understanding Data Source joins and errors

Join fields must be of similar type error

Receiving this error message means that one or more of the Data Sources used within the report is linked to another Data Source using a key field which is of a different data type.

Open

First, navigate to the Data Sources tab and ensure that the data type of each key field matches to the data type of the key field it is being linked to.

In the example below, an issue was caused as the key field courseid has a varchar data type, whereas the field it is being linked to, coursecalid, has a datatype of int (Integer)

Open

Column 'dbo.XXXXXXXXX' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is because all other fields have been grouped except this one. Navigate back to the fields tab, group the field and try again

Why can't I delete a Report?

The ability to delete a report is a platform-specific setting that must be enabled on your site. By default, this option is disabled to help prevent reports from being deleted accidentally, ensuring that important data and configurations are preserved.

If you currently do not have the option to delete a report and require this functionality, please contact the Customer Support Team to request that the setting be enabled for your Platform.

How do I schedule reports?

To schedule a report, navigate to the ‘Misc’ tab while in ‘Edit Report’ mode. Here, you can configure the following settings:

• Start Date and Time: Select the first date and time you want the report to be sent.

• Repeat Type: Choose the frequency for the Report to be sent — options include daily, weekly, or monthly.

• Recipients: Enter the email addresses of the individuals who should receive the report separated using ‘;' between each email address. For example
  ’joe.blogs@joebloggs.com; jane.doe@janedoe.com; brian.mead@brianmead.com'

  Open

  

Why can't a User see/edit a Report I have made?

Each report you create can have customised permissions applied specifically to that individual report. These permissions allow you to control who can view, edit, or manage the report.

To configure these settings, navigate to the 'Misc' tab within the report. Here, you can define the access levels for different users or user groups, ensuring that sensitive data is only accessible to authorised Users

Open

Why can't I see all results on my filter?

In Report Writer, the drop-down menus used for filtering are limited to displaying a maximum of 500 results. If the value you are searching for does not appear in the list, it may be due to this limitation.

How do I add a Custom Field into a report?

The correct Data Sources to use for the different types of custom fields are below:

• User fields - users

• Course Date fields - coursescalendar

• Course Template fields - coursescompanies

• Delegate fields - userscoursescalendar

• Invoice fields - invoices

• Opportunities - opportunities

My report is duplicating results 

If you are having issues with your Report where results are duplicating, please check the following:

• Check the rows of data

• Check the Data Sources of the Report

How do I get my report to stop showing child course sessions?

The field you need is located in the coursescalendar data source and is called sessional (bit). This field must be added as a filter in your report. The possible values are as follows:

• Head Sessional Course → True

• Child Sessional Course → (Empty)

• Standalone Course → False

To display only Head Sessional or Standalone courses in your report, set the filter as:

sessional (bit) Not Blank

This ensures that only Courses classified as Head or Standalone are included in your report results.

As an alternative, if your Course names contains the following ‘Day 1’, ‘Day 2’, ‘Day 3’ etc. then you can use the following filter:

course_name → Isn't like → ‘Day’

‘course_name' can be found under ap_coursecalendar_view

Filter Logic

It is important to note that Filter Logic cannot be used simultaneously with the ‘Blank’ option in reports, as these settings are mutually exclusive and will conflict with each other.

For example, consider a filter logic expression such as:

1 AND 2 AND 3 AND 4 AND (5 OR 6) AND 7 AND 8 AND 9

If the ‘Blank’ option is selected for filter 4, a contradiction arises:

• The logic requires filter 4 to exist and meet a specific condition.

• Selecting ‘Blank’ indicates that the field does not exist or is empty.

Since these two conditions cannot coexist, the Filter Logic takes precedence, and the report will not behave as intended.

To avoid this conflict, either adjust the filter logic or remove the ‘Blank’ option for the relevant filter.

Unable to link a Placeholder booking to a Course in my Report

In the ap_placeholders_view data source, the field targetid represents the Course Date ID. This is a data source that links placeholder bookings to specific Course within the Report.

How do I know which Attribute is linked to which attribute number?

Reach out to the Customer Support Team for a full list of attributes for your Platform

Unable to see logins using ap_systemeventslog_view, for the past 30 days

The ‘ap_systemeventslog_view’ Data Source in Report Writer only retains login records for the past 30 days.

If you require login information beyond this period, it is not directly available through the standard database sources, please reach out to the Customer Support Team for further support

Report using ‘ap_invoices_view’ not pulling through any results

When building reports that involve custom invoice prefixes (for example, ARV-), it is important not to use ap_invoices_view or any of the ap custom views. Instead, you should use the underlying database tables, such as Invoices or InvoiceItems.

The reason for this restriction is that the ap views are pre-filtered to include only Invoices with the default reference prefix (INV). As a result, any Invoices with custom prefixes or references will be automatically excluded from the view, which can lead to incomplete or inaccurate report results.

By querying the base tables directly, you can ensure that all Invoices—regardless of prefix—are included in your report.

How do I pull through the Billing Address details for a User or Account?

Billing addresses are stored in the locations table. To include them in your report, the locations.mappedid field should be joined appropriately:

• Join to the userid field if the report pertains to Users.

• Join to the companyid field if the report pertains to Companies.

Additionally, you will need to filter on the locations.locationtype field, setting it to Equals 4, since a location type of 4 corresponds to Billing.

By correctly joining and filtering on these fields, you can ensure that your report accurately captures the relevant billing address information.