Query Parameter problem

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

Query Parameter Dialog Box

When I am looking at a dataset and use the "!" execute button I get the pop
up dialog box for parameters. How must I enter my values if i want it to
test multi-value selection?
I've tried
ABC, DEF
'ABC,DEF'
'ABC','DEF'
"ABC","DEF"
ABC;DEF
'ABC';'DEF'
I can't get it to work. Can you only use single values in this view?how do you have the parameter specified? you must use in(@.param) when using
expecting multivalues
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:9E5DF01E-77AA-4D98-8C94-CFEEE2A4E48D@.microsoft.com...
> When I am looking at a dataset and use the "!" execute button I get the
> pop
> up dialog box for parameters. How must I enter my values if i want it to
> test multi-value selection?
> I've tried
> ABC, DEF
> 'ABC,DEF'
> 'ABC','DEF'
> "ABC","DEF"
> ABC;DEF
> 'ABC';'DEF'
> I can't get it to work. Can you only use single values in this view?

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

Query on dataset

I have a dataset filled with data.
I want to get a particular (row,column) value from the dataset.

How do I query on the dataset if the query is
firstname, lastname where employeeid='1234'?

I feel like I have missed the point, but maybe something like:

select firstName,
lastName
from dataset
where employeeId = '1234'

?

Dave

|||What do you mean...dataset? Is this a .NET programming question? Or query in SQL question? Can you post the code you have so far?|||I'm not trying to query the database and fill a dataset.

I get a dataset already containing data and I need to query on the dataset to get a specific value from the dataset.
I know Dataset.Select() exists.
But it does not allow me to pick a (row,column) value based on a criteria.

For example, if the database contains FirstName, LastName, Salary and DOJ in it.
I want to pull only the firstname and lastname of people who joined on maybe 01/06/2006.|||Dave,

This query would work on database and i'm trying to do similar query on a 'DATASET'|||

Krutika:

"Dataset.Select ()" looks to me like a member function; if that is the case this post probably ought to be place in another location to get a better response.

Dave

|||which forum should I place this in?|||

I think you can't directly use Select on a DataSet, but you can do it on a DataTable. So it should look like this :

expression = "EmployeeId = '1234'"

DataSet.Table[0].Select(expression)

Then you'll get an array of DataRows.

you can look at this article : http://msdn2.microsoft.com/en-us/library/det4aw50.aspx

|||Thanks this worked

Query on dataset

I have a dataset filled with data.
I want to get a particular (row,column) value from the dataset.

How do I query on the dataset if the query is
firstname, lastname where employeeid='1234'?

I feel like I have missed the point, but maybe something like:

select firstName,
lastName
from dataset
where employeeId = '1234'

?

Dave

|||What do you mean...dataset? Is this a .NET programming question? Or query in SQL question? Can you post the code you have so far?|||I'm not trying to query the database and fill a dataset.

I get a dataset already containing data and I need to query on the dataset to get a specific value from the dataset.
I know Dataset.Select() exists.
But it does not allow me to pick a (row,column) value based on a criteria.

For example, if the database contains FirstName, LastName, Salary and DOJ in it.
I want to pull only the firstname and lastname of people who joined on maybe 01/06/2006.|||Dave,

This query would work on database and i'm trying to do similar query on a 'DATASET'|||

Krutika:

"Dataset.Select ()" looks to me like a member function; if that is the case this post probably ought to be place in another location to get a better response.

Dave

|||which forum should I place this in?|||

I think you can't directly use Select on a DataSet, but you can do it on a DataTable. So it should look like this :

expression = "EmployeeId = '1234'"

DataSet.Table[0].Select(expression)

Then you'll get an array of DataRows.

you can look at this article : http://msdn2.microsoft.com/en-us/library/det4aw50.aspx

|||Thanks this worked