Select Into Parameters Grouped Data
May 20, 2007
Hi all,
This is probably one of the easier questions you get, but I have brain freeze and just can't do it and very rusty.
How can i assign the the values from sql below into variables e.g I want to get all new, pending and cancelled Leads from the rows returned which will come from the status id.
SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
FROM [dbo].[tLead] L
Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
Inner Join dbo.tClick C on linkClick = ClickID
WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
Group by LeadStatusID
Order by LeadStatusID asc
Cheers
View 10 Replies
ADVERTISEMENT
Jan 12, 2007
I'm having trouble using session data in my select parameters. If I manually plug a value into the selectcommand or create a hard coded value using a parameter it works, but if I try to use the session data the query pulls no results. I know the session data is set because I print the value at the top of the page, but for some reason it's not getting passed to the sessionparameter???
This is the datasource code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:flautoconnstr %>"
SelectCommand="SELECT * FROM [tblVehicles] WHERE ([profileid] = @profileid)">
<SelectParameters>
<asp:SessionParameter Name="profileid" SessionField="profileid" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
This is the line I use to set the session data.
Session["profileid"] = myQuote.Profileid;
View 1 Replies
View Related
Nov 28, 2005
Hi,I want to get the count of rows per each 20 minutes, is that possible?Is there a date function or any other function that I can use in Groupby clause, which will group the data of every 20 minutes and give methe count?Thank you.Vidya
View 3 Replies
View Related
Apr 15, 2008
Hello,
I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).
Now I want to aggregate some numbers of the table on each page (year) of the list.
The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.
Can anyone help?
Thanks a lot -
Markus
View 2 Replies
View Related
Jun 7, 2007
Hello,
I have the following situation :
I have a reportmodel where i have the a datetime value (dd/mm/yyyy hh:mms), a salespersonid , a salespersondescription, and an amountsold.
i would like to display a report where i can see per hour (start worktime is 8 am and end is 8 pm) which salesperson sells the most. the objectif of this report is to see in what period of the day the sales are highest.
Can anybody provide me some tips how to accomplish this.
Vinnie
View 1 Replies
View Related
May 12, 2015
I am trying to export report to .CSV , but I am unable to export grouped data, instead to grouped data , it exporting detail data,for example my grouped data in matrix is as below , I need to export below data to .csvÂ
column1 colum2   year      amount
1 Â Â Â Â Â Â 1 Â Â Â Â Â 2011 Â Â Â Â Â Â $1.0
        2      2012       $2.0
        3      2013       $3.0
              2014       $4.0
