Query or Parameter issue

I am using the following query for my dataset:
SELECT DISTINCT
vwProtocol.ProtocolNumber,
vwProtocol.HSCNumber,
PIName = (SELECT MemberName
FROM vwMemberNames
WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
TotalAccrual = (SELECT Count(PatientID)
FROM tb_PatientEnrollment
WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
tb_Protocol.ProtocolID,
Reg = (SELECT MemberName
FROM vwMemberNames
WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
RegPhone = (SELECT CASE Len(Telephone)
WHEN 7 THEN stuff(Telephone, 4, 0, '-')
WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
ELSE Telephone
END
FROM tb_Membership
WHERE tb_Membership.MemberID = tb_Protocol.RegulatoryMemberID),
tb_Protocol.Title,
IRBExpiration = vwProtocol.HSCExpiration,
vwProtocol.Status
FROM
tb_Protocol
RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
WHERE
vwProtocol.HSCExpiration between @.begindate
and @.enddate
AND
ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
I am using another dataset to populate the @.reg parameter. When I run this
in preview it works fine. When I deploy it, no matter what I do I get an
error:
This report requires a default or user-defined value for the report
parameter 'reg'. To run or subscribe to this report, you must provide a
parameter value. (rsReportParameterValueNotSet) Get Online Help
I have not a clue what to do next or what could possibly be going wrong. I
have another report on that same server that works just fine with a drop down
so I don't think it is the setup of my report server.
Any ideas, place to look, *anything* would be most appreciated!!!
Thanks,
EvaIn the report designer, under Report menu, look under parameters. You
should see a list for @.begindate, @.enddate and @.reg. If you choose the @.Reg
property in the left column, you should be able to set the default value.
This should fix the problem.
"evaleah" <evaleah@.discussions.microsoft.com> wrote in message
news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
>I am using the following query for my dataset:
> SELECT DISTINCT
> vwProtocol.ProtocolNumber,
> vwProtocol.HSCNumber,
> PIName => (SELECT MemberName
> FROM vwMemberNames
> WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> TotalAccrual => (SELECT Count(PatientID)
> FROM tb_PatientEnrollment
> WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> tb_Protocol.ProtocolID,
> Reg => (SELECT MemberName
> FROM vwMemberNames
> WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> RegPhone => (SELECT CASE Len(Telephone)
> WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> ELSE Telephone
> END
> FROM tb_Membership
> WHERE tb_Membership.MemberID => tb_Protocol.RegulatoryMemberID),
> tb_Protocol.Title,
> IRBExpiration = vwProtocol.HSCExpiration,
> vwProtocol.Status
> FROM
> tb_Protocol
> RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> WHERE
> vwProtocol.HSCExpiration between @.begindate
> and @.enddate
> AND
> ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> I am using another dataset to populate the @.reg parameter. When I run
> this
> in preview it works fine. When I deploy it, no matter what I do I get an
> error:
> This report requires a default or user-defined value for the report
> parameter 'reg'. To run or subscribe to this report, you must provide a
> parameter value. (rsReportParameterValueNotSet) Get Online Help
> I have not a clue what to do next or what could possibly be going wrong.
> I
> have another report on that same server that works just fine with a drop
> down
> so I don't think it is the setup of my report server.
> Any ideas, place to look, *anything* would be most appreciated!!!
> Thanks,
> Eva|||Thanks for the reply!
When I set a default I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot read the next data row for the data set Upcoming.
(rsErrorReadingNextDataRow) Get Online Help
Syntax error converting datetime from character string.
However, if I remove the reg parameter entirely the 2 datetime parameters
work as expected.
Any other ideas?
"goodman93" wrote:
> In the report designer, under Report menu, look under parameters. You
> should see a list for @.begindate, @.enddate and @.reg. If you choose the @.Reg
> property in the left column, you should be able to set the default value.
> This should fix the problem.
> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
> >I am using the following query for my dataset:
> >
> > SELECT DISTINCT
> > vwProtocol.ProtocolNumber,
> > vwProtocol.HSCNumber,
> > PIName => > (SELECT MemberName
> > FROM vwMemberNames
> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> > TotalAccrual => > (SELECT Count(PatientID)
> > FROM tb_PatientEnrollment
> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> > tb_Protocol.ProtocolID,
> > Reg => > (SELECT MemberName
> > FROM vwMemberNames
> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> > RegPhone => > (SELECT CASE Len(Telephone)
> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> > ELSE Telephone
> > END
> > FROM tb_Membership
> > WHERE tb_Membership.MemberID => > tb_Protocol.RegulatoryMemberID),
> > tb_Protocol.Title,
> > IRBExpiration = vwProtocol.HSCExpiration,
> > vwProtocol.Status
> > FROM
> > tb_Protocol
> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> > WHERE
> > vwProtocol.HSCExpiration between @.begindate
> > and @.enddate
> > AND
> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> >
> > I am using another dataset to populate the @.reg parameter. When I run
> > this
> > in preview it works fine. When I deploy it, no matter what I do I get an
> > error:
> > This report requires a default or user-defined value for the report
> > parameter 'reg'. To run or subscribe to this report, you must provide a
> > parameter value. (rsReportParameterValueNotSet) Get Online Help
> >
> > I have not a clue what to do next or what could possibly be going wrong.
> > I
> > have another report on that same server that works just fine with a drop
> > down
> > so I don't think it is the setup of my report server.
> >
> > Any ideas, place to look, *anything* would be most appreciated!!!
> >
> > Thanks,
> > Eva
>
>|||Most likely this comes from passing in a string to compare to
vwProtocol.HSCExpiration. Try doing a "CAST(@.begindate as datetime)" in
your query for the begindate and "CAST(@.endate as datetime)" for the
enddate. You will probably also need to do a CAST(@.reg as integer) for that
to work do--depending on the data type of RegulatoryMemberID.
Hope this helped.
"evaleah" <evaleah@.discussions.microsoft.com> wrote in message
news:EC231B4D-EC8C-4224-AD07-A09E2191ACAC@.microsoft.com...
> Thanks for the reply!
> When I set a default I get the following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot read the next data row for the data set Upcoming.
> (rsErrorReadingNextDataRow) Get Online Help
> Syntax error converting datetime from character string.
> However, if I remove the reg parameter entirely the 2 datetime parameters
> work as expected.
> Any other ideas?
> "goodman93" wrote:
>> In the report designer, under Report menu, look under parameters. You
>> should see a list for @.begindate, @.enddate and @.reg. If you choose the
>> @.Reg
>> property in the left column, you should be able to set the default value.
>> This should fix the problem.
>> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
>> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
>> >I am using the following query for my dataset:
>> >
>> > SELECT DISTINCT
>> > vwProtocol.ProtocolNumber,
>> > vwProtocol.HSCNumber,
>> > PIName =>> > (SELECT MemberName
>> > FROM vwMemberNames
>> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
>> > TotalAccrual =>> > (SELECT Count(PatientID)
>> > FROM tb_PatientEnrollment
>> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
>> > tb_Protocol.ProtocolID,
>> > Reg =>> > (SELECT MemberName
>> > FROM vwMemberNames
>> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
>> > RegPhone =>> > (SELECT CASE Len(Telephone)
>> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
>> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
>> > ELSE Telephone
>> > END
>> > FROM tb_Membership
>> > WHERE tb_Membership.MemberID =>> > tb_Protocol.RegulatoryMemberID),
>> > tb_Protocol.Title,
>> > IRBExpiration = vwProtocol.HSCExpiration,
>> > vwProtocol.Status
>> > FROM
>> > tb_Protocol
>> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
>> > WHERE
>> > vwProtocol.HSCExpiration between @.begindate
>> > and @.enddate
>> > AND
>> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
>> >
>> > I am using another dataset to populate the @.reg parameter. When I run
>> > this
>> > in preview it works fine. When I deploy it, no matter what I do I get
>> > an
>> > error:
>> > This report requires a default or user-defined value for the report
>> > parameter 'reg'. To run or subscribe to this report, you must provide a
>> > parameter value. (rsReportParameterValueNotSet) Get Online Help
>> >
>> > I have not a clue what to do next or what could possibly be going
>> > wrong.
>> > I
>> > have another report on that same server that works just fine with a
>> > drop
>> > down
>> > so I don't think it is the setup of my report server.
>> >
>> > Any ideas, place to look, *anything* would be most appreciated!!!
>> >
>> > Thanks,
>> > Eva
>>|||What have you got your date params datatypes set as.
I tend to pass them as strings in mm/dd/yyy format, and it works fine for
evaluating against date datatypes.
"goodman93" wrote:
> Most likely this comes from passing in a string to compare to
> vwProtocol.HSCExpiration. Try doing a "CAST(@.begindate as datetime)" in
> your query for the begindate and "CAST(@.endate as datetime)" for the
> enddate. You will probably also need to do a CAST(@.reg as integer) for that
> to work do--depending on the data type of RegulatoryMemberID.
> Hope this helped.
> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> news:EC231B4D-EC8C-4224-AD07-A09E2191ACAC@.microsoft.com...
> > Thanks for the reply!
> >
> > When I set a default I get the following error:
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot read the next data row for the data set Upcoming.
> > (rsErrorReadingNextDataRow) Get Online Help
> > Syntax error converting datetime from character string.
> >
> > However, if I remove the reg parameter entirely the 2 datetime parameters
> > work as expected.
> >
> > Any other ideas?
> >
> > "goodman93" wrote:
> >
> >> In the report designer, under Report menu, look under parameters. You
> >> should see a list for @.begindate, @.enddate and @.reg. If you choose the
> >> @.Reg
> >> property in the left column, you should be able to set the default value.
> >> This should fix the problem.
> >>
> >> "evaleah" <evaleah@.discussions.microsoft.com> wrote in message
> >> news:E0743865-AA3A-4038-8E40-A67AEF6B42A7@.microsoft.com...
> >> >I am using the following query for my dataset:
> >> >
> >> > SELECT DISTINCT
> >> > vwProtocol.ProtocolNumber,
> >> > vwProtocol.HSCNumber,
> >> > PIName => >> > (SELECT MemberName
> >> > FROM vwMemberNames
> >> > WHERE vwMemberNames.MemberID = tb_Protocol.PIMemberID),
> >> > TotalAccrual => >> > (SELECT Count(PatientID)
> >> > FROM tb_PatientEnrollment
> >> > WHERE tb_PatientEnrollment.ProtocolID = tb_Protocol.ProtocolID),
> >> > tb_Protocol.ProtocolID,
> >> > Reg => >> > (SELECT MemberName
> >> > FROM vwMemberNames
> >> > WHERE vwMemberNames.MemberID = tb_Protocol.RegulatoryMemberID),
> >> > RegPhone => >> > (SELECT CASE Len(Telephone)
> >> > WHEN 7 THEN stuff(Telephone, 4, 0, '-')
> >> > WHEN 10 THEN stuff(substring(Telephone, 4, 7), 4, 0, '-')
> >> > ELSE Telephone
> >> > END
> >> > FROM tb_Membership
> >> > WHERE tb_Membership.MemberID => >> > tb_Protocol.RegulatoryMemberID),
> >> > tb_Protocol.Title,
> >> > IRBExpiration = vwProtocol.HSCExpiration,
> >> > vwProtocol.Status
> >> > FROM
> >> > tb_Protocol
> >> > RIGHT JOIN vwProtocol ON vwProtocol.ProtocolID = tb_Protocol.ProtocolID
> >> > WHERE
> >> > vwProtocol.HSCExpiration between @.begindate
> >> > and @.enddate
> >> > AND
> >> > ISNULL(tb_Protocol.RegulatoryMemberID,'') = @.reg
> >> >
> >> > I am using another dataset to populate the @.reg parameter. When I run
> >> > this
> >> > in preview it works fine. When I deploy it, no matter what I do I get
> >> > an
> >> > error:
> >> > This report requires a default or user-defined value for the report
> >> > parameter 'reg'. To run or subscribe to this report, you must provide a
> >> > parameter value. (rsReportParameterValueNotSet) Get Online Help
> >> >
> >> > I have not a clue what to do next or what could possibly be going
> >> > wrong.
> >> > I
> >> > have another report on that same server that works just fine with a
> >> > drop
> >> > down
> >> > so I don't think it is the setup of my report server.
> >> >
> >> > Any ideas, place to look, *anything* would be most appreciated!!!
> >> >
> >> > Thanks,
> >> > Eva
> >>
> >>
> >>
>
>|||I had a problem with a ReportingServices Parameter named 'reg', too. The report was called from c#-code with parameters via soap. It should get something like "param1='Value1';reg='Value2';param3='Value3'" but got 'param1='Value1(REG)=Value2';param3='Value'" ((REG) for the registry symbol 'R' in a circle). If anybody knows why this happens or how to mask a parameter named reg, I would like to know. Yes, the problem disappeared when I renamed that parameter
From http://www.developmentnow.com/g/115_2005_7_0_0_560706/Query-or-Parameter-issue.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com