art_frank
asked on
SSRS, null values, but no default
SSRS report, I have a parameter that accepts integer values and (to make it optional) I have the "Allow null value" box checked. That's fine. What isn't fine is that there are NO default values assigned to this parameter, but when I run the report the "null" box IS checked and the report runs automatically. I want the null box to start out UNchecked and for the user to either fill in the parameter box OR check the NULL box before the report runs. Is there something I'm missing here? Why is this parameter pulling in null as the default, and is there a way for me to stop it?
ASKER
On the "General" page, the "Allow blank value" option is not available and is greyed out -- this is because the data type is integer.
On the "Default Values" page, the "No Default Value" option is selected, therefore there is no list to edit. If I select the "Specify Values" option, then the list is available, but it is empty.
On the "Default Values" page, the "No Default Value" option is selected, therefore there is no list to edit. If I select the "Specify Values" option, then the list is available, but it is empty.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Close enough, as this led me in the right direction. Following your directions exactly, the report STILL runs automatically, although with an empty result set so it's a bit faster. Really, here's what I had to do: change the data type to text (this is the key); uncheck the "allow null values" box; specify "no default values"; change the where clause in the query from (@Param IS NULL OR Field = @Param) to (RTRIM(@Param) = '' OR Field = @Param). Now the report doesn't run automatically, and if the users leave the field blank they get everything.
Regardless, it seems odd that if I allow null values that the default is null no matter what I pick on the "Default Values" screen. And it's mildly annoying that users can put in non-numeric values and get an error.
Thanks for your assistance.
Regardless, it seems odd that if I allow null values that the default is null no matter what I pick on the "Default Values" screen. And it's mildly annoying that users can put in non-numeric values and get an error.
Thanks for your assistance.
I just worked out another approach you could try.
Create a new dataset which has a single field - the possible values you are trying to look up.
Based on my example, I created a new datasource called "PossibleIDs" which selects the Stocktake_ID values from the same source that the parameter refers to. Depending on whether or not you have replicated values, you may need to use a SELECT DISTINCT query to eleiminate duplicate values from the list.
Then in the parameter for STOCKTAKE_ID, I have allow blank and allow null, no default value, but in the available values, I have Get values from query referring to the PossibleIDs dataset.
Then when I run the report, the report is blank until I select the value I want from the dropdown list which displays all of the possible values.
Create a new dataset which has a single field - the possible values you are trying to look up.
Based on my example, I created a new datasource called "PossibleIDs" which selects the Stocktake_ID values from the same source that the parameter refers to. Depending on whether or not you have replicated values, you may need to use a SELECT DISTINCT query to eleiminate duplicate values from the list.
Then in the parameter for STOCKTAKE_ID, I have allow blank and allow null, no default value, but in the available values, I have Get values from query referring to the PossibleIDs dataset.
Then when I run the report, the report is blank until I select the value I want from the dropdown list which displays all of the possible values.
Reporting services are somewhat of a dark art. Best of luck with your project.
FYI - the Data type in the general tab only refers to the type of parameter control that is rendered in the parameter bar, not to the type of data in your dataset, which is why using the textbox to retrieve an integer works.
FYI - the Data type in the general tab only refers to the type of parameter control that is rendered in the parameter bar, not to the type of data in your dataset, which is why using the textbox to retrieve an integer works.
ASKER
I don't want to use a drop-down here, as there are thousands of possible values. But thanks for the suggestion.
Then go to the Default Values page and select "Specify values".
Click the Add button, then delete the value (Null) which will be entered into the Value field by default. The Value field should be completely empty. Click the OK button.
Now when you run the report, the parameter textbox will be active and empty and the Null checkbox will be unchecked.