Cascading Parameters with no default

Let's say I have 3 dropdown Sites->Depts->Areas
Each ones have their dataset, and use from query.
How can I only have Sites with a default and not Depts?
If Sites has a default, it populates Depts automatically with values and
<Select a value>. That's fine. But in order to make the report work, I also
have to put a default to Depts so Areas gets something with <Select a value>
otherwise I get an error "The value provided for the report parameter
'AreaId' is not valid for its type".
I don't want that, I want something like this:
Sites Depts Areas
<Site A> <Select a value> <Select a value>
ThanksHello joe,
When you use the Cascading Parameters, how you query for the Areas?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||From query.
"Wei Lu [MSFT]" wrote:
> Hello joe,
> When you use the Cascading Parameters, how you query for the Areas?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||hello,
I would like to get your query.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sites
--
ALTER PROCEDURE [dbo].[repGetSites]
(
@.parentid int=null
)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
if @.parentid is null
Select * from sites
else
Select * from sites where parentid=@.parentid
END
Depts
--
ALTER PROCEDURE [dbo].[repGetDepts]
(
@.siteid int=null
)
AS
BEGIN
SET NOCOUNT ON;
if @.siteid is null
Select DeptId,DeptName,parentid from Departments
else
Select DeptId,DeptName,parentid from Departments where parentid=@.siteid
END
Areas
--
ALTER PROCEDURE [dbo].[repGetAreas]
(
@.parentid int=null
)
AS
BEGIN
SET NOCOUNT ON;
if @.parentid is null
select Null as AreaId,'Any' as AreaName
union
Select AreaId,AreaName from Areas
else
if exists(Select AreaId,AreaName from Areas where parentid=@.parentid)
select Null as AreaId,'Any' as AreaName
union
Select AreaId,AreaName from Areas where parentid=@.parentid
else
select Null as AreaId,'Any' as AreaName
END
Parameters definition
--
<ReportParameter Name="siteid">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>Sites</DataSetName>
<ValueField>SiteId</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Site:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Sites</DataSetName>
<ValueField>SiteId</ValueField>
<LabelField>SiteName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="deptid">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>Departments</DataSetName>
<ValueField>DeptId</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Department:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Departments</DataSetName>
<ValueField>DeptId</ValueField>
<LabelField>DeptName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Areaid">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<AllowBlank>true</AllowBlank>
<Prompt>Area:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Areas</DataSetName>
<ValueField>AreaId</ValueField>
<LabelField>AreaName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
By the way, is there any way I can change the layout of how Parameters are
displayed?
Thanks for your help. Much appreciated.
"Wei Lu [MSFT]" wrote:
> hello,
> I would like to get your query.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Joe,
I tested on my side.
The report server could not valid for the third cascading parameter if the
second one did not have default value.
Also, why your Area dataset is using the @.parentId parameter?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes Wei, each parameter in the chain must get a valid param from previous.
The thing is why the report engine is not letting the user doing the
selection one at the time? It is so common behaviour!! You select one value
from first dropdown (parent), the second dropdown(child) gets the param
value, execute the query and populate its list. You select a value from
second dropdown(parent for third dropdown), the third dropdown(child) gets
the param value, execute the query and populate its list. You select a value
from third dropdown and there you go, report generates! I can't imagine that
it is not feasible! What a big flaw it is if we can't do it! I will have to
use the ReportViewer Web Control and handle this myself and pass the
parameters to the report and I don't like that. Reporting Services is
suppose to save me time... well... it is not.
I am surprised not seeing many posts complaining about that. Anyway I do.
Not counting the fact that we cannot disposed the parameters the way we want
on that toolbar...
About the Area parent id, it is there because I reused the query from
something else.
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello, Joe
Please submit your idea to the http://connect.microsoft.com/sqlserver.
The product team would monitor this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Cascading parameters and multi-value parameters

