.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

multi-valued parameter

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
I need to do something like this: DECLARE @SalespersonCode SET  @SalespersonCode = ''smith' + ',' + 'jones''@SalespersonCode = ''SMITH','JONES','ADAMS'' SELECT * FROM Salesperson WHERE SalespersonCode in (@SalespersonCode) but I cannot get any results to return.  Is there a way to put multiple parameters in a single variable to use with in IN statement? John Schroeder* FROM Salesperson@Salesperson Varchar(100)

View Complete Post

More Related Resource Links

SSRS 2005 Error: Cannot add multi valued query parameter for data set because it is not supported b



getting the above error when running a report in SSRS 2005, using a stored procedure with many input parameters,

connected to a Sybase IQ database,

I want to have some of the prompts to be multi valued prompts.......

Please advise on how to fix......



Creating a subscription that sends out an email for each value in a multi valued parameter


I have a report that has  a paramter (drop  down) which has about 1000 possible values. I need to create a subscription that generates an excel sheet for each value in that paramter and send it as an email attachment to a selected set of people based on the paramter chosen.

assume that the paramter is called bank name and has the following possible values, pnc, wachovia, boa..

when the subscription runs

it should run the report with the parameter value for bankname as "pnc" generating an excel and emailing it to user@pnc.com

then it should run the report with the parameter value for bankname as "wachovia" generating an excel and emailing it to user@wachovia.com

then it should run the report with the parameter value for bankname as "boa" generating an excel and emailing it to user@boa.com

and so on

What is the best way to accomplish this


Thanks for any help in advance



Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

NULL label doesn't appear on drop down or multi-select parameter.

Hi, I am using SSRS 2005 SP3. I have the following query that I use for my parameter: SELECT 0 AS c1, 'NOT Specified' AS c2 UNION SELECT 1 AS c1, NULL AS c2 UNION SELECT 2 AS c1, 'Test 1' AS c2 UNION SELECT 3 AS c1, 'Test 2' AS c2 Let's call my parameter prm1. The type of prm1 is string and it has "Allow null value" and "Allow blank value" options ticked. C1 is the value field and C2 is the label field for prm1. When I preview the report, and I didn't see NULL in my drop down list,  but that record has been shown as 1 (which is the value). I am wondering if this is a normal behavior of SSRS? It's not a big deal. I can replace NULL with something else, but I am just curious. Cheers, Uzzie

ReportViewer 2008 multi-select parameter alignment

We are using SSRS 2008 SP1 & ReportViewer 2008 SP1. The multi-select parameter options are left aligned in the drop-down listbox on the Report Server but when run thru ASP.Net & reportViewer, they are aligned in the center of the drop-down list. This is very problematic because the width of the drop-down is also narrow compared to the text size of the fields. I tried wrapping the ReportViewer in a Div tag (<div style="vertical-align: left; text-align: left">), but still no joy.  Would appreciate any help thanks.

Issue with multi-valued crawled property

Hi, The problem is, we have a multi-valued crawled property (Managed Metadata type) which we are mapping to a managed property. The managed property has been created, setting MergeCrawledProperties to false.   On the page http://technet.microsoft.com/en-us/library/ff393811.aspx , it states that if MergeCrawledProperties is not set, only the first element is stored in the managed property. We are seeing this behaviour in our environment.   That is causing an issue with refinement results based on this managed property, because it is not including the second element as a result. For example, if we crawl the following content:   Doc1.doc           Property1;Property2 Doc2.doc           Property1;Property2 Doc3.doc           Property2   Refinement results look like this: Property 1           RefinementCount = 2 Property 2           RefinementCount = 1   Ideally we would like to see Property 2’s RefinementCount = 3 to accurately reflect our source data.   We also tried setting MergeCrawledProperties to true , however this merges results together.   For example, with our scenario above, refi

Passing multi values to another multi value parameter

Hi, my requirement is as follows I have a parameter1 in which i populate values from database. the user can select multiple values from parameter1. after the parameter1 i have another parameter2 which is also mutliselection value. i,e depending on the selection of parameter i need to populate remaining values in Parameter2 (which is also a multi selection value). i have splited the values from parameter1 and passed the value of parameter1 (parameter1.value) to parameter2. but i am getting the following error An expression of non-boolean type specified in a context where condition is expected, near ',' incorrect syntax near the keyword 'ELSE' can anyone help me in this Thanks, Rajesh Peethala

multi parameter sub report



i have a sub report that i want to pass the same multi-select parameter through as in the main report itself.

i have set the parameter in the sub report exactly the same as in the main report but i get an error.

also, in the main report i have clicked on the properties of the sub report and selected the parameter, the only difference is that the paramter automatically end with "(0)" and i dont know what this means.

Any help would be much appreciated,





Multi value Parameter Float datatype in Sql Reporting Services2008


Hello there,

I am having a report where i am having 2 parameters to pass the values to Store Procedure in SQL 2008. 

1) @Name Nvarchar(50),

2) @Cost Float

I can able to pass the Multiple values to parameter 1 . From the example below link


but i cannot use that for the float data type. How can i pass multiple values of float data type to my store procedure any help much appriciated..

Thanks Jeevan Dasari....-:)


Issue with SSRS multi-parameter control


