Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssrs 2008 date parameter not always working Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 9:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
In an SSRS 2008 report, I am not getting some records selected when I run the report in the ssrs 2008 report.
However when I run the sql in ssis manager, the records are selected. Thus I am trying to determine why some of the records are not selected whe running the ssrs report.

In the table called 'transactionfile', the fields that I am trying to determine where there is a problem is the following:
SchoolNumber (varchar(50),null), and
TransactionPaymentDate (varchar(50),null).

Examples of data are SchoolNumber = '121' and TransactionPaymentDate = '04162014'

In the SSRS report, the parameter value for @SchoolNumber is text and the parameter value for @EndDate and @StartDate is date/time.

The following is the query I am having a problem with:

SELECT s.SchoolNumber,
CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring (TransactionPaymentDate,1,2)+ '-' +

substring(TransactionPaymentDate,3,2)) as TransactionPaymentDate

from [eF].[dbo].[transactionfile] f
INNER JOIN
[eF].[dbo].schools] s
on rtrim(ltrim(s.SchoolNumber)) =rtrim(ltrim(f.SchoolNumber))
where rtrim(ltrim(s.SchoolNumber)) = rtrim(ltrim(@SchoolNumber))
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date)
>= @StartDate
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date) <= @EndDate

Thus could you suggerst what could be wrong when selecting some records by date?
Post #1567085
Posted Friday, May 2, 2014 10:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:01 AM
Points: 10,282, Visits: 13,266
It's really hard to tell what could be the problem with all the data type manipulation being done in the query.

My first thought is that it doesn't have anything to do with the query but that there is a Filter in one of the objects in the report (dataset, tablix or data region) that is limiting the results returned. So I'd look for those first.

I'd also, in development, run Profiler to verify that what I think is being passed to the SQL Server is really what is being passed to the SQL Server.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1567108
Posted Friday, May 2, 2014 10:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
Are you using this query inside a stored procedure? If so there might be an issue of Parameter Sniffing.. Check in relation with that..



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1567115
Posted Friday, May 2, 2014 10:38 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:01 AM
Points: 10,282, Visits: 13,266
a4apple (5/2/2014)
Are you using this query inside a stored procedure? If so there might be an issue of Parameter Sniffing.. Check in relation with that..



Parameter sniffing won't cause the results returned to be incorrect it can cause a sub-optimal plan to be used skewing performance. If parameter sniffing causes incorrect results that's a bug and a very big one that I think would have been caught and fixed a long time ago.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1567116
Posted Friday, May 2, 2014 12:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
Jack Corbett (5/2/2014)

Parameter sniffing won't cause the results returned to be incorrect it can cause a sub-optimal plan to be used skewing performance. If parameter sniffing causes incorrect results that's a bug and a very big one that I think would have been caught and fixed a long time ago.


Jack, I only said since the data is not retrieved correctly may be check that aspect too.. I didn't mean that's the only root cause.


Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1567153
Posted Friday, May 2, 2014 12:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:01 AM
Points: 10,282, Visits: 13,266
a4apple (5/2/2014)
Jack Corbett (5/2/2014)

Parameter sniffing won't cause the results returned to be incorrect it can cause a sub-optimal plan to be used skewing performance. If parameter sniffing causes incorrect results that's a bug and a very big one that I think would have been caught and fixed a long time ago.


Jack, I only said since the data is not retrieved correctly may be check that aspect too.. I didn't mean that's the only root cause.


But parameter sniffing won't cause data to be retrieved incorrectly, just possibly not as quickly. There is no reason to consider parameter sniffing for incorrect results. For inconsistent performance yes, but not incorrect results.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1567155
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse