November 23, 2011 at 12:01 pm
SSRS 2008 report with three parameters and corresponding filters. I want the default to be "get me everything" but can't figure out the magic combo in the filter/parameter settings.
Filtering is single-select otherwise.
November 25, 2011 at 5:19 pm
You'll need to update the query/data set to handle null values for each of the three parameters
Where
SomeField = isNull(@SomeField, SomeField)
and NextField = isNull(@NextField, NextField)
and ThirdField = isNull(@ThirdField, ThirdField)
And set each parameter to allow blank and null values, set Default value to NULL
If you have a defined list of Available values for the parameter(s), you'll need to add a value for "get me everything". Label = "get me everything", Value = NULL.
If you are using dynamic list for parameters, add a select for the "get me everything" option. This is easily done with a union something like this:
Select
Label = 'get me everything'
,Value = NULL
,OrderIt = 0
UNION
select
Label = FieldName
,Value = FieldID
,OrderIt = 0
from
yourtable
order by OrderIt, Label
November 28, 2011 at 8:00 am
Done, but I'm still being forced to select something before the report will run. I want it to auto-run as soon as I open it.
November 28, 2011 at 9:15 am
Update: user wanted two more filtering fields so I added those in and those two *do* show up with a default value of all/NULL (using the default default value of "(Null)"). The original three still show with "<Select a Value>". What's odd is that it's working as desired in BIDS preview mode but not when I upload the file to the report server and run from there. I've tried deleting and re-adding the parameters but that didn't change anything.
Any ideas anyone?
November 28, 2011 at 9:33 am
Urgh. Was poking around looking at a few things on the report server itself and noticed that there was a 'Parameters" tab. Looked there and it was holding on to old, non-valid settings. Had to uncheck "Has Default", then re-check and check Null.
So there's a bunch of caching happening behind the scenes...
Is now finally(!) all working as desired.
November 28, 2011 at 9:42 am
Pam Brisjar (11/28/2011)
Update: user wanted two more filtering fields so I added those in and those two *do* show up with a default value of all/NULL (using the default default value of "(Null)"). The original three still show with "<Select a Value>". What's odd is that it's working as desired in BIDS preview mode but not when I upload the file to the report server and run from there. I've tried deleting and re-adding the parameters but that didn't change anything.Any ideas anyone?
Check the parameters in report manager. Sometimes a value gets thrown in there that overwrites the rdl.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 28, 2011 at 9:45 am
To get to parameters - select manage from the dropdown error for the report in question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 28, 2011 at 10:03 am
SQLRNNR (11/28/2011)
To get to parameters - select manage from the dropdown error for the report in question.
Yeah, I stumbled on that just before you posted (see above). That was indeed the problem.
Thanks.
November 28, 2011 at 10:18 am
Pam Brisjar (11/28/2011)
SQLRNNR (11/28/2011)
To get to parameters - select manage from the dropdown error for the report in question.Yeah, I stumbled on that just before you posted (see above). That was indeed the problem.
Thanks.
D'oh - I missed that post.:-D:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply