Parameter multi-value problem when using a stored procedure

Hi folks,
I am describing the issue as below:
1. create a stored procedure as below
....where age in (@.p_age)
note: age is the table coumn of table table1 with datatype tinyint
2. .... and create a second dataset for parameter @.p_age
select distinct age from table1
3. associate the parameter...run it
4. There is no problem with single value. But when two ages are selected,
I got error message, "...Erro convert data type nvarchar to tinyint"
Please advise. PeterYou cannot pass and use multi-value parameters to a stored procedure and use
it in a query as you have. If that query was in RS itself then it would
work. This is not a RS thing, it is a SQL Server stored procedure issue.
Just try it from Query Analyzer and you will see what I mean. I do the
following, I create
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter|||Try again, sent before done:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the 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
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter

Parameter is missing a value

Hi Folks,

I'm receiving the "Parameter is missing a value" error message while testing my report.

I have five parameters, two of which are hidden that gets prefilled from the query below. The hidden parameters are DatatechClient and DatatechProduct.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Three parameters are shown and the report works fine as long as the CMRNum and AcctNum is found in the V_TranslationTable however, the error generates when they are not found. I looked through the other threads in this forum that deals with "Parameter is missing a value" but it didn't appear to be a solution shown.

Thanks in advance for any assistance you give.

I take it that when either CMRNum or AcctNum don't exist, you don't want the report to error out. Use this in your query - the report should run and return nothing, but won't error out:

Where IsNull(CMRNum,'') = @.Cmr AND IsNull(AcctNum,'') = @.ClientNum

|||

Hi Jamvir,

Thanks for responding.

I need to give more clearity. My report consists of six datasets and I'm working with RS2005. @.Cmr and @.ClientNum gets populated by a query in my first two datasets. My two hidden parameters @.DatatechClient and @.DatatechProduct are a part of my third dataset and they are the columns that are coming back blank from the query below.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my fifth dataset query below.

SELECT EntityID, PrimaryDirInd, IsOnContract, [5DigitCode], EntityName, Addr1, City, State, Zip, Attn, Phone, ClientID, ProductID
FROM V_EntityDirectories
WHERE ((DirID = @.DirNum) AND (ClientID = @.DatatechClient) AND (ProductID = @.DatatechProduct))
ORDER BY EntityID

Also blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my sixth dataset query below.

SELECT PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID, PkgHdngs.HdngSeqNum, PkgHdngs.HdngText, PkgHdngs.HdngCode, PkgHdngs.DirVer, HdngAds.AdUnitID, HdngAds.AdRate
FROM PkgHdngs INNER JOIN
HdngAds ON PkgHdngs.ClientID = HdngAds.ClientID AND PkgHdngs.ProductID = HdngAds.ProductID AND PkgHdngs.DirID = HdngAds.DirID AND PkgHdngs.DirVer = HdngAds.DirVer AND PkgHdngs.HdngSeqNum = HdngAds.HdngSeqNum
WHERE ((PkgHdngs.DirID = @.DirID) AND (PkgHdgns.ClientID = @.DatatechClient) AND (PkgHdgns.ProductID = @.DatatechProduct))
ORDER BY PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID

Best regards

|||

Have Non queried default values for DatatechClient and DatatechProduct parameters and use the follwing expression for it:

=IIf(Count(Fields!DatatechClient.Value, "DataSet3") > 0, First(Fields!DatatechClient.Value, "DataSet3"), "SomeDefaultValueOrBlank")

and similarly for DatatechProduct

parameter dependency

hi folks
I have a Report Parameters issue:
Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
Parameter# 2 is a (organization) drop-down with 'All','US','Canada',etc.
There is NO dependencies between #1 & #2 and both are SQL Queries
Parameter# 3 is a (location) drop-down based on #1+#2:
like 'All-All', 'All-US','2001-Canada',etc.
I have a (functioning) stored proc. for parameter#3, but I'm having issues
with constructing the value for the sproc call. It doesn't let me do things
like:
exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
-or-
exec sp_location @.YR,@.CNTRY
also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
'2000-US' should bring back all locations for drop-down#3, like
(2000-US-)DETROIT, etc.)
I keep getting forward dependency errors on the build.
thanks
RobTried another option:
For parameter#3 I swapped the sproc for a query like:
select 'All' as YR, CNTRY, Location from myview with (nolock)
union
select YR, 'All' as CNTRY, Location from myview with (nolock)
union
select YR, CNTRY, Location from myview with (nolock)
ORDER BY Location
AND then applied filters for YR & CNTRY as the Parameters!?.value, but this
return nothing in Parameter#3 drop-down.
Rob
(done lots of drop-down direct dependencies, but never where a drop-down
depends on more than one parameter value... :(
"tutor" wrote:
> hi folks
> I have a Report Parameters issue:
> Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> Parameter# 2 is a (organization) drop-down with 'All','US','Canada',etc.
> There is NO dependencies between #1 & #2 and both are SQL Queries
> Parameter# 3 is a (location) drop-down based on #1+#2:
> like 'All-All', 'All-US','2001-Canada',etc.
> I have a (functioning) stored proc. for parameter#3, but I'm having issues
> with constructing the value for the sproc call. It doesn't let me do things
> like:
> exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> -or-
> exec sp_location @.YR,@.CNTRY
> also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> '2000-US' should bring back all locations for drop-down#3, like
> (2000-US-)DETROIT, etc.)
> I keep getting forward dependency errors on the build.
> thanks
> Rob|||"=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
For Parm #3, have you tried using code to build the query string instear
of a stored proc?
as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
> hi folks
> I have a Report Parameters issue:
> Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> Parameter# 2 is a (organization) drop-down with
> 'All','US','Canada',etc.
> There is NO dependencies between #1 & #2 and both are SQL Queries
> Parameter# 3 is a (location) drop-down based on #1+#2:
> like 'All-All', 'All-US','2001-Canada',etc.
> I have a (functioning) stored proc. for parameter#3, but I'm having
> issues with constructing the value for the sproc call. It doesn't let
> me do things like:
> exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> -or-
> exec sp_location @.YR,@.CNTRY
> also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> '2000-US' should bring back all locations for drop-down#3, like
> (2000-US-)DETROIT, etc.)
> I keep getting forward dependency errors on the build.
> thanks
> Rob|||Hi Asher
Just tried it and although it runs just fine from the Dataset, it has an
issue with converting the YR value of 'All' to an int...
This parameter stuff is 'sucking out my life force...' (what I think is a 5
minute job is getting close to 5 hours now...)
Let me trying...
Rob
"Asher_N" wrote:
> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
> For Parm #3, have you tried using code to build the query string instear
> of a stored proc?
> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>
> > hi folks
> >
> > I have a Report Parameters issue:
> >
> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> > Parameter# 2 is a (organization) drop-down with
> > 'All','US','Canada',etc.
> >
> > There is NO dependencies between #1 & #2 and both are SQL Queries
> >
> > Parameter# 3 is a (location) drop-down based on #1+#2:
> >
> > like 'All-All', 'All-US','2001-Canada',etc.
> >
> > I have a (functioning) stored proc. for parameter#3, but I'm having
> > issues with constructing the value for the sproc call. It doesn't let
> > me do things like:
> >
> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> >
> > -or-
> >
> > exec sp_location @.YR,@.CNTRY
> >
> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> > '2000-US' should bring back all locations for drop-down#3, like
> > (2000-US-)DETROIT, etc.)
> >
> > I keep getting forward dependency errors on the build.
> >
> > thanks
> > Rob
>|||got it!
adding an 'All' - 'All' portion to the query and putting it in a view was
the ticket (ie.
select TOP 100 'All' as YR, CNTRY, Location from myview with (nolock)
union
select TOP 100 YR, 'All' as CNTRY, Location from myview with (nolock)
union
select TOP 100 YR, CNTRY, Location from myview with (nolock)
union
select TOP 100 'All' as YR, 'All' as CNTRY, Location from myview with (nolock)
ORDER BY Location)
now I get drop-down values...
this is really 'hokey' and I wish somebody could do this with multiple sp
paramters.
rob
"Asher_N" wrote:
> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
> For Parm #3, have you tried using code to build the query string instear
> of a stored proc?
> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>
> > hi folks
> >
> > I have a Report Parameters issue:
> >
> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> > Parameter# 2 is a (organization) drop-down with
> > 'All','US','Canada',etc.
> >
> > There is NO dependencies between #1 & #2 and both are SQL Queries
> >
> > Parameter# 3 is a (location) drop-down based on #1+#2:
> >
> > like 'All-All', 'All-US','2001-Canada',etc.
> >
> > I have a (functioning) stored proc. for parameter#3, but I'm having
> > issues with constructing the value for the sproc call. It doesn't let
> > me do things like:
> >
> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> >
> > -or-
> >
> > exec sp_location @.YR,@.CNTRY
> >
> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> > '2000-US' should bring back all locations for drop-down#3, like
> > (2000-US-)DETROIT, etc.)
> >
> > I keep getting forward dependency errors on the build.
> >
> > thanks
> > Rob
>|||"=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
news:0EF21B79-A883-40ED-95DA-A3848E74AAB1@.microsoft.com:
> Hi Asher
> Just tried it and although it runs just fine from the Dataset, it has
> an issue with converting the YR value of 'All' to an int...
>
Pass it as a string to the code. The code is a VB function that would
build a SQL stmt with string concat, so it deals with strings better. You
could also tets for the values and isert the right stmt better.
> This parameter stuff is 'sucking out my life force...' (what I think
> is a 5 minute job is getting close to 5 hours now...)
> Let me trying...
> Rob
> "Asher_N" wrote:
>> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
>> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
>> For Parm #3, have you tried using code to build the query string
>> instear of a stored proc?
>> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>>
>> > hi folks
>> >
>> > I have a Report Parameters issue:
>> >
>> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
>> > Parameter# 2 is a (organization) drop-down with
>> > 'All','US','Canada',etc.
>> >
>> > There is NO dependencies between #1 & #2 and both are SQL Queries
>> >
>> > Parameter# 3 is a (location) drop-down based on #1+#2:
>> >
>> > like 'All-All', 'All-US','2001-Canada',etc.
>> >
>> > I have a (functioning) stored proc. for parameter#3, but I'm having
>> > issues with constructing the value for the sproc call. It doesn't
>> > let me do things like:
>> >
>> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
>> >
>> > -or-
>> >
>> > exec sp_location @.YR,@.CNTRY
>> >
>> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
>> > '2000-US' should bring back all locations for drop-down#3, like
>> > (2000-US-)DETROIT, etc.)
>> >
>> > I keep getting forward dependency errors on the build.
>> >
>> > thanks
>> > Rob
>>