I have a dataset where a user can select yard, well, or both yard and
well. (basically a parameter with yard and well as values, but I made
it a multi-value parameter so they can choose both if needed). The
problem I am having is that the next paramater is based off of the
first one. If yard is selected I run a certain query, if well is
selected a run a different query. How can I say if all is selected
join both the yard and well query to get the data results. Any ideas?
thanks in advance.Use an If in the datatab and use In ('Yard', 'Well') in your query.
Amarnath
"aggiechick717" wrote:
> I have a dataset where a user can select yard, well, or both yard and
> well. (basically a parameter with yard and well as values, but I made
> it a multi-value parameter so they can choose both if needed). The
> problem I am having is that the next paramater is based off of the
> first one. If yard is selected I run a certain query, if well is
> selected a run a different query. How can I say if all is selected
> join both the yard and well query to get the data results. Any ideas?
> thanks in advance.
>

Cascading parameters

Hi,
I have created a dataset say "DSMasterList" which has the list of items say
product list and also I have another dataset say "DSDetails" which will have
details of the products for a given productID.
My requirement is to have a report that will print ALL the products from
"DSMasterList" and its appropirate details using "DSDetails".
I tried using cascading parameters but then user will have a select the
product and then will create the product detail report for the selected
product. Is there any way where it will show the product details for all the
products available in the "DSMasterList" dataset without user selecting them.
Thanks & Regards,
RajCreate two independent reports and embed one as a subreport inside the other,
like this:
Create a report with the product details. Use productid as parameter.
Create a report with the master product list. You can use list or table data
region, because they can contain other data regions. Drag a subreport control
inside the list or data region. Go to properties of the subreport and specify
the report which has your product details. You also specify the link
parameter here, so that the product details appear before the correct product.
HTH
Charles Kangai, MCT, MCDBA
"Raj" wrote:
> Hi,
> I have created a dataset say "DSMasterList" which has the list of items say
> product list and also I have another dataset say "DSDetails" which will have
> details of the products for a given productID.
> My requirement is to have a report that will print ALL the products from
> "DSMasterList" and its appropirate details using "DSDetails".
> I tried using cascading parameters but then user will have a select the
> product and then will create the product detail report for the selected
> product. Is there any way where it will show the product details for all the
> products available in the "DSMasterList" dataset without user selecting them.
> Thanks & Regards,
> Raj|||Thanks for the reponse Charles.
I tired using the subreports, but it seems there is some issue with
exporting such reports to excel. It give a error saying "Subreports with
table/matrix cells are ignored." Any Ideas on how to handle this error?
Best Regards,
Rajiv
"Charles Kangai" wrote:
> Create two independent reports and embed one as a subreport inside the other,
> like this:
> Create a report with the product details. Use productid as parameter.
> Create a report with the master product list. You can use list or table data
> region, because they can contain other data regions. Drag a subreport control
> inside the list or data region. Go to properties of the subreport and specify
> the report which has your product details. You also specify the link
> parameter here, so that the product details appear before the correct product.
> HTH
> Charles Kangai, MCT, MCDBA
> "Raj" wrote:
> > Hi,
> >
> > I have created a dataset say "DSMasterList" which has the list of items say
> > product list and also I have another dataset say "DSDetails" which will have
> > details of the products for a given productID.
> >
> > My requirement is to have a report that will print ALL the products from
> > "DSMasterList" and its appropirate details using "DSDetails".
> >
> > I tried using cascading parameters but then user will have a select the
> > product and then will create the product detail report for the selected
> > product. Is there any way where it will show the product details for all the
> > products available in the "DSMasterList" dataset without user selecting them.
> >
> > Thanks & Regards,
> > Raj|||Raj,
I have a report that has salesperson data and underneath in a subreport the
sales details of the salesperson are listed. I even have picture of the
salesperson in the main part. I have used a subreport control inside a list
data region. And it is working. After reading your post I tried exporting it
to Excel, and it exported OK. there may be an issue I am not aware of if you
had a table inside a table. Mine was a table inside a list.
There is another option that also works. You can create master/detail report
by writing one join query to include both the master rows and the detail
rows. You can use a table data region and group by the fields from the master
record and show the detail rows underneath each one. But the first option I
showed you should work, as far as I know.
Cheers!
Charles Kangai, MCT, MCDBA
"Raj" wrote:
> Thanks for the reponse Charles.
> I tired using the subreports, but it seems there is some issue with
> exporting such reports to excel. It give a error saying "Subreports with
> table/matrix cells are ignored." Any Ideas on how to handle this error?
> Best Regards,
> Rajiv
>
> "Charles Kangai" wrote:
> > Create two independent reports and embed one as a subreport inside the other,
> > like this:
> >
> > Create a report with the product details. Use productid as parameter.
> >
> > Create a report with the master product list. You can use list or table data
> > region, because they can contain other data regions. Drag a subreport control
> > inside the list or data region. Go to properties of the subreport and specify
> > the report which has your product details. You also specify the link
> > parameter here, so that the product details appear before the correct product.
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Raj" wrote:
> >
> > > Hi,
> > >
> > > I have created a dataset say "DSMasterList" which has the list of items say
> > > product list and also I have another dataset say "DSDetails" which will have
> > > details of the products for a given productID.
> > >
> > > My requirement is to have a report that will print ALL the products from
> > > "DSMasterList" and its appropirate details using "DSDetails".
> > >
> > > I tried using cascading parameters but then user will have a select the
> > > product and then will create the product detail report for the selected
> > > product. Is there any way where it will show the product details for all the
> > > products available in the "DSMasterList" dataset without user selecting them.
> > >
> > > Thanks & Regards,
> > > Raj

