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

« Using/Creating an alias for SQL Server 2005

How to close all child windows on closing/logout of Main page/window (Asp.net web application) »

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

December 29, 2008 by Munish Bansal

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.

Like this:

2 bloggers like this post.

Posted in SQL Server, SSRS | 257 Comments

257 Responses

on January 14, 2009 at 7:54 pm Ifeoluwa Kehinde

Thanks a lot. Been trying to figure this out for 2 days. I am working with oracle though.
on January 29, 2009 at 12:04 pm Naveen Sikri

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.
on February 1, 2009 at 6:29 am Munish Bansal

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
on February 6, 2009 at 10:15 pm Angela

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
on February 9, 2009 at 7:21 am Munish Bansal

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
on February 9, 2009 at 6:47 pm Angela

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
on February 10, 2009 at 4:04 am Munish Bansal

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
on February 10, 2009 at 5:23 pm Angela

Hi Munish,

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

Thanks for your help!
Angela
on February 11, 2009 at 6:17 am Raj

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
on February 11, 2009 at 8:13 am Munish Bansal

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
on February 12, 2009 at 6:23 am Raj

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
on February 13, 2009 at 5:04 am Munish Bansal

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
on February 13, 2009 at 5:18 am Munish Bansal

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
on February 16, 2009 at 10:38 am Raj

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
on February 26, 2009 at 9:35 am Munish Bansal

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
on February 27, 2009 at 6:32 pm Zainab

Thanks for help
on March 16, 2009 at 3:34 am Rajesh

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.
on March 16, 2009 at 3:57 am Munish Bansal

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
on March 19, 2009 at 5:18 pm Sima

Thank you, Munish. It was very helpfull and worked from the first try.
on March 20, 2009 at 3:00 am Munish Bansal

Thanks for your comments, Sima.
on March 20, 2009 at 7:26 am xtian

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
on March 23, 2009 at 6:11 am veena

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…
on March 24, 2009 at 3:15 am Munish Bansal

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
on March 26, 2009 at 5:37 am Karthik Prabhu

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.
on March 27, 2009 at 4:50 pm victor

Thanks
on March 28, 2009 at 5:52 am Munish Bansal

Hey thanks a lot, Karthik.
on March 30, 2009 at 4:57 am bnaya eshet

TNX
very useful
on March 31, 2009 at 9:52 am Savan

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
on March 31, 2009 at 3:06 pm Miguel

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?
on March 31, 2009 at 9:09 pm Seth

This is great stuff. Any chance you would send me the splitlist code?
on March 31, 2009 at 9:45 pm Seth

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.
on April 1, 2009 at 3:09 am Munish Bansal

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.
on April 2, 2009 at 3:10 am Munish Bansal

Hi Miguel,

I really don’t get the problem you have mentioned…please explain it a bit more..
on April 2, 2009 at 8:10 pm Savan

Hey Munish,

Hope u have seen my post……

Looking forward for reply
on April 6, 2009 at 3:07 am Munish Bansal

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
on April 8, 2009 at 12:11 pm Munish Bansal

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

-Munish
on April 9, 2009 at 8:09 am Savan

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
on April 16, 2009 at 6:50 am Shruti

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..
on April 17, 2009 at 3:09 am Munish Bansal

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.
on May 4, 2009 at 2:09 pm Spandan Das

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
on May 5, 2009 at 3:25 am Munish Bansal

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
on May 12, 2009 at 9:12 pm HP

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.
on May 13, 2009 at 3:12 am Munish Bansal

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
on May 15, 2009 at 12:26 pm vikram

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, ‘,’))
on May 18, 2009 at 3:07 am Munish Bansal

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
on May 18, 2009 at 12:35 pm Mzwai

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
on May 19, 2009 at 2:52 am Munish Bansal

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.
on May 20, 2009 at 9:13 pm dbenn

This worked perfectly for me. Thank you for posting it. You rock!
on May 21, 2009 at 2:51 am Munish Bansal

Thanks for your comments, Dbenn.
on June 6, 2009 at 6:17 pm John

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
on June 10, 2009 at 10:12 am Munish Bansal

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.
on June 10, 2009 at 3:43 pm badri

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?
on June 11, 2009 at 5:01 am Robert

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.
on June 11, 2009 at 7:08 am Munish Bansal

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.
on June 16, 2009 at 3:23 pm badri

