0

I am failing to pass parameter value to data set using , =split(Parameters!Status.Value,",")

Parameter (@status)defult values -> C,P enter image description here

Returns Error: The value expression for the quer parameter '@status' contins ans error: Conversion from type 'Object()'to type 'String' is not valid.

Sam Bin Ham
  • 429
  • 7
  • 23
  • You might be approaching this the wrong way. Can you show how the parameters are used within your dataset query? For example is it something like `SELECT * FROM myTable WHERE aField IN ('C','P')` ? Once I know this I can give you a batter answer. – Alan Schofield Oct 16 '17 at 15:31
  • Basically i have 3 values in parameter ('C', 'P' , 'O') , I need to select 'C' and 'P' together and 'O' will be select separately . What you suggest in this case . – Sam Bin Ham Oct 16 '17 at 18:14
  • I still need to know how you use the parameters in your dataset query. Can you edit your question to show this. – Alan Schofield Oct 16 '17 at 19:26
  • as per my parameter , if select close(c,p) the query will be SELECT * FROM myTable WHERE aField IN ('C','P') otherwise WHERE aField IN ('O'). Hopes you clear now – Sam Bin Ham Oct 17 '17 at 08:46

2 Answers2

2

As it looks that the parameter is already a string, what you need to convert it into list is a join function:

=join(Parameters!Status.Value,",")

once you pass the comma separated list to SQL you may need to search the list as discussed here: SSRS selecting results based on comma delimited list

Jayvee
  • 10,670
  • 3
  • 29
  • 40
0

You could do this the way you want but I would approach this differently as it's easier to see what's going on especially if this gets passed to somebody else to debug later.

There are two ways of doing this. The first is to pass in a single value for Close or Open and then add some logic to the dataset

DECLARE @s TABLE(status char(10))
IF @states = 0  -- Closed
    BEGIN
        INSERT INTO @s VALUES('c'),('p')
    END
ELSE
    BEGIN
        INSERT INTO @s VALUES('o')
    END

select * from myTable a
    JOIN @s s on a.status = s.status

I realise it's more code but it's simple to follow.

IF THIS WORKS FOR YOU STOP READING NOW !!

The second option (assuming you don't want your users to just be able to select 'c' or 'p' individually) it a bit more effort to set up only the SSRS side but the SQL is very simple. Don't be put off by the length of the answer it actually pretty simple.

Your dataset query would just look something like this...

select * from myTable a WHERE a.Status in (@status)

In your report design set the @states parameter available values to c, p and o as separate items and make the parameter multi-value.

At this point you should be able to test that the report works and you can select 1, 2 or all three options and get the relevant results, there is no need for joins splits or anything else, it will just work.

Now, as I assume you don't want your users to be able to choose at this level, we need to make some changes.

First add a new dataset called say dsDefaultStatus and set the query to something like

DECLARE @s TABLE(status char(10)) IF @status = 0 -- Closed BEGIN INSERT INTO @s VALUES('c'),('p') END ELSE BEGIN INSERT INTO @s VALUES('o') END

SELECT * FROM @s

You should new have a new @status parameter. Give this two available labels/values such as 'Close'=0 and 'Open'=1 to match the query above.

Now go back to the @states parameter and change the default values to dsDefaultStatus, you can then hide this parameter if required.

As the user selects the Open/Close status in the first parameter, the defaults selection for this hidden parameter will change. When you run the report the values from the hidden parameter get passed to the report.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35