Pair of records

Hi folks,

I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B

I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?

Any urgent help will be highly appreicated.

Thanksany suggestion on my problem?|||possibly the reason no one answered within such a short time (it is courtesy not to bump your own question at all, but if you feel you must, give it more than just a few hours) is that your question is not very clear

here's one solution:create table pairs
( sr_no smallint not null
, week_no varchar(9)
)

insert into pairs values (1, '24-A')
insert into pairs values (2, '24-B')
insert into pairs values (3, '24-C')
insert into pairs values (4, '25-A')
insert into pairs values (5, '25-B')
insert into pairs values (6, '26-A')
insert into pairs values (7, '26-B')

select one.week_no
, two.week_no
from pairs one
, pairs two
where one.sr_no
= two.sr_no - 1
and floor(one.sr_no/2)
< floor((one.sr_no+1)/2)

24-A 24-B
24-C 25-A
25-B 26-A|||I am controlling data with Week numbers instead. So i am trying to get data of 24 and 25 togather and so one. Sr. Number is nothing but increament field.

Thanks for reply Mr. r937|||your data is very confusing

how about showing some rows of real data, and then show what results you expect

and which is it, oracle or sql server?|||here is your solution:select week_no/2
, amount
from yourtable
group
by week_no/2|||week_no/2?

You lost me on that one Rudy!

Mr roomi, I think your problem is that your table does not follow basic rules of normalization. You are trying to store two values ("24", and "A", for instance) in a single field. This makes it difficult to sort or group by the column's components.

Ideally, break the column into two different columns. If that is not an option, then you can simulate a "week_number" column using a formula:
Select left(week_no, charindex('-', week_no)-1) as week_number

(Double-check the syntax. I've been in Oracle recently and my TSQL is a little rusty...)|||week_no/2?

You lost me on that one Rudy!piece o' cake, my good man

divide the week number by 2, and because it's an integer, the result is an integer

so 22/2 is 11, 23/2 is 11, 24/2 is 12, 25/2 is 12, and so on

this allows you to group the pairs of weeks -- 22 with 23, 24 with 25, and so on

as for the normalization, check the other thread, it has better sample data, there's nothing wrong with the design|||aw crap, i meant to cross link the guy's other post and forgot to

http://www.dbforums.com/t1022906.html

guy has two identical threads going on in separate forums|||Jeez, a cross-post. This is a forum, not a freaking scavenger hunt.

I was baffled how you were going to divide the string "24-A" by two...

Mr. Roomi, here is a rule of thumb:
"You cannot code a problem clearly and simply until you can state the problem clearly and simply."

Really. I deal with people all too frequently who start coding without being able to define the problem, and they never get very far.