Can anybody help me out?
on June 16, 2009 at 7:18 pm sasi

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
on June 17, 2009 at 5:22 am Munish Bansal

@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
on June 17, 2009 at 5:48 pm Sasi

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

Thanks
on June 18, 2009 at 3:57 am Munish Bansal

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.
on June 18, 2009 at 4:01 am Logi

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
on June 29, 2009 at 10:25 pm Ahmed IG

SUPER SUPER SUPER… Just what I needed and spent a whole day searching for a workaround.. Thanks a billion!!
on June 30, 2009 at 3:05 am Munish Bansal

Thanks for your comments, Ahmed !
on June 30, 2009 at 2:18 pm Priya

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.
on July 1, 2009 at 3:40 am Munish Bansal

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
on July 1, 2009 at 4:06 pm Priya

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
on July 8, 2009 at 6:42 am Subodh

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
on July 29, 2009 at 11:26 am Chetan Patel

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?
on August 5, 2009 at 12:21 am jgpatel

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;
on August 5, 2009 at 5:13 am Munish Bansal

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.
on August 5, 2009 at 5:21 am Munish Bansal

@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.
on August 5, 2009 at 3:36 pm Charles Marsh

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.
on August 5, 2009 at 5:06 pm jgpatel

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
on August 6, 2009 at 6:25 am Munish Bansal

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
on August 6, 2009 at 4:56 pm Charles Marsh

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
on August 6, 2009 at 5:44 pm Charles Marsh

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?
on August 6, 2009 at 5:48 pm Charles Marsh

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

Charles
on August 6, 2009 at 8:21 pm Charles Marsh

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
on August 7, 2009 at 6:24 am Munish Bansal

Hi Charles,
You have to comment out the second
‘select @InputString’ statement. This is there as ‘-select @InputString’.
This will work now.
on August 7, 2009 at 6:38 am Munish Bansal

Hi Charles,

Please put one more condition in the While Loop as:

WHILE @InputString ‘,’ AND @InputString ”

Now this will work fine for you.
on August 7, 2009 at 6:41 am Munish Bansal

@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
on August 7, 2009 at 6:45 am Munish Bansal

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).
on August 7, 2009 at 1:07 pm Andy Vincent

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
on August 11, 2009 at 6:18 pm superSQL

When I try your exact example I get the error message ….. Invalid object name ‘SplitList’. Please help
on August 12, 2009 at 3:02 am Munish Bansal

@superSQL

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

-Munish Bansal
on August 13, 2009 at 10:02 am kavita

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
on August 31, 2009 at 8:05 pm jgpatel

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.
on August 31, 2009 at 8:08 pm jgpatel

