Report parameter constraint
-
How to create report parameter constraint? Let say, I have StartDate parameter and I would like to limit user input by 1 month back (i.e. any date from this period). I mean, Reporting Service will check this constraint and will not start report executing if criteria doesn't meet. Thanks
-
Best option would be to create a dataset that has the date range you want. Then set your report parameter to be query-based.
--------------------
Colt 45 - the original point and click interface
-
Phill, sorry, but I didn't get you. Certainly I can create dataset to provide list of available values (and I doing a lot this way). But how can I create a query to limit, let say, StartDay>Now()- 1 month ? I am not sure that this is a good way to provide a list of possible 30 days. Thanks
-
Do you have a nums table in your database? This is a general purpose table that has a single integer column with numbers increasing from 1 up to an abitrary number that you decide. It can be a very useful tool in ranking and has many other uses.
Given that you only want 30 days you could even do it with a temp table.
EG: -- populate nums table Create table #nums ( num int ) declare @x int Set @x = 1 While @x <= 40 begin insert into #nums values (@x) set @x = @x + 1 end
-- select date range SELECT CONVERT(varchar(11), DATEADD(dd, -num, GetDate()), 120) , CONVERT(varchar(11), DATEADD(dd, -num, GetDate()), 106) FROM #nums WHERE num <=30
drop table #nums
I've put the dates in two formats, generally I find that the second format works best in Reporting Services.
--------------------
Colt 45 - the original point and click interface -
Phill, thanks- it's cool, but it's not what I am looking for. I don want to create list with available values for parameter but instead to use some criteria to limit it, like "not earlier than 1 month back". I mean, user will enter any value for parameter (not from list), but Report Service checks constraint and will decide whether to proceed or stop execution. Sorry, if I wasn't clear. Thanks
-
The only way you'll be able to achieve that is with some VB.Net code, or an external .Net assembly.
I'm not sure this would solve your issue either. At what point would the call the function?
--------------------
Colt 45 - the original point and click interface -
Actually, I would like Report Service (RS) will check input parameter (-s) constraints (limitations) before executing report. But I am not sure it's possible. Frankly, it's seems a bit strange to me that RS doesn't have this option- I mean, not parameter available list, but constraint/criteria for input parameters (like validation for Web appl). Probably, there is only one way to implement it (as you mentioned)- with .Net script (assembly?).Thanks
-
Validation of parameters is provided by means of a drop-down list, as I've illustrated, prevention of null inputs and basic datatype mapping. To me this is not strange at all, that's like saying that SQL Server should check the range of values for stored procedures parameters before executing the stored procedure.
Reporting Services is not a programming language with events and methods its a report production tool. If you built your own ASP.Net or Windows.Net application you would be able to have all the validation you think necessary, and then render the report from Reporting Services using one of the available controls.
--------------------
Colt 45 - the original point and click interface -
Thanks a lot, Phill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply