Multi Value Parameters

Hi,

Thanks for the reply...I think I should have been a little more clearer.

I can specify the multi paramaters, but I want to be able to select certain values from that parameter like in the pic.

E.G. I have a dropdown with City's in, but I dont want to select one, which is what I can only do at the moment, I want to be able to put a tick in the tickbox next to it to select more than one?

Does this make sense?
Yes with multi-value parameter you can do that!!!
I have multi-value paramters and I can select as many values as I want.
I'm using RS 2005 april ctp and in june ctp also works.
What are you using?|||Dear Friend,

I have problems using multi values parameters in my reports, maybe you can help me.

In a simple store procedure like this:

...
@.parametro int
as
select *
from tb_Fact_Vehicles
where id_Dim_Location in (@.parametro)
...

whe I select one value in the preview screen the repors runs fine, but if I select more than one parameter the results is nothing, I mean empty: no records. Do you have any idea what's happening?

|||Ah...I'm using RS 2000 SP2. Is there anyway I can do this in RS2000? or do I need to move to RS2005?

Is RS2005 stable enough for a live environment?

Cheers|||RS2005 is not for production purpose. Wait until november 7th.
I think it's possible in RS2000, but I've never tested RS2000.
I'm sorry.

Multi- value parameter!

Hi every body!
I have two reports ,in first i have one multi value parameter .in
first report i select more than one value ,then in second report i
want to have these values becuse i have another multi value
parameter ,when i join these reports via navigation
in parameters (in first report) i put the values of multi value
parameter in fisrt report for value of multi value parameter in second
report,but in this case fore example i have it:
in first reprt-> navigation->parameters->
ParameterName ParameterVaue
EmployeeID =Parameters!EmployeeID.Value(0)
but in second report i need all of my selections in first report not
just first value
Can anybody help me?On Nov 4, 4:35 am, SHIMAR...@.gmail.com wrote:
> Hi every body!
> I have two reports ,in first i have one multi value parameter .in
> first report i select more than one value ,then in second report i
> want to have these values becuse i have another multi value
> parameter ,when i join these reports via navigation
> in parameters (in first report) i put the values of multi value
> parameter in fisrt report for value of multi value parameter in second
> report,but in this case fore example i have it:
> in first reprt-> navigation->parameters->
> ParameterName ParameterVaue
> EmployeeID =Parameters!EmployeeID.Value(0)
> but in second report i need all of my selections in first report not
> just first value
> Can anybody help me?
If I'm understanding you correctly, you should be able to use an
expression similar to the following to obtain the multi-select
parameter's values from the main report.
=Join(Parameters!EmployeeID.Value,",")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Multi value parameter issue?

How do I make a mvp optional? I don't want to always have to select a
value for it.
Thanks in advanceAs far as I can see, you just can't make it optional.
An alternative to your users could be to (select all). That shouldn't be
that much of a problem, as I assume your report stored procedure already
accepts multiple values.
"gte401e" wrote:
> How do I make a mvp optional? I don't want to always have to select a
> value for it.
> Thanks in advance
>

Multi value "Select All"

Hi all
I have a multi-value drop down list as my report's parameter.
Once the report is executed with the selected parameter, I am
displaying the selected parameter also as a part of the report's
results.
Is there a way I can display the word "All" in the report results when
the user selects "Select All" of the report parameter?
At present it shows a long list of concatenated values of the drop down
list.
Thanks heapsI might be misunderstanding you, but you can display the parameter
label that is selected: Parameter.label instead of parameter.value|||I am sorry if my question was not clear.
I tried Parameter.Label, but gives an error if the parameter is a Multi
Value type.
So, one has to use Join(Parameter.Label,",").
This displays a list of all the paramters that were selected.
What I want is, if the user selects "Select All" instead of showing the
list of parameters, i should be able to display the word "All".
Does this make sense?
Thanks

Multi Select type-in Parameter - To PYRO

Hi All

Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.

My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..

I'm using SSRS with SSAS cube all in BI all 2005

Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.

Thanks in advance

Gerhard Davids

Bump|||Hi Pyro,

I'm sad to say that I too tried more then once to do that but from soom reason I coud'nt.

The only way I manage was useing a query for the multi value parameter.

I don't want to say that its impasisble 'coz maybe some one will have an answer but its definitely not that easy.|||

Thanks Roy

I appreciate you letting me know. I'll post a solution here as
soon as I can find one.

Gerhard

|||Hi pyro.