Hi Folks,

I am having the attached error with a multi-parameter filter control. This is happening only when there is just 1 value in the list.

Has anyone come across this issue and is there a solution to this? I am using SSRS 2005.

Sorry was trying to attached a snap-shot here but don't know if thats possible.

I'll try yo put the issue in words. The list box does not show the item in it. It does not render fully.



Table-valued parameter and DataTable from ADO.NET


I'm going to use TVP:s to enhance performance in my c# .net fw 4 application which uses stored procs in SQL Server 2008. My first test was very successful and I inserted a batch of rows from by using a TVP in a stored proc. So far so good. Now to my question: when looking for the performance profit with SQL Profiler I saw it was really fast, but the generated sql-code made me a little bit confused. My "table" (DataTable class) from the .net-app was "retabled" like this in sql:

declare @table MyTvpType;

insert @table values (1, 2, 3...); ... x number of times

exec MyProc @table;

This was quite surprising, but when examining my .net code, I think there should be som way to "import" my MyTvpType into .net code, and avoid the extra delaration and inserts done as it is now. I'm using this code in .net:

Issue with multi valued parameters in SSRS using Oracle data source


Hi All,

I have a dataset which is getting data from oracle datasource and my Dataset query expression is

="select To_Char(Time_Stamp,'Month') as Month, Node_Name as Device,Connection,MSNAME,Monitor,Avg as Average,Max as Maximum FROM OracleDataVW where ((monitor='X' and msname in ('Y')) or (MONITOR = 'CPU Utilization' AND MSNAME = 'utilization')) and TO_Char(Time_Stamp,'Mon YYYY') in  ('"+ Join(Parameters!Parameter1.Value,",") +"')"

My parameter is multiValued parameter. The above query is working good if i select one value,however if select more than one value or all it is not giving me the data. I tried to put Ltrim and Rtrim in Join but it is giving me another syntax error.

Please help me with this

SSRS Multi value Parameter


I Have a Report Parameter (single Value)@Path. The Available Values for @Path are from a Dataset.

The Values are like this. \a1\b1 , \a1\b1\c1, \a1\b2, \a1\b2\c1. Now if i select the value \a1\b1 , i will get  all of the values for under the  path \a1\b1. ie., it selects '\a1\b1%'. The Main dataset of the report has the query like this. Select Col1,Col2 WHERE path = '@Path+'%' It works fine until now.

Now I want to make this Parameter to select multiple values. So I changed the Properties of the Parameter and the SQL like        Select Col1,Col2 Where path IN (@Path). This works fine as i can select  multiple values \a1\b1 and \a1\b2 and also i can get the result correctly covering these two paths. But the problem is , i want to cover the total sub paths also. So if i select \a1\b1 and \a1\b2 i should get the result covering \a1\b1\c1 and \a1\b2\c1 also.

Please help!!

Table-valued parameter with ObjectDataSource


I'm trying to figure out if it's possible to use an ObjectDataSource Update method with a table-valued parameter.  Here's my ODS:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
        SelectMethod="GetTestData" TypeName="TestProject.BLL.TestBLL"
            <asp:Parameter DefaultValue="65" Name="testId" Type="Int32" />

I bind this to a GridView but I'm not looking to edit row by row.  Instead my GridView has a bunch of TemplateFields with textboxes.  I also have a Button that when pressed, invokes the Update method of the ODS.  So then the "UpdateTestData" function in my TestBLL is called.  This looks like this:

FullTextSqlQuery using Multi-Valued Lookup Field

Hi There,

I have written a bespoke search webpart powered by the Enterprise Search FullTextSqlQuery object (MOSS 2007). Everything is working well apart from one slight hiccup:

Part of the requirement is to be able to filter the result set using the value of a metadata property on the list items. This property is of the  Multi-Valued Lookup type. A user would assign one or more lookup values to the item and then would expect it to be returned in the search results when one or more of these values matches the filter criteria.

I've looked all over Google and I can't find any examples of where a multi-valued lookup field has been used in the WHERE clause of a full text sql query. Could anyone point me in the right direction?

Thanks very much,


Multi-Value Parameter Dynamic MDX Syntax


I have the following SSRS MDX working with single-value parameters (Note the StrToMember() function calls below).  I need to write a similar SSRS report which has a multi-value parameter which will replace the TopCount([Dim Developer]...) below.  I'm uncertain how to write similar syntax for multi-value parameters.

SET [Top 10 Developers] AS TopCount({Filter(Filter([Dim Developer].[Developer Id].Children,
                                [Dim Developer].[Tracked].[Tracked].&[Actively Tracked]),
                         [Dim Division].[Hierarchy].[Country].&[No Country])},
                     10, [Measures].[3 Year Total])
SET [Rest of Developers] AS {Except(Filter(Filter([Dim Developer].[Developer Id].Children,
                                [Dim Developer].[Tracked].[Tracked].&

Multi Valued Fields flooding the term store



In the User Profile store we have lots of multivalued properties. Somehow it seems like all the values for the Multi Value fields for user profiles are getting auto populated in the term store.

Can some one explain if by default Multivalued attributes get added to term store?

Also how to disable this behavior. We do not want some of the multivalued fields to get added to the Term Store.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend