Date Parameters ignored on SSRS

(0) Share

Report

Posted on by Shane Brown

190

I am having trouble with SSRS reports I have 2 date field parameters and when i run the report it ignores the 2 dates i input and displays all the records. I want it to only show me records where the sab_enddate is within 2 dates.

WHERE (ST.sab_shortcode LIKE 'SB%') AND (SP.sab_enddate BETWEEN @EndDateFrom AND @EndDateTo)

Dataset 1:

SELECT COUNT(*) AS ContactCount, ST.sab_shortcode AS ShortCode, C.sab_Address1_CountryIdName AS Country from Filteredsab_subscriptionplan SP INNER JOIN Filteredsab_subscriptiontype ST
ON SP.sab_subscriptiontypeid = ST.sab_subscriptiontypeid INNER JOIN FilteredContact C ON
C.ContactId = SP.sab_primarycontactid
WHERE ST.sab_shortcode like 'SB%'
GROUP BY C.sab_Address1_CountryIdName, ST.sab_shortcode

Dataset 2: (WHICH HAS THE PARAMETER)

SELECT SP.sab_name AS Subscription, SP.sab_startdate AS SDate, SP.sab_enddate AS EDate, ST.sab_name AS SubscriptionType, ST.sab_shortcode, C.fullname, C.sab_address1_countryidname
FROM Filteredsab_subscriptionplan AS SP INNER JOIN
Filteredsab_subscriptiontype AS ST ON SP.sab_subscriptiontypeid = ST.sab_subscriptiontypeid INNER JOIN
FilteredContact AS C ON C.contactid = SP.sab_primarycontactid
WHERE (ST.sab_shortcode LIKE 'SB%') AND (SP.sab_enddate BETWEEN @EndDateFrom AND @EndDateTo)

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

Do you get the right records if you run the query (with the same date range) in MSSQL Management studio?

Was this reply helpful? Yes No
Shane Brown 190 on at

Like (0)

Report

It works in Management Studio when i set the dates but in the report it does not work.

Was this reply helpful? Yes No
joman 617 on at

Like (0)

Report

The only way, i see, is open SQL server profiler, and find there a query, when SSRS builds Report.

Then you can find out what is happens.

Was this reply helpful? Yes No
Verified answer

Shane Brown 190 on at

Like (1)

Report

Fixed by scrapping the second Dataset

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

I always tried my best to stick to only one main dataset when trying to develop a SSRS report. By doing this it gives me the flexibility to remove the criteria from the report and pushing the criteria to the users by giving them the control of pre-filter (they can set whichever date they want).

Was this reply helpful? Yes No