Hi i am trying to implement Multivalue parameter in my report....
So for the Parameter PlanId i have selected multivalue parameter... and the avialable values are from a query...
when i run the report and just check 1 Plan it works fine.. but when i go to check more than one plan.. I get this error
An error has occured duing local report processing.
an error has occured during report processing.
Query exceution failed for data set Statement.
Error converting data type nvarchar to int.
any help will be appreciated.
Regards
Karen
How are you using the parameter in the SQL?
Can you post an example?
BobP
|||BobP,
thanks for ur answer.. I have a dataset called statements which is a sproc and is as follows
Code Snippet
ALTER PROCEDURE [dbo].[rpt_Statements]
@.PeriodId int,
@.ClientId int,
@.PlanId int = NULL,
@.StartPart int = NULL,
@.StopPart int = NULL
AS
IF @.StartPart IS NULL
SELECT @.StartPart = MIN(ParticipantId)
FROM Participant
IF @.StopPart IS NULL
SELECT @.StopPart = MAX(ParticipantId)
FROM Participant
SELECT DISTINCT
s.PlanId,
PeriodId,
s.ParticipantId,
p.AddrZip, -- Added by Karen
dbo.udf_BeginDate(@.PeriodId) BeginDate,
dbo.udf_EndDate(@.PeriodId) EndDate
FROM
vwParticipantSourceSummary s
JOIN ClientPlan c
ON s.PlanId = c.PlanId
Join Participant p
ON s.ParticipantId = p.ParticipantId
WHERE
ClientId = @.ClientId
AND
PeriodId = @.PeriodId
AND
(
s.PlanId = @.PlanId
OR
(
s.PlanId = s.PlanId
AND
@.PlanId IS NULL
)
)
AND
(
BeginningBalance <> 0
OR
Contributions <> 0
OR
EndingBalance <> 0
)
AND
s.ParticipantId BETWEEN @.StartPart AND @.StopPart
ORDER BY
s.PlanId,
p.AddrZip DESC--ParticipantId
in the report parameter for the parameter @.PlanId i am giving a queried value and selecting a particular dataset.. which is as follows
Code Snippet
ALTER PROCEDURE [dbo].[usp_GetPlansByClient]
@.ClientId int = NULL
AS
--Temp Debug Code
/*declare @.ClientId int
set @.ClientId = 1
*/
SELECT
NULL PlanId,
'< All Plans >' PlanName
UNION
SELECT
PlanId,
PlanName
FROM
ClientPlan
WHERE
ClientId = @.ClientId
OR
(
ClientId = ClientId
AND
@.ClientId IS NULL
)
so when i run the sproc get plans i will get
1
2
3
etc....
but when i try to put it as a multivalue parameter its gonna 1,2,3
i think there is some conversion problem
any help will be appreciated.
Regards
Karen
|||Ok, on multivalue parameters, you shuold use an IN clause in the sql...
So...
Where PlanID in (@.PlanID)
That is what is causing your error, i beleive.
HtH
BobP
|||i changed my sproc to as follows* rpt_Statements
*
* Main stored procedure for Statement reports.
*
* HISTORY
* 3/2006 - Stephen Garrett
* Created.
* 1/28/2007 - Stephen Garrett
* Updated to allow reports to be run for all plans for a client in a given period.
**************************************************************************************************/
Code Snippet
ALTER PROCEDURE [dbo].[rpt_Statements]
@.PeriodId int,
@.ClientId int,
@.PlanId int = NULL,
@.StartPart int = NULL,
@.StopPart int = NULL
AS
IF @.StartPart IS NULL
SELECT @.StartPart = MIN(ParticipantId)
FROM Participant
IF @.StopPart IS NULL
SELECT @.StopPart = MAX(ParticipantId)
FROM Participant
SELECT DISTINCT
s.PlanId,
PeriodId,
s.ParticipantId,
p.AddrZip,
dbo.udf_BeginDate(@.PeriodId) BeginDate,
dbo.udf_EndDate(@.PeriodId) EndDate
FROM
vwParticipantSourceSummary s
JOIN ClientPlan c
ON s.PlanId = c.PlanId
Join Participant p
ON s.ParticipantId = p.ParticipantId
WHERE
ClientId = @.ClientId
AND
PeriodId = @.PeriodId
AND
s.PlanId In (@.PlanId)
--(
-- s.PlanId = @.PlanId
-- OR
-- (
-- s.PlanId = s.PlanId
-- AND
-- @.PlanId IS NULL
-- )
-- )
AND
(
BeginningBalance <> 0
OR
Contributions <> 0
OR
EndingBalance <> 0
)
AND
s.ParticipantId BETWEEN @.StartPart AND @.StopPart
ORDER BY
s.PlanId,
p.AddrZip Desc--ParticipantId
and in my sql query window , i gave this
rpt_statements 6,1,'374,375'
and got this error
Msg 8114, Level 16, State 5, Procedure rpt_Statements, Line 0
Error converting data type varchar to int.
Hope u can help me out here.
Regards
Karen
|||
Since you are using a proc, you will have to pass in the parameter as a string and build your sql as a string and then execute it.
Whenever I use Multi-Valued parameters, I use embedded SQL, not a stored proc, just so I can use the IN clause. It seems to be faster executing than passing the list of values to a proc.
BobP
|||can u pls give me a example as to how u do it..
Regards
Karen
|||Sure. What I do is not use a stored proc, and just put the SQL in the report dataset.
However, you can also pass the parameter string to the proc and build the SQL that way. It makes it harder to test/debug, but it is doable.
here is an example:
Code Snippet
ALTER PROCEDURE [dbo].[rpt_Statements]
@.PeriodId int,
@.ClientId int,
@.PlanId nvarchar(max) = NULL,
@.StartPart int = NULL,
@.StopPart int = NULL
AS
IF @.StartPart IS NULL
SELECT @.StartPart = MIN(ParticipantId)
FROM Participant
IF @.StopPart IS NULL
SELECT @.StopPart = MAX(ParticipantId)
FROM Participant
Declare @.SQLText nvarchar(max)
if @.PlanID is null
Begin
SELECT DISTINCT
s.PlanId,
PeriodId,
s.ParticipantId,
p.AddrZip, -- Added by Karen
dbo.udf_BeginDate(@.PeriodId) BeginDate,
dbo.udf_EndDate(@.PeriodId) EndDate
FROM
vwParticipantSourceSummary s
JOIN ClientPlan c
ON s.PlanId = c.PlanId
Join Participant p
ON s.ParticipantId = p.ParticipantId
WHERE
ClientId = @.ClientId
AND PeriodId = @.PeriodId
AND (
BeginningBalance <> 0
OR
Contributions <> 0
OR
EndingBalance <> 0
)
AND s.ParticipantId BETWEEN @.StartPart AND @.StopPart
ORDER BY
s.PlanId,
p.AddrZip DESC--ParticipantId
End
Else
Begin
Select @.SQLText = 'SELECT DISTINCT
s.PlanId,
PeriodId,
s.ParticipantId,
p.AddrZip, -- Added by Karen
dbo.udf_BeginDate(@.PeriodId) BeginDate,
dbo.udf_EndDate(@.PeriodId) EndDate
FROM
vwParticipantSourceSummary s
JOIN ClientPlan c
ON s.PlanId = c.PlanId
Join Participant p
ON s.ParticipantId = p.ParticipantId
WHERE
ClientId = @.ClientId
AND PeriodId = @.PeriodId
AND s.PlanID in (' + @.PlanID + ')
AND (
BeginningBalance <> 0
OR
Contributions <> 0
OR
EndingBalance <> 0
)
AND s.ParticipantId BETWEEN @.StartPart AND @.StopPart
ORDER BY
s.PlanId,
p.AddrZip DESC--ParticipantId'
execute (@.SQLText)
End
I didn't test that, but it should work fine. (No tables, etc)
BobP
|||Bob
thanks for ur answer... but when i run the sproc i am getting this error
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@.PeriodId".
|||Ahhhh.. sorry about that... here is the corrected code:
Code Snippet
ALTER PROCEDURE [dbo].[rpt_Statements]
@.PeriodId int,
@.ClientId int,
@.PlanId nvarchar(max) = NULL,
@.StartPart int = NULL,
@.StopPart int = NULL
AS
IF @.StartPart IS NULL
SELECT @.StartPart = MIN(ParticipantId)
FROM Participant
IF @.StopPart IS NULL
SELECT @.StopPart = MAX(ParticipantId)
FROM Participant
Declare @.SQLText nvarchar(max)
if @.PlanID is null
Begin
SELECT DISTINCT
s.PlanId,
PeriodId,
s.ParticipantId,
p.AddrZip, -- Added by Karen
dbo.udf_BeginDate(@.PeriodId) BeginDate,
dbo.udf_EndDate(@.PeriodId) EndDate
FROM
vwParticipantSourceSummary s
JOIN ClientPlan c
ON s.PlanId = c.PlanId
Join Participant p
ON s.ParticipantId = p.ParticipantId
WHERE
ClientId = @.ClientId
AND PeriodId = @.PeriodId
AND (
BeginningBalance <> 0
OR
Contributions <> 0
OR
EndingBalance <> 0
)
AND s.ParticipantId BETWEEN @.StartPart AND @.StopPart
ORDER BY
s.PlanId,
p.AddrZip DESC--ParticipantId
End
Else
Begin
Select @.SQLText = 'SELECT DISTINCT
s.PlanId,
PeriodId,
s.ParticipantId,
p.AddrZip, -- Added by Karen
dbo.udf_BeginDate(' + convert(varchar,@.PeriodId) + ') BeginDate,
dbo.udf_EndDate' + convert(varchar,@.PeriodId) + ') EndDate
FROM
vwParticipantSourceSummary s
JOIN ClientPlan c
ON s.PlanId = c.PlanId
Join Participant p
ON s.ParticipantId = p.ParticipantId
WHERE
ClientId = ' + convert(varchar,@.ClientId) + '
AND PeriodId = ' + convert(varchar,@.PeriodId) + '
AND s.PlanID in (' + @.PlanID + ')
AND (
BeginningBalance <> 0
OR
Contributions <> 0
OR
EndingBalance <> 0
)
AND s.ParticipantId BETWEEN ' + Convert(varchar, @.StartPart) + ' AND ' + Convert(varchar, @.StopPart) + '
ORDER BY
s.PlanId,
p.AddrZip DESC--ParticipantId'
execute (@.SQLText)
End
Try that... again, i cant really test with out the tables, but that should give you a really good starting point.
BobP
|||Thanks a lot bob, with works great..
I have a another quick question... is it possible to give a case statement in the order by... Like for examples.. suppose the user will be wanting to send some plans to the client and some plans to the participant.. and if the plans that are selected are to be sent to the client i want them to sort by planId and Addr zip else i want them to sort by.. Addrzip only...
Regards.
Karen
|||It sure is. The only issue that I have found with that is the ASC/DESC flag sometimes will not work with a case...
Order by Case When @.SortBy = 'name'
Then Name
else
City
end
BobP
|||is this right
ORDER BY
Case when psi.MailMethodId = 1
Then
p.AddrZip Desc
Else
s.PlanId,
p.AddrZip desc
End '
cause when i run the sproc i am getting a error that says incorrect syntax near the keyword desc
|||The asc/desc has to go outside of the case statement.ORDER BY
Case when psi.MailMethodId = 1
Then
p.AddrZip
Else
s.PlanId,
p.AddrZip
End desc '
BobP
|||
BobP - BIM wrote:
The asc/desc has to go outside of the case statement. ORDER BY
Case when psi.MailMethodId = 1
Then
p.AddrZip
Else
s.PlanId,
p.AddrZip
End desc '
BobP
Looks likes its not accepting
the Else part... cause i am getting an error saying Incorrect syntax near ','
Regards
Kareb
No comments:
Post a Comment