Default Parameter Value in the Reporting Services
-
Does somebody have experience with reporting services? they are just killing me with their non-flexibility. For the report I am making now the request was not to do it in ASP or .NET but in reporting services with stored procedure. So I wrote stored procedure that uses parameters with dynamic sql. Let's say if parameter is *, it brings all the records, otherwise it filters. Store procedure works fine.
Then I use that stored procedure in the reporting services. Report runs OK but I have problem with parameter. Its a drop down menu that gets populated from the table. I also need to add to the list of the values * to choose all records (default).
So in the layout tab I go to Report menu, click Report parameters. There I choose Available values "From Query" blah-blah ... then Default Values "Non-Queried" and I type *. But then when I run a report, it doesn't give me that * as a default, it just says <Select a value> in the parameter drop down and brings values using query.
What's going on, how to set a default value for parameter?
-
I declare all my report parameters as NVarchar and I have also seen it as Nvarchar(max) with all the needed conversions in my stored procedure and in the Report configuration I choose allow Null value and it works fine. That means you will choose the parameters as from query. Hope this helps.
It helps to know SQL so you can clean up a lot before passing it to SSRS.
Kind regards,
Gift Peddie -
I add the '*' option to the list of available values in teh query that populates the drop down list, then in the default value I specify -1 so that it chooses the *. Hope that makes sense.
for Instance
select -1 as sid, '*' as fname
union all
select sid, fname from tblusers
order by 1
-
Thank you very much, everything works wonderfully! I just finished that report!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply