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?
>
>