Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row
basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.
I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.
I'm trying to put a range of dates into the datatextfield of my ddl. I'm using the query:
string strProps2 = "SELECT sc_id, cast(begin_date AS varchar) + ' - ' + cast(end_date AS varchar) AS XYZ From Archived_Property_Changes WHERE Property_number = " + qryPN + " ORDER BY end_date";
but it gives me an empty ddl. the datavaluefield shows the correct results, but it's not displaying the correct data from the datatextfield. I removed the casts from the SQL query and it returns:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Below is my ddl code:
SqlCommand objCommandProps2 = new SqlCommand(strProps2, myConnection); SqlDataReader objReaderProps2 = objCommandProps2.ExecuteReader();
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 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
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).
I have my stored procedure set to Territory_code IN (@Territory)
, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?
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?
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?
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'. USE [merck_acronyms] GO
I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
I am using SSRS 2008 R2 Report Builder 3.0 (10.50.4276.0) . I have simple set of data which has a persons Title and Name e.g. Mr Smith, Miss Jones, Doctor Foster
The report has a parameter where the user can select which records to show based on the matching titles (Mr, Miss, Doctor)
The Query for the report uses Title in (@Title) where @Title is the only parameter which can take multiple values. The report works correctly for any 1 value selected, but as soon as 2 or more values are ticked in the drop down, it fails.
I believe the parameter value is being passed into the query with a comma separating the values e.g. Mr,Miss which causes the IN statement to give an error, as the statement would be where Title IN ('Mr,Miss') which does not match any of the data values.
The parameter value passed needs to be 'Mr','Miss' for the IN statement to work. What statement do I have to put in the report query to get it to select any of the data rows where the title matches any 1 of the selected values?
I am creating a SSRS report using a SSAS cube as it data source. The user would like to select multiple values from a reporting parameter that is then used as a filter on the MDX statement. I am bale to have the report work successfully when only one value is selected but not when multiple values are selected; the report uses only the first value from the reporting parameter when it contains multiple values. How do I filter an MDX query using a SSRS report parameter with multiple values?
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
we can  assign one parameter value for each excecution of  [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..
Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .
1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??) 2.What are the options to pass multiple parameter values to ssis package through stored procedure.?
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 21 Boolean fields that I would like to calculate a percentage from. If the boolean value is true I would like to be able to return a percentage of Outcomes available from a possible of 21. So If 7 of my boolean fileds are true then i would like to be able to calculate that 33% of Outcomes are present.
Does anyone have anything that will get me started?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Client] GO
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.
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
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following table name: DownloadTable fileName | DownloadSize | file1 | 45 file2 | 50 file3 | 20 --------------------------------------------- second table table name: VideoTable fileName | VideoSize | file1 | 40 file2 | 60 file3 | 20 ---------------------------------------- Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following: SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me? Thanks in advance Regards,
Hi guys, I need some help again please. On my page load, I need the following done... I have a label control that must display a certain numeric value. This value must be the sum of an array of values that must be retreived from a table ("Details") with the column name "DealValue" but only where the column "ConsultantID" = txtConsultant.text I understand the select statement, but I don't know how to "run" through the table to add all the values. Can someone please help me with this and how I would construct the MSSql select statement for this. Thanks again
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
select (sum(ColA) + sum(ColB) + sum(ColC)) / 3 from tableA Where ....
I want to add the sum of the columns and then divide it by a constant. The problem I am having is there are a lot of nulls in the database that I am working on (i.e. one of the values in the ColB is Null, which makes the result of the entire expession null).
How do I get around this. I need to add the values of the columns?
I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d = select amount from exp_data where period = '1111' and exp_data.account = split_table.b and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...