Set Minimum & Maximum values for report date parameters in SSRS

Question

Set Minimum & Maximum values for report date parameters in SSRS

uma t

Old Hand

Points: 308
More actions
May 12, 2008 at 1:02 am

#380441

Hi,
I have a SSRS report that has two report date parameters: Start date and End date. I would like to know if it is possible in SSRS to set maximum and minimum values for these report date parameters.
Any help much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

grk5 SSC Veteran Points: 258 More actions · Answer 1

I don't believe you can define a minimum and maximum value to confine the user selection.

If there are specific dates, then you could populate with a result set which makes a drop-down control -- Not a calendar control.

I would leave it and allow the users to select any dates. In the Stored Procedure where the report gets its final data to display, I would check the date range first. If it is outside the date range, send back an indication with a message to the user that the minimum date is dd/mm/yyyy and the maximum date is mm/dd/yyyy. That way the report gives the user feedback. If the date is in the desired range, then return the result set.

Another good practice is to put a 'hint' in the label for the date. You could indicate what the min and max are for the two dates.

-- Al

maya potiyevskaya SSCrazy Points: 2091 More actions · Answer 2

sure :-). you can either hard-code the value in 'default section' of parameter dialog for each one or point it to a dataset if it'll be dynamic 🙂

Dave K-596875 Ten Centuries Points: 1006 More actions · Answer 3

This has sort of been said already, but you can set the default dates in a parameter to the results of a SQL query/SSRS dataset. For example, the query below returns the first day of the current month and the last second of the day before today. In the Report Parameters management screen, you would set the default for for Start Date to the results of a query, and you would choose this dataset and Start Date for the value and label columns. You'll do it the same way for the End Date.

select dateadd(mm,datediff(mm,0,getdate()),0) [Start Date],

dateadd(s,-1,dateadd(dd,datediff(dd,0,getdate()),0)) [End Date]

Being able to set parameter defaults to the results of a query makes pretty much anything possible.

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 4

Uma,

I believe the solution proposed regarding having the stored procedure return a readable message be your best solution for this scenario. I played around with another option using an additional parameter but the message returned to the screen is too cryptic for a user to understand.

Hope this helps...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

rbb 96410 SSC Journeyman Points: 89 More actions · Answer 5

Hey,

I know this is an old post, but I came across it because i had the same scenario so I will list my solution.

Basically I had the same thing, two date parameters and I wanted the report to display some kind of error if the beginning date was before some threshhold date. So I added a 3rd parameter (hidden, string) to the end of the parameter list that had a calculated value based on the beginning date parameter. If the beginning date parameter was before the threshhold then the error parameter got a value of the error message, otherwise an empty string. Then from there all I needed to do was set the initial visibility of my report items calculated based on the 3rd paramter having a length = 0 and my error message textbox on length > 0. The error message textbox got its value based on the error parameter value. Visibility made easy as I just grouped all of my detail items in a rectangle and had to set the visibility of only the rectangle.

With this technique your error parameter could check for multiple conditions and concatenate to its own value, but in my case I was only checking the one condition.

Hope this helps anyone down the line.

Other options I considered were having one of the sprocs return an error and display it, but we were using a custom portal to display reports and this was the simplest solution.

Thanks,

Ryan

mariahalt Newbie Points: 5 More actions · Answer 6

If the dataset comes from a stored procedure, I often check the date parameters inside the stored procedure and swap them if need be.

IF @pToProcessDate < @pFromProcessDate
BEGIN
   SELECT @Swap = @pToProcessDate;
   SELECT @pToProcessDate = @pFromProcessDate;
   SELECT @pFromProcessDate = @Swap;
END;