Cascading Parameters

I am using DB2 ... how do I tie the parameter from one dataset to another. I
have a query for dataset 1 based on metrics. The metric they choose needs
to be a parameter in the second dataset query I have. I have created a
parameter for the metric and have it tied to a query (in the report
parameters window) ... but if I put metric (which of course has to be a ? for
unnamed parameters) as a parameter in the second dataset then I get an error.
How does reporting services know that the metric is a parameter to the
second dataset?In the dataset click on the ... go to the parameters tab. Map the query
parameter to the parameter that the cascading parameter is based on.
In layout tab go to Report Menu -> Report Parameters. Make sure the
parameter the second dataset is dependent on is before the depenent
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
>I am using DB2 ... how do I tie the parameter from one dataset to another.
>I
> have a query for dataset 1 based on metrics. The metric they choose
> needs
> to be a parameter in the second dataset query I have. I have created a
> parameter for the metric and have it tied to a query (in the report
> parameters window) ... but if I put metric (which of course has to be a ?
> for
> unnamed parameters) as a parameter in the second dataset then I get an
> error.
> How does reporting services know that the metric is a parameter to the
> second dataset?|||Bruce,
I'm not sure if I was clear in my post. The first dataset does not have a
parameter but the selection from that result set needs to BE the parameter
for the second dataset
dataset 1:
select distinct metric_code, metric_name
from grsinst1.metric met
join grsinst1.availability_def availdef
on availdef.derived_metric_code = met.metric_code with ur
dataset 2:
SELECT res_name, start_lcl_dt, res_grp_name, res.res_type, value AS
Percent, value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
grpthresh.threshd_target) AS threshd_target,
coalesce(thresh.threshd_warning, grpthresh.threshd_warning) AS
threshd_warning,
coalesce(thresh.threshd_critical,
grpthresh.threshd_critical) AS threshd_critical, MAX(start_lcl_dt) OVER() AS
MaxDate,
CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
THEN(((100.0 - VALUE) / 100) * VALUE_CNT) / 60 ELSE 0 END AS DTMin
FROM grsinst1.availability_monthly_vw am JOIN
grsinst1.metric met ON met.metric_code =am.metric_code JOIN
grsinst1.restree_resgrp_client_vw res ON res.res_id =am.res_id AND res.res_grp_id = am.res_grp_id LEFT JOIN
grsinst1.threshold thresh ON thresh.res_code =res.res_code AND thresh.metric_id = met.metric_id AND thresh.res_type =res.res_type JOIN
grsinst1.grp_threshold_vw grpthresh ON
grpthresh.res_grp_id = res.res_grp_id AND grpthresh.metric_id = met.metric_id
WHERE res.client_code LIKE ? AND DATE (start_lcl_dt) >= ? AND DATE
(start_lcl_dt) <= ? AND res.res_code LIKE ? AND group_path LIKE ? AND
res.res_type = ?
GROUP BY res_name, start_lcl_dt, res_grp_name, res.res_type, value,
value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
grpthresh.threshd_target),
coalesce(thresh.threshd_warning, grpthresh.threshd_warning),
coalesce(thresh.threshd_critical, grpthresh.threshd_critical)
with ur
Dataset 2 used to have metric_code in the WHERE clause - however ... I
needed to make metric code dynamic so I took it out and put it in its own
dataset. If I have in the where clause "where metric_code = ? ... and "
then I get an error saying there are too many parameters for Dataset 2. I
have metric_code (the parameter) mapped to the query for dataset 1 on the
report parameters tab. What am I missing from what you said?
"Bruce L-C [MVP]" wrote:
> In the dataset click on the ... go to the parameters tab. Map the query
> parameter to the parameter that the cascading parameter is based on.
> In layout tab go to Report Menu -> Report Parameters. Make sure the
> parameter the second dataset is dependent on is before the depenent
> parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
> >I am using DB2 ... how do I tie the parameter from one dataset to another.
> >I
> > have a query for dataset 1 based on metrics. The metric they choose
> > needs
> > to be a parameter in the second dataset query I have. I have created a
> > parameter for the metric and have it tied to a query (in the report
> > parameters window) ... but if I put metric (which of course has to be a ?
> > for
> > unnamed parameters) as a parameter in the second dataset then I get an
> > error.
> > How does reporting services know that the metric is a parameter to the
> > second dataset?
>
>|||Is the user supposed to pick the metric? If so, then the first dataset is
being used as the source for the first parameter. Then everything else I
said holds true. If not, then you should do remove dataset 1 and change the
second one to this:
select ... where ... metric_code in (select distinct metric_code from ...)
Note that you only want one field being returned if you are using an IN
clause.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:2254419F-D76F-4161-BA4D-FD6FFE7316B0@.microsoft.com...
> Bruce,
> I'm not sure if I was clear in my post. The first dataset does not have
> a
> parameter but the selection from that result set needs to BE the parameter
> for the second dataset
> dataset 1:
> select distinct metric_code, metric_name
> from grsinst1.metric met
> join grsinst1.availability_def availdef
> on availdef.derived_metric_code = met.metric_code with ur
> dataset 2:
> SELECT res_name, start_lcl_dt, res_grp_name, res.res_type, value AS
> Percent, value_cnt, metric_name, client_name,
> coalesce(thresh.threshd_target,
> grpthresh.threshd_target) AS threshd_target,
> coalesce(thresh.threshd_warning, grpthresh.threshd_warning) AS
> threshd_warning,
> coalesce(thresh.threshd_critical,
> grpthresh.threshd_critical) AS threshd_critical, MAX(start_lcl_dt) OVER()
> AS
> MaxDate,
> CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
> THEN(((100.0 - VALUE) / 100) * VALUE_CNT) / 60 ELSE 0 END AS DTMin
> FROM grsinst1.availability_monthly_vw am JOIN
> grsinst1.metric met ON met.metric_code => am.metric_code JOIN
> grsinst1.restree_resgrp_client_vw res ON res.res_id => am.res_id AND res.res_grp_id = am.res_grp_id LEFT JOIN
> grsinst1.threshold thresh ON thresh.res_code => res.res_code AND thresh.metric_id = met.metric_id AND thresh.res_type => res.res_type JOIN
> grsinst1.grp_threshold_vw grpthresh ON
> grpthresh.res_grp_id = res.res_grp_id AND grpthresh.metric_id => met.metric_id
> WHERE res.client_code LIKE ? AND DATE (start_lcl_dt) >= ? AND DATE
> (start_lcl_dt) <= ? AND res.res_code LIKE ? AND group_path LIKE ? AND
> res.res_type = ?
> GROUP BY res_name, start_lcl_dt, res_grp_name, res.res_type, value,
> value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
> grpthresh.threshd_target),
> coalesce(thresh.threshd_warning, grpthresh.threshd_warning),
> coalesce(thresh.threshd_critical, grpthresh.threshd_critical)
> with ur
>
> Dataset 2 used to have metric_code in the WHERE clause - however ... I
> needed to make metric code dynamic so I took it out and put it in its own
> dataset. If I have in the where clause "where metric_code = ? ... and
> "
> then I get an error saying there are too many parameters for Dataset 2.
> I
> have metric_code (the parameter) mapped to the query for dataset 1 on the
> report parameters tab. What am I missing from what you said?
> "Bruce L-C [MVP]" wrote:
>> In the dataset click on the ... go to the parameters tab. Map the query
>> parameter to the parameter that the cascading parameter is based on.
>> In layout tab go to Report Menu -> Report Parameters. Make sure the
>> parameter the second dataset is dependent on is before the depenent
>> parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
>> >I am using DB2 ... how do I tie the parameter from one dataset to
>> >another.
>> >I
>> > have a query for dataset 1 based on metrics. The metric they choose
>> > needs
>> > to be a parameter in the second dataset query I have. I have created a
>> > parameter for the metric and have it tied to a query (in the report
>> > parameters window) ... but if I put metric (which of course has to be a
>> > ?
>> > for
>> > unnamed parameters) as a parameter in the second dataset then I get an
>> > error.
>> > How does reporting services know that the metric is a parameter to the
>> > second dataset?
>>|||Bruce,
I am a little confused but I believe I have it correct. Dataset 1 does
not have a parameter - it is a query that is providing the selections for a
parameter (metric_code) Dataset 2 has a query with a where clause that
includes Where metric_code like ? and I want the ? to be the selection
that was made from the choices provided from Dataset 1. In Dataset 2 - on
the parameters tab for the dataset, I have the ? representing metric_code
tied to Parameters!metric_code. On the report parameters tab, I have the
parameter metric_code tied to Dataset 1 for it's available values. So I
believe that I am running the query for Dataset 2 with whatever has been
supplied as the choice from Dataset 1. I put the parameter back in the WHERE
clause for the Dataset 2 query and I didnt get the error I was getting before
... about too many parameters for Dataset 2 ... so I hope that I have this
correct now.
"Bruce L-C [MVP]" wrote:
> Is the user supposed to pick the metric? If so, then the first dataset is
> being used as the source for the first parameter. Then everything else I
> said holds true. If not, then you should do remove dataset 1 and change the
> second one to this:
> select ... where ... metric_code in (select distinct metric_code from ...)
> Note that you only want one field being returned if you are using an IN
> clause.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:2254419F-D76F-4161-BA4D-FD6FFE7316B0@.microsoft.com...
> > Bruce,
> > I'm not sure if I was clear in my post. The first dataset does not have
> > a
> > parameter but the selection from that result set needs to BE the parameter
> > for the second dataset
> >
> > dataset 1:
> > select distinct metric_code, metric_name
> > from grsinst1.metric met
> > join grsinst1.availability_def availdef
> > on availdef.derived_metric_code = met.metric_code with ur
> >
> > dataset 2:
> >
> > SELECT res_name, start_lcl_dt, res_grp_name, res.res_type, value AS
> > Percent, value_cnt, metric_name, client_name,
> > coalesce(thresh.threshd_target,
> > grpthresh.threshd_target) AS threshd_target,
> > coalesce(thresh.threshd_warning, grpthresh.threshd_warning) AS
> > threshd_warning,
> > coalesce(thresh.threshd_critical,
> > grpthresh.threshd_critical) AS threshd_critical, MAX(start_lcl_dt) OVER()
> > AS
> > MaxDate,
> > CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
> > THEN(((100.0 - VALUE) / 100) * VALUE_CNT) / 60 ELSE 0 END AS DTMin
> > FROM grsinst1.availability_monthly_vw am JOIN
> > grsinst1.metric met ON met.metric_code => > am.metric_code JOIN
> > grsinst1.restree_resgrp_client_vw res ON res.res_id => > am.res_id AND res.res_grp_id = am.res_grp_id LEFT JOIN
> > grsinst1.threshold thresh ON thresh.res_code => > res.res_code AND thresh.metric_id = met.metric_id AND thresh.res_type => > res.res_type JOIN
> > grsinst1.grp_threshold_vw grpthresh ON
> > grpthresh.res_grp_id = res.res_grp_id AND grpthresh.metric_id => > met.metric_id
> > WHERE res.client_code LIKE ? AND DATE (start_lcl_dt) >= ? AND DATE
> > (start_lcl_dt) <= ? AND res.res_code LIKE ? AND group_path LIKE ? AND
> > res.res_type = ?
> > GROUP BY res_name, start_lcl_dt, res_grp_name, res.res_type, value,
> > value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
> > grpthresh.threshd_target),
> > coalesce(thresh.threshd_warning, grpthresh.threshd_warning),
> > coalesce(thresh.threshd_critical, grpthresh.threshd_critical)
> > with ur
> >
> >
> > Dataset 2 used to have metric_code in the WHERE clause - however ... I
> > needed to make metric code dynamic so I took it out and put it in its own
> > dataset. If I have in the where clause "where metric_code = ? ... and
> > "
> > then I get an error saying there are too many parameters for Dataset 2.
> > I
> > have metric_code (the parameter) mapped to the query for dataset 1 on the
> > report parameters tab. What am I missing from what you said?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> In the dataset click on the ... go to the parameters tab. Map the query
> >> parameter to the parameter that the cascading parameter is based on.
> >>
> >> In layout tab go to Report Menu -> Report Parameters. Make sure the
> >> parameter the second dataset is dependent on is before the depenent
> >> parameter.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
> >> >I am using DB2 ... how do I tie the parameter from one dataset to
> >> >another.
> >> >I
> >> > have a query for dataset 1 based on metrics. The metric they choose
> >> > needs
> >> > to be a parameter in the second dataset query I have. I have created a
> >> > parameter for the metric and have it tied to a query (in the report
> >> > parameters window) ... but if I put metric (which of course has to be a
> >> > ?
> >> > for
> >> > unnamed parameters) as a parameter in the second dataset then I get an
> >> > error.
> >> > How does reporting services know that the metric is a parameter to the
> >> > second dataset?
> >>
> >>
> >>
>
>|||I guess what confused me about what you wrote is this "make sure the
parameter the second dataset is dependent on is before the dependent
parameter" ? there is only one parameter shared between the 2 datasets and
it is the metric_code. The first dataset does not have a parameter ... it is
just a query to supply values FOR a parameter (metric_code) . The second
dataset has a parameter called metric_code which is a *value* chosen from the
first dataset. Therefore ... I have metric_code as the first parameter in
the list of parameters on the report parameter window ... that is followed by
client, start_date, end_date ... etc etc which are all parameters for the
second datatset. So if you meant to say "make sure the parameter the second
dataset is dependent on appears at the top of the parameter list" then that
makes sense. If you are implying that I somehow need 2 parameters for
metric_code then I guess I dont understand. Thanks, Bruce for helping with
this.
"Bruce L-C [MVP]" wrote:
> In the dataset click on the ... go to the parameters tab. Map the query
> parameter to the parameter that the cascading parameter is based on.
> In layout tab go to Report Menu -> Report Parameters. Make sure the
> parameter the second dataset is dependent on is before the depenent
> parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
> >I am using DB2 ... how do I tie the parameter from one dataset to another.
> >I
> > have a query for dataset 1 based on metrics. The metric they choose
> > needs
> > to be a parameter in the second dataset query I have. I have created a
> > parameter for the metric and have it tied to a query (in the report
> > parameters window) ... but if I put metric (which of course has to be a ?
> > for
> > unnamed parameters) as a parameter in the second dataset then I get an
> > error.
> > How does reporting services know that the metric is a parameter to the
> > second dataset?
>
>

