I need to create a series of reports, which will have multiple parameters. I will have 2 parameters, which I need to make available for both to be used, none to be used or one or the other.
I can achive this in the 'data' area of VS2005, the parameters are listed in the 'layout' area, however I can not get the report to run with out selecting both the parameters in the 'preview' area.
I think it may be to do with the Allow Null check box but I can not get it to work! with data type string.
He is my where clause, which works: -
WHERE (NewMainintro = @Intro) AND (NewStatus = @Status)
OR
(@Intro = '') AND (@Status = '')
OR
(NewMainintro = @Intro) AND (@Status = '')
OR
(NewStatus = @Status) AND (@Intro = '')
Hello, I have a report in which I would like to give the user the ability to select how a parameter is filetered. Option 1 would be to filter by a range of value ie: WHERE member.age BETWEEN (@Start) AND (@End) -say everyone between the ages of 50 through 80 Option 2 would be to filter by specific but multiple values ie: WHERE member.age IN (@Age) -say anyone who is 25, 50 and 75. How can this be done? Thanks
I'm writing an Insert form which will write records to a few tables. What I want to know is how do I write multiple answers to one question in different rows in the table but keeping the ID?
For example.
The form has the following fields:
HotelIDHotelFacilities (CheckBoxList)
Now each hotel (in this case) will only have one ID but more than one HotelFacility .
How do I get my table to read...
HotelID
HotelFacility
1
Bar
1
Restaurant
1
Cafe
1
Wi-Fi Access
I presume INSERT INTO tblHotelFacilities(HotelID, HotelFacility) VALUES(@HotelID, @HotelFacility) won't write more than one selected facility?Thanks,Brett
The variable @Item can contain multiple values separated by a comma. e.g. ItemA, ItemB
But the STORED PROCEDURE query sees it as one string instead of 2 separate ones.
How do I solve this? Thanks.
Code SnippetWHILE(@StartDate < @EndDate) BEGIN IF LEFT(DATENAME(dw,@StartDate),3) NOT IN ('SAT','SUN') INSERT INTO @temp(myDate) VALUES(@StartDate); SET @StartDate = DATEADD(d,1,@StartDate); END
Code SnippetSELECT LEFT(DATENAME(dw, te.myDate), 3) + ' ' + CONVERT(char(11), te.myDate, 106) AS 'NewsDate' FROM @temp AS te LEFT OUTER JOIN NewsItemTable AS t ON t.NewsDate = te.myDate AND t.Item IN(@Item) WHERE t.NewsDate IS NULL
I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement. Many Thanks Delli
I have a question about how to have multiple chart options for a report? Like when I view a report, I want to see the report in different charts formats. It is possible in SQL Server 2005 Reporting Services (designer) to change from different chart options? Hope my question is clear for your advices.
Thank you very much in advance for your help and advices. And I am looking forward to hearing from you.
I need to set multiple values for some SQL statements, for exampleSET NUMERIC_ROUNDABORT OFFGOSETANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOin a .sql file, but would like to reset them to their previous settingsagain for other SQL statements. I didn't find anything in books online. Ithought just calling "set" was enough, but apparently not ....Thanks.
I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.
Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.
...
FirstPub as datetime
LastPub as datetime
FirstSale as datetime
LastSale as datetime
Name as varchar(50)
Addr as varchar(50)
DetailText as text....
I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.
I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.
I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.
Questions:
1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?
2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.
3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?
4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?
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?
is it possible to return multiple output values in stored prcodeure for eg. Create Proc XYZ ( @x int output,@y int output,@z int ouput) As Begin SET @X= Select from table SEt @Y=SELECT from table set @z=slect from table end
I have some 5 parameters which I've specified as multi-valued and my report uses a stored procedure. When I select all values in my dropdown (parameters) within my reports nothing shows up. How can I pass multiple values of a parameter into my stored procedure such that it works?
I'm hoping someone out there has a creative solution for my scenario. I have the report code below. I want to create a parameter that allows the user to select which where statement to use in the report. Then I want to use the corresponding where description below ( ex: @WHEREDESC1 ) in my header which will describe (in laymen's terms) which parameter was chosen and what is being reported on.
Has anyone achieved this or have an idea how it may be done?
I have a sqlserver stored procedure with multiple output parameter like below. I am not sure how I can read the two output parameters at my VB.net code. I am trying to use ExecuteScalar from VB.net and it is throwing an exception. Procedure:CREATE PROCEDURE dbo.spGetUser @UserLoginId VARCHAR(80), 'E-mail of user @Password VARCHAR(50), 'Password in hash @UserId INT=NULL OUTPUT, 'system generated user id@UserType CHAR(2)=NULL OUTPUT 'type of the user AS BEGIN .......... END VB Code: Inside my business layer class =========================== TryWith objSQLCommand .CommandType = CommandType.StoredProcedure .CommandText = "spGetUser".Parameters.AddWithValue("@UserLoginId", UserLoginId) .Parameters.AddWithValue("@Password", Password).Parameters.AddWithValue("@UserId", UserId) .Parameters.AddWithValue("@UserType", UserType).Parameters("@UserId").Direction = ParameterDirection.Output .Parameters("@UserType").Direction = ParameterDirection.Output End With bolReturn = mobjDataAccess.Execute(objSQLCommand) If bolReturn Then GetDetail = True End If ========================== Inside my dataaccess layer class =========================== Public Function Execute(ByVal objSQLCommand As SqlCommand) As Boolean Execute = False Dim intReturn As Integer ' 0 = Success, Otherwise Error intReturn = 1 TryCall Connect()With objSQLCommand .Connection = mobjConnection .ExecuteScalar() End WithCall Disconnect() If intReturn = 0 Then Execute = TrueEnd If Catch ex As ExceptionCall Disconnect() ex.Source = "Execute" Execute = False End Try End Function
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.
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
My end result will be a query with about six parameters total. But for right now I am trying to one by one for testing purposes. This is my first SQl project. All of my information goes into an Access DB. I am basically creating a search page with parameters for the employees to use to search for data. I need the query to work if all parameters, or combinations of parameters, or just one parameter is chosen. I started with one and got to three but now I'm stuck. My first two parameters are dates, so the data can be chosen by date range, and the second is location id. If I choose date range by itself it works, If I choose location id by itself it works, but If I choose both it doesn't work. Instead of filtering it gives me everything and I have a statement for it. Can someone please help me and tell me what am I not doing right. Here is my code;
SELECT * FROM ASFdata WHERE (ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#"> AND <cfqueryparam value="#Form.datefield#"> <> datefield AND <cfqueryparam value="#Form.datefield2#"> <> datefield) OR (ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND NOT <cfqueryparam value="#Form.reportlocid#"> = reportlocid ) OR (ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">)
I have a parameter "country" that currently has something like 50 labels (each with a different value) for different countries. I want to be able to design this so that I can select US, Canada and World. This requires me to put multiple values under the "World" label.
I need to somehow set up a third parameter which allows me to control the tag quantity type either by ALL - % , IS NULL, or IS NOT NULL.
I tried TAG_QUANTITY LIKE :Tags but this would only work if I had the % option select and not the IS NULL or IS NOT NULL
Any thoughts on how to format the select statement?
SELECT ORGANIZATION_ID, TAG_ID, PHYSICAL_INVENTORY_ID, TAG_NUMBER, CREATION_DATE, VOID_FLAG, TAG_QUANTITY, TAG_UOM, SUBINVENTORY, LOCATOR_ID, COUNTED_BY_EMPLOYEE_ID FROM INV.MTL_PHYSICAL_INVENTORY_TAGS WHERE (ORGANIZATION_ID = ite) AND (PHYSICAL_INVENTORY_ID = :InventoryID) AND (VOID_FLAG = 2) AND TAG_QUANTITY IS NULL ORDER BY TAG_NUMBER
We have timed-based subscriptions set up for our standard set of 10 client reports which run monthly with the appropriate parameters set for each of our clients. Our management has requested that we provide them with the ability to run the set of 10 reports together at will. So we would provide the client parameter and all 10 reports would be generated. What is the recommended approach to accomplish this? We are using Sql Server 2005, Visual Studio 2003 and .Net 2.0.
I have one page, one connection, and three dropdowns. The connection looks like (as an example):<asp:SqlDataSource ID="DropDownConn" runat="server" ConnectionString="<%$ ConnectionStrings:aousConnectionString %>" SelectCommand="SELECT [Value], [Text] FROM [DropDown] WHERE (([Group] = @Group) AND ([Viewable] = @Viewable))"> <SelectParameters> <asp:Parameter Name="Group" Type="String" /> <asp:Parameter DefaultValue="True" Name="Viewable" Type="Boolean" /> </SelectParameters></asp:SqlDataSource> The DropDowns Look like this: <asp:DropDownList ID="DropDown1" runat="server"></asp:DropDownList><asp:DropDownList ID="DropDown2" runat="server"></asp:DropDownList><asp:DropDownList ID="DropDown3" runat="server"></asp:DropDownList>The C# Code I am trying is like this:DropDownConn.SelectParameters["Group"].Equals("DropDown1");DropDownConn.SelectParameters["Viewable"].Equals(true);DropDown1.DataSourceID = "DropDownConn";DropDown1.DataTextField = "Text";DropDown1.DataValueField = "Value";DropDown1.DataBind(); As an example. I can not get it done so that I don't have to create 3 dataconnections. Any help, PLEASE?
I reviewed many threads and looked through many pages in my book, I cannot find my answer.
I have a report in need of carrying multiple values per parameter label. Here is the report parameter and how it is used;
Our IS system considers A-Active, L-On Leave, P-Paid Leave to be "Active" Our IS system considers T-Terminated, D-Deceased to be "Termed"
Instead of having to have the user click upon three options (A, L, P), I would like to group these and have the Label appear as "Active", while the two options for "Termed" (D, T), would appear as the second parameter option.
Does anyone know how I would get this correctly setup in the Report Parameters... module?
I am trying to select some values as default for a parameter, I add my data set,add my parameter then I select the values in the default pane, when I run the report I get :
'the default value expression for the query parameter contains an error[BC30451] name is not declared.
When I look at the expression of the default parameter (in the report data pane under the specify values) it appears like
=new Object() {[Sty].[ST].&[1], [Sty].[ST]..&[15]} with a red scrible under the ()
I am using RS2005. i have a requirement in which i need to pass more than 1 value to a parameter. this parameter is used in a dataset for filtering.
Eg: if i have a parameter as @years, i want to assign values 2005, 2006 to it and use it in the dataset to filter like select.... where year in (@years).
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");
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs ( @OrderList varchar(500) ) RETURNS @ParsedList table ( OrderID int ) AS BEGIN DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ',' SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID <> '' BEGIN INSERT INTO @ParsedList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1)
END END RETURN END GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
Attempting to build a report were you can place a specific code in the parameter field and it will return all row values based on that particular code. I have a similar report that works great, but the specific code is just in 1 column, the one I'm trying to create has the potential to have that code in up to 20 different spots. I have the report built, but the issue I'm facing is linking the parameter. Is there a way to link 1 parameter to multiple column options?
Here's an example:
Docflo Distribution Group Queue Status Pend1 Pend 2 Pend 3 Pend 4 Pend 5 ABC ABC1 Catch All NEW 123 126 125 621 129 ABC ABC1 Various PENDED 621 123 872 542 630
Right now if I were to link the parameter to the Pend1 field, I would get every line I wanted that had Pend "123", but it would not include any of the lines where Pend "123" was in Pend 2, Pend 3, Pend 4, so on.
How would I link the parameter to more than 1 column so it would return all rows with a specific code no matter which Pend column it was in?
I want to write an Query for a report that has 7 parameters. I want to allow Null for these parameters but at the same time in the Query i want to put an AND condition for all the parameters. I cannot put an OR condition.
For Example; If i have a report with 2 parameters then the script will have 2* 2 If conditions
something like:-
if( @parameter1 is not Null and @parameter is not Null) select A, B, C From Table 1 Where parameter1 = @parameter1 and parameter2 = @prameter2
if( @parameter1 is not Null AND @parameter2 is Null)
select A, B, C From Table 1 Where parameter1 = @parameter1
If( @parameter1 is Null AND @parameter2 is Not Null) select A, B, C From Table 1 Where parameter2 = @parameter2
If( @parameter1 is Null AND @parameter2 is Null) select A, B, C From Table 1
For 7 parameter there will be way too many If conditions. Is there an better approch to write a Query for such a situation?
I have a report where I'm showing a list of users to select. When previewing the report, the width of the field is automatically set to the largest label in the dropdown list. But when I change the parameter to have "Multi-Values", then the width is set to a fixed value and a horizontal scrollbar appears. Does anyone know how to have a "Multi-Values" parameter, but keeping the lasgest width of the labels in the dropdown list ?
