February 10, 2014 at 1:31 pm
In an existing SSRS 2008 report, I need to add a parameter called State. The value for the field called state is defined
as a bit. State = 0 is for NE, State = 1 is for MN. I also need to add a selection for both where the user can select either MN or NE states.
Thus my questions are the following:
1. The value for both can be a bit value of either 0 or 1. Thus when assigning parameter values, how can I assign a value for 'BOTH' that means 1 or 0? I can assign NE to mean 0 and I can set 1 to mean MN. However I do not know how to
assign the value for 'both'?
Should I use a dataset to define the value? If so, can you how me the sql I can use to populate the values in a dropdown list box?
2. For the main query to get the values I want, how would I setup the sql? Would I use =@state or would I use
in (@state)?
February 10, 2014 at 2:27 pm
As you can choose both parameter values, you need a multi-value parameter, so you need to work with IN in your main query.
You can use the following query to populate your parameter (to configure in available values for your parameter)
SELECT CONVERT(BIT,1) AS State
UNION ALL
SELECT CONVERT(BIT,0) AS State
Because there are only two values, you can just as well hardcode them into your parameter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 8:50 am
The value for state can be a null value. I cannot change this since the database is setup by vendor software.
Thus can you tell me how to change the query when the state value can be null?
February 11, 2014 at 1:38 pm
What would the desired result be if the state is null? Should selecting either MN or NE bring up NULL values as well as the records that match the selected state? Should only one of the two include the NULL values? Currently, I suspect the NULL values won't be included in the report.
February 11, 2014 at 2:25 pm
The user wants me to display 'NO State' if the value is null. There are cases where the value is NULL since this is based upon vendor software application.
February 11, 2014 at 3:24 pm
What choice of parameters would result in Null values being included in the results? Should they be included no mater what state(s) the user selects, or only if the user selects both states, or only if the user selects a particular state? Once we have them in the results we can easily display them as "No State" using ISNULL or an IIf expression, but before then we need to determine what the user would select that would tell the report to include the No State records.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply