April 22, 2010 at 8:31 am
I have been working on a report that will have two simple parameters start date and end date.
I need to be able to limit the time span entered. I say for example someone goes in and runs this report for like 8 years they will end up crippling the server for god only knows how long.
while the data comes from a Stored procedure and I can easily add some code to that to fail if a date limit is exceeded, I would like to do this in SSRS if possible so that some useful information could be returned to the end user like a simple message of please enter a smaller time frame.
I have Google’d, Binged, and even asked jeves but I cannot find any examples of validating a parameter in SSRS. Any ideas would be welcomed and appreciated.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 22, 2010 at 8:40 am
I think you can raise an error and have it returned to the report.
Am I missing something?
-- Gianluca Sartori
April 22, 2010 at 8:54 am
But where in SSRS do you tell it what to do with that error. The dataset expects certain fields to be returned from the stored procedure if the SP returns an error code how do you tell SSRS what to do with that error. this is where I get stuck. When I have played with this SSRS gives a generic error about the dataset becuase the value returned did not match the meta data. I have not been able to find an example anywhere where you can tell SSRS to handle an error code returned.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 22, 2010 at 8:55 am
I had the need of doing something similar but for different reasons.
It's not an ideal solution but perhaps you could tinker with it and see if you can make it better.
I turned my start and end dates into strings and used the option of selecting the available values from a query. It worked well in practice but you lose the date picker and instead have two drop down lists for your start and end dates. Of course it depends how far back you want to go, scrolling through a list of hundreds of dates to find the one you want isn't very user friendly, but you simply limit how far back a selected start date can go in the query that returns your available dates.
If you modified the method to use a drop down list each for the day, month and year element of your date it would present the end user with something more familiar to them but still retain the ability to limit historic records. It's a bit more work but probably much more elegant than my initial solution.
Admittedly it's not quite what you asked for in your post in that it doesn't give the user a message to select a more sensible date range but it will at least give you control over what your users are able to select, this should negate the need to ask the user to reconsider their selection.
Hope this helps, it sounds like a fun challenge,
Paul
Keep the rubber side down and the shiny side up.
April 22, 2010 at 9:07 am
I had thought of that and basically I would allow them to enter the startdate as normal and then populate a list of possible ending dates that do not exceed the 1 year limit we determined as the cap. I am trying to avoid that however since you get a list of 365 possible choices.
It just seemed like surely you could put in some logic to say if it is over x then do y. Even Ms Access reports allowed you to put in a Ms box.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 22, 2010 at 9:41 am
It just happened to occur to me that I was not very clear on what I had actually tried. Some of you actually likely thought I was crazy when I said there were no examples. There are actually tons of examples and suggesstions. What I am looking for however is a method in which a report would never be submitted if the user enters a date range bigger then 1 year. This is the key piece. I have seen many example were you can inform the user then entered a large date range but as far as I can tell all of those suggesstion also result in the query being submitted anyway.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 22, 2010 at 10:26 am
I am not sure how I missed this solution but here is a link to a page that handles this.
http://stackoverflow.com/questions/606621/reporting-services-parameter-constraint
The last option does infact prevent the query from being submited and displays a nice message box for the user to understand what they have done wrong.
That is assuming that it works after deployment which there seems to be some doubt about on other sites.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply