Passing multi-value parameter in stored procedure (SSRS report)

In SSRS reports we can have multi valued paremeters, which means we can select multiple values for that parameter and based on all those selected values result in the report is shown. We can write the SQL query to pass those parameter’s values and populate the data set, but sometimes it is required or to optimize the performance we need to use the stored procedure instead of direct sql query. This stored procedure will be taking the report paremeter as input and returing the result set to populate the data set.

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values as different-2 parameters (as they all are ‘,’ separated). And thus if we do multiple selection (or SelectAll) error would be there, though it will work fine if we select only one value.

error(s): Must Declare Scalar variable @parameter OR number of parameters being passed are more than expected

Example: We will specify the ‘Multi-Value’ option while creating  a report paremeter in SSRS reports. Lets take a report which displays the products information based on the selected colors of the product (Multiple selection). For that lets create a table report which uses a data source to AdventureWorks database. AdventureWorks is the DataSet being used to execute a stored procedure & to get the desired data based on @Color parameter.

EXEC GETProducts @Color

untitled1

 

GetProducts is stored procedure which uses @Color parameter in the WHERE clause.

 WHERE @Color IS NULL OR Color IN (@Color)

Now we create a dataset (dsColor) for populating the drop down list of the color parameter.

 SELECT DISTINCT Color FROM Production.Product

Now we will define the @Color parameter: 

untitled21

  

 

Now if preview the report it will work fine for single value of color, but if we select multiple colors from colors parameters, it will give error. To pass the complete list of selected values of @color parameter as a single value/string we should use a function JOIN().

 In data set properties of AdventureWorks dataset, click on parameters tab and use JOIN() funtion as:

=JOIN(Parameters!Color.Value, “,”)

 This function will join all the color values and will a give a comma separated values thus a single value as a whole will be passed to the SP. We can use any other de-limiter other than ‘,’ if our values already contain ‘,’ .  

untitled31

 

 

        To use this list of comma separated values in the stored procedure we need to split them in a proper way as they are as : ‘value1, value2, value3′, but we need them as ‘value1′, ‘value2′, ‘value3′ to use them in IN clause. So we need to have a UDF function which will take this string of values and will return all them splitted. And then use them in SP as:

 WHERE  @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’)) 

where SplitList is a UDF which takes the list and the de-limiter as input parameters and splits all the values.

About these ads
This entry was posted in SQL Server, SSRS. Bookmark the permalink.