(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?
on September 1, 2009 at 6:27 am Munish Bansal

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
on September 1, 2009 at 1:23 pm jgpatel

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
on September 16, 2009 at 6:26 pm Karen Roslund

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
on September 17, 2009 at 8:46 am Munish Bansal

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
on October 12, 2009 at 6:05 pm superSQL

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!
on October 14, 2009 at 8:39 am Munish Bansal

@superSQL,

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

thanks for your comment !
Munish Bansal
on October 21, 2009 at 4:08 pm Jill

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.
on November 20, 2009 at 5:17 am Hemanth

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.
on November 25, 2009 at 9:14 pm krushna

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.
on November 25, 2009 at 9:49 pm krushna

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.
on November 26, 2009 at 11:52 am Munish Bansal

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
on November 26, 2009 at 11:53 am Munish Bansal

Thanks a lot, krushna.
on November 30, 2009 at 2:22 pm krushna

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.
on December 1, 2009 at 4:25 am Munish Bansal

Thanks for the information, krushna.
on December 4, 2009 at 2:44 pm Cami Paulson

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
on December 8, 2009 at 7:03 pm jgpatel

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;
/
on December 8, 2009 at 7:36 pm jgpatel

how many maximum number of paramter we can pass to Stored procedure from multivalues parameters
on December 9, 2009 at 4:42 pm Cami Paulson

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
on December 10, 2009 at 12:42 am David Richardson

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.
on December 10, 2009 at 7:02 pm Isondart

Munish,

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

Regards
on December 11, 2009 at 7:20 am Munish Bansal

Thanks David.
on December 14, 2009 at 12:03 pm Munish Bansal

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
on December 14, 2009 at 12:05 pm Munish Bansal

Hi Cami,

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

Thanks,
Munish Bansal
on December 14, 2009 at 5:28 pm Isondart

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
on December 16, 2009 at 12:43 pm Nikita Mironov

Thanks a lot! I’ve been looking for the solution for 2 days!!!
on December 17, 2009 at 9:43 am Munish Bansal

Thanks for your comments, Nikita.
on December 27, 2009 at 10:19 am jazzy

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!
on December 28, 2009 at 5:20 am Munish Bansal

Thanks for your comment, jazzy !
on January 11, 2010 at 10:14 pm Cami

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
on January 12, 2010 at 6:04 am Munish Bansal

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
on January 12, 2010 at 7:41 pm Cami

That did it!! THANKS SO MUCH!!
on January 12, 2010 at 9:02 pm Cami

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
on January 13, 2010 at 5:19 am Munish Bansal

Thanks Cami.
on January 20, 2010 at 8:15 am Hoflieferant

Thanks Munish!

You just saved my job
on January 20, 2010 at 8:25 am Munish Bansal

Thanks for kind comments, Hoflieferant !
on February 12, 2010 at 4:45 pm Bhaskar

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
on February 15, 2010 at 1:31 pm Munish Bansal

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?
on February 16, 2010 at 12:03 pm Diego

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
on February 26, 2010 at 1:50 am Brian Blessed

Thanks for your help with this.
on February 26, 2010 at 9:45 am Munish Bansal

Thanks for your comments, Brian.
on March 4, 2010 at 6:19 pm Julio Caldas

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

cheers,
julio
on March 5, 2010 at 1:44 pm Munish Bansal

Thanks to you too, Julio.
on March 11, 2010 at 10:06 am Dragon

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
on March 17, 2010 at 11:09 pm Ruth

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
on March 18, 2010 at 9:12 am Munish Bansal

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
on March 18, 2010 at 4:22 pm Ruth

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.
on March 18, 2010 at 4:35 pm Munish Bansal

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.
on March 18, 2010 at 5:51 pm Ruth

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!
on April 2, 2010 at 8:22 pm Diana Bodell

Thank you so much for the really clear explanation and example. This solved a very frustrating problem. You couldn’t have explained it better!
on April 5, 2010 at 8:51 pm Munish Bansal

Thanks for ur comments, Diana !
on April 7, 2010 at 9:50 pm Sam

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
on April 8, 2010 at 1:50 pm Munish Bansal

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
on April 8, 2010 at 2:31 pm Sam

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
on April 8, 2010 at 8:39 pm Munish Bansal

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
on April 8, 2010 at 9:00 pm Sam

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
on April 9, 2010 at 8:45 am Munish Bansal

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
on April 13, 2010 at 3:57 am Nik A

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.
on April 13, 2010 at 8:53 am Munish Bansal

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
on April 13, 2010 at 2:00 pm Sam

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
on April 13, 2010 at 9:36 pm Nik A

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.
on April 20, 2010 at 10:28 am Vasavi

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
on April 20, 2010 at 2:16 pm Munish Bansal

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
on April 21, 2010 at 9:35 am Vasavi

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
on May 5, 2010 at 10:01 am Mini Searches with Answers - Answer My Searches

[...] Passing multi-value parameter in stored procedure (SSRS report) « Tech Updatesstored procedure input parameter multiple values [...]
on May 10, 2010 at 4:34 am malavika

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
on May 20, 2010 at 4:58 am Kunal Singh

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
on May 20, 2010 at 2:27 pm Edward Baker

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
on May 20, 2010 at 3:41 pm Munish Bansal

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
on May 20, 2010 at 8:27 pm Edward Baker

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!
on May 21, 2010 at 7:24 am Kunal Singh

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.
on May 21, 2010 at 9:24 am Kunal Singh

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.
on May 21, 2010 at 9:54 am Munish Bansal

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
on May 21, 2010 at 9:56 am Munish Bansal

@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.
on May 21, 2010 at 9:57 am Munish Bansal

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?
on May 21, 2010 at 11:14 am Kunal Singh

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.
on May 22, 2010 at 7:32 am kunal singh

Yes i m saying the same “default values to be selected based upon thos 1500 or 1000 values coming from DB “.
on May 27, 2010 at 9:51 pm Vijay

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.
on May 29, 2010 at 6:14 am Amit

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.
on June 29, 2010 at 11:45 pm Reddy421

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………)
on June 30, 2010 at 9:21 am Munish Bansal

