Troubleshooting checklist

Resolving issues when building a report can be frustrating if you don't understand the process of troubleshooting.

The following checklist can help you understand the steps to go through when trying to resolve an issue with a report.

• Identify if the issue is with missing results, incorrect data or a report writer error
  
  • If the report is missing results, what results is it missing? Use datagrids to help.
  • If the report has incorrect data, what data the report incorrectly showing and how does this differ from the data you expect?
• Identify the cause of the issue.
  • Is this an issue with the Datasources?
  • Is this an issue with the filters?
  • Is this an issue with the data in accessplanit?
• Understand what needs to change to resolve the issue

Troubleshooting process

1. 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.

1.1 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.

For further information on the errors, please click here

1.2 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:

1.2.1 Identify the missing data  

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

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:

The report however is showing a different number of 122

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.

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

1.2.2 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

The report shows only 3 results.

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?

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

1.2.3 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.

1.2.4 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:

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

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.

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

1.2.5 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.

2. Check the 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:

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:

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:

3. Check the 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".

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.

4. Contact the Help Desk team

If you have followed the process above and the issue has not been resolved, please contact our Help Desk team who can assist further.

Resolving report writer error messages

Join fields must be of a similar type

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.

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)

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