282 Responses to Passing multi-value parameter in stored procedure (SSRS report)

  1. Thanks a lot. Been trying to figure this out for 2 days. I am working with oracle though.

  2. Naveen Sikri says:

    Hi Munish, can we pass null value in multi value Parameter because you have written WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’)) or this is for Null value in select list if any.

  3. Hey Naveen.

    Yes, we cannot select ‘Allow Null Values’ in case we are using Multi Select parameter option in SSRS reports..
    But what I have mentioned in the article, is meant for some value (say ‘All’) which is mapped to Null to be passed in the stored procedure..
    This can be achieved like as:
    1. Add one extra value (say All) to the values in the data set populating the drop down list for the report parameter using Union All
    2. Map this value to DBNull so that when it’s get selected from the drop down, Null is passed to the stored procedure

    And that is why there is checking of @Color IS NULL in the article

    Also this same value (All) can be selected for the default value option to pass Null as default value.

    Thanks for your comments..keep posting

    Thanks,
    Munish Bansal

  4. Angela says:

    Hi Munish,

    Using the JOIN function is just what I needed. However, there seems to be a limit (20 values) as to how many values can be chosen. Any more than that and I get an error. Any way around this? I have the ‘select all’ option in my drop-down already, and checking that selects everything in the list.

    Thank you in advance,
    Angela

  5. Hi Angela,
    It would have been helpful if you would have provided the exact error; you are getting.
    But still, as far as I think it’s giving error because you might have taken the datalength of the parameter very less in the stored procedure. And thus all the values you are selecting from the report is not getting accomodated into that parameter in the SP.
    So try to take the datatype as NVARCHAR(Max) in the SP.
    Also I do not think there is any limitation as far as the number of selection of values is concerned.

    Thanks,
    Munish Bansal

  6. Angela says:

    Thank you for your reply Munish.
    Actually, my parameters are being passed to a user defined function to format the string, called dbo.fn_SplitMulti(@carrier, ‘,’). The variable in this function is a NVARCHAR(MAX)

    I just thought of something else I can try.

    Thank you for your help!
    Angela

  7. Hey Angela,
    It’s still not clear whether you are getting error due to report or there is an issue with your Function; being called from the report.
    So you can probably, check by displaying the values being selected in the text box on the report (using the same Join(Fields!…., “,”) function). If it shows all the selected values correctly in a string format with ‘,’ separated, then you can check your function by passing this same string of values to the function in SQL Server itself.

    Thanks,
    Munish Bansal

  8. Angela says:

    Hi Munish,

    I found the problem. It was something with my data, which I have corrected.

    Thanks for your help!
    Angela

  9. Raj says:

    Hi Munish,

    In SSRS 2008, I am trying to enable and disable parameter in report base on value of another parameter.

    Like there is 3 parameter in report

    one parameter contain 2 values Free range date and Week

    2nd parameter is ToDate and 3rd parameter is FromDate.

    If I select Free range Date then 2nd and 3rd parameter will enabled and when I select week then

    ToDate should be enable and FromDate parameter should be disable

    Please give me reply.

    Thanks,

    Raj

  10. Here are two things to be known before selecting any solution for this:
    1. Do we need an auto post back on selection of the first parameter (Free range date or week) and making other parameters enable/disable as per the values selected value from the first i.e. do we require to automatically enable/disable 2-3 parameters as soon as some value is selected from the first parameter? OR

    2. We just want to have some option there on the Report, so that we can select & disable/enable the optional parameters (like parameter# 3).

     If we need to go with the option#1, then we can achieve this using the Report Viewer in our application and we can have our own drop down lists there on the page.
    For these drop down lists, we can mark “AutoPostBack=TRUE” for parameter#1. So that when some value is selected from this drop down; a post back will occur & we can set the other drop down (enable/disable) accordingly. Then we can pass the selected values of those drop down lists to the report using URL or so.
    Obviously, we will have to handle the query if no some parameters are disabled.

     If option#2 can also do, then we can make parameter#2, #3 nullable by selecting the checkbox for Allow Null. In this case, a check box for NULL will appear along with the parameter & if we want to disable any parameter, we can select that checkbox.

    -Munish Bansal

  11. Raj says:

    Thanks for your reply.

    I am using BIDS to develop the reports, if i go with option #2 its like a manual selection, but i need auto selection to disable the parameter, is any other way to disable the parameters in SSRS 2008, i mean through XML view code for the .rdl?

    Thanks
    -Raj

  12. Hey,
    If you do not have the option to customize the UI, then I would suggest you to make that parameter optional by having some default value in that. So if someone selects some particular value from parameter#1, there is no need to select anything from #2.
    And you can handle it in the query/SP to ignore parameter#2; if some specific value is selected from #1.

    -Munish Bansal

  13. If you feel comfortable in playing with Javascript & handling the events of parameter’s controls, then you can go ahead and put/modify some functions in the javascript file of the SSRS reports provided at the location:
    C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportManager\js\ReportingServices.js

  14. Raj says:

    Hi,

    I have to select the param #2 value based on param #1, how can i modify the .js file in the Reporting
    Services if i change anything in the file other reports wont work properly.plz give some other suggestion its urgent requirement for me. is any other possibility to hide the parameters based on the other param value?

    Thanks
    -Raj

  15. Hi Raj,

    I have found one solution for your problem.
    You can make one parameter’s values to be dependent upon the selection of value from another parameter.
    For ex. in the query used for data set of the second parameter, you can impose the WHERE condition and in that you can provide the value of first parameter as:

    SELECT Week FROM table
    WHERE col1 = @param1
    :where @param1 will be the value selected from parameter1′s drop down list.
    So this way if it does not find any value for the second parameter for some specific values in the first parameter, there would be no value in the second parameter to be selected.
    Also for some default value to be selected from second parameter in this case, you can add a NULL value into that…while creating data set for it.

    Hope this may help you.

    Munish

  16. Zainab says:

    Thanks for help

  17. Rajesh says:

    Hi Munish,

    I recently Started reading your blog. Thanks for all the solutions and articles you are posting. I have a question in SSRS .

    In SSRS , I am using a parameter as Multi value and my default is to select all the values. When I select all the values in the parameter value its showing like

    Paramvalue1,paramvalue2,paramvalue3… .My question is ,is there any way that instead of displaying all the values like that, can we show just ‘select all’.

    Hoping to get a reply from you.

    Thanks,

    Rajesh.

  18. Hi Rajesh,

    Yes, we can do this by adding an hard-coded extra value (‘Select All’) to the parameter list. This can be done by using ‘Union All’ in the select statement being used in the Data set for the parameter.

    Select param1 from table1
    Union All
    Select ‘Select All’

    And in the stored procedure you can check for this ‘Select All’ value in the where clause like:

    WHERE @Param = ‘SELECT ALL’ OR col1 IN (@Param)

    Thanks,
    Munish Bansal

  19. Sima says:

    Thank you, Munish. It was very helpfull and worked from the first try.

  20. Thanks for your comments, Sima.

  21. xtian says:

    hi guyz,

    i have a report generator created on .net 2005 which calls different report that have different parameters.im planning to hide the parameter toolbar on the report so i could just create dropdown lists in the generator after selecting a certain report.how can i pass the selected parameters in my stored proc even if the sequence of the parameters are different in the .rdl file?is this possible?

    tnx for the help..

    xtian

  22. veena says:

    Hi all,

    I am having an SSRS report that has connection with the oracle DB. I have three parameters used there.. But even when I select ‘Allow null’ check box of the parameter and run the report by just giving one value for a parameter, then no records are returned to it…

    Please tell me how to pass the null value to the database..

    i have given the parameters in the where clause of the query
    as field name in (:param name) and this works fine if I give all the three params….

    Thanks for your help…

  23. Hi Veena,

    Assuming there are drop down lists for the parameters you are talking about, so if you need to show & then select a NULL value from a drop down list; you first have to add NULL value to the dataset used to populate that drop down list.
    You can do this using UNION ALL in the select statement. Now this Null value will appear as or with an alias if you are using one. This value can be set as default value whil defining the parameter or can be selected explicitly from the drop down list whilc viewing the report.
    Now in the SP/query you can check if Param IS NULL or column = @param and can show the results accordingly.

    Thanks,
    Munish

  24. Karthik Prabhu says:

    Thank you very much. Very very useful post this is.. Got frustrated with a work. Finally found a solution from you. No words to thank.

  25. victor says:

    Thanks :)

  26. Hey thanks a lot, Karthik.

  27. bnaya eshet says:

    TNX
    very useful

  28. Savan says:

    Hey Munish,

    This is the first post of mine on your blog. Actually i was looking on net that is it possible to pass an Argument from out side of the Report.

    Let me make it more clear. I am using Sql Server 2008. I have this report with Two Parameter passed in an SP i.e. EmpId and EmpType. After Running the SP in report when it get succesfully finished i found that i am in such stage where i need to Provide one more Argument let say “MailID” but this i can come to know only when the user that perticular mail. So if User opens mailID 2 then i need to pass that arguement also.

    Is it possible from VB.Net 2008????

    Kind Regards,

    Savan

  29. Miguel says:

    Hi Munish Bansal!
    my problem is that when i chosse more then one on report just appears the information of first and i dont understand why :S can you help me?

  30. Seth says:

    This is great stuff. Any chance you would send me the splitlist code?

  31. Seth says:

    Sorry, one more question. How larger are you making the varchar variable in your stored procedure to accommodate an unknown amount of listed values? I say that because if we have 2K customers and someone selects 1500 of the 2K customers and assuming our customers are 6char, then our varchar variable would need to be defined as @custid varchar(6*1500) or varchar(9000).

    Does this sound like I’m thinking correctly? Otherwise my variable will truncate the users selection and the results will be inaccurate.

  32. Hey Seth,

    Regarding your question about data length selection, I would say if you are not sure about total length of all the selected values…choose nvarchar(max) in that case.

  33. Hi Miguel,

    I really don’t get the problem you have mentioned…please explain it a bit more..

  34. Savan says:

    Hey Munish,

    Hope u have seen my post……

    Looking forward for reply

  35. Hi Seth,

    there are many ways we can implement this SplitList function, one of them is as:
    CREATE FUNCTION SplitParameterValues
    (@InputString NVARCHAR(max), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(max)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString ”
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    –select @InputString
    END

    RETURN

    END

  36. Hey Savan..
    I am not getting your question…please elaborate it a bit more.

    -Munish

  37. Savan says:

    Let Me make it more clear. I am using SQL Server 2008. for the Report to create Dataset i am using Store Procedure. In store Procedure i am passing tow Arguements the SP is as mantioned Below:

    ALTER PROCEDURE [dbo].[sp_MailshotReports] ( @WorkerID as Nvarchar(4000), @WorkerType as Nvarchar(4000))

    AS
    BEGIN
    SELECT A.WorkerID, A.Name, A.Address, A.Telephone, A.JobCategory, A.CostCentre, A.Postcode, A.JobTitle,
    A.WorkerType, A.Town
    FROM viewMailShotWorkers A
    WHERE (A.WorkerType IN (Select value FROm dbo.ufn_Split(@WorkerType,’,’)) OR ‘ALL’ IN (@WorkerType)) AND
    (A.WorkerID IN (SELECT value FROM dbo.ufn_Split(@WorkerID, ‘,’)) OR ‘ALL’ IN(@WorkerID))
    END

    As you can see i have passed two arguements that is WorkerID and workerType and so in report user will be able to fatch the data on the base of this two arguements.

    Now this report should be such that it will display the Data about the Worker who fall under a specific Department.

    Means in my application when i doubl click any perticular Department all the workers under that department will be listed.

    Now the main point is that i never know which department will be opened so right now i have selected all the workers in my report but i want to make that only the workers who fall under the department which is opened come in reports.

    so as i don’t know which department will be open how can i pass that as an argument?????

    Regards,

    Savan

  38. Shruti says:

    Heyy

    I am trying to publish the Adventure work reports on ASP.NET using the report Viewer control. The reports that have more than one parameter are not working..As in I am able to select values for only one parameter and the others seem disabled though these reports work fine on SSRS. Please guide..

  39. Hi Shruti,

    You have not mentioned whether you are using ProcessMode as Server or Client in the report viewer.
    In case of server mode, there should not be any issue.
    Check if other parameters are dependent upon the selection of the another parameter; as you said some parameters are disabled.

  40. Spandan Das says:

    Hi Munish,

    As per your solution for multiple selection we can add “NULL” value through dataset is correct. but the problem is if you see the report multi select list boxes in browser u will found one “Select all” option getting populated automatically so its not making any sense if in one drop down we have both “Select All” and “All” options.

    So can u suggest something to remove this “select All” option.

    Thanks,
    Spandan

  41. Hi Spandan,

    Thanks for you comment.
    In case we need a NULL value in our multi value parameter, then as I said we can add a NULL value in the dataset and can mark this value by any desired name like ‘UnKnown’ so that when this is selected from the drop down values then in our SP/Query we can check for it.
    Also if we select ‘Select All’ & this all values get selected, then also we can check for this Unkown value.
    I did not get why we need an extra ‘All’ value to be added to the data set then.

    Munish Bansal

  42. HP says:

    Hi Munish,
    Can you please let me know whether we need to make any changes if we need to pass multivalued parameter of type NUMERIC?

    I tried above steps for the mulivalued parameter of NUMERIC type , but it is picking up only the first parameter values?

    Thanks in advance.

  43. Yes HP.
    For any data type, in case of multivalued parameters we have to pass them this way only. Otherwise all selected values would be taken as different – 2 input parameters in the SP; but in our SP we might be expecting only one.

    Munish Bansal

  44. vikram says:

    HI Munish ,
    i try according your post , but it give me following error in
    Report viwer
    Error is “invalid object spiltList”
    for your info here is my SP

    @StartDate datetime,
    @EndDate datetime,
    @County nvarchar(max)

    AS
    SET NOCOUNT ON

    SELECT *
    FROM View
    WHERE
    (ReportedDate > @StartDate)
    AND
    (ReportedDate < @EndDate)
    AND
    countyName IN (SELECT * FROM SplitList(@County, ‘,’))

  45. Hi Vikram,

    To make it working, you must have SplitList function as well. I have provided the script for this function in the comments part of this post. Create this function in your database and try it again.

    Munish Bansal

  46. Mzwai says:

    Hi Munish

    When selecting one value everything works but when selecting Multiple values nothing returns and there are no errors. I suspect it might have to do with the Split function, could you please look at it and see what i have done wrong.
    Create FUNCTION [dbo].[SplitList]
    (@InputString NVARCHAR(max), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(max)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString =’,’
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    END
    RETURN
    END

  47. Mzwai,
    Are u passing proper parameters to the SplitList function? I mean it expects two parameters, one the list of values & second is the delimiter used to separate out those values.

  48. dbenn says:

    This worked perfectly for me. Thank you for posting it. You rock!

  49. Thanks for your comments, Dbenn.

  50. John says:

    Hi Munish,

    Great Blog! I’m hoping that you or others on the blog can help me. I’m a relative newbie to SSRS/MVReportViewer, & .net. I have a vb.net app that will set report parameters and send them to a “ReportViewer” widget that will in turn run a remote report that was created in VS2005 and uses SQLServer2005.

    I’ve been spinning my wheels for days on some of the following questions:

    - In my report query I have an IN clause (IN (@account_list)) that is setup as a multi-value string parameter. As everyone else, in my application when I set the report parameter “account_list” to one value it works perfectly, more than one, it fails. I have tried many formats for the list – “1,2,3″ or “’1′,’2′,’3′”,………. Now I noticed that you mentioned that the a spliter is needed for stored procedures. I am not using a stored procedure. My report query was created in VS2005 in the data tab of my report project. Do I need a spiter for this report? Interestingly enough, when I run the report by itself in preview mode and input multiple values from the report parameter area on top for account_list it works.

    - I’m also running into problems with the UDF function spliter. I created the function in SQL Server and it works fine. I put it into my select statement as such – “IN (SELECT item FROM dbo.fnSplit(@account_list, [,]) AS fnSplit_1)”. When I run the report I now get several errors. I get the “must declare the scalar variable “@accountlist” and I also get one for another parameter for some strange reason. Since this is not a SP, I’m not sure how to resolve.

    I have other questions as well, but these are the one’s that are driving me crazy at the moment. I can provide more detail if needed or my rambling is unclear.

    Any advice would be greatly appreciated!

    John

  51. Hello John,

    As you have mentioned that you are not using any SP to pass the selected values & get the result, thus you need not to use any function (SplitList) as well.
    When we use multi-value parameter in SSRS report & use the same in the data set query using IN clause, it automatically handles the query for all the selected values of the multi-valued parameter.

    The logic implemented in the above article is needed if we are using a SP to fetch the result, passing all the selected parameter values. In that case we explicitly have to join the selected values (multi-values) before passing them to SP &amp ; then we have to use a function (SplitList) to split them out in our SP.

    Hope this will resolve your issue.

    Thanks for your comment.

  52. badri says:

    When i try to deploy my report i am getting a POP UP saying reporting services login (username and pwd)… I am the SA, its not taking my password…
    target server: http:\\localhost\reportserver

    Can you help me out?

  53. Robert says:

    Hi, Munish, thanks for already reminding me of the SP variable size!

    Now, I’m trying to use your Split code to create a function but get errors.

    What is this line supposed to say:

    WHILE @InputString ”

    Also I get this error:

    Msg 102, Level 15, State 1, Procedure SplitParameterValues, Line 17
    Incorrect syntax near ‘–’.

    Thanks in advance for any help.

  54. Hi Robert,

    This error might be due to the reason that you are having some variable/fields in the SP with ‘-’ in their name. So write them in [] e.g. [variable-name].

    Thanks for your comments.

  55. badri says:

    Can anybody help me out?

  56. sasi says:

    Is there a way where we could get dynamic columns in ssrs 2008 based on the multivalue parameters selected.Say if we select 3 mutlivalue parameters then 3 columns corresponding to these 3 parameters should appear.If I select 5 parameters then then 5 columns should appear in the report.

    Is there anyway to do it

    Thanks

  57. @sasi,
    Yes, you can make your query to give output result/columns as per the selected values from multi-value parameter. And a matrix can be configured to display those columns which automatically takes care of dynamic (varying number of) columns.

    Thanks,
    Munish Bansal

  58. Sasi says:

    Munish,
    Thanks for the reply.But can you please explain in brief how you can configure matrix to take care of dynamic columns

    Thanks

  59. Sasi,
    By dynamic columns I meant if we are getting multiple values of a column & we want to display them in the column headers. e.g. for a month column we are getting values like Jan, Feb etc which we want to dynamically bind in the matrix. Matrix will automatically takes care of it using column groups.

  60. Logi says:

    Hi Munish,

    Is there any alternate solution to pass ‘Allow null’ value for multi select input parameter. That is if we select null, we should not be able to select other value.

    Logi

  61. Ahmed IG says:

    SUPER SUPER SUPER… Just what I needed and spent a whole day searching for a workaround.. Thanks a billion!!

  62. Thanks for your comments, Ahmed !

  63. Priya says:

    Munish,

    I have a question in SSRS report, that I am designing using BIDS, I am not doing any coding.

    I have to show the Date Selection Prompts either as Calendar Dates or as Year and Month.

    For this I need a initial Promt saying

    Calendar Format ( values 1. Date and 2. Year/Month)

    1. Now If I select Date – then It should show me FROM and TO DATE Prompts

    2. But If I select Year/Month Then It should not diplay From/To prompts, instead should display Year promt and Month prompt.

    I am unable to hide other prompts in this even if I select Date or Year/Month, All four prompts are always visible.

    Please reply
    Thanks in Advance.

  64. Hi Priya,

    As I have already answered the same in the above posts, you can implement in SSRS defining some dependent parameters. You can prompt two boolean parameters (1. Date, 2. Year/Month) and make other parameters (From/To) dependent upon the selection of boolean (checkboxes) parameters.
    And in the query you can handle them accordingly.

    Thanks,
    Munish Bansal

  65. Priya says:

    HI Munish,

    Thanks for the Reply!!!!!

    I am doing something like that rite now, but now when I select Format as Date –
    It immediately shows NULL in Year and Month and I will select dates from the Calendar.

    But The Year Month are enabled showing NULL.

    When I select Year/Month then From and TO shows NULL and I just don’t select the dates.

    But I don’t have any Checkbox prompts as I dont know how to create Checkbox prompts, All are drop down based on dataset values.

    Can you help me with creation of Checkbox prompts using BIDS SSRS.
    Thanks
    Priya

  66. Subodh says:

    Hi Munish

    Thanks for the posts… they are quite helpfull. One query to autohide the columns on the basis of dynamic parameters.

    Suppose I have a parameters (slabs) i.e. I want to show figures on the basis of some calculations in slabs and I have that dynamic parameter. i.e. If I choose 2 slabs then only the designer should autohide the other slabs (suppose the maximum slabs are 6 on the designer/layout). Is it possible in SSRS to autohide the un-required columns.

    best regards
    Subodh

  67. Chetan Patel says:

    1.SP

    ALTER proc [sp_select_MatchCashDocNo]
    @docno varchar(1000),
    @Date datetime
    as

    select * from cashtrans where Doc_No in (@docno) and
    convert(varchar(10),@Date,112)
    convert(varchar(10),date,112)=@Date

    2.Query
    select * from cashtrans where
    (convert(varchar(10),date,112)= convert(varchar(10),’20090701′,112))
    and (doc_no in (’522′,’1082′,’1085′,’58′))
    and foid=123 and applicationType=’a’order by DOc_no

    My problem is query is working fine but in sp i have pass the docno =’522′,’1082′,’1085′,’58′ so it is not working in sp .so how can it possible in sp?

  68. jgpatel says:

    Hi I have 1 problem . I am using SSRS 2008 and ORacle10g as my back end . I have to pass Multivalue parameter from SSRS report to Oracle10g Stored procedure .

    So i have created Function which will create list

    param1= ‘a,b,c’
    output value from Function will be ‘a’,’b’,’c’

    But problem is when ever i am trying to use this function in
    where clause of Stored procedure then it is not giving me result

    Select * from table table_field in (stringtotable(param1)

    is this the way to call function ??

    even i am using
    Select * from table table_field in (stringtotable(‘a,b,c’) then also it is not giving me result

    but if i run function alone from Toad for oracle select function right click execute function then give values then it is working fine ..
    CREATE
    OR REPLACE FUNCTION PATELJA.stringtotable(param1 IN varchar2) RETURN varchar2 IS
    modparam
    varchar2(100);
    startvar
    int;
    endvar
    int;
    incr
    int;
    BEGIN
    startvar
    :=1;
    endvar
    :=1;
    incr
    :=1;
    While startvar>0
    loop
    select instr (‘param1′,’,’,1,incr) into endvar from dual;

    if
    endvar
    0 then
    select modparam ||””|| trim(substr (‘param1′, startvar,endvar-startvar))||””||’,’ into modparam from dual;
    incr
    :=incr+1 ;
    startvar
    :=endvar+1;
    Else
    select modparam ||””||trim(substr (‘param1′, startvar,length(param1))) ||”” into modparam from dual;
    –DBMS_OUTPUT.PUT_LINE(‘Final Result String is ::: ‘ || modparam);
    EXIT;
    End if ;
    –insert into string1 values(modparam); — if we can return Table varible
    End
    Loop;
    return
    modparam;
    END
    stringtotable;

  69. Hey Jgpatel,
    You have to put it like :
    Select * from table
    WHERE table_field in (Select * from dbo.stringtotable (@param1))

    It will work now; provided others things are configured properly.
    ‘dbo’ is the schema of the function.

  70. @Chetan Patel

    The problem is with the way you are passing values to the @docno variable. If you are passing it as a single string of values, then it would not work. So use a function to split all these values to use them in the ‘WHERE IN’ clause.
    I have already stated the use of splitList function for the same purpose.

  71. Charles Marsh says:

    I liked your strategy, but I can’t seem to find where to get the “In data set properties of AdventureWorks dataset, click on parameters tab …” If I go to the queries, all I see are the two queries that I built. If I go to Reports on the Menu bar and select Reports and then select Parameters all I see are my parameters. I just don’t see how to get the control box that you presented to use the join function.

    Let me know anything that you can.

    Thanks.

    By the way, you have some great solutions. Keep them coming.

  72. jgpatel says:

    Hi Munish

    Thanks But it is not working ….

    Select * from jde_dw.ps_jwd_bus_unit
    where mm_bu_division_cd in (Select * from patelja.Stringtotable(:par_division));

    where :par_division is parameter ( Oracle10g)

    it is saying missing right parenthese

    here my function is returing a Vaarchar2 instead of Table Variable so is that ok ??

    Can u pls send me Sample function and how to use in Stored procedure for this problem in Oracle10g so i can create

  73. Hey Charles,

    Thanks for your comments!

    Regarding your question, you can find that dialog box to put the JOIN function in the dataset properties.
    1. Click on eclipse () in the data tab to edit the dataset properties.
    2. Click on the Parameters tab of the dialog box of Dataset.
    3. Put the Join Function and map it to the respective parameter.

    Thanks,
    Munish Bansal

  74. Charles Marsh says:

    I have tried to create your function SplitList:
    CREATE FUNCTION SplitParameterValues
    (@InputString NVARCHAR(max), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(max)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString = ‘,’
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue

    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))-select @InputString
    END

    RETURN

    END

    but I get the error:
    Msg 156, Level 15, State 1, Procedure SplitParameterValues, Line 18
    Incorrect syntax near the keyword ‘select’.
    and I can’t figure out what the issue is.

    Also, is there a way to debug the function to insure you are getting what you expect and everything is working as expected?

    Thanks for your previous help. I knew it would be something simple.

    Charles

  75. Charles Marsh says:

    This is the table function that I created:
    ALTER FUNCTION [dbo].[SplitList]
    (@InputString VARCHAR(max), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param VARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue VARCHAR(max)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString != ‘,’
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue

    IF LEN(SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString), LEN(@InputString) – CHARINDEX(‘,’, @InputString))) = 1
    BEGIN
    SELECT @InputString = ‘,’
    END
    ELSE
    BEGIN
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    END
    END

    RETURN

    END

    This is what I have used to test it:
    select * from SplitList(‘A,b,c’,’,’)

    And this is the error that I am getting:
    Msg 536, Level 16, State 5, Line 1
    Invalid length parameter passed to the SUBSTRING function.

    Any ideas?

  76. Charles Marsh says:

    One last comment, why is SplitChar passed but never used?

    Charles

  77. Charles Marsh says:

    Got it all working.

    Thanks for the guidance. Once more question.

    I am getting this error:
    [rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox960’ contains an error: Value cannot be null.
    Parameter name: Argument ‘Number’ is Nothing.

    My textbox has this formula:
    “IT IS FURTHER ORDERED THAT THE DEFENDANT SHALL BE ALLOWED A TOTAL OF ” +
    iif(First(Fields!credit_time_served_days.Value, “OPCrditTime_RS”) > 0 AND First(Fields!credit_time_served_days.Value, “OPCrditTime_RS”) < 9999,
    Str(First(Fields!credit_time_served_days.Value, "OPCrditTime_RS")),
    + "DAYS AS";

    I know that when there are no records returned, the value is NOTHING, but I don't know how to get rid of this error. Nothing I have tried has helped. Do you, by any chance have an idea?

    Charles

  78. Hi Charles,
    You have to comment out the second
    ‘select @InputString’ statement. This is there as ‘-select @InputString’.
    This will work now.

  79. Hi Charles,

    Please put one more condition in the While Loop as:

    WHILE @InputString ‘,’ AND @InputString ”

    Now this will work fine for you.

  80. @Charles,
    Here I did not use this variable (@SplitChar), assuming that values are comma separated. But if you fell that you are having some other delimiter and you don’t want to hard code this in the SP, you can pass that character to this variable. And in the SP, you have to replace all the commas (,) being used in the Substring functions with this variable (@SplitChar).

    Thanks,
    Munish Bansal

  81. Hey Charles,

    In this case, you should first check for its value as Nothing by putting a condition:
    IIF(param != NOTHING, then put all your conditions).

  82. Andy Vincent says:

    Hi, this is very useful, but i have a question re.:

    2. Map this value to DBNull so that when it’s get selected from the drop down, Null is passed to the stored procedure

    How is this achieved, please?

    My code:
    SELECT DISTINCT DiscountName
    FROM DiscountsApplied AS D WITH (nolock)
    UNION ALL
    SELECT ‘No discount’
    ORDER BY DiscountName

    Regards,
    Andy

  83. superSQL says:

    When I try your exact example I get the error message ….. Invalid object name ‘SplitList’. Please help

  84. @superSQL

    You need to first create the function Splitlist which has already been provided in the same thread of comments.

    -Munish Bansal

  85. kavita says:

    Hi Munish,

    I have created a report for multiple selection.Iam able to select the multiple options from report but iam not getting data .if i select single parameter iam getting data.i could’t find the reason.can you please help me out in this

    Thanksinadvance
    kavita

  86. jgpatel says:

    I have a subreport embedded in a detail cell of a main report. I want the rows in the main report to alternate colors, how can I get the subreport cell to alternate colors also.

  87. jgpatel says:

    (1)How can i use 2 different Dataset in 1 report wihtout using Sub report Concept ??

    i.e. in Main report one of Report filed i need to use difererent Datset values based on Parameter and report field value from main report .. so pls help me …

    (2)Main Report (Group level) Sum(FinalPayment) ** Completed

    SubReport Sum(PaymentAdvances) ** Completed (passing parameters)

    This is the part that I need help. I need to use an original value from the main report and a “return” value from the subreport.

    Main Report (Footer) Sum(FinalPayment) + SubReport.Sum(PaymentAdances)

    Is it possible to pass a return value back to the main report from a subreport?

  88. Hi Jgpatel,
    You can make use of RowNumber() function in the expression for the color property of the detail row like:

    =IIF(RowNumber(“grp_Scope”) MOD 2, “BLACK”, “GREEN”)

    The same way you can put it for the subreport as well.

    Munish Bansal

  89. jgpatel says:

    Hi Munish

    thanks but No it is not working …

    in main report i have alternate color for each row… but for 3 report field i am gettting data from sub-report so i have tried to change color itself in Sub report but it is showing same color for all rows from Sub- report .

    thanks
    jgpatel

  90. Karen Roslund says:

    Munish,
    I am trying to do this 3 mulitvalue parameters but i am not sure how do it…

    I have used your approach to for one parameter and it work perfectly… any ideas as to how do the same for more than 2 parameters..

    Thanks,
    Karen

  91. Hi Karen,
    It does not make any difference how many parameters you are having of this kind. For each parameter (multivalue) you can use the same method.
    e.g. if you have three parameters then your SP should accept three parameters and inside the SP handle each parameter the same way which we deal with in case of single multivalue parameter.

    Thanks,
    Munish Bansal

  92. superSQL says:

    Great info. However, the splitlist function does not seem to be working when I pass it a value with an apostraphe. For example (Tiger’s). Any idea what I need to do.

    Thanks for your help!

  93. @superSQL,

    It should work even in case of value as Ice’2, without any twiking.

    thanks for your comment !
    Munish Bansal

  94. Jill says:

    Hi Munish! First, I would like to say that I find your postings very helpful. I hope you continue to post.

    Maybe you can give me some insight on the following. I have tried your way above, but it doesn’t seem to work if the parameter is an int value. My stored procedure will not return data unless I have an int parameter.
    Perhaps you may know why this is.

  95. Hemanth says:

    I have used Varchar Max in the Split function but when i choose the Parameters. The list shown does not contain all that matches the condition.

    I tried running the splitfunction from sql query wizard and result is the same .

    Please help.

  96. krushna says:

    Hi,

    My report calling a stored procedure.

    I have this problem using multivalued parameters in SSRS.
    i am trying copy and paste custom values in to my multivalue parameter from excel. when i do that i am keep getting an error called must declare scalar variable. if i pass the values comma delimited(1234,1345,1456) it works fine. but if i paste it from my excel like
    1234
    1345
    1456

    i am keep getting the error.
    i tried removing char(13) from the values in the stored procedure, but no luck.

    i am using split function in my stored procedure to split the values by comma.

    i tried the horizontal values from my excel which has a tab between the values and i removed char(9) from the values then it worked fine.

    but i dont know what to do for the vertical values from excel.

    please help.

  97. krushna says:

    ok

    i got it guys. i was doing it as a commandtext on the SSRS instead of storedprocedure in my designing.

    which was causing the problem, once i replaced it as a stored procedure.

    it worked fine.

    any way thanks.

  98. Hi krushna,

    I am really not getting how you are copying list of values from an excel sheet and pasting them into the multi-value parameter since muti-value parameter allows to select multiple checkboxes to select values.

    Thanks for your comment..!

    Munish Bansal

  99. Thanks a lot, krushna.

  100. krushna says:

    Munish,

    it allows you to enter custom values if you check the multi value checkbox and make the available values as “Non query” instead of “From Query and giving a dataset” for the parameter.

  101. Thanks for the information, krushna.

  102. Cami Paulson says:

    I have a slight problem with this functionality. For some reason, my parameter is only recognizing the first item in the list.

    With this portion of the code, I have selected 4 Major codes from the parameter list, but it only returns data for the first code in the list.

    @Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(@Major,’,’))

    If I replace the parameter @Major in the code with ‘A,G,I,J’ as below, it returns all items with no issues.
    @Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(‘A,G,I,J’,’,’))

    What is the problem? I know the function TL_SplitList works as when i type SELECT * FROM TL_SplitList(‘A,B,C’,’,’) it returns 3 rows. Why isn’t my parameter being recognized?

    I have the DATASET PARAMETERS for the @Major parameter =JOIN(Parameters!Major.Value,”,”) – I’m just not seeing the issue.

    any help would be great!
    Cami

  103. jgpatel says:

    Oracle 10g is back end, SSRS is report

    An error occurred during client rendering.
    An error has occurred during report processing.
    Query execution failed for dataset ‘DataSet_Stockpile’.
    ORA-01460: unimplemented or unreasonable conversion requested ORA-06512: at “PATELJA.SP_STOCKPILE_QA_6″, line 22 ORA-06512: at line 1

    here is the code for function which split comma seperated values as i have multivlue paramters
    —- Function STRtoTBL

    CREATE OR REPLACE function PATELJA.str2tbl( p_str in varchar2) return myTableType
    as
    l_str long default p_str || ‘,’;
    l_n number;
    l_data myTableType := myTabletype();
    begin
    loop
    l_n := instr( l_str, ‘,’ );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) :=
    ltrim(rtrim(substr(l_str, 1, l_n – 1)));
    l_str := substr( l_str, l_n+1 );
    end loop;
    return l_data;
    end;
    /

    –procedure in which i am using this above function

    CREATE OR REPLACE PROCEDURE PATELJA.multi(
    par_busunit IN varchar2,
    op_data OUT sys_refcursor
    )
    IS
    BEGIN
    OPEN op_data FOR
    SELECT DISTINCT j_bus_unit_srccd
    FROM jde_dw.ps_jwd_bus_unit where ( j_bus_unit_srccd IN (
    SELECT *
    FROM TABLE
    (CAST
    (str2tbl (par_busunit) AS mytabletype
    )
    ))
    — OR par_busunit = ’0′
    )
    AND j_bu_type_srccd IN
    (’01′,
    ’02′,
    ’03′,
    ’04′,
    ’05′,
    ’06′,
    ’07′,
    ’08′,
    ’09′,
    ’10′,
    ’11′,
    ’12′,
    ’14′
    ) and J_COMPANY_ID ’09999′
    ;
    END multi;
    /

  104. jgpatel says:

    how many maximum number of paramter we can pass to Stored procedure from multivalues parameters

  105. Cami Paulson says:

    Munish,

    Were you able to help with my earlier post where for some reason the parameter is only returning data for the first item in the multi-value list?

    Thanks,
    Cami

  106. David Richardson says:

    You are the BOMB! Thanks. I have been pulling my hair out trying to figure this out… Other web pages have shown me bits and pieces, but yours was the only step-by-step example I’ve found.

  107. Isondart says:

    Munish,

    Great article thanks, very helpful.
    Have you tried using Dynamic SQL as an alternate solution?

    Regards

  108. Hi Isondart,
    The idea is to make use of stored procedure so that we can modify it at any point of time without opening up the report or so. And thus this article walks us through as how we can pass the multiple selected parameters (dynamic number of parameters) to an SP.

    Thanks for your comment..!

    Munish Bansal

  109. Hi Cami,

    Please provide me some more details like query/SP or so that I may help you on this.

    Thanks,
    Munish Bansal

  110. Isondart says:

    Munish,

    Yes, I understand. I did exactly that, as far as building the stored proc; what I did was create a stored procedure that takes the output from the SSRS report. I used the JOIN expression from your article to pass the data to the stored proc.
    I then use T-SQL’s REPLACE function to replace the commas with a comma surrounded by single quotes.
    This changes the data from ‘value1, value2, value3’ to value1, ‘value2, ‘value3.
    Then I build my Dynamic SQL statement including the values
    IN (”’+@SetOfValues+”’)
    and execute the entire statement
    exec sp_executesql @Query

  111. Nikita Mironov says:

    Thanks a lot! I’ve been looking for the solution for 2 days!!!

  112. Thanks for your comments, Nikita.

  113. jazzy says:

    Great!! A useful tip which has saved lot of time. I spent hrs to get this right and was able to finish it after I hit this site.

    Keep adding useful tips!!

    thanks a lot!

  114. Thanks for your comment, jazzy !

  115. Cami says:

    Munish,

    Sorry for delay, finally getting back to my issue. Here is the stored procedure. It doesn’t seem to catch the list from the parameter.

    alter procedure [dbo].[TL_usp_MarginAnalysis]

    @StartDate datetime
    ,@EndDate datetime
    ,@Type int
    ,@Major varchar
    as

    IF @Type=1
    BEGIN
    SELECT * FROM TL_RPT_MarginAnalysisSummary
    WHERE (Region=’10′ OR Region=’24′ OR Region=’30′ OR Region=’35′ OR Region=’40′ OR Region=’52′ OR Region=’53′ OR Region=’59′ OR Region=’60′ OR Region=’89′ OR Region=’91′ OR Region=’92′ OR Region=’94′ OR Region=’95′)
    AND (InvoiceDate BETWEEN @StartDate AND @EndDate)
    AND (@Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(@Major,’,’)))

    END

    ELSE IF @Type=2
    BEGIN
    SELECT * FROM TL_RPT_MarginAnalysisSummary
    WHERE (Region=’96′ OR Region=’90′ OR Region=’85′ OR Region=’99′)
    AND (InvoiceDate BETWEEN @StartDate AND @EndDate)
    AND (@Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(@Major,’,’)))

    END
    ELSE IF @Type=3
    BEGIN
    SELECT * FROM TL_RPT_MarginAnalysisSummary
    WHERE (Region=’98′)
    AND (InvoiceDate BETWEEN @StartDate AND @EndDate)
    AND (@Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(@Major,’,’)))

    END
    ELSE IF @Type=4
    BEGIN
    SELECT * FROM TL_RPT_MarginAnalysisSummary
    WHERE (Region=’10′ OR Region=’24′ OR Region=’30′ OR Region=’35′ OR Region=’40′ OR Region=’52′ OR Region=’53′ OR Region=’59′ OR Region=’60′ OR Region=’89′ OR Region=’91′ OR Region=’92′ OR Region=’94′ OR Region=’95′ OR Region=’96′ OR Region=’99′)
    AND (InvoiceDate BETWEEN @StartDate AND @EndDate)
    AND (@Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(@Major,’,’)))

    END
    ELSE IF @Type=5
    BEGIN
    SELECT * FROM TL_RPT_MarginAnalysisSummary
    WHERE (Region=’10′ OR Region=’24′ OR Region=’30′ OR Region=’35′ OR Region=’40′ OR Region=’52′ OR Region=’53′ OR Region=’59′ OR Region=’60′ OR Region=’89′ OR Region=’91′ OR Region=’92′ OR Region=’94′ OR Region=’95′ OR Region=’98′)
    AND (InvoiceDate BETWEEN @StartDate AND @EndDate)
    AND (@Major IS NULL OR Major IN(SELECT * FROM TL_SplitList(@Major,’,’)))
    END

    Thanks,
    Cami

  116. Hi Cami,

    There is a very small problem within your SP.

    In the very last parameter @Major, you have not mentioned the size of varchar variable.

    It’s declared as:
    @Major varchar

    It should be like:
    @Major varchar (max)

    And that’s because it always takes only single char out of the whole string; if you use this parameter.

    Hope this will resolve your issue.

    Thanks,
    Munish Bansal

  117. Cami says:

    That did it!! THANKS SO MUCH!!

  118. Cami says:

    ok… i can run the stored procedure from development studio in the data tab with no issues. but when i run fro preview, it states…

    query execution failed for data set ‘MarginAnalysis’
    must declare a scalar variable “@Major”

    i’m not understanding why it runs from one spot but not when previewing the report. if i remove this parameter from the report and from the stored procedure, everything runs fine.

    any help would be great

  119. Hoflieferant says:

    Thanks Munish!

    You just saved my job ;-)

  120. Thanks for kind comments, Hoflieferant !

  121. Bhaskar says:

    Hi Munish,

    I have complex error with multi value parameter

    i have a report which has 4 parameters all are multi value out of which 3 parameters bound to database and 4 th parameter is user entered. how to bind 4th parameter with others.

    vendor Product cost count
    Hp x 100 error
    Hp y 200 error

  122. Hi Bhaskar,
    I did not get exactly what you meant by binding the 4th parameter with others.
    Do you want to impose some condition on what value(s) should a user be allowed to enter in 4th parameter?

  123. Diego says:

    this sp work
    create PROCEDURE [dbo].[sp_users]
    @User varchar(3000)

    as
    CREATE TABLE #User ([User] [varchar](50) NOT NULL)
    set @User = ltrim(rtrim(@User))+ ‘,’

    while (CHARINDEX (‘,’,@User )>0)
    begin
    insert into #User ( [User] ) select substring(@User ,1,CHARINDEX (‘,’,@User )-1)
    set @User = substring(@User ,CHARINDEX (‘,’,@User )+1,len(@User))
    end

    select
    a.UserName
    from
    UsersTable a with (nolock)
    join #User u with (nolock) on a.User = u.User

  124. Brian Blessed says:

    Thanks for your help with this.

  125. Thanks for your comments, Brian.

  126. Julio Caldas says:

    Munish,
    thanks for your help from Brazil !!
    It’s worked fine!!

    cheers,
    julio

  127. Thanks to you too, Julio.

  128. Dragon says:

    Hi Munish,

    Do you have any suggestion how to create dynamicаl columns according values, selected from a multivalue parameter? I.e. if select 10 are values are selected – we must get 10 columns. Thanks.

    Cheers,
    Dragon

  129. Ruth says:

    Hi Munish,

    I have a report that needs to pass a multi-value parameter to a 2nd report when you click on an image. I can send single parameters to the 2nd report, but when i try to send all of them, it doesn’t work. I have tried everything I can think of in the “What here?” portion of the IIF statement below, but everything I’ve tried leaves the parameter box blank on the 2nd report.

    =IIF(Fields!phase.Value = “Z”, “What here?”, Fields!phase.Value)

    Any ideas?

    Thanks,
    Ruth

  130. Hi Ruth,

    While passing the mutiselected parameter values to another report, first use Join() function. Join them using some delimiter say comma (,) and then receive that string of all the selected values in a parameter of type String/text in another report.

    Thanks for your comment.
    Munish Bansal

  131. Ruth says:

    I’m not selecting values though. In Report 1, there are several images, each one linking to Report 2 and passing different parameters. For one of the images, I need to send multiple values to one of the parameters. So, if the phase.Value = “Z”, then I need to send I,E,C,T,P to the next report, otherwise, I just want to pass the phase.value. It works fine for passing one parameter. I have tried lots of different things, including the Join() function, but when I click the image and go to Report 2, the Phase parameter is blank.

    In my mind, the following should work, but it doesn’t. What am I missing?

    =IIF(Fields!phase.Value = “Z”, “I,E,C,T,P”, Fields!phase.Value)

    That IIF statement is in the “Action” tab which indicates that the image hyperlink should go to Report 2.

  132. Hi Ruth,

    Rather than using this expression under Action, you need to use it on Parameters option. Just add one parameters under ‘Parameters to run the report’ on Action tab and put this expression in the value box for that mapped parameter.

    Hope this works.

  133. Ruth says:

    Hi Munish,

    Sorry I didn’t explain myself correctly. That expression IS in the ‘Parameters to run report’ area. It’s in the value box for the mapped parameter, but when I run the report, the associated parameter on Report 2 is blank when I click the image that should pass multiple parameters. I’ll keep looking for an answer. Thanks!

  134. Diana Bodell says:

    Thank you so much for the really clear explanation and example. This solved a very frustrating problem. You couldn’t have explained it better!

  135. Thanks for ur comments, Diana !

  136. Sam says:

    Hi Munish,
    I came across this Forum, and i do have exactly same issue, i tried this with your kind help using the following,
    “WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’)) “.
    But i am getting this error: “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.”

    Your help will be appreciated.

    Thanks
    Sam

  137. Hi Sam,

    If you are also using the same kindda syntax wherein we are using IN clause and looking into all of the selecting values, there should not be any issue.
    It happens when in the sub query we are selecting more than one value but on the left side we are not using any keywork/clause to handle multi selection.
    Hope this helps…if not pls share the exact query you are using.

    Thanks,
    Munish Bansal

  138. Sam says:

    Hi Munish,
    This is the SP i am using and i have inserted your statement into the parameter section but still the same error. Please have a look into it.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROCEDURE [dbo].[usp_CMV_AccidentByFactor]
    @startDate datetime, @endDate datetime,
    @CMV_Inv varchar(20), @Factor varchar (50)
    AS
    SELECT a.INCID_NO,a.CRASH_DATE,a.CMV_INVOLVED,b.UNIT_NO, c.FACTOR
    ,b.VEH_UNIT_TYPE, b.VEH_YEAR,b.VEH_MAKE, b.VEH_MODEL
    ,a.STREET_BLOCK_NO, a.STREET_NAME, a.STREET_TYPE
    ,a.STREET_SUFFIX, a.STREET_CODE, a.CRASH_WEEK_DAY
    ,a.LIGHT_CONDITION,a.ROAD_SURFACE_TYPE, a.WEATHER,a.SURFACE_CONDITION
    FROM ACC_ACCIDENT a LEFT OUTER JOIN ACC_VEHICLE b ON a.INCID_NO = b.INCID_NO LEFT OUTER JOIN ACC_FACTORS c ON b.INCID_NO = c.INCID_NO AND b.UNIT_NO = c.UNIT_NO
    WHERE (a.CRASH_DATE BETWEEN @startDate AND dateadd(dd, 1, @endDate)) AND (a.CMV_INVOLVED IN (@CMV_Inv))
    AND (@Factor IS NULL OR Factor IN (SELECT * FROM dbo.fn_Split(@Factor, ‘,’)))
    –AND (c.FACTOR IN (@Factor))
    Order By a.incid_no,a.CRASH_DATE

    Thanks
    Sam

  139. Hi Sam,

    The issue seems to be with the statement:
    (a.CMV_INVOLVED IN (@CMV_Inv))
    Here @CMV_Inc might be causing the issue, so check with this.

    Also I would suggest you to use @Factor etc with NVARCHAR(max) to
    accomodate the larger no. of values since it’s meant for multi-selection.

    -Munish Bansal

  140. Sam says:

    Hi,
    I am using single value for @CMV_Inv at the moment, anyway i even tried by removing @CMV_Inv parameter and kept only @Factor from the statement but still the same error. I have even used nvarchar(max). Just for your knowledge i am using Reporting Services 2005.

    Thanks

  141. Hi Sam,

    Then probably your function fn_split must be returning multiple columns. If that is the case use correct column name instead of * in the SELECT * FROM dbo.fn_Split(@Factor, ‘,’) statement.
    Also in the statement ‘NULL OR Factor IN’ use proper table suffix for the Factor column like c.Factor or so.

    Thanks,
    Munish Bansal

  142. Nik A says:

    Hi Munish,

    We’re using SSRS 2008 and BIDS –

    I have a report with 1 parameter and supplied it a default value.

    When the report is requested it runs immediatley without prompting for the parameter and when it finishes it lets you enter a new value for the parameter and you can get that report out.

    Do you know of a way of not running the report immediately when requested so the user is prompted / can confirm the default parameter value?

    If I remove the default value I get prompted but have no default.

    Thanks for your help
    Nik.

  143. Hi Nik,

    Since there is no such built-in option provided in SSRS 2008, I would suggest you to create one more parameter (say Confirm) with DataType as ‘Boolean’ and available values as ‘Yes’ or ‘No’ etc. Don’t give any default value to this (Confirm) parameter.

    So now irrespective of the default value in the first parameter, user has to select/confirm the second parameter & then hit the ‘View Report’ button to get the report back.

    Hope this helps…

    Thanks,
    Munish Bansal

  144. Sam says:

    Hi Munish,
    Thanks for the tips. It was my function which was giving problem. But there is another issue.
    The ‘Factor’ parameter has about 75 values, when I choose ‘Select All’ with a date range I am not getting all parameters in the report but when I de-select some of them with the same date range other are being displayed. When I run the SP separately with all parameters with same date range everything is being displayed. It seems that it’s not reading all the parameter values. Do you think if there a specific reason for that?

    Thanks
    Sam

  145. Nik A says:

    Thanks for the help Munish, great idea and it works well.

    Let’s hope the next version of SSRS has this functionality (that the programmer can decide if the report runs immediately when requested, even if there are no parameters or all parameters have default values) !

    Regards
    Nik.

  146. Vasavi says:

    Hey Munish,
    I just wanted your help regarding the navigation of one SSRS report page to another SSRS report page..

    Note: There are same dropdown names with values in both the SSRS pages(only few)..

    In one SSRS page,there are certain dropdowns which contains the default values as well as a link to another SSRS page. When i click on this link using “Jump to Report” option and thereby passing the selected parameter values to the other SSRS page using Navigation tab
    (..as p1—Parameters!p1.Value)

    So,if there is a dropdown by name “p1″ which contains values “a”,”b”,”c”,”d” in the first SSRS page , then i click on the link,the second SSRS page will be displayed which also contains a dropdown by name “p1″ with values “a”,”b”,”All”..

    So if i select “a” in the “p1″ dropdown of the first SSRS page,then on clicking the link,the “p1″ dropdown in the second SSRS page shows “a”
    But if i select “c” in the “p1″ dropdown of the first SSRS page,then on clicking the link,the “p1″ dropdown in the second SSRS page is showing ..But it should show “All” as “c” is not there in the “p1″ dropdown of the second SSRS page..

    How can i achieve this?

    Thanks,
    Vasavi

  147. Hi Vasavi,

    There are two solutions to your requirement, based upon how the second report parameter values are being populated.

    A. If it’s been populated with some hard coded values defined in the parameter, then you can define a custom code i.e. a function and while passing/mapping the parameter to the selected value of first parameter, you can use that in the exp.

    Custom Code (Parent Report): Report -> Report Properties -> Code

    FUNCTION CheckValue(Value AS STRING) AS STRING
    IF Array.IndexOf(New String() {“a”, “b”}, Value) -1 THEN
    RETURN Value
    ELSE
    RETURN “All”
    END IF
    END FUNCTION

    And put the expression as:

    =code.CheckValue(Parameter!FirstParam.value)

    B. If second report parameter is being populated using a Dataset, then create another Hidden parameter in the second report and pass on the value from first report to that hidden parameter.
    Use that hidden parameter’s value to impose the condition in a dataset to populate the default value of the main parameter (including All) of the second report.

    Hope this resolves the issue.

    Thanks,
    Munish Bansal

  148. Vasavi says:

    Hi Munish,

    Thanks for your reply.

    The first solution worked pretty well, but am having a problem with the second solution..

    Can you please tell me how to write the select query in the second SSRS report using the hidden parameters..
    When i did so,i got an error message as “Forward dependencies are not allowed”..

    Please suggest a solution.

    Thanks,
    Vasavi

  149. Pingback: Mini Searches with Answers - Answer My Searches

  150. malavika says:

    Hi Munish,

    This is my first post on your blog.
    I too have a similar issue with my report.
    I need to make a parameter optional . i tried with keeping a ‘select all’ option. But this works for only single valued parameter. but i need to keep multi-valued parameter.

    please suggest.

    Regards,
    Malavika

  151. Kunal Singh says:

    Hello Munish,
    Need your help….

    I have migrated from SSRS 2005 to SSRS 2008 .In SSRS 2005 every thign was working good. but in SSRS2008 in multvalued parameter if no of items is more then 1500 then all item are coming from second index. That means when report gets loaded first impression comes that dropdown list is empty but when i click then i can see all values . But if i remove 500 item from DB that Every thing is working fine.

    Please let me know your view or solution

  152. Edward Baker says:

    Munish:

    I have a job costing report that shows a one page snapshot for each job my company runs. Each job snapshot shows the total Revenue and Expenses for that job.

    Thanks to your awesome post here, I have been able to add in a bunch of multi value params and that is all working great.

    The questions I have, is I need to summarize (sum up) all of the revenue and expenses for the total range of projects selected. I read how you can’t access report data in the header, so you have to set a textbox in the body to visible then point the header to the =ReportItems!textboxname.value . I can get it to do that, however I am unsure of how to sum up the revenue and expenses for the whole range.

    I wish there was a way to do a report cover page that let you select data elements to summarize because most of the time the end user is looking for the big picture rather than small details.

    Thanks, Ed

  153. Hi Ed,
    Thanks for your comment.

    As I understood, user will select some projects & then it will generate report for all of those selected jobs/projects. Each job is having fields for total revenue & expenses. And you want to show summarized value somewhere on header or so, right?

    Since it’s all based upon the selection of paramater values for projects, you can create one more dataset which will take these selected values, and will return back total summarized value for revenue & expenses. Then you can use the fields of this dataset, wherever you want to display that.
    As you have correctly mentioned, if you need to display it on header, you can use a hidden textbox for that.

    Thanks,
    Munish Bansal

  154. Edward Baker says:

    That makes sense. I am using a Stored Procedure to populate 2 temp tables which I then use to pull the data into the report. When I try to setup a 2nd data source using the same stored procedure it gives a message saying it can’t drop the temp tables, prob bc it is already in use once. Shoudl I just create another stored Proc but rename the temp tables to something else, then try again?

    Thanks for the fast response!

  155. Kunal Singh says:

    Hello Munish,
    Thsi is 2nd post to you .
    I have migrated from SSRS2005 to SSRS2008.One report having group by and that report contain static column and dynamic column both. in SSRS 2005 it is working good. but as i migrated to SSRS 2008 i m geting error message

    “The Group expression used in
    grouping ‘table1_group1′returned a data Type that is not Valid”

    Please let me know if you can help me. This is happning only in one perticular condition when we don;t want to show any data but only message to ened user.When we want to show Data even in SSRS 2008 every thing is working good.

  156. Kunal Singh says:

    Hello Munish,

    My Problme:- i have multivalued Parameter. In DataBase all most 1500 items are coming but when load report it look like that multivalued is returning blank value by default when i click on multivalued parameter i can see all 1500 items. Please let me know why it is happning and what is the solution.

    But when i remove 500 rows from Database every thing is working and i can see all 1000 rows as selected in multivalued parameter by default when report load
    Please help ASAP as i’m in big trouble.

  157. Hi Ed,
    Not sure how you are creating those temp tables in your SP. If you are using table variables (DECLARE @table TABLE), there should not be any problem calling the same SP again.
    Also in case of #table (temp table), you can drop them at the end of stored procedure.

    Thanks,
    Munish Bansal

  158. @Kunal,
    It might be due to mismatch in the data type of the field specified & then value being returned. Just verify it on the fields tab of the dataset created.

    Hope this helps.

  159. Hi Kunal,
    Are you talking about the default values to be selected based upon thos 1500 or 1000 values coming from DB or just the population of multivalued parameter?

  160. Kunal Singh says:

    Hello Munish,

    I’m populating multivalued parameter from Database table (Ex:- Select distinct (Id) from table1) so when Id count is 1000 they are coming as default selection in multivalued parameter of report in SSSR2008 but if Id count is 1500+ all then default selction is blank in report mutlivalued parameter. But when i click on multivalued parameter i can see all values as selected.
    Hope this is clear to you.

    Note:- This was not happning in SSSR 2005. I have migrated the same report in SSRS 2008 and facing this problem.

    Thanks for spending time on my issue.

  161. kunal singh says:

    Yes i m saying the same “default values to be selected based upon thos 1500 or 1000 values coming from DB “.

  162. Vijay says:

    Hi Munish,

    I am facing one issue with SSRS 2008 parameter default value selection. I have some parameters in my report. Let’s say 3 of them are as follows:

    STARTDATE
    ENDDATE
    SPECIFICDATE

    based upon the value of the STARTDATE and ENDDATE the specific dates in the multivalued SPECIFICDATE parameter is being populated. I want all the values selected by default so I am using a stored procedure to select all the values. This stored procedure has the params STARTDATE and ENDDATE in the where clause so that the values can come based upon these two params.

    Now the problem is as follows:

    - User choose STARTDATE as 1st Jan 2010
    - User choose ENDDATE as 30th April 2010
    - User choose other parameters
    - User sees that the SPECIFICDATE params has the values between the STARTDATE and ENDDATE range and all are selected.
    - Now the user went back and changed ENDDATE as 30th May 2010
    - User sees that the SPECIFICDATE params has the additional values shown for the extended date range , however they are not selected by default, the problem is user doesn’t have any idea if all the values are selected or not without opening the drop down for the SPECIFICDATE param.

    Do you know of any method which i can use to make sure that all the values are always selected by default in the SPECIFICDATE parameter.

    Thanks for your consideration.

  163. Amit says:

    Hi Munish

    I am facing one problem that how can we pass comman separted value in “IN” Parameter. when we pass only one value then this is running but when we pass more than 1 value then this is showing Invalid argument. please reply ASAP. I am using Oracle 10g also 9i.

  164. Reddy421 says:

    Hi,
    I am just curious to know one thing.
    i did the same thing as you have done, and my question is if we already have the where condition which you specfied in the stored procedure
    i.e WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’))

    then i dont think we need the Function again in the data set parameters.

    ( i just had the where condition and in the report parameters i selected the multiple parameter option and it worked without writing any join statement which you specified.
    Let me know if i am wrong, Anyways good link man………)

  165. @Reddy,
    In case your data is having comma (,) in the value then you may need to have some other delimiter which you can specify in that Join function & then split the multiple values passing that same delimiter to Split function.

  166. Anna says:

    Hi Munish,

    Your site is AWESOME! I am learning SSRS. I have a sp, it works in the reports. I need to have the parameter multi-value. I try to run th SplitList you provided but still returning errors.

    Here’s what I am using:
    Create FUNCTION [dbo].[SplitList]
    (@InputString NVARCHAR(8000), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(8000)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString =’,’
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    END
    RETURN
    END

    Error:
    Msg 170, Level 15, State 1, Procedure SplitList, Line 17
    Line 17: Incorrect syntax near ‘–’.

    Thanks,
    Anna

  167. Meg says:

    Hi Munish,

    I have a question what if i have two data sets.
    1st data set has name and
    2nd data set has color.
    Hear 1st data set is not multivalue but 2nd is multivalue??

    Thanks in adv,
    Meg.

  168. frigate says:

    My $0.02 – in order to split a comma-separated string in SQL 2005/08/08R2 you could use a XQUERY selectNodes method that does not required neither loops nor UDF.

  169. Sri says:

    Hi Munish,

    I have question regarding displaying values. One of my SSRS report needs the values to be displayed in one text box for selection of values of the multi value parameter. I am looking for the solution something like JOIN(Parameters!…). But my question is, i have to display Fields! instead Parameter.Right now i am able to display in list, but not in one text box with Coma delimited.
    Please help.

  170. Hi Sri,

    Try using Fields(Parameters!……).

  171. Sri says:

    Hi Munish,

    Fields(Parameters!……) didn’t work. But i figfured it out in the SQl itself and worked.
    Thanks for the reply
    Sri

  172. carl s. says:

    This is what I used and it worked. I cut and paste the examples above and I always ended up with an error, so I used the above code as a spring board and found all the errors. Hope this helps:
    ======================================
    ALTER FUNCTION [dbo].[SplitParameterValues]
    (@InputString NVARCHAR(max))
    RETURNS @ValuesList TABLE
    (
    GENDER NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(max)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString != ‘ ‘
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT LTRIM(RTRIM(@ListValue))
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    END

    RETURN

    END

  173. Kally says:

    Hi Munish,

    Is it possible to write stored procedure like tat?

    SET @query_part = name like % @keyword %

    Select * from
    table_name
    where
    phone like %@keyword%
    or
    address like %@keyword%
    or
    @query_part

  174. Bevin says:

    You don’t need Split function. Just concatenate on the parameters

  175. Bevin says:

    If we are able to concatenate in the parameters ,then why use Split function in the first place?

  176. Jack says:

    Hi Munish,

    Is there any way we can use allow null values and all multiple values in SSRS report..

    Here is the scenario.
    Dataset1:
    SELECT DISTINCT Color
    FROM Production.Product

    Dataset2:
    SELECT Name
    FROM Production. Produc

    Main Report Query:
    SELECT ProductID, Name, ProductNumber, Color
    FROM Production.Product
    WHERE (Color IN (@name)) AND (Name IN (@level)

    What i want is to allow user to be able flexible to supply sometimes only one parameter then also report should work. can above solution fit ino that situation?

  177. Hi Jack,
    I would suggest to have one value named as ‘NULL Value’ in both of the datasets, probably by using UNION.
    e.g. SELECT DISTINCT Color
    FROM Production.Product
    UNION
    SELECT ‘NULL Value’ AS Color

    And select this value (in both the parameters) as the default value. Now in the stored procedure/query, first check
    If EXISTS(SELECT 1 WHERE ‘NULL Value’ IN @name)
    BEGIN
    SELECT ProductID, Name, ProductNumber, Color
    FROM Production.Product
    WHERE Name IN (@level)
    END
    Thus not checking for the value of the color selection. Same for the other parameter, impose corresponding conditions in the query.

    Hope this works.

    Thanks,
    Munish Bansal

  178. Jack says:

    Hi Munish,

    Thanks for your reply. I tried to use your suggestion and it works for one column but doesn’t work when i change columns. So i will explain my scenario in more details.

    Report Query:
    if exists(select 1 where ‘NULL’ in (@city))
    select AddressID,AddressLine1,StateProvinceID,PostalCode,city from Person.Address
    where PostalCode IN (@postcode)

    Dataset1:
    select postalcode from Person.Address
    UNION
    select ‘NULL’ as city

    Dataset2:
    select city from Person.Address
    UNION
    select ‘NULL’ as city

    I have specified default value to NULL for both the parameter as you specified. Now above scenario works but when i change the column in the report query like this,

    if exists(select 1 where ‘NULL’ in (@postcode))
    select AddressID,AddressLine1,StateProvinceID,PostalCode,city from Person.Address
    where PostalCode IN (@city)

    Then it doesn’t work. So in the second scenario i am putting postcode null and selecting cities. But that doens’t work. For your information i am using AdventureWorks database. Any idea? can you please let me know as well that how can i combine both conditon in one query? using if else?

    cheers,
    Jack

  179. Bevin says:

    Here’s a better way to do it. You don’t need a split function. Just concatenate as below and pass the parameter directly in your query using IN. Let me know if this helps.

    =”‘”&JOIN(Parameters!Status.Value,”‘,’”)&”‘”

  180. Balu says:

    Hi Munish,

    I am using multi-value parameters.
    I have created SP with split function to split parameter values. When I run SP with multiple values (ex: ’1,2,3,4′) it worked fine.But when i executed report, it gave me below error message Incorrect Syntax near ‘)’.
    Would you please tell the solution. Thanks in Advance.

    Balu

  181. Katja says:

    So it works:

    CREATE FUNCTION SplitParameterValues (@InputString NVARCHAR(max), @SplitChar VARCHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(max)
    SET @InputString = @InputString + @SplitChar
    WHILE @InputString @SplitChar
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, (CHARINDEX(@SplitChar, @InputString)-1))
    IF (CHARINDEX(@SplitChar, @InputString) + len(@SplitChar))>(LEN(@InputString))
    BEGIN
    SET @InputString=@SplitChar
    END
    ELSE
    BEGIN
    SELECT @InputString = SUBSTRING(@InputString, (CHARINDEX(@SplitChar, @InputString) + len(@SplitChar)) , LEN(@InputString)-(CHARINDEX(@SplitChar, @InputString)+ len(@SplitChar)-1) )
    END

    INSERT INTO @ValuesList VALUES( @ListValue)
    END
    RETURN
    END

    greeting from Germany

  182. Bea says:

    This is great and also fast

    Just 1 correction to the function above

    WHILE @InputString != @SplitChar

  183. Seasons says:

    You you could edit the page subject title Passing multi-value parameter in stored procedure (SSRS report) Tech Updates to something more catching for your webpage you write. I enjoyed the post however.

  184. shreya says:

    Hi Munish,
    I need some help.

    My back end is oracle 10g and front end is SSRS2008 R2. I have created a report with two parameters Year and Degree. Year is a single valued whereas Degree is multivalued parameter. If I select only one degree or select all or select more than 3 values the report works fine. If I select only 2 values from drop down I am getting operation time out. I tried in Report Builder 3.0 as well as BIDS I am getting the same error.
    Would you please tell why it is like this.
    Thanks in advance.

  185. KunalSingh says:

    Hello Munish,

    I have a query.
    I have on multi value parameter call output
    (Table,Line)

    i have another multivalues Paramter Range (P1,P2,P3,P4,P5).

    Now if i Selected table from output then all (P1,P2,P3,P4,P5) should be selected and use can’t make any check box uncheck as i want all option should be selected.
    But if i selected Line from output then user can selected any option or Select all also.

    Help me you have any solution fo this.

  186. khalid latif says:

    Thanks you very much.
    I really appreciate that you spent time just for us
    It is very useful post,

  187. Nalasi Mendy says:

    Hello Munish:

    My company is looking to render rdl reports in PDF format from Reporting Server based on Create Button on an ASP.NET website. I am looking for VB.NET code to do this. I have found code snippets on the Internet, but when I try to use them, I get errors as “object not defined” I have been struggling with this for two weeks, and have not been able to show any progress. Please help. Thanks in advance.

  188. Hi Kunal,
    make the second parameter dependent upon the first one and if Table is selected in the first one, just prompt value as All Ps (or p1,p2 etc) in the second one and make that as selected by default.
    hope this works.

  189. Jaya says:

    Hi ,

    I am using the MAX Server using the ODBC Connection setup.

    For single parameter.
    select [Manager Name] from [Manager$] where [Manager Name]=? this works fine but

    How to use multi value paramter in this case as i tried.

    select [Manager Name] from [Manager$] where [Manager Name] in ?

    It does not fetch any value when i gave paramter value as 1,2

    Plz let me know .

  190. Sam says:

    Munish,

    Thanks for the lovely tutorial. It Helped me a lot.

    Well I tried your solution
    WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’))
    and it work fine for single multi-value parameters but when I tried to implement this on three controls, it shows some weird result.

    I believe its doing some kind of cross join. Do u know some alternate approach for handling multiple multi-value parameter controls…??

    Karen, did you find any solution to your problem…??

    Thanks,
    Sam.

  191. Sam says:

    I found the solution to my problem.

    Step1: var declaration in StoreProcedure
    @var_A AS NVARCHAR(MAX) = ‘ ‘,
    @var_B AS NVARCHAR(MAX) = ‘ ‘,
    @var_C AS NVARCHAR(MAX) = ‘ ‘

    Step2: var declaration in SplitList Function
    @RowData nvarchar(Max)=’ ‘,
    @SplitOn nvarchar(5)=’ ‘

    Step3:
    WHERE
    [var_A ] IN (SELECT * FROM SplitList(@var_A ,’:’))
    AND [var_B ] IN (SELECT * FROM SplitList(@var_B ,’:’))
    AND [var_C ] IN (SELECT * FROM SplitList(@var_C ,’:’))

    Thanks once again Munish :)

    Cheers,
    Sanyam.

  192. Sam says:

    here is the splitlist function if any1 needs :)

    ALTER FUNCTION [dbo].[SplitList]
    (
    @RowData nvarchar(Max)=”,
    @SplitOn nvarchar(5)=”
    )
    RETURNS @RtnValue table
    (
    –Id int identity(1,1),
    Data nvarchar(100)
    )
    AS
    BEGIN
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
    Insert Into @RtnValue (data)
    Select
    Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
    END

  193. Steve says:

    I tried to implement the UDF solution but it never quite worked using the sample code. Bevin’s solution from October 14th worked perfectly for me. Thanks, Bevin.

  194. tam says:

    hello,
    i have this issue

    jan feb …..
    2009|23 3.2 ……
    —– |————–
    2010|32 5.34 ….

    i want to add the growth rate which is equal to the amount in 2010/ amount in 2009 multiplied by 100

    how ill enter that row in sql repoting 2005
    it should be like that

    jan feb …..
    2009|23 3.2 ……
    —– |————–
    2010|32 5.34 ….
    ———————
    % |

  195. Sam says:

    There is an easier way.

    When you populate the list of values, use the following type of sql and it will prepend the correct quote symbols that can then be directly fed to a stored proc that expects an IN (val1,val2) structure.

    select name as Label, char(39)+name+char(39) as [value] from [dbo].[myNameTable]
    union
    select ‘All’ as Label,char(39)+’%’+char(39) as [value]
    order by Label

    In the SSRS report param definition, point the label field at the Label field of the dataset running the above sql and the value field to the corresponding value field.

    Now when the report runs, it automatically renders a correct sql expression with NO udfs, JOIN or other gymnastics.

  196. Sam says:

    sorry, one other point, because SSRS dumps extra junk on the params
    Use a PIPE char as a delim on the param value list, then use the following type of code in your stored proc and it works great

    SElect * from [dbo].[mynamelist]
    where @paramlist like ‘%|’ + Name + ‘|%’

  197. Parikshit says:

    Hi,
    I am Parikshit. I want to ask a question about SSRS 2008. I was done some reports in SSRS 2005. but i want upgrade these reports in SSRS 2008 now. I am confusion about to pass the parameter value one to another report. In SSRS 2005 we can use Jump to report Action. and Passing the parameter values easily but. In SSRS 2008 there is a Action Property having some step to for Jump the report by Go To Report and Go To URL.
    My Problem is, When I am using the Go To Report functionality and selecting the another report and adding the parameter name with value. but report can drill through by using this but not passing the Parameter values to that another report.
    For Ex.
    I am having two reports Report1 and Report2. Report1 is having a Pia chart setting the values as Business Units and WorkOrder Count. another Report2 is having Bar chart and having the filter as Business Unit i. Parameter. I am using the Report1 Chart Action using the Go To Report and selecting the listed report as report2 and also selecting the report parameter value. report2 to is links to report1 But It Pass the null value. and report chart is not showing.
    Pleas tell me answer…
    Immediately

  198. mos says:

    Hi,

    I ‘ve a question about multiple values pass it to stored procedure.i got a script when you pass parameter to stored procedure it goes to function,that function bring it back all kids ids for parent id passed to function.if i want to pass multiple parentID to stored proc,how would i deal it??

    Thank you

  199. Muhammad Omer Zubair says:

    Hi Guys,

    This solution which I created is for SSIS 2008. I could not find any solution on web for my problem which is similar to the question above.

    Problem:
    =======

    I had a UDF which was joining a lot of tables thus producing a lot of rows for my report in SSIS 2008 to filter. It was taking 12 minutes for report to show result. No matter if i select one value in a report parameter or all of the parameters, the report will take same time to run.
    I had a multi-valued parameter which was applied after the function was run. There was a need to send the multivalued parameter to function before the the function was run.

    Solution:
    =======

    It took me a full day to figure out as nothing was on web. It is rather a simple solution. I wish i could add screen shots.

    Step 1: In the ‘Main Dataset property’ in SSIS 2008 . Select the parameters from the list on LHS.

    Step 2: You should ADD a parameter (if it doesnt exist) which will be used to send to the function.

    Step 3: under the parameter value select the expression button “fx”.

    Step 4: This will present u with a new screen. In this screen put the function as follows:

    =IIf(
    Parameters!Depots.IsMultiValue = False
    ,Parameters!Depots.Value(0)
    , join(Parameters!OAWGDepots.Value,”#”)
    )

    Step 5: The above function will concatinate the selected parameters into one big string seprated with hash ‘#’.

    Step 6: now modify your UDF in your database. It should accept one parameter.

    Step 7: New create another functon which will convert the string to a table.

    CREATE FUNCTION [dbo].[udf_SplitInt]
    (
    @List nvarchar(4000),
    @Delimiter char(1)= ‘,’
    )

    RETURNS @Values TABLE
    (
    Position int IDENTITY PRIMARY KEY,
    Number int
    )
    AS
    BEGIN
    – set up working variables
    DECLARE @Index INT DECLARE @ItemValue nvarchar(100) SELECT @Index = 1
    – iterate until we have no more characters to work with
    WHILE @Index > 0
    BEGIN
    – find first delimiter
    SELECT @Index = CHARINDEX(@Delimiter,@List)

    – extract the item value
    IF @Index > 0
    – if found, take the value left of the delimiter
    SELECT @ItemValue = LEFT(@List,@Index – 1)

    ELSE
    – if none, take the remainder as the last value
    SELECT @ItemValue = @List
    – insert the value into our new table
    INSERT INTO @Values (Number) VALUES (CAST(@ItemValue AS int))
    – remove the found item from the working list
    SELECT @List = RIGHT(@List,LEN(@List) – @Index)

    – if list is empty, we are done
    IF LEN(@List) = 0
    BREAK
    END

    RETURN
    END
    GO

    Step 8: Now use this new function in your query and limit the records to make your query go faster. E.g

    SELECT
    j1.AuthID,
    j1.OrgaName
    FROM
    dbo.Org j1
    INNER JOIN
    dbo.OrgTyp j2
    ON j1.OrgTypID = j2.OrgTypID
    AND j2.Typ = ‘Dep’
    AND j1.AuthID IN ( — Apply the user filters
    Select
    Number
    From
    [dbo].udf_SplitInt(@DepotsIDs,’#’)
    )

    ===================END=================================

  200. Mike Partridge says:

    Hi Munish and everyone,
    I have tried to incorporate this into my SSRS report but can’t get multi valued parameters to work. Can someone please provide me with the extact function that needs to be used to format the string correctly?

    Many thanks,
    Mike

  201. Mike Partridge says:

    Never mind, I found an easier way to do this.

    I started off my dropping the stored procedure and function in the database.

    I then embedded the SELECT query in my SSRS report. The result was a report that worked like a charm, passing the parameters correctly into SQL.

    There’s no need for any need for any gymnastics in SQL.

  202. GT says:

    Hi Munish,

    I was tring to create below function in SQL SErver 2008. When I executes this function, I am getting the the error saying ” Invalid column name”
    Could you help me ?

    Create FUNCTION [dbo].[SplitList]
    (@InputString NVARCHAR(8000), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(8000)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString =’,’
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    END
    RETURN
    END

    Thanks in Advance

    GT

  203. Ronald says:

    Hi,
    I have made a small modification to the expression that is being passed as a parameter to the stored procedure so that you deliver a multi-value string to the SP and you don’t need the split function anymore.

    The expression in the Parameter Value is:
    =”‘” + JOIN(Parameters!par_Users.Value, “‘,’”) + “‘”

    This effectively generates a string like:
    ‘Adam’,’Eve’,’Brit’,’John’

    in the SP you can use this in the select statement as following:
    SELECT Fullname, Birthday FROM employees
    WHERE firstname IN (@par_Users)

    I have only used this in a SP and in a SELECT statement, I have not tried any other fancy tricks

  204. Jimmy says:

    Ronald,

    I am getting an error while putting value for the expression: “The expression that references the parameter ‘Param1′ does not exist in the parameter collection. Letters in the name of parameter must be in the correct case”

    Thanks in advance.

  205. Jimmy says:

    Hi Ronald,

    While using this I am getting syntext error: “Identifier contains invalid character”.

    The expression in the Parameter Value is:
    =”‘” + JOIN(Parameters!par_Users.Value, “‘,’”) + “‘”

    Where do I put this expression for parameter value. I am using this as parameter available value –> specify value (Label, Value) Option field.

    And in the main query I am using IN(:Param1) (Oracle 10g)

    Thanks in advance

  206. Ronald says:

    Hi Jimmy,

    you need to put this expression in the parameter assignation of the dataset. Right-click the dataset and go to Properties, then select Parameters. Click on the expression button for the parameter you want to assign the expression to.

    Ronald

  207. Jimmy says:

    That’s great! Thanks for the reply. Yeah I did solve it that time. I was wrong by adding properties on the parameter list not in the dataset. Got it work now.

    Thanks

    Jimmy

  208. Sasikala says:

    Hi,

    We are using SSRS 2008 R2. In this how to pass muti-value parameters in URL? no.of characters are restricted in IE.

    pls help in this.

  209. Jimmy says:

    Sasikala,

    I think You should try to do following things for multivalue paramters

    1) Create a split function
    2) Create a separate dataset for multivalue parameters
    3) Change main dataset to populate multivalue parameter values from split function i.e: and (param1 in (select column_value as param1 from table(HR.SPLIT(:Param1))) OR :Param1 = ‘ALL’)
    4) Go to the main dataset properties –> parameter –> select multivalue parameter expression –> add this: =Join(Parameters!Param1.Value,”,”)
    5) Should work now

    For the URL populate multivalue paramter list externally using this query

    select distinct param1 from table1
    where param1 is not null
    union
    select ‘ALL’ as param1 dual

    Thanks

  210. suv says:

    I applied the way you mentioned and it worked.
    Thanks for the post… much appreated.

  211. Shibu P says:

    Thanks, your post is helpful.

  212. Rajesh says:

    Hey all…

    I currently have a multi-value parameter which has a really long list of values, the problem is users are forced to scroll through this list to find the value that they want.

    Is there a way to allow users to type the value and the list might “jump” to that character in similar fashion to the single select drop down box?

    I have thought about trying to implement different work arounds, but i have come up with nothing. My list is a long list of store codes which 99% of the time a user would only select 1. However, the area managers need to select multiple stores. 1 work around was to provide them with a free text box however this looks messy and occasionally users need to scroll to find there store as they don’t know the store code. I thought about putting both input parameters type on the page except you can not leave a multi-value parameter blank (if a user had typed the store code into free text)…

    Any ideas?

  213. Ronald says:

    Rajesh,

    can you group your stores? Maybe an area code or store type or something similar?

    Then you could have a first multi-select so users can select 1 or more “store group” and then have a second (or even third if you add more than 1 grouping level) multi-select that lists the stores of the select groups only (you can use the parameter of the first multi-select in your WHERE clause of the second)

    Ronald

  214. Enrique says:

    Thanks so very much for posting this. It saved me a tremendous amount of time and frustration. Excellent work!

  215. sarwan says:

    Hi Munish,

    I am following the same procedure you mentioned here.
    But I have an integer parameter.
    I am still having the error:
    Error converting datatype nvarchar to int.

    Please help.

  216. Kim Kieu says:

    Hi,
    I create a function in database under schema TEST. Function name is getValue

    How I can assign this fuction to the field of dataset in SSRS ?

    In the layout, I click on that field then go to value from Properties, click on Expression

    In Edit Expression window I put:
    =Fields!select TEST.getValue

    I got error:
    [rsFieldReference] The Value expression for the textbox ‘cmtPrimaryIP’ refers to the field ‘select’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

    Please help me for the syntax.
    Thanks,
    Kim

  217. Rajesh says:

    Thanks ronald for the reply….i have done the cascading parameters and reduced the list.

  218. Rajesh says:

    Guys,
    From earlier discussion from this same thread….I have a question.. i have 4-5 multi value parameters each one has a huge list one such parameter is clients….so when the user selects ‘select all’ it passes a string of comma seperated values to the query so its performance sucks. So in my query i have created a check for null value and if the null gets passed from RDL it gives the values for all the clients but my prob is is there any way i can remove the select all default in drop down so tht i can name my null value as ‘ all ‘ . because if user select ‘select all’ it again has performance Issues.

  219. Kavitha says:

    I have a situation in SSRS i want one parameter to have default value which is the address and if the user wants they should be able to change it.

  220. Santosh Salvi says:

    Hi Munish,

    I am using Stored procedure in Crystal Reports & passing multivalued parameter to it frm main Report exactly in same way you mentioned above(Passing Multivalued Param frm Main Report to Sub-Report). Everything works fine in Crystal Report but once I deploy the report in BO it gives me error “Error in File abc.rpt: Database Connector Error : ”
    Have checked connection parameters, everything is correct. Also noticed that if i deploy only sub-report it works fine there.
    I am somewhat got stuck here. Please Help

    Thanks in Advance,
    Santosh.

  221. Paul says:

    I followed your instructions, however didn’t achieve expected result:

    1. Created a function:
    CREATE FUNCTION [dbo].[SplitList]
    (@InputString NVARCHAR(max), @SplitChar CHAR(5))
    RETURNS @ValuesList TABLE
    (
    param NVARCHAR(255)
    )
    AS
    BEGIN
    DECLARE @ListValue NVARCHAR(max)
    SET @InputString = @InputString + ‘,’
    WHILE @InputString !=”
    BEGIN
    SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1)

    INSERT INTO @ValuesList
    SELECT @ListValue
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1
    , LEN(@InputString) – CHARINDEX(‘,’, @InputString))
    END
    RETURN
    END
    GO

    select * from [SplitList](‘WER,ASD’,’,’)

    I have got a table with 2 rows: WER in first row and ASD ins second row.

    2. Created a procedure:

    CREATE PROCEDURE IN_OUT(@mars varchar(100))
    AS
    Select
    NationalIDNumber,Title,MaritalStatus
    from dbo.rep
    Where
    @mars is null or MaritalStatus IN (select MaritalStatus from SplitList(@mars,’,’))

    3. Added DataSet, entered sp IN_OUT:
    Have got a dataset, created a simple table and changed =JOIN(Parameters!Color.Value, “,”) in the Data Properties\Parameters; Allowed multiple values and blank ‘ ’ value in the parameter @mars, added available values for @mars parameter from a second dataset.
    4. Run Preview – it doesn’t matter what parameter I choose, I get a complete dataset without filtering it through a parameter!

    Please check where is an issue?

  222. Angela says:

    Hi Munish,

    I would like to know if a Mainframe Stored Proc can be called from SSRS. It calls DB2….

    Thanks,
    Angela

  223. HC says:

    Hi Munish

    This solve my problem completely.
    Thanks for it! ^^

  224. Karen says:

    Munish Thank you for posting this. It has really helped me. I still have one problem. The values in my dataset are open, closed, on call, missed. I am getting everything except for the on call.
    Any suggestions for this one?

  225. Biju says:

    Thanks,
    This was something really useful. Great

  226. Rob says:

    Thanks, this was really helpful, I could not understand why a users report was returning no data when all I’d done was wrap the code in an SP for easier reference.

    Thanks again.

  227. komal says:

    Hi Manish,
    I created the split function and stored proc as you mentioned.But in the report , when I try to select just “Black ” I am able to see all colors in the report.I checked my UDF its working fine.My guess is my Stored Proc has some issues.

    My Stored Proc :

    Alter PROCEDURE [dbo].[ProductList]
    @Color varchar(20)
    AS
    BEGIN
    SELECT * from Production.Product
    WHERE Color = @color or Color In (Select Color FROm dbo.SplitParameterValues(@Color, ‘|’))

    END

    Thanks,
    Komal

  228. Stan says:

    Hi Munish Bansal,

    I have used your exercise and it works perfectly when running in the report,
    but when I try to access via a code i created,
    e.g //one parameter, it works:
    Response.Redirect(“http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fWZ%2fMultiple&rs%3aCommand=Render&rc%3aParameters=true&CID=101″);
    //but when I pass 2, when reportViewer comes up, it is blank…
    Response.Redirect(“http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fWZ%2fMultiple&rs%3aCommand=Render&rc%3aParameters=true&CID=101,102″);

    Any ideas? how do I pass multiple parameters to the report? please advice, Thanks!!

  229. Chetan says:

    I have a stored procedure, which emits @employeeID parameter, But that parameter is not accepting any query values, wat would be the problem?

  230. Frans says:

    This helps me a lot
    I did not under stand why it does not work with a stored procedure
    like with aview or a table

    Only the function did not work for me and I build my own
    See below
    Best regards,
    Frans Habekotté

    Create FUNCTION [dbo].FRH_fnSplitParameterValues(@SearchStr NVARCHAR(4000), @delimiter CHAR(1))
    RETURNS @ValuesList TABLE (Result NVARCHAR(255))
    /*
    select * from [dbo].FRH_fnSplitParameterValues(‘een,twee,drie’,’,’)
    */
    AS
    BEGIN
    DECLARE @s2 NVARCHAR(4000)
    declare @p1 bigint
    declare @p2 bigint
    set @p1=0
    SET @SearchStr = @SearchStr + ‘,’
    WHILE @p1<len(@SearchStr)
    BEGIN
    set @p2=charindex(@delimiter,@SearchStr,@p1+1)
    if @p20
    begin
    INSERT INTO @ValuesList
    select substring(@SearchStr,@p1,@p2-@p1)
    set @p1=@p2+1
    end
    else set @p1=len(@SearchStr)+1
    END
    RETURN
    /*
    declare @s1 nvarchar(4000)
    set @s1=’een,twee,drie’
    declare @s2 nvarchar(4000)
    set @s2=””+replace(@s1,’,’,”’,”’)+””
    select @s1,@s2
    */
    END

    Best regards, Frans Habekotté

  231. Lalit says:

    Hi munish,
    I am creating a report in SSRS2005, wherein the data set has to be stored procedure. But while using report wizard, and after selecting the data-source and clicking the next button, query builder pops up with command type as TEXT (display only ). But there is no way to change the command type to stored procedure. Please suggest how to do this.

  232. Ankit says:

    Is there any way to have parent child relationship in parameter. Suppose i have two parameter group and division, and i want the division to get selected as group gets selected and same with group, when all division of that group gets unchecked than its group automatically gets unchecked from the group parameter i.e multidropdown in my case, as we usually do in aspx page.

  233. Ned says:

    This is awesome.

    Thanks!

  234. Mike says:

    Munish, this may have been covered but I didn’t see anything on this. I need to have more than one multi-value parameter in my report like two or three parameters with multi-value select options. What would my where clause look like? Do i need more than one split function? this is a great post, learned a lot

    thanks! Mike

  235. durga says:

    Hi Munish,

    Can we create multi select parameter based on groups in SSRS reports but groups should not be able to select
    Ex: Movie Parameter in SSRS Report, is Multi select parameter

    English (not selectable)
    Movie-e-1 (selectable)
    Movie-e-2 (selectable)
    Movie-e-3 (selectable)
    Hindi (not selectable)
    Movie-h-1 (selectable)
    Movie-h-2 (selectable)
    Movie-h-3 (selectable)

    OR
    Also, I need this in table format rows and columns

    English (not selectable) Hindi(not selectable)
    Movie-e-1 (selectable) Movie-h-1 (selectable)
    Movie-e-2 (selectable) Movie-h-2 (selectable)
    Movie-e-3 (selectable) Movie-h-3 (selectable)

    Please help me

    thanks
    Durga
    welcomedurga@gmail.com

  236. Ranjeet Singh says:

    awesome

  237. Tony says:

    Hi Munish,
    I’m creating a report in SRSS 2008 and am having some issues with my parameters. I am creating a query based parameter that will pull in distinct propertyID and propertyDesc and will allow multiple selections. i want the parameter to only display the propertyDesc for end users to see. It works fine for one prop but for more than one selections I get the declare scalar variable. I read your post above but still can get it to work. Any help will be appreciated.

  238. Dhinesh says:

    Hi Munish,

    I want to create report which takes date range as parameter, if am passing 2/10/2011 and 8/10/2011,the report should 7 columns as Sun,Mon,Thu,Wed….Sat. if am passing the date range 2/10/2011 and 15/10/2011, the report should show 14 columns as two set of weeks like Sun,Mon,Thu,Wed….Sat,(Sum of values for last seven days),Sun,Mon,Thu…..Sat,Sum(of values for last seven days),Sum(14cdays values).like wise the column should vary. please suggest me the solution as soon as possible. thanks in advance

  239. Akshay says:

    Hi munish,

    Is there any limitations on records a single ssrs reports can hold. I am using ssrs 2008. Please its urgent.

  240. MM says:

    Hi Munish,

    Report is executing based on ‘ALL’ parameter value, below query written for dataset for Parameter called PO….

    In Query Analyzer it is taking hardly 2 mns and i have enabled profiler for “Report Server” for the query and the same query i have executed in Query Analyzer, it is also taking not more than 1 min…

    But in Report Manager to load data into the parameter it is taking long long time (half an hour) ad finally it is populating the data and when i click on the parameter “Report Manager” is hanging…

    Any suggestions pls….

  241. MM says:

    Hi Munish,

    sorry i have not posted dataset query in the last comment…please find here

    SELECT
    ‘ALL’ AS ID,
    ‘ALL’ AS NO,
    ‘ALL’ AS DIV,
    ‘ALL’ AS TYPE,
    ‘ALL’ AS JOB
    INTO #TP
    UNION ALL
    SELECT DISTINCT

    CONVERT(VARCHAR,TP.ID) AS ID,
    TP.NO,
    TP.DIV AS DIV,
    TP.TYPE AS TYPE,
    TPJ.JOB AS JOB
    FROM TP
    LEFT JOIN DIV DIV
    ON TP.DIV = DIV.DIV
    LEFT JOIN TPL
    ON TPL.TP_ID=TPL.TP_ID
    LEFT JOIN TPJ
    ON TPOJID=TP_ID AND TPJ.TPLID=TPL.TPL_ID

    CREATE INDEX IDX_PONO ON #TP(ID)

    SELECT *
    INTO #TEMP
    FROM #TP
    WHERE NO NOT LIKE ‘R%’
    AND (DIV IN (@DIV) OR @DIV = ‘ALL’)
    AND (NO LIKE ‘%’+@Search+’%’ OR @Search = ‘ALL’)

    SELECT * FROM #TEMP
    WHERE (Job IN (@Job) OR ‘ALL’ IN (@Job))

    DROP TABLE #PO
    DROP TABLE #TEMP

  242. Naveen Reddy says:

    Hi Munish,

    Hope I hear an answer from you. I’ am working on a multi-value, multi page report. I have 2 parameters that I pass in to the report from my .Net code.

    Parameter 1. class Id –> an Integer
    Parameter 2. person Id’s –> multi value Id’s separated by a delimiter “,” (this is built as a string in .Net code and is passed to the the report (12, 13, 14, 15).

    I tried many ways on getting this report work accepting multi value parameter.
    At last I got it by building an expression for multi value parameter on SSRS report

    =Split(Join(Parameters!ppersonid.Value, “,”),”,”)

    Till here the report works fine. Now, I need to generate this report in such a way that each person’s info is printed on a separate page. Each person is associated to a single class id. so, I need to generate a letter/ hall ticket kind of report for each person on each page so that we can mail the candidates these letters.

    Suggest me if you have any ideas.

  243. Tuhin says:

    Dear Sir,
    My name is Tuhin Sengupta and I recently joined one software company as a fresher and going to work in BI team. I had read your blogs and its really helpful for the peoples who are really eager to learn the new concepts and tricks associated with SSRS 2008. Sir I am good in SQL Server and able to write the proper queries depending upon the requirements but I faced problems while creating the reports in SSRS 2008 using that queries. So I want to learn from the basic level that how to create the basic reports and what is the difference between the tablix and matrix reports. Actually I did’nt get the proper guidance from my mentor in my company so I want your help that you guide me as a mentor to resolve this problem. If you have some e-books of SSRS 2008 then please kindly send me, I will very very thankful to you. Its my humble request to you that please guide me that from where I should start so it will be easier for me to learn such that I create the good reports in SSRS 2008.

    Sir if you have any account in facebook, please add me as your friend or send me your facebook Id I will send you the friend request.
    Its my facebook Id:- coolsona143@gmail.com

    Regards

    Tuhin Sengupta
    Mobile:- 8123442398

  244. Tuhin says:

    I did’nt say anything wrong about my mentor…he is one of the best employee in our company and he always completed his task in right time…he only wants me to learn the new technologies and grasp the knowledge of reporting services…he puts all his effort to make me learn…actually initially i faced some problems while creating the reports but after reading your impressive blogs and the proper guidance from my mentor make me to generate standard basic reports…really its my heartly thanks to both of u…

  245. jotiram says:

    hi sir,
    I got all the ans from your note ,
    but how I call ssrs report and pass parameter to this report from code

  246. BlackRacerBoy says:

    Thank you! This is the best article I’ve seen so far about multivalue parameters.

  247. migules says:

    Thanks munish, how to create stored procedure in ms sql server that return a two or more values.

  248. anki says:

    i want to update report parameter AppointmentNumber on the basis of Customer parameter selection in my dynamics ax SSRS report. I am not using the query for this. Please update me as soon as possible that how i will do this.

  249. anki says:

    Hi
    I am creating a AX 2009 SSRS report. In the report I have two
    parameters, based on the first parameter which is a drop down , the second
    parameter needs to be filtered with specific values. For eg AccountNum is the
    first parameter which displays all the CustomerId. Based on the
    AccountNum selected, the second parameter should display the AppointmentNumber. Can you
    let me know how to achieve this scenario. Is this scenario possible in AX
    2009 SSRS?

  250. Satish says:

    Hi,

    I need help in the below described scenario.

    I have created a report in SSRS as below:

    Param1: A101 (Drop down) Param2: 1,2,3 (Drop Down)

    (When i give the above inputs as parameters for my Report, then result would be:

    PersonID Divisions Value_Division
    ————- ————————- ————————-
    A101 1 200
    2 117
    3 314
    )

    Now i want to create a new Data Driven Subscription for this Report in order to export excel file report(on a timely basis) for each PersonID (ex: A101), and all his Divisions (1,2,3,4,5) in the excel.

    So, final result should be, if there are two PersonIDs(A101, A104) in table, and each have 5 Divisions,
    Then 2 Files should be exported as ‘A101.xlsx’, ‘A104.xlsx’ in to a folder.

    Each should have their respective Rows in the fiels.

    Ex:

    ‘A101.xlsx’

    PersonID Divisions Value_Division
    ——– ———- ————-
    A101 1 200
    2 117
    3 314
    4 209
    5 114

    ‘A104.xlsx’

    PersonID Divisions Value_Division
    ——– ————— ————
    A104 21 220
    43 179
    32 419
    76 806
    9 114

    Can anyone please help me here.

    This is an urgent request :(

    Thanks,
    Satish.

  251. Siladitya Basu says:

    Hi Munish,

    Can u help me out in solving an issue.

    I am trying to pass multi value parameter to the DB2 query(Not stored procedure),from ssrs.

    It will be very much helpful if provide an simple example.

    Thanx in advance.

    Siladitya Basu

  252. anki says:

    Hi Munish,
    Please help me in finding the solution
    I am creating a AX 2009 SSRS report. In the report I have two
    parameters, based on the first parameter which is a drop down , the second
    parameter needs to be filtered with specific values. For eg AccountNum is the
    first parameter which displays all the CustomerId. Based on the
    AccountNum selected, the second parameter should display the AppointmentNumber. Can you
    let me know how to achieve this scenario. Is this scenario possible in AX
    2009 SSRS?

  253. Siraz says:

    thanks for the solution

  254. Siraz says:

    Anki, if you are using 2008 version, you can do the following.
    go to your Data set properties, select Parameter from the option
    add the necessary parameter as name & value (both should be same, ex: @p and [@p])
    Note: normally, once you add parameter in your query this (ex: @p), it gets added automatically as mentioned above.

    Repeat the same for all the dependent data sets you have for the report.

    hope this helps.

  255. anki says:

    Hi Siraz,

    Thanks for your reply. What you have said, please explain it.

  256. Ravi Gupta says:

    Thanks, this helped.

  257. Satendra says:

    hiiiiiiiiii

    i m using ssrs 2005, i am having the data in the table as:

    YearMonth Sales
    201001 200
    201002 300
    201003 500
    201101 400
    201102 600
    201103 700

    I want to show this record in report as:

    Year Month Sales Year Month Sales
    2010 01 200 2011 01 400
    2010 02 300 2011 02 600
    2010 03 500 2011 03 700

    I have tried a lot but did’nt get the proper solution. Please lemme knw how can we achieve this scenario. Suggest me if u have any idea about it. It will be very helpful for me.

    Thanks,
    Satendra

  258. Harsha says:

    thanks dude !!!

  259. John Glasgow says:

    Reblogged this on Oh Yeah, Microsoft Did That 5 Years Ago and commented:
    This is a really good article on multivalued parameters in a stored procedure. Very good read for SQL Server Reporting Services.

  260. Tim J says:

    Hey Munish…
    seems u r away for last 2 years…no new post, no reply to the questions…
    would be great if could pls allow me to write somethin on here…some stuff I may help with…
    thanks,

    -Tim

  261. shan says:

    HI Munish,

    I am new to Stored procedure as well SSRS.. I ma able to select single value and see my report in SSRS but I am not able to selectmultiple values in my SSRS Drop down Menu…
    AS you said , in the store proc where condition i have given in (@color).
    and in maindataset Parameter i used the join function as you mentioned.

  262. E M says:

    Thanks for explaining how to join the parameters. I will add that once the parameters are passed you need to be able to parse or split them. Itai Goldstein (http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx) has a function and good explanation of how to accomplish this.

    Thank you very much once again.

  263. Bhawani says:

    Hi Munish,

    I have a similar issue but I don’t see the options you have shown to try the recommended fix.
    here’s what I am trying to do.

    I my Building a SSRS report which has multiple parameters (backward dependency). All parameters values comes from datasets based on stored procedure (this can be changed to SQL query as well).

    The main report dataset is also coming from a stored procedure which uses all above parameters as input. I am using In clause in the stored procedure to get the desired report data.

    One of the parameters is Customer ID. When I “select all” for Customer parameter, the SSRS report fails with webform error. The number of customers are above 3K.

    To try your suggestion, I do not see where I can join the parameter values into single string. Please help.

    let me know if you need more or any specific detail. FYI, I have sql 2008 and my using project type “Dynamics AS Reporting project” for my report in VS2008.

    Thanks in advance,
    -Bhawani

  264. Pankaj says:

    Hello Friends,

    I have one problem while using SSRS multivalued parameter in stored procedure.

    I know that we can pass multivalued parameter to stored procedure by using Split function, but my problem is that my Organization do not allow me to use function, is it possible to pass multivalue parameters to stored Proc without using Split function,

    Thanks.

  265. Zakir says:

    Hi,
    I have a multi value parameter in SSRS report which has a list of values around 600..When the user selects ‘SELECT ALL’,its passing the long list of values
    to procedure and impacting performance..its getting timed out even..we
    used ‘ALL’ option also for improving performance..but the user requirement is like..
    sometimes they need to select 590 values out of 600 values..which will not work with ‘ALL’
    Can any one have a better way to resolve this with better performance.

    -Zak

  266. Pingback: SSRS multi-value parameter using a stored procedure | TONY'S BIG WORLD

  267. Pingback: SSRS Gotcha: Select All, Multi-value Parameters, Stored Procedures, Comparing Unicode to ASCII, and Table Value Functions | The Degenerate Dimension

  268. Tony says:

    Is anyone going to answer the question??? How do you pass multiple parameters in the address line or calling the address from code?

  269. Sasidhar Nalla says:

    Hello Friends,

    Finally I was able to get a simple solution for this problem. Below I have provided all (3) steps that I followed.

    I hope you guys will like it 

    Step 1 – I have created a Global Temp Table with one column.

    CREATE GLOBAL TEMPORARY TABLE TEMP_PARAM_TABLE(
    COL_NAME VARCHAR2(255 BYTE)
    ) ON COMMIT PRESERVE ROWS NOCACHE;

    Step 2 – In the split Procedure, I didn’t use any array or datatable, I have directly loaded the split values into my global temp table.

    CREATE OR REPLACE PROCEDURE split_param(p_string IN VARCHAR2 ,p_separator IN VARCHAR2
    )
    IS
    v_string VARCHAR2(4000);
    v_initial_pos NUMBER(9) := 1;
    v_position NUMBER(9) := 1;
    BEGIN
    v_string := p_string || p_separator;

    delete from temp_param_policy;

    LOOP
    v_position :=
    INSTR(v_string, p_separator, v_initial_pos, 1);
    EXIT WHEN(NVL(v_position, 0) = 0);

    INSERT INTO temp_param_table
    VALUES (SUBSTR(v_string, v_initial_pos
    , v_position – v_initial_pos));

    v_initial_pos := v_position + 1;
    END LOOP;
    commit;

    END split_param;
    /

    Step 3 – In the SSRS dataset parameters, I have used

    =Join(Parameters!A_COUNTRY.Value, “,”)

    Yours,

    Sasidhar Nalla
    Hyderabad

  270. Sasidhar Nalla says:

    Hello Friends,

    Finally I was able to get a simple solution for this problem. Below I have provided all (5) steps that I followed.

    I hope you guys will like it :)

    Step 1 – I have created a Global Temp Table with one column.

    CREATE GLOBAL TEMPORARY TABLE TEMP_PARAM_TABLE(
    COL_NAME VARCHAR2(255 BYTE)
    ) ON COMMIT PRESERVE ROWS NOCACHE;

    Step 2 – In the split Procedure, I didn’t use any array or datatable, I have directly loaded the split values into my global temp table.

    CREATE OR REPLACE PROCEDURE split_param(p_string IN VARCHAR2 ,p_separator IN VARCHAR2
    )
    IS
    v_string VARCHAR2(4000);
    v_initial_pos NUMBER(9) := 1;
    v_position NUMBER(9) := 1;
    BEGIN
    v_string := p_string || p_separator;

    delete from temp_param_policy;

    LOOP
    v_position :=
    INSTR(v_string, p_separator, v_initial_pos, 1);
    EXIT WHEN(NVL(v_position, 0) = 0);

    INSERT INTO temp_param_table
    VALUES (SUBSTR(v_string, v_initial_pos
    , v_position – v_initial_pos));

    v_initial_pos := v_position + 1;
    END LOOP;
    commit;

    END split_param;
    /

    Step 3 – In the SSRS dataset parameters, I have used

    =Join(Parameters!A_COUNTRY.Value, “,”)

    Step 4: In the start of your stored procedure executes the Procedure

    Exec split_param(A_Country, ‘,’);

    Step 5: In your stored procedure sql use the condition like below.

    Where country_name in (select * from TEMP_PARAM_TABLE)

    Yours,

    Sasidhar Nalla
    Hyderabad

  271. Raji says:

    hi Manish,
    My report shows data correctly if i select single Paramteres . If i select multiple parameters the report is rendered with out any data. I am stuck on that .Could you help on that…

    Raji

  272. Undeniably believe that which you said. Your favorite justification seemed to
    be on the web the easiest thing to be aware of.
    I say to you, I certainly get irked while people consider worries
    that they plainly don’t know about. You managed to hit the nail upon the top and defined out the whole thing without having side effect , people could take a signal. Will likely be back to get more. Thanks

  273. baked ziti says:

    Great article! That is the kind of info that are meant to be shared across
    the internet. Shame on Google for not positioning this publish upper!

    Come on over and discuss with my site . Thank you =)

  274. ltraylo says:

    Thank you for posting with screenshots. I have seen a few article on this topic, but they were not clear on where to enter the “=JOIN”.

  275. Hari says:

    Thanks a lot

  276. Mae says:

    Hi people. I have a pretty extensive report I am building in SQL2008r2 using BI. I have been using the list property to display the different groupings. There should be one list with 8 possible other lists for each record. For each list I have a different data-set and the query for each points to the same ID field so they should display based on all having that ID but they are not. Each table has a related base id, header id, and record id. Instead of showing record one with sub1, 2, 3, 4, 5, and 6, then record 2 with sub 3,4,and 8, and maybe record 3 with sub 1-8 I am getting record 1, 2, 3, 4, 5, 6 ….. and the sub1,1,1,1,1….2,2,2,2….3,3,3,3…. and so on. Can anybody help me fix this?

  277. Corey says:

    Ok, So everyone says this function work perfectly…I have tried to use it and I am not getting the expected results. I am using SQL 2012, with VS 2010…

    Here is my code for my function………My Stored Procedure is below that…..

    When I select just one of the parameters, the stored procedure returns All of the Commercial_Residentl values, same no matter how many are selected, All of the values for Commercial_Residentl is returned…

    CREATE FUNCTION [dbo].[FnSplit]
    /* This function is used to split up multi-value parameters */
    (
    @ItemList NVARCHAR(max),
    @delimiter CHAR(1)
    )
    RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
    AS
    BEGIN
    DECLARE @tempItemList NVARCHAR(max)
    SET @tempItemList = @ItemList

    DECLARE @i INT
    DECLARE @Item NVARCHAR(max)

    SET @tempItemList = REPLACE (@tempItemList, @delimiter + ‘ ‘, @delimiter)
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
    IF @i = 0
    SET @Item = @tempItemList
    ELSE
    SET @Item = LEFT(@tempItemList, @i – 1)

    INSERT INTO @IDTable(Item) VALUES(@Item)

    IF @i = 0
    SET @tempItemList = ”
    ELSE
    SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) – @i)

    SET @i = CHARINDEX(@delimiter, @tempItemList)
    END
    RETURN
    END
    –use the following line in your stored procedure
    –WHERE ProductID IN (SELECT Item FROM dbo.Split (@Item, ‘,’))

    STORED PROCEDURE…..

    USE [AA_Helper]
    GO

    /****** Object: StoredProcedure [dbo].[rpt_OHE12Month] Script Date: 2/27/2014 10:42:02 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    – =============================================
    – Author:
    – Create date:
    – Description:
    – =============================================
    ALTER PROCEDURE [dbo].[rpt_OHE12Month]

    @Com_Res as nvarchar(10)

    AS
    BEGIN
    – SET NOCOUNT ON added to prevent extra result sets from
    – interfering with SELECT statements.
    SET NOCOUNT ON;

    — Insert statements for procedure here

    SELECT ALSUBSCR.ACCOUNT_NUMBER, ALSUBSCR.COMMERCIAL_RESIDENTL, ALSUBSCR.ACTIVATE, ALSUBSCR.START_DATE, ALSUBSCR.INACT_DATE
    FROM ARLSQ01.DSG.dbo.DICE_ALSUBSCR AS ALSUBSCR
    WHERE (ALSUBSCR.COMMERCIAL_RESIDENTL = (SELECT DISTINCT COMMERCIAL_RESIDENTL FROM AA_Helper.dbo.FnSplit(@Com_Res, ‘,’)))

    END

    GO

  278. Damian Jolly says:

    Perfect! Thanks mate – That’s the answer I needed.

  279. Ranjan says:

    HI i am facing one problem in the Mutivalue selection in SSRS Report.. the thing is that in the Drop down list we have 1400 names and we are passing this all values through Select All (not using customize All option)in the store proc to fetch the data , but when we click on the view report option in Report Manager . the Report is blank and all the vendor names are unchecked.
    in the SP the parameter in nvarchar(Max)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s