i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
I have the SQL query. If the user is selecting all the vendor Numbers available in the vendor number parameter drop down then, I will not include the vendor Number condition in the where portion of the sql query. For that I want to know whether the user has selected all the values available in the drop down. How to identify this?
I have a simple table on my webpage which shows data from my database. It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value. I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work. If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
I have a requirement for the following SSRS expression ( I am using Report Builder 3.0). The end user will input a selection via a mutli select parameter (financial month/s), if all month are inputted then sum(Field1.Month1.value...Field12.Month12.value )/12 * 100 else if parameter.value =1 then sum(field/s.values for selected month)/1 *100 else if month.values = 1 AND 2 then sum(field/s.values for selected month)/2 *100 and so on for any combination of the selected inputs.
Put in simpler terms I would like to calculate a percentage from a field/s containing relevent values, based on month/s the user inputs.
Hi, I have dropdown parameter with multi-values allowed. In my report headed I want to show all the dropdown values that were checked by the user to run the report. But since there could be a couple of hundred values I want to show ALL when all the values are selected instead of listing them one by one. How can I do that? Thanks,
I have requirement where first I need to show only one report parameter. Based on user selection I need to prompt or show the user another report parameter.
Say suppose I have 3 parameters. User selects first value in first parameter I should not show the other 2 parameters. If user selects second value in first parameter I should show second parameter and hide third parameter. There is no relationship between these 2 parameters except user selection. Similarly if user third value in first parameter then I should show third parameter and hide second parameter.
Is this possible? I can not see any Visible property for report parameters.
I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.
Now here is my account query: select contact_id, username, password from account. My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.
In my report parameters, I define contact_id like this: ******************************************* name = contact_id data type = integer prompt = [blanked out] hidden = checked internal = checked multi-value = unchecked allow null value = checked allow blank value = unchecked
available values: from query: dataset = account value field = contact_id
label field = contact_id
default values: from query: dataset = account value field = contact_id **********************************************
Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.
There is a multi value parameter called "include" in the report where "Allow Multiple Values" is checked and it has 4 Available values as shown in the attached screen shots and preview of the report is also shown .There is no data set for this parameter and the values will get displayed on the report based on the visibility condition set in the report.Example : If first value is selected then 1 is passed and based on the visibility condition set in the report - the report output is displayed.None is default value and has value 4 and when the report is run with this option i.e. "None" then rest three parameter values are not applicable .
Requirement : -When the end user selects (Select All) Check box then (None) -check box must be disabled or must not appear for selection for the end user -When the end user selects check boxes either of the first three except None then also None check box must be disabled or must not appear for -selection for the end user -when the end user selects a combination of first three then also None check box must be disabled or must not appear for selection for the end user -The None is set as default with a value as 4 and is applicable only when the user does not select either of the first three values and the report will run.
I am having trouble using Output parameters. I have set up an Execute SQL Task to call a Stored Procedure. I am passing an input parameter and indicating 1 global variable for output to retrieve a unique value from the stored procedure call. When I execute the step, it completes successfully but nothing is returned in my output parameter for the unique value??? Below is the call that I use:
EXEC SYS_GENERATE_ID ?
-in the Execute SQL Task I click the Parameters button to set up the input: Status = Parameter1 and the Output Variable Type: Rowset = GUID.
the stored procedure: CREATE PROCEDURE dbo.SYS_GENERATE_ID (@Statusvarchar(20), @GUID uniqueidentifier = NULL OUTPUT) AS BEGIN SET @GUID = NEWID() INSERT INTO dbo.ACTIVITY (GUID, STATUS) VALUES (@GUID, @Status) SET @GUID = convert(varchar(50), @GUID) RETURN @@ERROR END
A table is correctly populated but the GUID value does not make it back to the calling task??? The Global Variable Type under Package Properties has been changed to "Dispatch" and the value is "Not Displayable"??? Any help that is offered will be appreciated GREATLY!!
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp), CASE DATENAME(dw, sph.datestamp) WHEN 'Monday' then 'Monday' ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
With cmdCategoriesUpdate .Parameters("@Field1Tag").Value = txtFld1.Text .Parameters("@Field2Tag").Value = txtFld2.Text End With cmdCategoriesUpdate.ExecuteNonQuery() SqlConnection1.Close()
Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.
Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?
I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...
I am trying to run a query against the time & resourceorganization tables that returns selected entries from the time table, the date of the first day of the work week, and the current department. The query returns an "Exists" error. When I add exists or where exists I get ..an error near From. The main query and the sub query each run by themselves I just can;t seem to combine them. Here is what I have:
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.
How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.
I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.
Ok..............so I'm totally new to this whole SQL thing and I need some help..........please! I have a table rf_log with the following fields: PACKSLIP varchar(20) BINLABEL varchar(8) EXTENDED varchar(50) TERMID smallint USERID varchar(8) ACTION varchar(8) QUANTITY int Q_SCALER smallint REFERENCE2 varchar(30) REFERENCE3 varchar(30) DATE_TIME varchar(23) DATE_CREAT timestamp(8) LOCATION varchar(20) TOTLABEL varchar(20) CLIENTNAME varchar(15) PO_NUM varchar(25) SERIAL varchar(25) LICENSE_PLATE varchar(22)
What I need to do is to get a list of packslips where the packslip number will BEGIN with the user selected parameter. For example if the user input is 'ORD000888' then I could possibly get a return of: Ord000888-0 Ord000888-1 Ord000888-2...............and so on. I have tried this but unsuccesfully: SELECT PACKSLIP,BINLABEL,EXTENDED,TERMID,USERID,QUANTITY,Q_SCALER,TOTLABEL,REFERENCE2,REFERENCE3,DATE_TIME, DATE_CREAT,CLIENTNAME,PO_NUM,SERIAL,LOCATION,LICENSE_PLATE FROM rf_log WHERE PACKSLIP LIKE ?C
I have also tried: DECLARE @Pickslip varchar(30) SET @Pickslip = ?C SELECT PACKSLIP FROM rf_log WHERE PACKSLIP LIKE @Pickslip
None of these worked. Any help would be greatly appreciated.
Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.
I want to INSERT some data already SELECTED in a query and also some other variables.. Will the **dodgy code** make it easier to understand I wonder..
** DODGY CODE **
INSERT INTO tblTableOne(Column1, Column2, Column3, Column4) VALUES( ( SELECT Column1, Column2 FROM tblTableTwo ), "Text for Column3", "And text for column4")
** END OF DODGY CODE **
I understand it may have something to do with TEMP TABLES perhaps??
Any help greatly appreciated..
KingRoon
Chaotician Man, Slice the lines of virgin pathways. Harmony Hero.
Well i have three parameters, first two are interedependent . i.e. the second parameter dependent on the first one. In other words when i select the first one then that automatically selects or sets values to the second. In the mean while i have the third parameter that is not dependent on the first two. when the report loads, first and the third parameters highlights for selection. I don't want the third parameter available for selection unless the second parameter is selected. How can it be possilbe?
I'm trying to set up my reports with parameters, to select the paramater by the users login. For example, if someone from the Northwest Region accesses Report mangers and then a report. Instead of them clicking the drop down, for the report parameter. The report automatically generates from the login that gave them access to report manager. So it diplays the information for the Northwest Region.
I know how to do this for Data Driven subscriptions.Such as creating a table , with Region, login, email, and all the information relevant. But how do i do this for users accessing the site theirselves? Is this possible?
I have four parameters in my report: startDate, endDate, group, and name. My goal is to only present those groups and names with a record the falls in the startDate to endDate range. I have the group parameter set up with a dataset based on the following query:
Code Snippet
SELECT DISTINCT G.Group_Name, G.Group_Id FROM dbo.Group as G INNER JOIN dbo.Records as R On G.Group_Id = R.Group_Id WHERE (R.Rn_Create_Date BETWEEN @startDate and (@endDate+1) or R.Resolve_Date BETWEEN @startDate and (@endDate+1)) ORDER BY 1
I've created this type of query and parameter several times, however this time when I select any of the values in the parameter, the parameter always selects only the first item in the list. Any ideas what I am doing wrong?
Can this be done? I have a procedure where I have Values and Selected Table items that have to be inserted into a nother table that require them to be stored in the same record to the data correctly placed?
I have seen some information on this but have not understood fully the answers. I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer. How do I get selected values from a LIstbox into a SQL statement using the IN command First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End IfNextLabel5.Text = strPC The result in the lable looks something like: 100, 101, 102 , I get rid of the trailing blank and commar with:Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) I have created a parameter called @PROPCLASS that will use the values from the selected values in the listbox in a SQL query using an IN statement: Cmd.Parameters.Add(New OleDbParameter("@PROPCLASS", Label6.Text)) strSQL = "SELECT * FROM web_transfer WHERE ( PROP_CLASS IN (@PROPCLASS)) AND (ACRES >= @lowSize)AND (ACRES < @HighSize) AND (YEAR = @lowYear)AND (SALE_PRICE > @lowPrice) AND (SALE_PRICE < @highPrice) " It is really the first bit of the WHERE clause I am interested in. The sql statement works if I either type in the actual values IN( 100, 101,102) or if I select only one value in the list box. If I select two then the sql statement does not work. My question is: How do I get the values selected in the List box into my SQL statement Thank you in advance, s
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
