Multi Value Parameters

I just created a report with 2 parameters and it works fine. If I change one of them to MV it still works but if I pick 2 values I get a message about a problem with the "," - Incorrect syntas near ','

Is this a known problem or is it something I'm doing. Is a problem is there a fix for it.

YOu habe to change your query after changing the parameter type, as your query probably look like

WHERE SomeColumn = @.MyParameter

which will evaluate in a MV-Parameter to

WHERE SomeColumn = 'A','B'

This is not valid SQL. Therefore you to write your query like the one below.

WHERE SomeColumn IN (@.MyParameter)

That should work for you and your value list.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Works perfect - thank you.

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 Record Insert

Hi,
This works:
INSERT INTO test (valueField) VALUES (1);
This does NOT:
INSERT INTO test (valueField) VALUES (1),(2),(3);
Why and how do i make it work? i want to insert multiple records with one
insert statement.. if i insert each one seperately then it takes too long..
i guess i could use transaction locking... but isn't there an easy (aNSI
compatible) way of inserting many records quickly with as few statements as
possible?
I am trying to compare MySQL 4.1 and 5 to SQL Server 2005 and i would most
like to have SQL statements that work in both.
Thanks for any help!
Ok i am have to create a program that per SQL over ODBC is compatible with
different DBMS's. Qurrently i have a proUse INSERT...SELECT:
insert <table>
(
<column>
)
select <column>
union all
select <column>
union all
select <column>
..
ML
http://milambda.blogspot.com/

multi parameters

