ssrs assign parameter values

Question

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)?

Answer 1

Hi , 

• To generate report for both the state  pass value for both bit value 0 and 1 as comma separated string .
• write a function to break comma separated string into column value
• use this function in report e.g. select * from  Tablename where state in ( select state from Fn_dummy()) 

 

---

Gaur

Answer 2

Can you show me some sql that actually does the splitting the comma delimited values? How would I set this up in the parameter area? Do I need to use a dataset? If so, can you show me some of the sql I can use in the dataset?

Answer 3

create Split function like below where your delimiter can be ','

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))

RETURNS @Results TABLE (Items nvarchar(4000))

AS

BEGIN

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

--     ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0

BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Items) VALUES(@SLICE)

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

END

=========================

make the parameter as multi select  and you can use dataset to provide the value 

1. Create a dummy table tbl_table with 2 columns state and Bit
2. Insert your state and bit value in this table 
3. use query Select State,Bit from tbl_Table   for parameter data set
4. Select appropriate Dataset name and bit as value field 
5. 

you can follow this link for more detail 

---

Gaur