Solved

SSRS Parameter Allowing Only One Selection

Posted on 2014-07-25
5
698 Views
Last Modified: 2014-08-02
Hello:

If we run an SSRS report, we can select a value in the parameter and the report previews successfully.  But, if choose more than one selection of choices for the parameter, the preview displays no data.

What do we need to modify to allow for the report to show data for multiple values?  We have this set up for the parameter, but we are not getting the expected behavior.

Thanks!

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40220939
Hi,

If you are using Inline query in your Dataset then simply write query with Where clause having In parameter like

Select * from table where id in (SELECT * FROM  [dbo].[fn_MVParam](@parameter,',')) and in the parameter properties tick allow multiple values

But if you are using Stored procedure you have to use Split and Join.

Split in the Dataset where you have to use function to split the value and in SSRS parameter use Join function with "," Separator ( =join(Parameters!<your param name>.Value,",") ).

The Split function for your SP is here

CREATE FUNCTION [dbo].[fn_MVParam]
   (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @Piece nvarchar(100)
  SELECT @chrind = 1 
  WHILE @chrind > 0
    BEGIN
      SELECT @chrind = CHARINDEX(@Delim,@RepParam)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@RepParam,@chrind - 1)
      ELSE
        SELECT @Piece = @RepParam
      INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR))
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
  END

Open in new window

0
 
LVL 12

Expert Comment

by:Tony303
ID: 40221026
If you created the report in BIDS, open the project and then open the report.
You can then see the dataset.
Look as the properties, look at the filters.

You may see the filter already set to filter the data based upon your parameter.
To get multiple results you need to have the sign change from "=" to "in".
When you use the in clause you also need to have the parameter show without the (0) at the end. (this is the default option when the parameter is chosen).

This is a reasonably hard thing to explain  without screen captures, sorry.

I hope this helps.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 40221531
This article has a lot of useful information about multi value parameters:
http://www.experts-exchange.com/Database/MS-SQL-Server/MS-SQL_Reporting/A_2002-Reporting-On-Data-From-Stored-Procedures-part-2.html

But when you use a query as the source for you data you can use something like this:
Select * from tablename where fieldname in (@multivalueparameter)

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
ID: 40224781
Hello:

I have tried these suggestion but no success.  I did not realize that multi-value parameters are "tricky" when a stored procedure is the data source.

Any other options?

Thanks!

TBSupport
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40225065
Can you tell us a bit more details about your problems. What does the data look like, what data types do the tables have. What is the stored procedure like and how is the parameter set up on the report.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question