locked
ssrs assign parameter values RRS feed

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

    Monday, February 10, 2014 8:23 PM

Answers

  • 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 

    you can follow this link for more detail 


    Gaur


    • Edited by gpshukla Tuesday, February 11, 2014 12:00 AM
    • Proposed as answer by Katherine Xiong Tuesday, February 11, 2014 10:46 AM
    • Marked as answer by wendy elizabeth Tuesday, February 11, 2014 3:42 PM
    Monday, February 10, 2014 10:18 PM

All replies

  • 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




    • Edited by gpshukla Monday, February 10, 2014 9:02 PM
    Monday, February 10, 2014 9:01 PM
  • 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?
    Monday, February 10, 2014 9:11 PM
  • 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 

    you can follow this link for more detail 


    Gaur


    • Edited by gpshukla Tuesday, February 11, 2014 12:00 AM
    • Proposed as answer by Katherine Xiong Tuesday, February 11, 2014 10:46 AM
    • Marked as answer by wendy elizabeth Tuesday, February 11, 2014 3:42 PM
    Monday, February 10, 2014 10:18 PM