capture sql output parameters

When I use a stored procedure for a dataset, is there anyway to capture
output parameter values when the stored procedure executes?
--
Thanks,
CGWSorry, the current limitation is one recordset. The Microsoft folks may
clarify future plans to support multiple recordsets or output parameters.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:96E5024E-A545-4D13-A49D-79ECAC33AD36@.microsoft.com...
> When I use a stored procedure for a dataset, is there anyway to capture
> output parameter values when the stored procedure executes?
> --
> Thanks,
> CGW

Can''t use a Dataset in my script component...reference required...whats going on?

Hi

Finding this forum really useful...I wonder if you could help me with this.

I've got a very simple script component and I just want to use a Dataset in it.

When I declare the Dataset i get a warning and then consequently an error. herers an image grab of whats happening:

http://www5.webng.com/hopelist/error.jpg

If you can't see the image please let me know.

Essentially VS gives me a hint to add a required assembly which it needs....but when I click to add I get a 'Visaul Basic Compiler has encountered a problem and needs to close' type error.

Anyone got any idea whats going on and why I'm having such a hard time just accessing a Dataset?

thanks

Andy

Well, you do need to add a reference to the System.XML assembly, because datasets have a reference to it. Not sure why the environment is crashing, though. If you are using the add option on the error dialog, you might try adding the reference by using the project explorer, and going to the references folder, or Project..Add Reference from the menu bar.