@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.
on July 2, 2010 at 4:17 pm Anna

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
on July 12, 2010 at 3:02 pm Meg

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.
on August 30, 2010 at 5:09 am frigate

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.
on September 1, 2010 at 12:30 pm Sri

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.
on September 2, 2010 at 8:22 pm Munish Bansal

Hi Sri,

Try using Fields(Parameters!……).
on September 3, 2010 at 11:28 am Sri

Hi Munish,

Fields(Parameters!……) didn’t work. But i figfured it out in the SQl itself and worked.
Thanks for the reply
Sri
on September 17, 2010 at 10:03 pm carl s.

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
on October 4, 2010 at 9:52 am Kally

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
on October 12, 2010 at 4:13 pm Bevin

You don’t need Split function. Just concatenate on the parameters
on October 12, 2010 at 7:41 pm Bevin

If we are able to concatenate in the parameters ,then why use Split function in the first place?
on October 13, 2010 at 7:00 am Jack

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?
on October 13, 2010 at 3:30 pm Munish Bansal

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
on October 14, 2010 at 1:24 am Jack

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
on October 14, 2010 at 9:09 pm Bevin

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,”‘,’”)&”‘”
on October 17, 2010 at 4:48 pm Balu

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
on October 19, 2010 at 2:40 pm Katja

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
on October 26, 2010 at 3:41 pm Bea

This is great and also fast

Just 1 correction to the function above

WHILE @InputString != @SplitChar
on October 30, 2010 at 7:59 am Seasons

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.
on November 12, 2010 at 5:36 pm shreya

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.
on November 23, 2010 at 11:44 am KunalSingh

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.
on November 24, 2010 at 2:47 pm khalid latif

Thanks you very much.
I really appreciate that you spent time just for us
It is very useful post,
on November 24, 2010 at 4:10 pm Nalasi Mendy

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.
on November 26, 2010 at 4:03 pm Munish Bansal

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.
on November 26, 2010 at 4:24 pm Jaya

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 .
on December 2, 2010 at 1:32 am Sam

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.
on December 3, 2010 at 11:22 pm Sam

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.
on December 3, 2010 at 11:24 pm Sam

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
on December 4, 2010 at 1:20 am Steve

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.
on December 13, 2010 at 8:23 am tam

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 ….
———————
% |
on December 19, 2010 at 5:30 pm Sam

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.
on December 19, 2010 at 6:21 pm Sam

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 + ‘|%’
on December 23, 2010 at 6:26 pm Parikshit

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
on January 4, 2011 at 5:08 pm mos

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
on January 14, 2011 at 4:37 am Muhammad Omer Zubair

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=================================
on January 17, 2011 at 9:51 pm Mike Partridge

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
on January 19, 2011 at 11:18 pm Mike Partridge

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.
on January 20, 2011 at 6:15 pm GT

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
on January 21, 2011 at 9:07 am Ronald

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
on January 25, 2011 at 7:26 pm Jimmy

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.
on January 26, 2011 at 6:08 pm Jimmy

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
on January 27, 2011 at 8:18 am Ronald

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
on January 28, 2011 at 10:00 pm Jimmy

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
on February 9, 2011 at 9:56 am Sasikala

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.
on February 9, 2011 at 6:38 pm Jimmy

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 aram1 = ‘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
on February 18, 2011 at 1:12 pm suv

I applied the way you mentioned and it worked.
Thanks for the post… much appreated.
on March 5, 2011 at 5:31 am Shibu P

