Transact SQL :: Passing Multi-valued Data As Single Parameter In Query
Jul 2, 2015
I have a sp where query is as below.
DECLARE @ServerCIName varchar(5000)
SELECT * FROM dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.
I have a multi-valued parameter that I want to pass to a subreport. The values are 11, 12, 13, and 14.
So here's what I've done:
1. For the properties of the clickable field of the first report, I have gone to the Navigation tab and chosen the subreport in the "jump to report" pulldown.
2. Then I clicked on the Parameters button and added a parameter name for the multi-valued parameter I am trying to send. For the Parameter value, I have tried 11,12,13,14. I have also tried =Split(11,12,13,14)
3. On the subreport, I create a report parameter with the same name, data type is string, multi-value is checked, and the available values pulldown is populated by a query.
So I run the first report and click on the link that brings me to the subreport, but all the subreport shows is the results for 11, not 12, 13, and 14.
How can I get the second report to understand that I want it to show all the records related to 11, 12, 13, and 14? If I bypass the first report and simply use the drop-down to choose 11, 12, 13, and 14 in the subreport, it works fine. I just can't seem to figure out how to correctly have the first report tell the subreport that it wants 11, 12, 13, and 14.
Hi, I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC. To consider a sample code:
EXEC('select * from country where country_key in ('+@country+')')
where @country is a multi-valued report parameter.
I have a query :Exec 'Select * From Receiving Where Code In (' + @pCode + ')'@pCode will contain more than one string parameter, eg : A1, A2, A3How can i write that parameters, I try use :set @pCode='A1','A2','A3'but get an error : Incorrect syntax near ','Please help meThanks
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
Hello, I am attempting to create a cascading parameter. Parameter's 1 & 2 are multi-valued. Datasets 1 & 2 supply Parameter's 1 & 2. The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'. The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1. Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing. What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?
I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.
Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.
I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...
I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?
Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?
Hello Everyone, I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects. Is this a bug in SSRS, and is there another way to accomplish this. Please let me know.
I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about?
I'm running 2014 enterprise and getting an error on this form of a query...it says the multi part identifier "mns.col3" could not be bound. I'm aware that a cross apply would be more appropriate but i'm just prototyping and probably going to move to a set based approach anyway.The udf returns a table.
select mns.col1, mns.col2 from table1 mns left join dbo.udf_udf1(@firstofmonth,@lastofmonth, mns.col3) x on 1=1
I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .
I have a report that includes two multi-valued parameters. In the Default Values section, I choose 'from query' and select dataset and value field. In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field. When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected). How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.
I have a report running with both a db and Analysis services data source..When I change any of the multi-valued parameters a post-back is done and then the page is re-loaded all good. However. When I change one of the single value parameters a post-back occurs however the page said "Report parameter values must be specified before the report can be displayed. Choose parameter values in the parameters area and click the Apply button." If I THEN press APPLY the report loads perfectly with the selected option.
I have un-hidden ALL my parameters and they all seem to get values.. Does anyone have any clue what could be going wrong ?
we are using SSRS 2012.Oracle 9i as back end database. Select A,B,C from view where A in (Param1) in above query when I am passing 1 value getting output in 30 sec. when I passed two values getting time out error in SSRS. how to pass multiple values in Oracle 9i in SSRS report.
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
We run std 2008 r2. I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested.
But when he selects * he gets a single row.
I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it.
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
I've seen some theads on this but cant seem to get this right. Report A has 5 parameters 2 of them are multi value-- when I click on part of Report A I want to jump to report B and pass all parameter values from report A. The single value ones work but multi value only passes on value and not all values selected-- what am I missing here? Do I need to pass the values in the multi value parameters in an array??
I am currently working with 3 multi-valued parameters whose data sources are queries. The first 2 are required to have entries, 100% of the time, but the third one may or may not require selecting a value. Parm3's data source is filtered by the selections of Parm1 & Parm2. The data source for my report references Parm3 in a derived table that is then LEFT OUTER JOINed.
In the cases where the report does not require any selection from Parm3 I am still required to pick at least 1 entry. Can anyone shed some light on this, or provide a solution so I am not forced to pick any if I don't want?
I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine. In the dataset , I set command type as Text and typed the following statement. EXEC spMultiValue " & Parameters!Month.Value &" its not returning anything. can anyone tell me how to pass multivalue parameter to stored procedure. Thanks for your help.
Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.
Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
Hello I created a table adapter with the following query select * from building where buildingname in (@param1) I used the query builder to the test the query and it works ok if I pass a single value ex: name: @param1 value: Building1 but when I use the value: Building1, Building2 then it doesn't return anything If i do a direct sql: select * from building where buildingname in ('Buiding1','Buiding2') returns many rows. Now I have a class that has the following function<System.ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Select, False)> _ Public Function getBuilding(ByVal building As String) As myDataSet.buildingDataTableReturn adapter.getBuilding(building) End Function When I call this function I try to pass the building value sorta like dim building as string = 'Building1,Building2' and it doesn't work. I tried a whole bunch of variations. How are you supposed to format the parameter string? Thanks Peter
Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.
CREATE PROCEDURE TestParam1 @Map varchar(4) AS BEGIN SET NOCOUNT ON;
SELECT ID, Map_Area FROM TestParamOne WHERE Map_Area=@Map END GO
I have researched this question and so far have found very different opinions as to how or if it is possible.I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them.
Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:
paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000))) FROM Reports..SalesForce t2 WHERE t1.SalesManagerID = t2.SalesManagerID ORDER BY SalesManagerID FOR XML PATH( '' )), 3, 1000 )
To create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.
Hello all - I currently have a project that has a gridview on the front end. The user can select multiple items from this grid, hit a button and all of the selected records should be updated in the process. However, this resultset can have a large amount of data coming back, and I'm stumped on how to pass all of the ID's to the sp. I'd rather not call the SP for each record selected, as there could be 1,000 items selected, and well, I'd rather not call the SP 1000 times :p. I thought of generating a comma delimited list as I'm looping through the grid and using dynamic sql, but the IDs are about 6-7 numbers long, and including comma, would take up almost all of the max space in a varchar. Are there any good solutions to this problem? Passing the items as an array? Generating a data table in .NET and passing that? Any help would be appreciated. -Jaime
I got issue when passing multiple values to a single parameter. Here is my stored procedure as following:
CREATE PROCEDURE [dbo].[School] @Grade AS varchar(50), @Class As varchar(50) AS BEGIN SELECT Name, Grade, Class FROM School WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class )) END
In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter.