I've just encounter this problem again :(

Is there any chance that you found a solution?

Thanks.|||

Hey Roy

No solution yet :S

But I have been thinking of one and the following is what i've come up with:

Create a Parameter that is non-queried so that it is type-able.
then create some form of custom code to split user input by 'comma' or
some form of seperation maby '; '. The next step would be to filter the table/matrix accordingley.
Using something like: =Fields!Company = Code.split(Paramters!typeMe.value)
{The code would then have a counter that builds up i guess}

I have yet to test this and the report may become slugish on
large queries, but this is pure creative thinking at the moment.

I most probabley find time to test this in the near future as complaints
are mounting ;P

G

Multi Select type-in Parameter

Hi All

Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.

My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..

I'm using SSRS with SSAS cube all in BI all 2005

Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.

Thanks in advance

Gerhard Davids

Bump|||Hi Pyro,

I'm sad to say that I too tried more then once to do that but from soom reason I coud'nt.

The only way I manage was useing a query for the multi value parameter.

I don't want to say that its impasisble 'coz maybe some one will have an answer but its definitely not that easy.|||

Thanks Roy

I appreciate you letting me know. I'll post a solution here as
soon as I can find one.

Gerhard

|||Hi pyro.

I've just encounter this problem again :(

Is there any chance that you found a solution?

Thanks.|||

Hey Roy

No solution yet :S

But I have been thinking of one and the following is what i've come up with:

Create a Parameter that is non-queried so that it is type-able.
then create some form of custom code to split user input by 'comma' or
some form of seperation maby '; '. The next step would be to filter the table/matrix accordingley.
Using something like: =Fields!Company = Code.split(Paramters!typeMe.value)
{The code would then have a counter that builds up i guess}

I have yet to test this and the report may become slugish on
large queries, but this is pure creative thinking at the moment.

I most probabley find time to test this in the near future as complaints
are mounting ;P

G

Multi Select Parameters?

