Reports - Different Data Preview and Report Rows

[Val C]

I have a issue where rows are being returned by Data Preview but when the report is run there are no rows returned.
Tables selected

Columns Selected

Filter condition

Data Preview Results shows matching rows

However report doesn't return any rows

Please help?

[Jim]

Check your sql limit, I believe 2500 is the max, and add a filter on date range, I belive they max out and thus end up with 0 

[Val C]

Hi Jim, thanks for your reply.  
1. How do I check the sql limit?

2. When I run the same report without the link to the request table, I get 517 rows returned instead of zero.  When I've run other reports that have maxed out they return 25000 rows not 0.  
What I would like someone to explain is why the Data preview returned rows and the repot does not, and why when the the task table is linked to the request table it returns 0 rows in the report but when not linked it returns 517.

Any help in getting the linked report working would be gratefully appreciated. 

[Steve Giller]

The Data Preview is limited to 500 rows.

If the full report is not returning any results then it is possible that this is timing out before it completes.
What happens if you reduce the time period to the last month, rather than the last year?

[Val C]

Hi Steve, thanks for your reply,

I understand that the data preview returns a max of 500 rows, but not why the report returned 0 rows.

The time period I am using is tasks created on value is greater than 27-02-2024.

When using the tasks table linked to the requests table, 0 rows are returned, however when the request table link is removed and only the tasks table is used, the report returned 517 rows.

Any ideas on how I should do this to get results when the report runs please?

[Steve Giller]

4 minutes ago, Val C said:

I understand that the data preview returns a max of 500 rows, but not why the report returned 0 rows

Because when you limit the results to 500, this will never take very long - if the report attempts to return 500,000, this will take a lot longer.

5 minutes ago, Val C said:

The time period I am using is tasks created on value is greater than 27-02-2024.

My apologies, I misread the config.

5 minutes ago, Val C said:

however when the request table link is removed and only the tasks table is used, the report returned 517 rows

This could be due to your JOIN, I suspect the RIGHT JOIN is returning all of your Requests (or possibly Tasks) and you may need an INNER JOIN here - although please discuss this with a DBA as this is not my area of expertise.

[Val C]

Hi Steve

Thanks for you help on this, it's really appreciated.

I have tried with both JOIN and RIGHT JOIN and both report runs return 0 rows.  However if I remove the date filter (but keep the request and task tables  linked), the report returns the maximum of 25000 rows.

Is the date filter timing out and if so, is there any way to add a date filter to the report without it timing out?

[Victor]

@Val C what exactly are you looking to get from this report?

[Val C]

Hi Victor,

I am trying to produce a report of task activities that were either created or completed on a specific day for specific service ids, which is why I need to link the task activities and request tables and limit it to a specific day - we have various other reports that do this and they do work, however the task activities report and the call diary report (using created and updated dates) are the only two that aren't working.

Any help troubleshooting would be appreciated.