Multi-value Parameters
Jun 29, 2007
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?
Any help would be great.
View 1 Replies
ADVERTISEMENT
Apr 12, 2005
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!
View 2 Replies
View Related
Jun 24, 2007
Now do you know how to take multi-value parameter array values and pass to Sql Stored Proc for summing ?
View 3 Replies
View Related
Mar 9, 2007
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
View 6 Replies
View Related
Nov 4, 2007
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
Can anybody help me?
View 1 Replies
View Related
Jan 21, 2008
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!!)
Help would be very much appreciated.
Shodman
View 5 Replies
View Related
Sep 5, 2007
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)
END
View 5 Replies
View Related
Jan 14, 2008
I have a MultiValued parameter called Owners, which stores Owner Ids.
I wish to set the value of a textbox to the following:
=iif(3 IN(Parameters!Owners.Value),"foo","bar")
but the "IN" is not recognised.
How can I achieve this functionality using an expression?
Thanks,
Kate
View 1 Replies
View Related
Nov 8, 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
View 8 Replies
View Related
Jul 21, 2006
Hi,
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.
Any help greatly appreciated.
Christopher
View 5 Replies
View Related
Jul 19, 2007
Hi,
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.
Thanks,
DD
View 3 Replies
View Related
Apr 15, 2007
Hi
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
e.g. for the third chart
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
Parameters!LOOKUPITEMCategoryDescription.Label(2),
"xxxxx")
doesnt work - gives an #Error as a reult
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 !!!
help please
thx
View 4 Replies
View Related
Apr 3, 2008
Hi,
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?
Any help or advice is greatly appreciated.
Thanks.
View 1 Replies
View Related
Apr 26, 2007
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"?
View 5 Replies
View Related
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
[Code] ....
View 3 Replies
View Related
Jun 1, 2008
Is anybody here knowing how to create a dynamic query based on a multi-value parameter?
e.g. there is a multi-value report parameter called names. For a static query, the where clause of a select statement likes the following
select * from students where name=@names
For the dynamic one, I tried something like the below, but it did not work.
="select * from students where name=(" & Join(Parameters!names ,',') & ")"
Any suggestion would be great appreciated.
Thanks,
KY
View 2 Replies
View Related
May 8, 2008
Hello all,
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?
thx
View 3 Replies
View Related
Jun 8, 2007
Hello,
I have the following problem.
i have a report where you need to fill in a few parameters
start date, end date and subject selection.
the subject selection is a multivalue paramter (select all, param1, param2, param3)
the multivalue parameter is based on another dataset with paramid as value field and paramdescription as value label.
in my report i want to display something like this :
from startdate to enddate
summary for campaign(s) : selected parameters.
in the selected parameters is can display a =join(Parameter!campaig.value) but this only returns the 32bit guid.
instead i would like it to display the parameterdescription label.
anybody has some ideas ?
greetings
vince
View 1 Replies
View Related
Aug 30, 2005
I'm trying to use cascading report parameters using the mult-value select option. The first parameter is used as input for second parameter.
View 6 Replies
View Related
Jul 9, 2007
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?
Erik
View 1 Replies
View Related
May 2, 2006
Hi,
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.
So long
Lemmi
View 41 Replies
View Related
Apr 24, 2007
Does anyone know how to set multi-value parameters using ReportExecutionService in C#?
If I have
rsExec = new ReportExecutionService2005.ReportExecutionService();
.
.
.
ReportExecutionService2005.ParameterValue[] parameters = new ReportExecutionService2005.ParameterValue[2];
parameters[0] = new ReportExecutionService2005.ParameterValue();
parameters[0].Label = "Report_Begin_Date";
parameters[0].Name = "Report_Begin_Date";
parameters[0].Value = "4/15/2007";
parameters[1] = new ReportExecutionService2005.ParameterValue();
parameters[1].Label = "SalesID";
parameters[1].Name = "SalesID";
parameters[1].Value = ??? //Here, this parameter should accept multiple values like 200, 201, 202, etc.
rsExec.SetExecutionParameters(parameters, "en-us");
How would I set the SalesID to take multiple values?
View 12 Replies
View Related
Feb 15, 2007
Hi folks,
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:
AVA,BEL,CAL,CAP,CCA,CEL,COC,COM,ECE,EDT,EMC,EMT,EPB,EPC,EPM,EPO,EPS,ETB,MET
where each element separated by comma is a valid param value. Here´s the code I use to set the params values before the call to CreateSubscription:
Dim ReportParams As New NameValueCollection
ReportParams = Session("ReportParams")
NumeroParametros = ReportParams.Count
Dim parametrosRpt(NumeroParametros - 1) As ReportService2005.ParameterValue
For i = 0 To NumeroParametros - 1
Dim parameter As New ReportService2005.ParameterValue()
parameter.Name = ReportParams.GetKey(i)
parameter.Value = ReportParams(i)
parameter.Label = Nothing
parametrosRpt(i) = parameter
Next
RS.CreateNewSubscription(Session("Reporte"), "Some description", scheduleXml, txtPara.Text, _
txtCopia.Text, txtCopiaOculta.Text, txtAsunto.Text, comboFormato.SelectedValue, memoCuerpo.Text, _
parametrosRpt)
Getting nuts on this one. Any help/code samples will be greatly appreciated.
Cato
View 9 Replies
View Related
May 7, 2007
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.
PeriodEndDate Code Statistic1 Statistic2 Statistic3 Statistic4 ...
XX/XX/XXXX XX 999 999 999 999 ...
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?
Thanks,
Fred
View 1 Replies
View Related
Apr 11, 2007
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:
=IIf(Parameters!ReportSelect.Value(0)= 1, FALSE, TRUE)
But this only works if the "=1" is the 1st one selected.
I need it to work for all 4 options, so that all selected will be visible.
Does this make sense?
View 7 Replies
View Related
Jul 3, 2007
Hi There.
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!
Thanks, Mike
View 4 Replies
View Related
May 7, 2008
I am trying to selectively show or hide a table based on the values in a multi valued parameter. How can I examine all values (simulate the like functionality) within an expression?
View 4 Replies
View Related
Dec 20, 2007
Hello,
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.
Can any one help me with this. Its very urgent.
Thanks,
SqlNew
View 1 Replies
View Related
Jan 10, 2007
Hello,
I have a report with a multi-value parameter. In its' drop drop box, I can select ALL or I can individually select single items, but it doesn't appear that I can select a range of items using the shift key. The problem is; occasionally the user will want to select everything, and them eliminate an item or two. The list it too long to select every one individually. It would be an easy solution if hey could select the entire list, then deselect what they don't want. Is there a way?
Thanks.
View 1 Replies
View Related
Aug 27, 2007
Hello,
since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"
First Parameter:
available vlaues: From query
dataSetsGetCountry (defined as select from tblCountries)
ValueFied:ContryName
LabelField:CountryName
DefaultValues: from Query
dataSetsGetCountry
ValueFied:ContryName
SecondParameter:
available vlaues: From query
dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)
ValueFied:AreaName
LabelField:AreaName
DefaultValues: from Query
dataSetsGetAreas
ValueFied:AreaName
First time I open the report
the first and the second parameters are properly filled and for both parameters "select all" is checked.
I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool
Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!
Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?
Thankx for any helps!
Marina B
View 7 Replies
View Related
Feb 6, 2007
I'm trying to list out my a multi-value parameter in a table or list. Is this possible?
So far I've tried the simple do-it-the-same-way-as-a-dataset approach, and that doesn't work because it comes back with an error saying I need a dataset in my table.
I then tried creating a dataset that's identical my multi-value parameter, but I was stumped when it came to creating the correct SQL. I am able to pull the last value of my parameter into a dataset with a simple SELECT @MyParam query, but that's not going to cut it...
Any more ideas? Thanks!
View 1 Replies
View Related
Sep 11, 2015
I have a multi-value parameter that I am having a hard time writing a COUNT expression for in SSRS. Here is the situation:
1. If the "(Select All)" in the drop down is selected, COUNT all last names for ALL of the Auditor parameter
2. If a specific or multiple auditors are selected from the drop down, COUNT all last names based on that selection for the Auditor parameter
Currently, I am having it COUNT by ALL and it works but if a specific or multiple auditors are chosen, then the COUNT doesn't work.
View 3 Replies
View Related