In SQL Reporting Services, Is there anyway to create a parameter in
which a user can be prompted to choose multiple values from a list?
Ultimately, the multi-selected values would be passed to the Dataset
query as an "IN" qualification.
Example:
SELECT first_name, last_name
FROM employees
LEFT OUTER JOIN department ON
employees.dept_id = department.dept_id
WHERE dept_name IN ('Accounting', 'Sales', Support')
Crystal Reports has this functionality but I can not figure out how to
do this in MSRS.
If you know how to do this and would like to help a guy out, could you
include step by step answer or a link to a step by step tutorial?
Thanks!Here are a couple of options:
Option #1: Use a query expression as follows: ="select top 10 name, type
from sysobjects where type in (" & Parameters!Report_Parameter_0.Value &
")".
Option #2: Check
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=0a3800c4-4180-419c-a117-bfa21b2de099.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:ltnrg01f5qf251e89c5vsrmcdn7fu01k4i@.4ax.com...
> In SQL Reporting Services, Is there anyway to create a parameter in
> which a user can be prompted to choose multiple values from a list?
> Ultimately, the multi-selected values would be passed to the Dataset
> query as an "IN" qualification.
> Example:
> SELECT first_name, last_name
> FROM employees
> LEFT OUTER JOIN department ON
> employees.dept_id = department.dept_id
> WHERE dept_name IN ('Accounting', 'Sales', Support')
> Crystal Reports has this functionality but I can not figure out how to
> do this in MSRS.
> If you know how to do this and would like to help a guy out, could you
> include step by step answer or a link to a step by step tutorial?
> Thanks!sql

Multi Select Parameter Problem

I have a multi-select parameter for a report, but If I select more than one value the report does not return any data. However, if only one value is selected data for that value is returned. I am using the IN option for the SP parameter.

SELECT Code,Description FROM Product

WHERE Code > 0 AND Description IN (@.Description)

I am using SP2 on SQL2005, also I do not have the option of Select-All which I expected to be present.
Any help would be most appreiated.

Thanks

JohnJames

Did you try running the same scenario in SQL query analyser.

A sample data with your executable statements what you have tried in sql server to get the result might help to focus on the issue

|||Hi Raj

Thanks for your reply.
Two values I am selecting are Radio,Television.

These are selected from the Report Viewer.
I have captured values in Temp table and the appear to be correct.

I suspect it is the way SQL Server is interpreting the csv list of values.
|||It does appear to be a problem with the SP.
If I copy the SQL to query designer in the report
the multi-select parameters work fine.
I have written a T-SQL Split function to overcome the problem,
but I am a little surprised that the SP cannot handle the
multi-select parameter.
Is this a problem other developers have come across ?
or is it something wrong I am doing.

JohnJames

Multi Select Parameter in RS2005

I'm using the possibility to define some parameters of my reports having the
property multi-select. On all my reports I always give information on the
first page on the parameters used to generate the output. For parameters that
do not have the property multi-select this works by using expressions
= Parameters!id_status.Value and = Parameters!id_status.Label
where id_status is a parameter
For a multi-select parameter I want to do the same; how ?I guess the new release of RS 2005 does support multi value select. I was
reading FAQ section on Microsoft site, that it is possible to pass multi
value parameters into the report. The way it is implemented is like this.
In a dropdown which lists the values, check all the values and then view the
report. RS constructs the SQL behind the scene.
Hope this helps..
Suresh
"RDC" wrote:
> I'm using the possibility to define some parameters of my reports having the
> property multi-select. On all my reports I always give information on the
> first page on the parameters used to generate the output. For parameters that
> do not have the property multi-select this works by using expressions
> = Parameters!id_status.Value and = Parameters!id_status.Label
> where id_status is a parameter
> For a multi-select parameter I want to do the same; how ?|||I known it works; I want to display the selected values in my report by using
something like
= Parameters!parameter_name.Value and = Parameters!parameter.Label
"Suresh" wrote:
> I guess the new release of RS 2005 does support multi value select. I was
> reading FAQ section on Microsoft site, that it is possible to pass multi
> value parameters into the report. The way it is implemented is like this.
> In a dropdown which lists the values, check all the values and then view the
> report. RS constructs the SQL behind the scene.
> Hope this helps..
> Suresh
> "RDC" wrote:
> > I'm using the possibility to define some parameters of my reports having the
> > property multi-select. On all my reports I always give information on the
> > first page on the parameters used to generate the output. For parameters that
> > do not have the property multi-select this works by using expressions
> > = Parameters!id_status.Value and = Parameters!id_status.Label
> > where id_status is a parameter
> > For a multi-select parameter I want to do the same; how ?|||If you change a report parameter to be multi value, the .Value property will
return an object[] rather than an object. Hence you can no longer e.g. write
expressions like =Parameters!MVP1.Value.ToString().
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"RDC" <RDC@.discussions.microsoft.com> wrote in message
news:4CBB1232-BEC3-4D0F-BC55-F1FD732F3AB8@.microsoft.com...
>I known it works; I want to display the selected values in my report by
>using
> something like
> = Parameters!parameter_name.Value and = Parameters!parameter.Label
> "Suresh" wrote:
>> I guess the new release of RS 2005 does support multi value select. I was
>> reading FAQ section on Microsoft site, that it is possible to pass multi
>> value parameters into the report. The way it is implemented is like this.
>> In a dropdown which lists the values, check all the values and then view
>> the
>> report. RS constructs the SQL behind the scene.
>> Hope this helps..
>> Suresh
>> "RDC" wrote:
>> > I'm using the possibility to define some parameters of my reports
>> > having the
>> > property multi-select. On all my reports I always give information on
>> > the
>> > first page on the parameters used to generate the output. For
>> > parameters that
>> > do not have the property multi-select this works by using expressions
>> > = Parameters!id_status.Value and = Parameters!id_status.Label
>> > where id_status is a parameter
>> > For a multi-select parameter I want to do the same; how ?

Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

sql

Multi select parameter dropdown

Hi there,
Does anyone know if it is possible to make drop down parameters multiple select? Much like one can in a list box by holding down ctrl and clicking on the records, but for parameters.
Please any help will be great.
RegardsIn box?
How could I let parameter accept multi value in a textbox?
Just like i want two parameter values "2003, 2004" in one parameter textbox,
then select the product in 2003 and 2004?
My syntax is..
Select A,B,C From XYZ Where (A in (@.Aparameter) or @.Aparameter='')
How should I do?
Thanks!
Angi
"Myles" <Myles@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:5F9259CD-6036-4F16-B26E-C694085DD0CB@.microsoft.com...
> I believe you can select multiple rows in a drop down, but I don't believe
that Reporting Services (out of the box) will accept multiple values for a
parameter. There may be a way to use the WebService, however - there is a
ReportParameter.MultiValue Property available in there - it is read only,
however. Is it possible to have multi valued parameters using the web
service?
> "PLSH" wrote:
> > Hi there,
> >
> > Does anyone know if it is possible to make drop down parameters multiple
select? Much like one can in a list box by holding down ctrl and clicking on
the records, but for parameters.
> >
> > Please any help will be great.
> >
> > Regards

Multi select parameter and code module

How do I pass a multi-select parameter to a code module?Depending on what you want to do with it, probably the easiest way to pass
its values is to use the Join() method.
join(Parameters!myMultieValueParam.Value, ",")
"Asher_N" <ashernat@.gmail.com> wrote in message
news:Xns97E875A63AC991203214562@.207.46.248.16...
> How do I pass a multi-select parameter to a code module?|||That worked, Thanks.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in
news:eBAQtOIlGHA.4512@.TK2MSFTNGP04.phx.gbl:
> Depending on what you want to do with it, probably the easiest way to
> pass its values is to use the Join() method.
> join(Parameters!myMultieValueParam.Value, ",")
>
> "Asher_N" <ashernat@.gmail.com> wrote in message
> news:Xns97E875A63AC991203214562@.207.46.248.16...
>> How do I pass a multi-select parameter to a code module?
>
>

Multi Select Behaviour

Hi
I am using Sql Server 2005 April CTP with RS. Also I am using VS 2005
Beta 2, I have an issue that multi select drop down with checkboxes
perfectly alright when use in VS 2005 or with http://localhost/Reports,
but when I use it with http://localhost/ReportServer/ it does not
produce that same behvior, here when I hit view report it only keeps
seleted the last one on the multi select list and auto clears the
rest... is this is a bug in April CTP...
please help me thanksThere may have been an issue like that in CTP April, but this definitely
works in CTP September (available through MSDN).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<farooqkarim@.gmail.com> wrote in message
news:1129299389.318290.241380@.g44g2000cwa.googlegroups.com...
> Hi
> I am using Sql Server 2005 April CTP with RS. Also I am using VS 2005
> Beta 2, I have an issue that multi select drop down with checkboxes
> perfectly alright when use in VS 2005 or with http://localhost/Reports,
> but when I use it with http://localhost/ReportServer/ it does not
> produce that same behvior, here when I hit view report it only keeps
> seleted the last one on the multi select list and auto clears the
> rest... is this is a bug in April CTP...
> please help me thanks
>

multi rows SELECT

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = TransactionID FROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

Quote:

Originally Posted by Cshrek

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = VolIDFROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

hi
I am not clear with your code. try with this code .this is not exactly suit for ur requirement but this idea will help you

[code]
declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end|||hey, thanks.
wehn i try to run that code:

----------------------------------

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,

@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end

----------------------------------
it all seems to be good, but i get that error:

"Msg 156, Level 15, State 1, Procedure AccountTrakingSELECT, Line 22
Incorrect syntax near the keyword 'for'."

and i defently have no idea what is that mean? or what am i doing worng?

appriciate your help.sql

multi parameters problem in CTP June

In CTP June Reporting Service and try the multi-value,
use Adventure Work DB and the SQL as follow..
SELECT Name, CountryRegionCode
FROM Person.CountryRegion
WHERE (CountryRegionCode = @.code)
When I checked the multi-value then browse,
I keyin the multi value AF, AL and the textbox show as AF, AL
click " View Report " then response the error message:
An error has occurred during report processing.
Query execution failed for data set 'Adventure'.
Incorrect syntax near ','.
What's happen?
How could I use multi-value in reporting services?
The multi-value dose work on AS Cube Report, but dosen't work on relational
DB?
Thanks for any advice!
AngiYou have to use the IN keyword in the query:
SELECT Name, CountryRegionCode
FROM Person.CountryRegion
WHERE CountryRegionCode (IN @.code)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angi" <enchiw@.msn.com> wrote in message
news:%23ndxNNduFHA.3660@.tk2msftngp13.phx.gbl...
> In CTP June Reporting Service and try the multi-value,
> use Adventure Work DB and the SQL as follow..
> SELECT Name, CountryRegionCode
> FROM Person.CountryRegion
> WHERE (CountryRegionCode = @.code)
> When I checked the multi-value then browse,
> I keyin the multi value AF, AL and the textbox show as AF, AL
> click " View Report " then response the error message:
> An error has occurred during report processing.
> Query execution failed for data set 'Adventure'.
> Incorrect syntax near ','.
> What's happen?
> How could I use multi-value in reporting services?
> The multi-value dose work on AS Cube Report, but dosen't work on
> relational DB?
> Thanks for any advice!
> Angi
>

Multi Parameter Select query

Hi All,

I have a procdeure as written below.

I have created datasets in te report and in Report parameters clicked the Multi-value Parameter option.

When I run the report, I get all the customer names, when I select one customer report returns correct data. When I select two customers in the list box, the result set is empty. Can anyone guide me on what the error could be?

Thanks

Josh

Procedure:

create procedure MyMultiReport @.customername nvarchar(30), @.businessplantype nvarchar(30), @.businessplanyear nvarchar(10) as

Select PlanDatameta.sort,sysperiod.id,Planmeta.id,Planmonthlydata.Productmainpkey,Country, BusinessDivisiondescription, PlanSegmentPkey, Plantext.referencepkey, Plantext.usage, sheet, name, Plantext.text, Brand, Size, text1, PlanDatameta.sort+' '+Plantext1.text as LineDescription,line, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12, Total from Planmonthlydata join Plantext on Plantext.referencepkey=Planmonthlydata.Plansegmentpkey join PlanDatameta on PlanDatameta.pkey=Planmonthlydata.PlanDatametapkey join Productdescription on Productdescription.Productmainpkey=Planmonthlydata.Productmainpkey join Productmain on Productdescription.Productmainpkey=Productmain.pkey join Plansegment on Plansegment.pkey=Planmonthlydata.Plansegmentpkey join bpamain on bpamain.pkey=Plansegment.bpamainpkey join sysperiod on sysperiod.pkey=Plansegment.sysperiodpkey join Planmeta on Planmeta.pkey=Plansegment.Planmetapkey join Plantext Plantext1 on PlanDatameta.pkey=Plantext1.referencepkey where Planmonthlydata.status<>'d' and (PlanDatameta.sheet='PlanProductSummary') and Plantext.text<>'' and (PlanDatameta.line='MyPlanBaselineVolumeBasic' or PlanDatameta.line='BaselineVolumes' or PlanDatameta.line='IncrementalVolumes'or PlanDatameta.line='TotalVolumes') and name in (@.customername) order by PlanDatameta.sort,Plantext.text,text1

return

Hi,

If thecustomer name is the multi valued parameter you can't use that customer name @.CustomerName in the Where clause.

suppose Customer Name parameter contain these values:aaa, bbb , ccc then if you select the aaa, bbb from the parameter it will pass to the Stored procedure in the following format:

@.CustomeName='aaa,bbb'

When you selecting the one value from customer name parameter it is passing to the stored procedure like this: 'aaa' this when you used in In cluase will give you the result.

This @.CustomeName you can't directly use IN Cluase of where.

Select .. from Where name in('aaa,bbb') this will not give any result just you check by running the above select.

Instead you can do one thing

a)First create a table valued function like the following which will take the @.CustomerName as Input parameter and will return the table containg the

aaa

bbb splits the input string by comma and place in the Table.

ALTER function [dbo].[GetCSV]

(@.array varchar(max))

Returns @.t Table (Col1 varchar(max))

as

Begin

DECLARE @.separator_position INT

,@.array_value VARCHAR(1000)

,@.separator CHAR(1)

Set @.separator=','

--For my loop to work I need an extra separator at the end. I always look to the

-- left of the separator character for each array value

SET @.array = @.array + @.separator

-- patindex matches the a pattern against a string

WHILE PATINDEX('%' + @.separator + '%', @.array) <> 0

BEGIN

SELECT @.separator_position = PATINDEX('%' + @.separator + '%',@.array)

SELECT @.array_value = LEFT(@.array, @.separator_position - 1)

INSERT INTO @.t SELECT @.array_value

-- This replaces what we just processed with and empty string

SELECT @.array = STUFF(@.array, 1, @.separator_position, '')

END

Return

End

And use that resulted table in the where clause of your select statement

Select ..

from ..

Where name in (Select * from dbo.GetCSV(@.CustomerName))

It will give you the result.

Hope this helps.

Thanks

|||

You can also use dynamic SQL and filter the records and put them in a temporary table (#temp) first and use this query clause in main query:

code for dynamic SQL:

DELCARE @.strSQL VARCHAR(MAX)

CREATE TABLE #temp (name VARCHAR(50))

SET @.strSQL = 'SELECT name INTO #temp FROM [Table] WHERE name IN (' + @.customername + ')'

EXEC(@.strSQL)

code for main query:

AND name in (SELECT name FROM #temp1) AND ....

Also, dont forget to change the datatype of the input parameter @.customername to NVARCHAR(MAX).

Shyam

Multi Parameter Question Part II

Hi,
I use the following syntax select multi parameter like 1,2,5 and if the
parameter is empty then get all data.
="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
OrderID IN (" + Parameters!ID.Value + ")")
And my problem is..
IF I want to select 3 parameters, ID, Name, Date and use that syntax,
how should I modify the Syntax? or use another way, like procedure? or the
RS can't support my situation?
Thanks!
AngiI would suggest using stored procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I use the following syntax select multi parameter like 1,2,5 and if the
> parameter is empty then get all data.
> ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> OrderID IN (" + Parameters!ID.Value + ")")
> And my problem is..
> IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> how should I modify the Syntax? or use another way, like procedure? or the
> RS can't support my situation?
> Thanks!
> Angi
>|||Stored Procedure can use @. to execute parameter, like
CREATE PROCEDURE sp_ActualVsQuota @.CalendarYear char(4)
But can Stored Procedure execute this parameter function -> (" +
Parameters!CalendarYear.Value + ")?
Cause I want to use multi parameter, and how to define it?
Thanks
Angi
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||About this issue, is any sample could offer?
Thanks!
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||Choose CommandType=StoredProcedure in the data pane of report designer,
choose the stored procedure from the dropdown, and run it. You will be
prompted for parameters.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:%23pLpmb6cEHA.1656@.TK2MSFTNGP09.phx.gbl...
> About this issue, is any sample could offer?
> Thanks!
> "Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> > I would suggest using stored procedure.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "angi" <angi@.microsoft.com> wrote in message
> > news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I use the following syntax select multi parameter like 1,2,5 and if
the
> > > parameter is empty then get all data.
> > > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > > OrderID IN (" + Parameters!ID.Value + ")")
> > >
> > > And my problem is..
> > > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > > how should I modify the Syntax? or use another way, like procedure? or
> the
> > > RS can't support my situation?
> > >
> > > Thanks!
> > > Angi
> > >
> > >
> >
> >
>

multi from multi

Hi,
in the select i got a cell with strings like Jhon#@.#Yos#@.#Rami and i want to
check in the where cluase if one of the names (jhon, Yos or Rami) is in
(Rami, Mos, Dave (not a close list, i get it from another cell). is that
possible?
can i split the string and check for the name in one select statment?
What do i need to use? can i use decode or case?
SagiHere is an example form Anith
--Modify it for your needs
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"sagi aviram" <crisrobin@.013.net.il> wrote in message
news:440530d7$1@.news.barak.net.il...
> Hi,
> in the select i got a cell with strings like Jhon#@.#Yos#@.#Rami and i want
> to check in the where cluase if one of the names (jhon, Yos or Rami) is in
> (Rami, Mos, Dave (not a close list, i get it from another cell). is that
> possible?
> can i split the string and check for the name in one select statment?
> What do i need to use? can i use decode or case?
> Sagi
>|||I'm trying to do this like that:
SELECT request_id REQUEST_ID, parameter6 NO__OF_DAYS, parameter13 TEAM
FROM KCRT_REQUEST_DETAILS
WHERE request_type_id=30132
and (SUBSTR(replace(CONCAT(parameter13, '#@.#'), '#@.#' , ' in
([p.MULTI].TO_STRING) or '), 1, length(replace(CONCAT(parameter13, '#@.#'),
'#@.#' , ' in ([p.MULTI].TO_STRING) or '))-4) )
p.MULTI resolve to ('jhon', 'Yos', 'Rami')
the string in parameter13 is something like Jhon#@.#Yos#@.#Rami and changing.
i was thinking to add #@.# to the end of the string, replace the #@.# with the
string ' in ([p.MULTI]) or ' and then cut the 4 last letters from the end
and get (after the system will resolve the [p.MULTI] token) this:
and ('Jhon' in ('Jhon', 'Yos', 'Rami') or 'Yos' in ('Jhon', 'Yos', 'Rami')
and that will be great but the [p.MULTI] token include ' so it make it
wrong.
any help in here?
Sagi
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OqTIZNPPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Here is an example form Anith
> --Modify it for your needs
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
> "sagi aviram" <crisrobin@.013.net.il> wrote in message
> news:440530d7$1@.news.barak.net.il...
>