T-SQL (SS2K8) :: Appropriate Query For Multi Parameters?
Mar 13, 2014
I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.
Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.
For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.
SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName
I am working with a table that has a column which stores multiple data/values that are comma separated.
I need to be able to query that table and get those rows where the values in that column match a pre-defined search list.
I was thinking of somehow trying to take the search list and convert it to a table(temp or a cte) and then JOIN to the table.
however, since the column may contain multiple values, i would need to parse/separate that first. I am not sure how to parse and then join to a list (if that is even the best way to solve this) to only get the rows where the search column contains one or more of the items we're looking for.
Below is some sample data:
Declare @BaseTable table (PKCol int, Column2Search varchar(2000)) Insert into @BaseTable (PKCol, Column2Search) Select 1001, 'apple,orange,grapefruit' UNION ALL Select 1002, 'grapefruit,coconut' UNION ALL
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1) insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2) insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3) insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4) insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)
[Code] ....
Current result: SVPWk1Wk2Wk3Wk4Wk5YQWk SVP10000201411 SVP02000201412 SVP00300201413 SVP00040201414
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 have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255) SET @myvendedor = '87,30' print @myvendedor SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor) Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to numeric.
I have a dataadapter on my asp.net page and am having issues passing values to the sql statement. See below:
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID WHERE (tblTemp.TempTime BETWEEN @From AND @To) AND (tblTemp.SensorID IN (@Sensor)) ORDER BY tblTemp.TempTime
@Sensor is populated in vb with the selected values from a checkboxlist control. I attempted to test this in the "Data Adapter Preview" window, but continually get an error. Is there a way to pass multiple values to a parameter? Any help would be appreciated, thanks!
I and trying to get some help with Multi-Value parameters in Reporting Services 2005 in VS 2005. I’m new to the product and struggling with the TSQL syntax.
(I have simplified my SQL statement for the purposes of this question) I have a dataset below which has 1 parameter;
="SELECT * " & " FROM dbo.Table" & Iif(Parameters!Sex.Value = "ALL",""," AND dbo.Table.Sex = '" & Parameters!Sex.Value &"'" )
The Sex parameter dataset contains F, M, U and ALL – the above parameter allows me to select on any of the 4 options. I would like to use the multi-value parameter but am struggling to grasp the syntax. Believe me I have tried a few things.
Could someone provide me with an idiots guide on how to make my basic parameter a multi-value parameter?
Greetings - I see a few postings regarding problems regarding multi-value parameters, and see no solution responses.
Am attempting to filter on data using a multiple values drop-down parameter. Dataset has been created and runs correctly. The Multi-value parameter setting has been checked. When report is run and 1 value selected from drop-down, report runs fine. If multiples (or all) are selected, then receive "Query execution failed for dataset "mydataset" incorrect syntax near ','" error.
Can someone please advise a solution to this? Tks & B/R
Hi every body! I have two reports ,in first i have one multi value parameter .in first report i select more than one value ,then in second report i want to have these values becuse i have another multi value parameter ,when i join these reports via navigation in parameters (in first report) i put the values of multi value parameter in fisrt report for value of multi value parameter in second report,but in this case fore example i have it: in first reprt-> navigation->parameters-> ParameterName ParameterVaue EmployeeID =Parameters!EmployeeID.Value(0)
but in second report i need all of my selections in first report not just first value
I know that there have been a number of Posts with reference to Multi-Value Parameters, but none have helped me resolve my issue.
I am relatively new to SSRS, and use it in it's simplist form. i.e. I do not use xml code or SQL Statements, I just use the Menus.
I have created a Multi-Value Parameter (Report > Report Parameters) and have assigned a number of Non Queried Labels and Values. I have also assigned this to the Filter Tab via Table Properties.
When previewing, if I select each parameter individually and view the Report everything is fine. If I select one or more, the report only returns values for the first parameter listed.
Have I missed a step, is there a bug or do I just need to be trainied to use SQL Statements? (Hopefully not the latter!!)
I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.
Code Snippet USE [RC_STAT] GO /****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] (@Region int = Null) AS BEGIN SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type, Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description, ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty, Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget, Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period, Forecast.SalesPerson_Purchaser_Code FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN (SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget ) AS Forecast INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code WHERE (Region.Region_Key IN( @Region)) AND (Forecast.Budget_Year = 2007)
I need help in creating a custom string for a multi-value parameter going against an Oralce 8 database. I'm trying to build a string that looks like ('1401', 'HACT', '1504') If I do it as an expression in a test report the results are perfect but in production it does not like it.
This is what I have based on some examples I have found but it complains it needs a ) These are the last few lines of the query
and f.shipto = a.shipto and f.salesman in & " ('" & Join(Parameters!Salesrep.Value,"','") & "')" GROUP BY a.company_id, a.whse#, f.salesman, .product_line, d.product_group, e.name ) order by conoxx, whsexx, slsrepxx, sortcode
can anybody tell me if it is possible in RS2005 to create a multi-value parameter in wich it is possibility to drill down? Say i want to use a multi-value parameter to show data for one or more specific years or months. Then the multivalue parameter would contain a list of years, with a + sign next to them, making it possible to drill down to the specific months.
Lets say I have two multi-value parameters on a report. One is required, the other is not. The multi-value parameter that is not required is allowed to be blank. In the WHERE clause of my query I wanted to do this:
Code Snippet AND EXISTS (SELECT * FROM SomeTable AS ST_1 WHERE (MyCode IN (@My_Codes2)) OR @My_Codes2 IS NULL)
Of course I can't do that since multi-value parameters are not allowed to be null. I see that they are allowed to be blank, but how do I check for that? I tried
Code Snippet
OR @My_Codes2=''
That doesn't work. I can set a default value of 'None' and say:
Code Snippet
OR @My_Codes2='None'
Unfortunately this doesn't work. As long as the default value is 'None' it's fine. When I fill in the parameter I get the following error:
An expression of non-boolean type specified in a context where a condition is expected, near ','.
Please note that I am not using a TSQL stored procedure here. It's just a plain old embedded SQL statement.
i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.
basically, the report gets loads of data back from DataCube.
if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.
but
i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart
so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)
am doing this by trying to get the selected value or "xxxxx" if noting selected
to hide the second chart when only one category has been selected, tried
But it works fine when there are > 2 categories selected
also
this works fine when there is 2 or >2 category selected
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
"more than 2",
"not more than 2")
it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!
I have a report that needs to use two multi-value parameters that are linked.
The report shows stock availability for the production of trial kits and the requirement is to enter multiple quantities of multiple kits as shown below:
Parameter 1 Parameter 2 Kit A 10 Kit B 15 Kit C 25
Is there a way I can select multiple kits and then enter a specific quantity for each selection?
How do you configure a multi-value parameter so it will allow the user to not enter the parameter?
I'm using integer multi-value parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".
How do you specify a multi-value parameter to allow "empty" or "null"?
I am using SP as my data set to generate report and there are some multi-value parameters which past as an array back to sql server. Since sql server can not handle the array pass in , how to handle it?
I have read that there isn't a simple way to use MVPs in stored procedures, however I'm simply using nested SELECT statements.
Select A.attr1, B.attr4
From( Select * from tb1) A, tbl2 B
Where A.attr1 = ((@param))
For some reason, I'm getting the "Conversion failed when convertng the nvarchar value ..." error. Is there some setting that I'm missing? This is just a text command, not a stored procedure. Has anyone had this problem before?
after the installation of SP1 I have a problem with multi-valued report parameters. The option to select all values are gone. Is there a bug within SP1?
Before the installtion of SP1 multi-valued report parameters works fine, the option to select all values was added automatically within the preview in Visual Studio 2005 and in published reports on the server.
The server was migrated from SQL2000 SP4 to SQL2005.
I am trying to create a SSRS subscription from an asp.net app. I collect the parameter values from the client and make the arrangements to send them to the CreateSubscription method. When the report has single value parameters, my code works perfectly. But if there is a report param with multiple values, I get the error
System.Web.Services.Protocols.SoapException: Default value or value provided for the report parameter '<paramName>' is not a valid value
My parameter is string type and supports multiple values. I am passing the following string as its Value:
I am creating a Line chart report from a table. The source table includes a code column (String), date column (Datetime), and 12 statistic columns (Int). The dates are end of month dates only. ("1/31/2006", "2/28/2006","3/31/2006", etc.) There are about 8 different codes for each month. The statisic columns are the totals for the month for each code for a particular statistic.
On my line chart I want to show one or more statistics for one year for one or more codes. (A line for each statistic for a given code) The report has multi-valued parameters for the codes and the statistics. I have figured out how to create the chart for one or more codes for a given statistic. How can I select one or more statistic? Can any one help me out?
I am trying to use a Multi-Value Report Parameter to determine Visibility, I found this to work great when I only had 2 values (0 or 1), but I have 5 now (0, 1, 2, 3, 4).
I can see what is happening, but I don't know how to write the Visibility Expression to get it to work for all conditions. I can clearly see that when more than one option is selected from the drop down box the Report Parameter goes from Parameters!ReportSelect.Value(0) to
Parameters!ReportSelect.Value(0)
Parameters!ReportSelect.Value(1)
Parameters!ReportSelect.Value(2)
Parameters!ReportSelect.Value(3)
Parameters!ReportSelect.Value(4)
one for each number of variables selected. I need to evaluate for all possible values and if any of them are the one selected I want it to be visable.
Currently I am using this expression on Visability:
I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.
This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!