Cant update values

Hi Guys

I already searched for one day and didn't find any solution. Unfortunately I can't update my dataset. It works when I remove the string "WHERE ID = ?" in the UpdateCommand but then it updates all the records. I think it must be something obvious which I just don't see:(
There are two tables; tblArtists and tblCountries which are connected together with a Inner Join. Now, I need the ID from the table Artists to update the dataset. How can I get it?

Here the code:
...
SelectCommand="SELECT tblArtists.ID AS IDArtists, tblArtists.ArtistName, tblArtists.FirstName, tblArtists.LastName, tblArtists.Address, tblArtists.PLZ, tblArtists.City, tblArtists.Region, tblArtists.Country_ID, tblArtists.MusicStyles, tblArtists.HomeDJ, tblArtists.Active, tblArtists.CDate, tblArtists.Description, tblCountries.ID AS IDCountries, tblCountries.Country, tblArtists.Producer, tblArtists.Picture, tblArtists.Homepage, tblArtists.Phonenumber, tblArtists.eMail FROM (tblCountries INNER JOIN tblArtists ON tblCountries.ID = tblArtists.Country_ID) WHERE (tblArtists.Active = ?) AND (tblArtists.HomeDJ = ?) AND (tblArtists.Producer = ?)"
UpdateCommand="UPDATE tblArtists SET ArtistName = ?, FirstName = ?, LastName = ?, Address = ?, PLZ = ?, City = ?, Region = ?, Country_ID = ?, MusicStyles = ?, HomeDJ = ?, Active = ?, Description = ?, Producer = ?, Picture = ?, Homepage = ?, Phonenumber = ?, eMail = ? WHERE ID = ?">
<SelectParameters>
<asp:ControlParameter ControlID="activeRadioButton" Name="Active" PropertyName="Checked" Type="Boolean"/>
<asp:ControlParameter ControlID="homedjRadioButton" Name="HomeDJ" PropertyName="Checked" Type="Boolean"/>
<asp:ControlParameter ControlID="producerRadioButton" Name="Producer" PropertyName="Checked" Type="Boolean"/>
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ArtistName" Type="String" Size="254" />
<asp:Parameter Name="FirstName" Type="String" Size="254" />
<asp:Parameter Name="LastName" Type="String" Size="254" />
<asp:Parameter Name="Address" Type="String" Size="254" />
<asp:Parameter Name="PLZ" Type="String" Size="254" />
<asp:Parameter Name="City" Type="String" Size="254" />
<asp:Parameter Name="Region" Type="String" Size="254" />
<asp:Parameter Name="Country_ID" />
<asp:Parameter Name="MusicStyles" Type="String" Size="254" />
<asp:Parameter Name="HomeDJ" Type="Boolean" />
<asp:Parameter Name="Active" Type="Boolean" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Producer" Type="Boolean" />
<asp:Parameter Name="Picture" Type="String" Size="254" />
<asp:Parameter Name="Homepage" Type="String" Size="254" />
<asp:Parameter Name="Phonenumber" Type="String" Size="50" />
<asp:Parameter Name="eMail" Type="String" Size="50" />
<asp:Parameter Name="original_IDArtists" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

Greetings
Pat

Hi ya,

is it used in the context of GridVIew? If so did u set the DataKeyNames of the GridView ?

<asp:gridview runat="server" id="GridView1" datasourceid="ArtistsSource"
 AutoGenerateColumns="false"
 DataKeyNames="original_IDArtists" AutoGenerateEditButton="true">
 <columns>
 <asp:boundfield datafield="ArtistName" headertext="Artist Name" />
 <asp:boundfield datafield="FirstName" headertext="First Name" />

.......................
 </columns>
</asp:gridview

Hope this will help,

Yani