How To Morethan One Value To Single Parameter
Nov 25, 2007
Hi everyone,I am passing parameter @Market, which has 3 values(okc,nfl,La). In BM_Markets table there is a column bm_name, it has cities.
Oklahoma City, tulsa cities comes under OKC Market,
'daytona','jacksonville','melbourne','north florida','orlando','west florida' cities comes under NFL Market and
'atlanta','dallas','houston cellular','pac los angeles' cities comes under LA Market.
By default, All Markets should Select. IF Client select only two markets, then report should display only two markets data.
I wrote query like this,
select transaction_status_desc,count(transaction_id)
from psc
where (creation_date between @startdate and @enddate) and @Market IN
(select distinct
case
when bm_name in ('oklahoma city','tulsa') then 'okc'
when bm_name in ('daytona','jacksonville','melbourne','north florida','orlando','west florida') then 'nfl'
when bm_name in ('atlanta','dallas','houston cellular','pac los angeles') then 'others'
end
from bm_markets
)
group by transaction_status_desc
In Report Parameters Properties window, i selected Multi-Value check box, but when i preview the report, it is giving following error
"An expression of non-boolean type specified in a context where a condition is expected,near','
Incorrect syntax near the keyword 'group'."
Could you please give me some advice, how to write this query for multivalue parameters.
Thanks
Santosh
View 4 Replies
ADVERTISEMENT
Dec 18, 2007
Retrieve details of all the titles whose name contains the word computer and the word cooking (words can be present in any order. Example, Computer xyz cooking or Cooking pqrs computer)
View 4 Replies
View Related
Feb 4, 2008
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.
Thanks
View 4 Replies
View Related
Jan 18, 2008
I have an application that needs user parameters for some reports, and it allows them to enter any amount of said parameter, so the query is built like this:"Select ... Where ParameterColumn IN (" + txtParameters.Text + ")";the parameters textbox only allows numbers and commas to be on the safe side of SQL injection. now, due to this the query has to be built dynamically in the code behind page. is there any way to use a single parameter to add all of those values? and set the parameter to the text in the control during design mode to the datasource control?something like: Select ... Where ParameterColumn IN (@Parameters) <asp:ControlParameter ControlID="txtParameters" Name="Parameters" PropertyName="Text " />whenever I try that I get something along the lines of "Error converting nvarchar '1,2,3' to datatype int"Thanks!
View 3 Replies
View Related
Mar 12, 2014
I am looking for a simple way to do multiple values in one single parameter in my simple Stored Procedures. Let's say for example I have a column called RoomNumber and the value data type is INT. Here is my Stored Procedures:
CREATE PROC ROOMVACANCY
@RoomNumber int,
SELECT vacancy, roomnumber
FROM hoteldb
WHERE Vacancy IN (@RoomNumber)
END
The value for roomnumber has 100 records. I want to be able to select for more than selection when I execute this stored procedures. How do I do that in the simple way?
View 10 Replies
View Related
Aug 29, 2007
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
View 4 Replies
View Related
Mar 6, 2014
I am writting the T-SQL using some parameters. One of the parameter is to display the ZoneID which has about 2100 records. So I declare the parameter (called it @ZoneID varchar(max)).
Now, how do I make all the value available for multiple selection? For example, if I execute the query, I can put the ZoneID (for example:) YH09, TR45, TTS0.
View 2 Replies
View Related
Mar 9, 2014
How do I pass the multiple value in the single parameter? Here is the select simple select statement with two fields (ID and Map_Area):
SELECT *
FROM TestParamOne
Here is the output:
ID Map_Are
12345KK45
657463IIY7
34345FGD
44342DFRE
4646DSAW
424245DSAW
12121DSAW
5753FRDE
575737FRDE
1121FRDE
1121F5FR
646462F5FR
8568F5FR
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
View 7 Replies
View Related
Dec 13, 2007
it looks like a multi value parameter is the only kind of parameter that displays a checkbox.
A single checkbox for yes/no parameters are more intuitive to my users than 2 choices from a query.
I tried to set the multi value attribute on such a param but at run time get errors from a couple of places 1) expressions that reference such a parameter, seemingly confused about the value field being allowed to be either an object or an integer 2) unchecking the checkbox causes a prompt demanding that a value be set.
Does anybody have a trick or see what I am missing in trying to make a parameter act like a single yes/no checkbox in just one line of a dropdown parameter list?
View 3 Replies
View Related
Jan 25, 2008
In my form i have a list box and i need to display paticular records of all employees I selected.So please tell me how to pass multiple values from a single parameter.
currently i am able to pass single value through a parameter using following code
osqlreport.reportViewer1.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver");
osqlreport.reportViewer1.ServerReport.ReportPath = "/employee/5_0emplyeedata";
List<Microsoft.Reporting.WinForms.ReportParameter> paramList = new List<Microsoft.Reporting.WinForms.ReportParameter>();
string stritem;
stritem = Convert.ToString(this.lstSelectedData.Items[0]);
paramList.Add(new Microsoft.Reporting.WinForms.ReportParameter("paraempno", stritem, false));
osqlreport.reportViewer1.ServerReport.SetParameters(paramList);
osqlreport.reportViewer1.RefreshReport();
osqlreport.Show();
what changes do i need to do to make it work.(lstSelectedData is my list box)
View 3 Replies
View Related
Jan 18, 2008
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
View 4 Replies
View Related
Jun 1, 2015
Below is the query for my procedure
ALTER PROC [dbo].[sp_GetInvitationStatusTest]
(
@invited_by NVARCHAR (50)
)
AS
BEGIN
-- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by
select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
View 4 Replies
View Related
Feb 26, 2008
We have a .NET drop down, which gets populated as the user types in letters(last name). If the user types in the single quote we get the error about not escaping the single quote. Question is, which way would it be easier to fix, in the .NET code or in the SQL procedure? I am not to sure if we have full access to the source code since that is a 3rd party control, so if that is not feasible how would I fix that in the stored procedure? This is the current proc that we are using:
Code Snippet
select @str = 'SELECT DISTINCT TOP ' + @Top + ' e.DisplayName
as DbComboText,
e.EmployeeID as DbComboValue
FROM DepartmentDirectory.dbo.Employees ee
INNER JOIN DataMart.dbo.Employees e ON ee.UIN = e.UIN
WHERE e.LastName like ''' + @LastName + ''' AND e.FirstName like ''' + @FirstName + '''
ORDER BY e.DisplayName'
Any help is greatly appreciated.
View 5 Replies
View Related
Jan 10, 2008
Hi all,
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 ?
Thanks.
View 4 Replies
View Related
Apr 16, 2013
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.
View 14 Replies
View Related
May 1, 2008
Hi,
I'm using SSRS 2005 which connects to SQL Server 2005. I need to pass Multi valus for single parameter...
E.g. Command type : Text
SELECT * FROM COUNTRY where CountryId IN ( @CountryID)
If i tried to preview using single parameter, it shows the record without any problem. However when i tried to put multi values, it gives error message?? I read somewhere which says, need to set to multivalue. But, i could not find the place to set the multi value option...
How to solve it?? Your suggestion/recommendations greatly appreciated.
Thanks
rama
View 5 Replies
View Related
Nov 27, 2014
The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
CREATE TABLE #AWP (
[TransDate] [datetime] NULL,
[Description] [varchar](1000) NULL,
[Amount] [float] NULL,
[TotalDueNow] [float] NULL,
[code]....
View 4 Replies
View Related
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).
How do I query the data?
View 6 Replies
View Related
Aug 20, 2007
Here's tricky one.
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.
Essentially, I want to create a for each loop.
Any ideas?
View 4 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Feb 19, 2007
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
View Related
Aug 21, 2015
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
View 5 Replies
View Related
Jul 29, 2015
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 .
View 2 Replies
View Related
Jan 30, 2007
Hi,
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
Thanks.
Kong
View 6 Replies
View Related
Oct 15, 2007
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
View 7 Replies
View Related
Aug 28, 2015
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies
View Related
Apr 17, 2008
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
View 2 Replies
View Related
Aug 11, 2005
Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
View 5 Replies
View Related
Oct 18, 2015
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
View 2 Replies
View Related
Jan 8, 2007
I have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
View 5 Replies
View Related
Jul 2, 2007
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
View 1 Replies
View Related
Aug 10, 2015
I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
View 5 Replies
View Related
Dec 3, 2015
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
View 3 Replies
View Related
