sql serverssrsssrs-2012
IN SSRS, I have a parameter named Comment which I set to Allow multiple values
and specified available values for it.
My problem is that if I select one value, I will get results but if I select more than one, I will get an error.
The problem is probably because I wrote my SQL statement like this:
dbo.tbl_Status.Comments LIKE ('%' + @Comment + '%')
NB: I need to use LIKE and not IN
I found a solution. I did not properly split the values in the stored procedure with a UDF.
To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:
=join(Parameters!<your param name>.Value,",")
This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.
Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.
Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:
WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))
Therefore, SSRS will pass the following value:
@Flag = 'A,B,C'
Then my UDF will parse that string out correctly to:
A
B
C
And populate my @Flag parameter correctly with SELECT * FROM UDF()...
Yes you are correct, you can prove it by showing the result of ExecutionLog in the SSRS database.
I created 2 identical reports based on the Adventureworks database, one with a filter on the dataset for City and one with a parameter in the query for City.
The query for this report is:
SELECT Person.Address.*
FROM Person.Address
And the filter is added like this:
The result of this report is:
The query for this report is
SELECT Person.Address.*
FROM Person.Address WHERE city=@city
There are no filters on this dataset
The result of this report is:
Where you can see the same data is shown, but the behaviour is different since the user has to put in a value for the filter (marked in yellow)
If this is undesired this can be overcome by adding a default value to the parameter and setting the visibility to hidden like this:
and this:
The efficiency of both reports can be proven by querying the executionlog table and views like this:
SELECT [ItemPath], [Parameters], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], [RowCount]
FROM ExecutionLog3;
Which for these 2 reports returns:
+--------------------------+--------------+-------------------+----------------+---------------+----------+
| ItemPath | Parameters | TimeDataRetrieval | TimeProcessing | TimeRendering | RowCount |
+--------------------------+--------------+-------------------+----------------+---------------+----------+
| /Report Project3/Report1 | NULL | 669 | 1878 | 880 | 19614 |
| /Report Project3/Report2 | city=Bothell | 8 | 42 | 4 | 26 |
+--------------------------+--------------+-------------------+----------------+---------------+----------+
So the second method not only fetched a lot less rows (26 versus 19614) but also consumed less time processing and rendering the report.
Best Answer
You would need to change your query in an expression and concatenate the condition using the
JOINexpression on your parameter values.For instance, if you create a report based on the Adventureworks 2014 database you could add a parameter like this:
With some values that appear in the table:
If you then enter an expression for your dataset instead of a query like this:
It would result in what you want when selecting a single value:
Because the concatenated expression is this:
And it would also work when you select multiple values:
Because the expression would result in this:
Full .RDL file can be found on Github Gist and requires a local Adventureworks 2014 database.