How Can I Have A Variable Number Of Parameter Values In A Dataset?
Apr 20, 2007
I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:
For iCount = 0 To UBound(sArray)
strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "
Next
Can I do this is a dataset method? How?
If I can't, what are my options?
Diane
View 6 Replies
ADVERTISEMENT
Jul 13, 2007
Hi
I have a dataset with 2 columns, a rownumber and a servername - eg
rownumber servername
1 server1
2 server2
....
15 server15
I want to display the servernames in a report so that you get 3 columns - eg
server1 | server2 | server3
server4 | server5 | server6
...
server13 | server14 | server15
I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap
I have also tried using a matrix control but cant find a way to do this.
Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005
Thanks
Anthony
View 1 Replies
View Related
Jul 10, 2007
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
In the dataset, I typed
=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")
I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.
Thanks.
View 4 Replies
View Related
Apr 22, 2015
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.Â
Is this a bug in Visual Studio? How do I get around this?Â
View 3 Replies
View Related
Dec 19, 2006
Hi,
I have two datasets in my report, D1 and D2.
D1 is a list of classes with classid and title
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
Is there any way to do this in RS2005?
View 2 Replies
View Related
Apr 10, 2007
Hi,
I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.
The idea is the create snapshot nightly and then display the report filtered based on parameters to users.
I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,
Right now I defined it this way :
Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)
I do this for the 4 parameters that are passed, if they are null, make then equals to the field.
I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.
Thanks
View 5 Replies
View Related
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
Apr 5, 2007
Hi every body...
I have a probleme
I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime))
I need to call this method. But my problem is how pass parameter ?
I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example...
now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression :
=First(Fields!idEq.Value, "EquipmentDataSet")
but when i execute the query, the promter display <NULL>...
So I don't know how to do and if it is possible !
I hope someone can help me !
Thank you !
View 3 Replies
View Related
Nov 4, 2015
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
View 6 Replies
View Related
Apr 18, 2007
hi friends ,
Is there anyway to share a single dataset for different forms.. coz i am repeatedly using the same query for different forms...
View 4 Replies
View Related
Sep 8, 2006
Hi,
i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".
What i need is a way to add a column to my transformed data which contains a value from a User-Variable.
My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.
So anybody know of an existing Data Flow component, which can do this?
Regards, Martin
View 3 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 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
Apr 11, 2008
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?
The field is a Varchar 20
View 1 Replies
View Related
Feb 11, 2004
hI,
I am using visual c# 2003 and sqlserver 2000 and i am trying to query a column in the sql server and store it into a dataset but i got an error msg:
The number of rows for this query will output 90283 rows.
--------------------------------------------------------------------------------
Query :
SELECT L_ExtendedPrice, COUNT (*) AS Count FROM LINEITEM GROUP BY L_ExtendedPrice ORDER BY Count DESC";
---------------------------------------------------------------------------------
Error msg :
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
----------------------------------------------------------------------------------
is there a limit to the number of rows a dataset can store?
View 5 Replies
View Related
Apr 16, 2007
Hello. I€™m new to SSIS. This forum and Kirk Haselden€™s book are my teachers. I€™m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I€™m simply misunderstanding the best practices of SSIS.
I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.
How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can€™t write a query against them in the Data Flow, since there is no connection manager that allows me to €œconnect€? to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?
Any advice greatly appreciated. Thanks in advance.
View 1 Replies
View Related
Apr 30, 2007
Hi All.
I have a parameter (hidden) that gets its value using an expression base on another parameter.
When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.
When I change the parent parameter again, the value of the child parameter does not seem to change.
How can I make this parameter change automatically when the parent is changed ?
Any help will be appreciated.
Thanks,
Neil
View 3 Replies
View Related
May 15, 2008
I'm having a very frustrating problem that no amount of formatting via properties and/or expression seems to be clearing up.
I have a column in the dataset that is data typed decimal (6,4) (from the SQL query). The numeric value is, for example, being returned to the data set as 0.6500.
The problem is the report wants to just show zero. Formatting efforts to date have only yielded, '0', '0.00', & '0.0000'.
I have no clue. Anyone have any ideas?
Tia
randyvol
View 7 Replies
View Related
Mar 27, 2007
i need to add values to dataset from an arraylist
any idea about how to achieve this
View 1 Replies
View Related
Dec 14, 2007
if a dataset doesnt use a stored proc to define a table variable, what is the scope of that table variable? Does the name need to be unique from such variables defined by other datasets?
View 4 Replies
View Related
Feb 11, 2005
I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.
"SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField
I know there is data in the db.
Please help :confused:
View 6 Replies
View Related
Feb 11, 2008
Hi All
I have two datasets (for querying active directory but this is kind of irrelevant for my problem which is a basic one)
The first gets a list of groups and I want to display GroupName in a dropdown list:
SELECT DistinguishedName, GroupName, GroupDescription
FROM tvf_GetListOfGroups('LDAP://OU=Applications,OU=Groups,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree')
ORDER BY GroupName
The above is fine and works but then I want to use the corresponding DistinguishedName of the GroupName selected in
the dropdown to drive the second query to populate the report
SELECT * FROM tvf_GetGroupMembers('LDAP://' + @DistinguishedName)
ORDER BY UserLogin
My problem is how to populate @DistinguishedName with the correct value to run run the query.
How? Please?
Thanks
Pete
View 1 Replies
View Related
Jun 15, 2007
Hello
Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses
I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works
The cutdown version off the query is...
Select Top @Param_TopNo SaleDate, Consultant, Productname
From tblSales
where product = 2000
Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code.
thanks
View 1 Replies
View Related
Apr 5, 2007
Hi !!!
I have a problem, I don't know how to do...
I have 2 dataset from 2 differents XML dataSource...
I want the result of one dataSet to be a parameter for the second !
I try to add a parameter in param tab, with expression, I choose DataSet and
=Sum(Fields!ID.Value, "DataSetID")
but when I execute the query, the prompt for the param appear, but the value of id was <NULL>...
Someone have ideas to resolve this problem ?
thank you for your help !
View 1 Replies
View Related
May 31, 2007
Any way to have the default value of a parameter for a multi select parameter be the first value of the query of that parameter?
View 7 Replies
View Related
Jul 7, 2004
Hi
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go
This is asp.net code
Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)
conn.Open()
dr = cmd.ExecuteReader
nRecords = convert.int32(cmd.parameters(@return).value)
conn.close
Thanks
Lbob
View 1 Replies
View Related
Jul 25, 2006
Hello,
How can I get all the dataset value in textbox.
e.g in dataset I jave field call "CustomerName".
I would like to get in the textbox all the cutomer name seperated by ",".
Is the same as I can use join(Parameters!CustomersName.Value,",") but I need to do that from the data set and not from the parameters since I don't have parameters for my customer name
Thanks
View 4 Replies
View Related
Mar 6, 2008
I have this case where I need to multiply the value of my dataset and finally deduce one to it. For instance if I have a dataset with three values, 2, 5, 15, I need to end up with a value of 150 (2*5*15). How can I achieve this?
View 3 Replies
View Related
Aug 21, 2007
Hi,
Is there any work around to passing a set of strings to a parameter in a Typed Dataset for example I am pasing '4226222172004','4212012182004' which I build on my code the number of items will vary passed on the user selection but since the Typed Dataset uses sp_executesql and the parameters are change to '''4226222172004'',''4212012182004'''
Any ideas how I can format the Parameter I am passing.so that it will end like where in ( '4226222172004','4212012182004') instead of where in ('''4226222172004'',''4212012182004''') I again the number of parameters will very.
Thanks
Julio D
View 1 Replies
View Related
May 3, 2007
I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.
How can I do this?
I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.
View 1 Replies
View Related
May 23, 2007
Let me simplify. The value of my dataset field Dimension_1 is
[SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION]
I want to use parameters for SOB Year and Yr Id like I do on the query.
But if I use
="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]"
it returns null for the values. I don't know how to make these parameters
I am passing in parameters for the table and column to build an MDX query here is my query.
="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Anyone know what value I need to use on the dataset field to access the column?
Thanks,
Roger
View 10 Replies
View Related
May 14, 2007
Hello
I get the following error in a very quick test system i created:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot set the command text for data set 'test'.
Error during processing of the CommandText expression of dataset 'test'.
My sql is:
= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)
There is nothing obviously wrong in the code as far as i can see
The parameter 'test1' is of type 'string'
The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'
I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine
Thanks
View 5 Replies
View Related
Feb 27, 2008
Hi,
Now I can query dataset and bind to report parameter. But I have some problem about setting default value. I don't know, how to select max value from dataset by using expression.
Example, I query disticnt year from my table. The result has 2 years "2007" and "2008"
select distinct year(mytime) as myyear from mytable.
I set available parameter by using this dataset. Then, I set default value for this parameter by the other dataset ...
select max (distinct year(mytime)) as maxyear from mytable.
I want to select max value by using expression, not select max by using dataset.
How can I do?
Thank you very much.
View 4 Replies
View Related