Thanks, your post is helpful.
on March 5, 2011 at 5:29 pm Rajesh

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?
on March 8, 2011 at 5:50 pm Ronald

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
on March 17, 2011 at 7:53 pm Enrique

Thanks so very much for posting this. It saved me a tremendous amount of time and frustration. Excellent work!
on March 23, 2011 at 2:42 pm sarwan

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.
on March 23, 2011 at 4:48 pm Kim Kieu

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
on April 7, 2011 at 11:42 pm Rajesh

Thanks ronald for the reply….i have done the cascading parameters and reduced the list.
on April 7, 2011 at 11:48 pm Rajesh

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.
on April 13, 2011 at 12:39 am Kavitha

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.
on May 8, 2011 at 7:14 am Santosh Salvi

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.
on May 16, 2011 at 2:38 am Paul

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?
on May 19, 2011 at 6:23 pm Angela

Hi Munish,

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

Thanks,
Angela
on June 21, 2011 at 1:47 am HC

Hi Munish

This solve my problem completely.
Thanks for it! ^^
on June 22, 2011 at 7:51 pm Karen

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?
on August 10, 2011 at 4:00 pm Biju

Thanks,
This was something really useful. Great
on August 15, 2011 at 11:26 am Rob

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.
on August 17, 2011 at 10:10 pm komal

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
on August 18, 2011 at 3:35 pm Stan

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!!
on August 19, 2011 at 1:19 am Chetan

I have a stored procedure, which emits @employeeID parameter, But that parameter is not accepting any query values, wat would be the problem?
on August 24, 2011 at 12:24 pm Frans

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é
on August 25, 2011 at 12:47 pm Lalit

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.
on September 9, 2011 at 5:19 pm Ankit

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.
on September 19, 2011 at 1:23 pm Ned

This is awesome.

Thanks!
on September 22, 2011 at 3:09 pm Mike

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
on September 23, 2011 at 11:40 am durga

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
on September 23, 2011 at 9:53 pm Ranjeet Singh

awesome
on September 27, 2011 at 8:48 pm Tony

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.
on October 7, 2011 at 9:37 am Dhinesh

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
on October 19, 2011 at 1:24 pm Akshay

Hi munish,

Is there any limitations on records a single ssrs reports can hold. I am using ssrs 2008. Please its urgent.
on November 10, 2011 at 3:17 pm MM

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….
on November 10, 2011 at 3:19 pm MM

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
on December 16, 2011 at 2:40 pm Naveen Reddy

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.
on December 19, 2011 at 10:46 am Tuhin

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
on December 27, 2011 at 9:59 am Tuhin

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…
on January 13, 2012 at 4:19 am jotiram

hi sir,
I got all the ans from your note ,
but how I call ssrs report and pass parameter to this report from code
on February 17, 2012 at 11:23 pm BlackRacerBoy

Thank you! This is the best article I’ve seen so far about multivalue parameters.
on February 27, 2012 at 2:24 am migules

Thanks munish, how to create stored procedure in ms sql server that return a two or more values.
on March 13, 2012 at 6:29 am anki

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.
on March 13, 2012 at 12:22 pm anki

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?
on March 16, 2012 at 6:34 am Satish

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.
on March 22, 2012 at 5:43 pm Siladitya Basu

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
on March 23, 2012 at 4:59 am anki

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?
on April 20, 2012 at 3:14 am Siraz

thanks for the solution
on April 20, 2012 at 3:23 am Siraz

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.
on April 23, 2012 at 5:03 am anki

Hi Siraz,

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

Comments RSS

	shimon on Extending SQL Server Reporting…
	How to get 5000 face… on rrdsc00295
	Emil on Dynamic columns mapping – Scri…
	anki on Passing multi-value parameter …
	Siraz on Passing multi-value parameter …
	Siraz on Passing multi-value parameter …
	Pranav Kapadia on How to remove duplicate record…
	diablo 3 beta on Disclaimer
	Shirley Kobie on Disclaimer
	Venice Zapata on Disclaimer
	外遇抓姦 on Disclaimer
	keerti on Dynamic columns mapping – Scri…

Tech Updates

…keep in pace with technology !