Hi,
I want to keyin many values in a parameter textbox, and I try the syntax
like following but it doesn't work, and response the syntax error, why?
SELECT * FROM Product
WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value +"))
Is any mistake in my syntax or how should I modify it?
Thanks!
AngiAngi,
I presume your query string is actually
="SELECT * FROM Product" +
"WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value + "))"
and that you enter values into the parameter field seperated by commas, i.e. 10,11,12
Then it should work. If you still have a problem, try using & instead of + for concatenation.
Regards
Chris McGuigan
"angi" wrote:
> Hi,
> I want to keyin many values in a parameter textbox, and I try the syntax
> like following but it doesn't work, and response the syntax error, why?
> SELECT * FROM Product
> WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value +"))
> Is any mistake in my syntax or how should I modify it?
>
> Thanks!
> Angi
>
>|||Chris,
Thanks to you first.
Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
Dataset SQL dialog or StoreProcedure?
Third, my situation like following..
If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
Excute function be disable, and in Preview, there is an error msg "syntax
error near '=' "
Really have no idea >"<
Thanks again, Chris.
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> Angi,
> I presume your query string is actually
> ="SELECT * FROM Product" +
> "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value + "))"
> and that you enter values into the parameter field seperated by commas,
i.e. 10,11,12
> Then it should work. If you still have a problem, try using & instead of +
for concatenation.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Hi,
> >
> > I want to keyin many values in a parameter textbox, and I try the syntax
> > like following but it doesn't work, and response the syntax error, why?
> >
> > SELECT * FROM Product
> > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > Parameters!Month.Value +"))
> >
> > Is any mistake in my syntax or how should I modify it?
> >
> >
> > Thanks!
> > Angi
> >
> >
> >|||Chris,
Thank you very much!
I know what's the problem with my syntax, I break(use Enter) the syntax, so
when I preview the Report appears syntax error!
With "int" data type, we can enter data value 1,2,5 directly, and I change
to "char" data type, the value must be write as '1','2','5'
Any convenience way to enter "char" value?
And I'm not VB programmer and the newbie of RS, but if there is any chance
to learn, I must to be learning more.
Thanks for your time and your kind. :)
Regards!
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> Hi Angi,
> To write dynamic queries like this, you need to be in the 'Generic Query
Designer' in the 'Data' tab of the report. By default you are in the
'Graphical Query Designer'. To switch, press the button to the left of the
'Run' button (!).
> A dynamic query is actually a SQL query built up in a Visual Basic string.
> I'm guessing you're not a VB programmer, if that's the case you don't have
to learn too much. String handling in VB is similar to string handling in
SQL. The big difference is that SQL strings are delimited by a single quote
('), in VB it's double quotes (").
> I hope that helps.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Chris,
> >
> > Thanks to you first.
> > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > Dataset SQL dialog or StoreProcedure?
> > Third, my situation like following..
> > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > Excute function be disable, and in Preview, there is an error msg
"syntax
> > error near '=' "
> >
> > Really have no idea >"<
> >
> > Thanks again, Chris.
> >
> > Angi
> >
> >
> > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
o¡Ps?D
> > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > Angi,
> > > I presume your query string is actually
> > > ="SELECT * FROM Product" +
> > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > Parameters!Month.Value + "))"
> > >
> > > and that you enter values into the parameter field seperated by
commas,
> > i.e. 10,11,12
> > >
> > > Then it should work. If you still have a problem, try using & instead
of +
> > for concatenation.
> > >
> > > Regards
> > > Chris McGuigan
> > > "angi" wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to keyin many values in a parameter textbox, and I try the
syntax
> > > > like following but it doesn't work, and response the syntax error,
why?
> > > >
> > > > SELECT * FROM Product
> > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value +"))
> > > >
> > > > Is any mistake in my syntax or how should I modify it?
> > > >
> > > >
> > > > Thanks!
> > > > Angi
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Angi,
The only way to make the entry of strings easier for the end user is to try and put the quotes in for them, when you build the SQL string.
Say the WHERE part of your SQL looks like this;
"WHERE Item IN (" + Parameters!Item.Value + ")"
Add a single quote before and after the parameter, and replace any commas with a comma surrounded by single quotes like this;
"WHERE Item IN ('" + Replace(Parameters!Item.Value, ",", "','") + "')"
Note where the single quotes are!
This would convert an entry of 1,2,3 into '1','2','3'
Just watch out for spaces around the commas, that may stop the where clause working properly. You can get round this but that will require more code.
Regards
Chris McGuigan
"angi" wrote:
> Chris,
> Thank you very much!
> I know what's the problem with my syntax, I break(use Enter) the syntax, so
> when I preview the Report appears syntax error!
> With "int" data type, we can enter data value 1,2,5 directly, and I change
> to "char" data type, the value must be write as '1','2','5'
> Any convenience way to enter "char" value?
> And I'm not VB programmer and the newbie of RS, but if there is any chance
> to learn, I must to be learning more.
> Thanks for your time and your kind. :)
> Regards!
> Angi
> "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> > Hi Angi,
> > To write dynamic queries like this, you need to be in the 'Generic Query
> Designer' in the 'Data' tab of the report. By default you are in the
> 'Graphical Query Designer'. To switch, press the button to the left of the
> 'Run' button (!).
> >
> > A dynamic query is actually a SQL query built up in a Visual Basic string.
> > I'm guessing you're not a VB programmer, if that's the case you don't have
> to learn too much. String handling in VB is similar to string handling in
> SQL. The big difference is that SQL strings are delimited by a single quote
> ('), in VB it's double quotes (").
> >
> > I hope that helps.
> > Regards
> > Chris McGuigan
> >
> > "angi" wrote:
> >
> > > Chris,
> > >
> > > Thanks to you first.
> > > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > > Dataset SQL dialog or StoreProcedure?
> > > Third, my situation like following..
> > > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > > Excute function be disable, and in Preview, there is an error msg
> "syntax
> > > error near '=' "
> > >
> > > Really have no idea >"<
> > >
> > > Thanks again, Chris.
> > >
> > > Angi
> > >
> > >
> > > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
> o¡Ps?D
> > > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > > Angi,
> > > > I presume your query string is actually
> > > > ="SELECT * FROM Product" +
> > > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value + "))"
> > > >
> > > > and that you enter values into the parameter field seperated by
> commas,
> > > i.e. 10,11,12
> > > >
> > > > Then it should work. If you still have a problem, try using & instead
> of +
> > > for concatenation.
> > > >
> > > > Regards
> > > > Chris McGuigan
> > > > "angi" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I want to keyin many values in a parameter textbox, and I try the
> syntax
> > > > > like following but it doesn't work, and response the syntax error,
> why?
> > > > >
> > > > > SELECT * FROM Product
> > > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > > Parameters!Month.Value +"))
> > > > >
> > > > > Is any mistake in my syntax or how should I modify it?
> > > > >
> > > > >
> > > > > Thanks!
> > > > > Angi
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

mssql wont find NULL values in datetime field?

Hi

I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:

SELECT * FROM ADMIN WHERE date_edited = NULL

I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:

thanksignore me, found the answer

SELECT * FROM ADMIN WHERE date_edited is NULL|||Consider yourself ignored. ;)|||I'm ignoring him right now. Intensely.|||please dun treat newbies like that, we all started off as a newbie right?

maybe we shall have a newbie section for them to post elementary questions...

but seriously, this NULL question does look dumb......TS shall try harder before posting it......|||personally I still hate nulls and three part logic. It causes problems for so many programmers. I slap as many not null constraints in my software as I can. Of course I spend so much time fighting fires these days, I only get to do reactive development or redevelopment.|||ROTFL ... look at Sean's title in the above post ... i almost missed that.

Another contradictory Seanism :p|||Declare @.ZenosParadox varchar(50)
Set @.ZenosParadox = 'This value is null'|||I learned in math class that zeno's paradox was that you could never reach any destination because you first had to travel 1/2 the distance, then 1/2 again, then again, so you'd never reach it.

poor zeno didn't know an infinite series can sum to a finite number.|||http://en.wikipedia.org/wiki/Liar_paradox ?

:)|||NULL value is good, I dun need to be bothered about putting constraint while showing them on a report hehe...