but, I am getting like below-
column1   column2   year      amount
1 Â Â Â Â Â Â Â Â Â 1 Â Â Â Â Â 2011 Â Â Â Â Â Â $1.0
1 Â Â Â Â Â Â Â Â Â 2 Â Â Â Â Â 2012 Â Â Â Â Â Â $2.0
1 Â Â Â Â Â Â Â Â Â 3 Â Â Â Â Â 2013 Â Â Â Â Â Â $3.0
1 Â Â Â Â Â Â Â Â Â 3 Â Â Â Â Â Â 2014 Â Â Â Â Â Â $4.0
View 2 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Apr 29, 2008
Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType
from Product
order by ProductType
Any suggestion? thx
View 12 Replies
View Related
Feb 27, 2008
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
View 1 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Dec 3, 2006
I am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.
The fields in the table are:
Time (for every stock price tick)
Price (stock price)
Symbol (of the stock)
A sample time series would look like this:
11:45:01 $22.11 MSFT
11:45:02 $22.10 MSFT
11:45:04 $22.25 MSFT
...
...
...
11:45:58 $22.45 MSFT
11:46:03 $22.22 MSFT
11:46:08 $22.25 MSFT
...
...
...
11:46:59 $22.45 MSFT
The result of the SQL query should be:
Symbol Time Open High Low Close
MSFT 11:45 $22.11 $22.45 $22.10 $22.45
MSFT 11:46 $22.22 $22.45 $22.22 $22.45
This is the SQL statement I came up with but obviously doesn't work:
SELECT DATEPART(Minute,Time), MIN(Price), MAX(Price) FROM dbo.TimeSales WHERE Symbol='MSFT' GROUP BY DATEPART(Minute,Time)
I would appreciate any suggestions.
Thank you!
View 10 Replies
View Related
Jan 16, 2007
I have a sqlDataSource control that has its select command and parameters set dynamically. One of the parameters is a date, which should be in smalldatetime format (ie, 12/22/2006). Obviously, using SQL Server, the data is stored with both date and time. The problem is, I cannot get any data selected. I initially thought that I needed to tell it to only look at the date and not the time, but from what I have seen this should work. Here is the pertinent code.
dim seldate as datetime
selDate = Session("ChosenDate")
Dim SelCmd As String = "SELECT [PatientName], [AssignedTo], [CPT], [CPT2], [HospitalID], [RoomNbr], [ACType], [TxDx1], [TxDx2], [MRNbr], [Notes], [PatientID], [PCPID], [ResidentID], [Status], [CaseID], [AdmitDate], [crtd_datetime] FROM [DailyBilling] WHERE ([crtd_datetime] = @crtd_datetime) and ([AssignedTo] = @AssignedTo) and ([HospitalID] = @HospitalID) ORDER BY [PatientName]"
SqlDataSource1.SelectCommand = SelCmd
SqlDataSource1.SelectParameters.Clear()
Dim parFilterProvider As New ControlParameter()
parFilterProvider.ControlID = "ddlProvider"
parFilterProvider.Name = "AssignedTo"
parFilterProvider.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(parFilterProvider)
Dim parFilterHospital As New ControlParameter()
parFilterHospital.ControlID = "ddlHospitals"
parFilterHospital.Name = "HospitalID"
parFilterHospital.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(parFilterHospital)
Dim parFilterStatus As New Parameter()
parFilterStatus.Name = "crtd_datetime"
parFilterStatus.DefaultValue = selDate.ToShortDateString
parFilterStatus.Type = TypeCode.DateTime
SqlDataSource1.SelectParameters.Add(parFilterStatus)
Gridview1.databind()
Any ideas?
View 5 Replies
View Related
Apr 13, 2007
Hi
I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId
The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>"
SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId">
<SelectParameters>
<asp:ControlParameter ControlID="Drop1" Name="SourceId" />
<asp:ControlParameter ControlID="Drop1" Name="CatId" />
</SelectParameters>
</asp:SqlDataSource>
Can anyone help me to define the parameters?
thanks
View 2 Replies
View Related
Aug 2, 2007
I have a form that has 4 fields to fill in. I have a button that can add these fields to a sql database. I also want to put a button next to the "add" button so you can fill out the same fields and search for those values. Here's what i have so far. I want to select based on the fields, but i'm having trouble with the syntax of the parameters. I also want to add something, so if nothing is filled out in one of those boxes, it retuns back all records for the default value.
string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnection);
String selectCmd = "SELECT * FROM users WHERE firstname = @firstame or lastname = @lastname or office = @office or team = @team";
SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50));
myCommand.SelectCommand.Parameters.Add("@firstname", txtFirstName.Text);
myCommand.Parameters.AddWithValue("@lastname", txtLastName.Text);
myCommand.Parameters.AddWithValue("@team", dwnTeam.Text);
myCommand.Parameters.AddWithValue("@office", dwnOffice.Text);
DataSet ds = new DataSet();
myCommand.Fill(ds, "users");
MyDataGrid.DataSource = ds.Tables["users"].DefaultView;
MyDataGrid.DataBind();
View 5 Replies
View Related
Jan 6, 2004
Im enabling an apllication to use ODBC to connect to sqlserver which currently uses Oracle OCI, i have no prior knowledge about odbc use.
Im unsure how to approach where clause parameters (bind parameters)
ie Oracle OCI
select name into :name from emp where name = :a_name
via ODBC, connecting to sql server i'm attempting
select name from emp where name = ?
with,
sqlprepare
sqlbindparameter
sqlexecute
sqlbindcol
sqlfetch
all seems ok with sqlbindparameter, but sqlexecute fails with sqlstate 22001, String data, right truncation.
The question: can i use ? parameter in where conditions, if not whats the best approach.
Many Thanks.
View 1 Replies
View Related
Oct 14, 2013
i have 3 table
DECLARE @a1 table(
p1 int ,
date1 smalldatetime
)
Insert Into @a1
(p1,date1)
Values
[code]....
how i get resulting table
num_p1 num_p2 num_p3
1 3 5
View 13 Replies
View Related
Jan 19, 2007
I know that when you select Multi from the parameter report properties, that it automatically makes "Select All" Available. So how would i take off the "All Value". My users dont want to see All, if Select ALL is available.
How do i exclude from "All" from my report parameter/dataset??
View 2 Replies
View Related
Mar 12, 2007
In multivalue parameters by default an option "All" was appearing. You can select both "All" and any of the individual parameters. However the latest report I created suddenly appeared with "(Select All)" as well as "All". (Select All) actually selects (or deselects) all items.
Does anyone know where (Select All) came from, is this a new thing in SP2? How do you control it.
Also, is there a functionality where you can select "All", but if you then select another paramater it automatically deselects "All"?
Richard
View 6 Replies
View Related
Aug 21, 2007
hello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim pageID As StringpageID = Request.QueryString("ID")
TextBox13.Text = pageID 'Test to make sure the value was stored
SqlDataSource1.SelectParameters.Add("@pageID", pageID)
End Sub
....
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource>
The error that i am getting, regardless of what i put inside the ' ' is as follows:
"Conversion failed when converting the varchar value '@pageID' to data type int."
anyone have any suggestions ?
View 2 Replies
View Related
May 12, 2008
SelectCommand="SELECT [id], [ref_id], [ref_name] FROM [ref] where [delete_flag] = 'N' AND [flag_transmit] = 'N' AND [ref_name] = @ref_name ">
<asp:ControlParameter Name="ref_name" ControlId="SrchRefName" PropertyName="Text" ConvertEmptyStringToNull="false"/>
The gridview of all the records is displayed on the page by default. There is a lookup of SSN and Name and putting values in those fields should filter the records and display only the row containing entered Name.I have trouble with the SelectCommand. No gridview is displayed with the above command even if value is entered in the Lookup textBox (SrchRefName).But when I change the selectcommand to- (using OR) SelectCommand="SELECT [id], [ref_id], [ref_name] FROM [ref] where [delete_flag] = 'N' AND [flag_transmit] = 'N' OR [ref_name] = @ref_name "> <asp:ControlParameter Name="ref_name" ControlId="SrchRefName" PropertyName="Text" ConvertEmptyStringToNull="false"/> the default gridview is displayed with all the records even though a value is entered in the lookup text box. Please help.
View 2 Replies
View Related
May 15, 2008
Hi, i have a big problem. I´m having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm
View 8 Replies
View Related
Mar 17, 2004
Hi,
thanks for reading!
Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.
if i try to return the @@identity as a plain select statement instead of through the parameter
i get System.DBNull.
I hope you can shed some light on this for me. Here is my stored procedure:
CREATE PROCEDURE cwSaveProductInquiry
@inquiryId int OUTPUT,
@libraryName nvarchar(500),
@contactName nvarchar(200),
@address nvarchar(100),
@city nvarchar(50),
@state nvarchar(3),
@zip nvarchar(10),
@phone nvarchar(50),
@email nvarchar(100),
@comment nvarchar(3000),
@productIds nvarchar(2000)
AS
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @objectId varchar(200) -- this holds each array value as it is returned
if(@productIds is not null)
begin
while patindex('%,%' , @productIds) <> 0
begin
select @separator_position = patindex('%,%' , @productIds)
select @objectId= left(@productIds, @separator_position - 1)
INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId)
select @productIds = stuff(@productIds, 1, @separator_position, '')
end
end
set nocount off
Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in
(Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)
GO
View 3 Replies
View Related
May 30, 2006
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View 1 Replies
View Related
Dec 29, 2006
I´ve build a report with Report Design. On this report I want to show the items customers bought. In this report I added a Report Parameter so the user can select a specific customer. I made an extra dataset and use the filter =@customer and this parameter works fine. Then I want to ad a second Report Parameter: the salesperson. When I leave one of the parameters blank, I get an error "Please select a value for the parameter....". Even if I choose the options allow blank or null values, I get this error. What I want is de possibility to get all the records, to filter records from one specific customer or to filter records from a specific salesperson.
View 19 Replies
View Related
Jul 7, 2007
Hi,
If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only.
Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component?
Thanks
John
View 5 Replies
View Related
Jul 24, 2007
how do i add parameters like this dynamically? do i need to change the select command? to add the @ID part?
View 4 Replies
View Related
Sep 29, 2005
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
View 2 Replies
View Related
Nov 14, 2006
Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters.
Something like this:
create storeproc
Param1, param2
if param1 <> null
Select *
from table
where id = param1
else
Select *
from table
where id = param2
end if
Thanks in advance....
View 2 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
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Mar 9, 2004
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
Thanks in advance.
View 1 Replies
View Related
May 7, 2008
Hi All,
Is there any way to disable the "Select All" option which comes by default for Multivalued parameters in the Report Manager?
Thanks in Advance
View 3 Replies
View Related
May 14, 2015
I have a multivalue parameter that has X options. Is it possible to implement a solution that when I go into the report through an action, to have one option (in this case is the first) not selected all all the others are checked?
View 4 Replies
View Related
