Parameter Validation ?

Just to add to what Magoo said, if you don't have a dates table (and can't create one) you could create a stored proc that produces one like so (just change the number in the TOP clause to match the desired number of dates.)

WITH

L1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

L3(N) AS (SELECT 1 FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c),

L9(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM l3 a CROSS JOIN L3 b)

SELECT TOP (200) CAST(dateadd(day,n,getdate()-60) AS date) AS thedate

FROM L9;

You will, however, lose the calendar control. If that is not acceptable you could also create an error message text box that is only visible if your date parameter is older than 60 days. The rest of the report would only be visible provided that the date parameter is NOT older than 60 days. You would set this using the visibility option and an expression. This way if the the user selects a date that is more than 60 days old the report would only return a text box that says, "the date must be within 60 days of today". If that does not makes sense I could add some screenshots.

Hi Alan and Magoo...

Thanks for the info , I do have a dimdate table and it does work, except you're right about losing the calendar control. If when you have a moment explain the error message text box routine, I'd love to try it...

Thanks

Joe

Sorry, I almost forgot about this post. I put together an example of how you would do what I was talking about; attached is the RDL so you can just plug it into BIDS or SSDT and run it.

Note the screen shot below...

The report has just one parameter called @TheDate; that's your calendar control.

There are two datasets: SampleData and DateChecker. SampleData feeds the report (it's just nonsense for demo purposes); DateChecker takes @TheDate as a parameter and returns the text, "too old" if @TheDate is older than 60 days; NULL otherwise. It uses this query:

SELECT CASE WHEN @TheDate < getdate()-60 THEN 'too old' END AS datestatus

(note that I prefer SQL to SSRS expressions)...

The Report Body has two items, a tablix and a textbox with my custom error message :hehe: . The textbox is visable based on this expression:

=First(Fields!datestatus.Value, "DataChecker") <> "too old"

The tablix is visable based on

=First(Fields!datestatus.Value, "DataChecker") = "too old"

I got them backwards, but you get the point...

Hopefully that's enough to help you figure out what I'm talking about.

For the Visibility settings for my Text box