SQL Query doesn't match Telerik Query

[John]

I have a complex query that I didn't build myself that I need to use for a report. I ran the query in SQL Server and I can find the expected results. When I try to run this query in Telerik, I likewise get the expected results. But the expected results encompass data from as old as 2006.

There's a good chunk of data in the query and in order to make sure my report is working correctly on relevant data, I want to filter out the data that is older than 2018. I've tried to go about this two different ways. The first is I have tried to use the filter settings on the report itself.

Expression:
= Fields.EntryDate.Year >= 2018

Operator
=

Value
=True

This does not work and the preview always displays very old information first (what would appear at the beginning of the query in SQL Server). The rendering process takes too long to sit and wait before I can skip through thousands of pages before I see any data from 2018 or later. In fact, Telerik just crashes usually.

My next thought was to limit the results in the query itself. I use the WorkOrder.EntryDate in my WHERE clause and filter out dates older than 2018. I tested this query in SQL Server and it performs exactly as I expect it to. When I use this same query in Telerik, not only does the preview return 0 rows, but the preview is nothing more than a blank image. Is there a reason my SQL Server can process my query without issue, but Telerik fails to do so?

[Todor]

Hello John,

The Reporting engine utilizes the specified ADO.NET Provider to connect to the database and fetch the results. Hence, the results displayed by the report depend on what the provider will return.

I tested the described scenarios against the AdventureWorks database with the query:

SELECT
	[Sales].[SalesTerritoryHistory].[StartDate], 
	[Sales].[SalesTerritoryHistory].[EndDate], 
	[Sales].[SalesTerritoryHistory].[SalesPersonID]
FROM [Sales].[SalesTerritoryHistory]

The filtering on the Report level I used:

'= Fields.StartDate.Year >= 2002' = '= True'

or

'= Fields.StartDate.Year' >= '=2002'

In both cases the result was correct.

I tested also with filtering on the DataSource level with the query:

SELECT
	[Sales].[SalesTerritoryHistory].[StartDate], 
	[Sales].[SalesTerritoryHistory].[EndDate], 
	[Sales].[SalesTerritoryHistory].[SalesPersonID]
FROM [Sales].[SalesTerritoryHistory]
WHERE [Sales].[SalesTerritoryHistory].[StartDate] >= @SalesTerritoryHistory_StartDate

Note that in the report it is necessary to link the query parameter '@SalesTerritoryHistory_StartDate' to a Report Parameter - see Using Parameters with the SqlDataSource component.

I have attached my sample report that utilizes the sample AdventureWorks database that we use in our demos. The Report Header shows all the data, the Detail Section - the data filtered on Report level, and the Report Footer - the data filtered on the DataSource level with the Report Parameter.

If the problem persists you may open a support ticket and send us the report and a backup of the database so that we can test locally.

Regards,
Todor
Progress Telerik