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

Capturing ODBC user name at runtime

Hi:
We are using SRS to report from a DB2 database running on AS400's. It is
working very well.
Pushing ahead, we want to capture information on when certain reports
(actually letters) are generated. The reports prompt for login credentials
at run time. These DB2 database credentials are passed to the database
server and the report returns the data just fine. Works great right out of
the box.
What we would like to do is capture the user name that is passed to the
database. This is not the Windows AD username that can be obtained by the
global field
User!UserID, but the DB2 specific user name that is passed by the ODBC
connection.
Can this be captured at runtime? We have reviewed the RS object model and
haven't found what we are looking for. Are we barking up the wrong tree?
Thanks in advance.
Bruce.
swgAre the credentials parameters?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:CC286D82-B3E9-48F4-AD5A-ACAB49A19C8B@.microsoft.com...
> Hi:
> We are using SRS to report from a DB2 database running on AS400's. It is
> working very well.
> Pushing ahead, we want to capture information on when certain reports
> (actually letters) are generated. The reports prompt for login
> credentials
> at run time. These DB2 database credentials are passed to the database
> server and the report returns the data just fine. Works great right out
> of
> the box.
> What we would like to do is capture the user name that is passed to the
> database. This is not the Windows AD username that can be obtained by the
> global field
> User!UserID, but the DB2 specific user name that is passed by the ODBC
> connection.
> Can this be captured at runtime? We have reviewed the RS object model and
> haven't found what we are looking for. Are we barking up the wrong tree?
> Thanks in advance.
> Bruce.
> swg|||Hi:
They are not parameters in the sense that they are built into the report as
parameters.
Instead of saving credentials securely on the report server, I selected the
"The credentials supplied by the user running the report." under the "Connect
Using:" portion of the datasource Properties page.
It is that username that the user enters at runtime that I need to capture.
Thanks.
Bruce.
"Bruce L-C [MVP]" wrote:
> Are the credentials parameters?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:CC286D82-B3E9-48F4-AD5A-ACAB49A19C8B@.microsoft.com...
> > Hi:
> >
> > We are using SRS to report from a DB2 database running on AS400's. It is
> > working very well.
> >
> > Pushing ahead, we want to capture information on when certain reports
> > (actually letters) are generated. The reports prompt for login
> > credentials
> > at run time. These DB2 database credentials are passed to the database
> > server and the report returns the data just fine. Works great right out
> > of
> > the box.
> >
> > What we would like to do is capture the user name that is passed to the
> > database. This is not the Windows AD username that can be obtained by the
> > global field
> > User!UserID, but the DB2 specific user name that is passed by the ODBC
> > connection.
> >
> > Can this be captured at runtime? We have reviewed the RS object model and
> > haven't found what we are looking for. Are we barking up the wrong tree?
> >
> > Thanks in advance.
> >
> > Bruce.
> >
> > swg
>
>|||Oh, I see. Sorry. I am not aware of a way to get this via built in
functionality but how about this. It depends on your database but SQL Server
has a function that returns the user:
SELECT 'The current user is: '+ convert(char(30), CURRENT_USER)
If your database has something similar then have a second dataset that uses
the same data source and get the information that way.-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:FB08FB96-06B5-4E39-A9DA-4929CFC3629E@.microsoft.com...
> Hi:
> They are not parameters in the sense that they are built into the report
> as
> parameters.
> Instead of saving credentials securely on the report server, I selected
> the
> "The credentials supplied by the user running the report." under the
> "Connect
> Using:" portion of the datasource Properties page.
> It is that username that the user enters at runtime that I need to
> capture.
> Thanks.
> Bruce.
> "Bruce L-C [MVP]" wrote:
>> Are the credentials parameters?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com>
>> wrote
>> in message news:CC286D82-B3E9-48F4-AD5A-ACAB49A19C8B@.microsoft.com...
>> > Hi:
>> >
>> > We are using SRS to report from a DB2 database running on AS400's. It
>> > is
>> > working very well.
>> >
>> > Pushing ahead, we want to capture information on when certain reports
>> > (actually letters) are generated. The reports prompt for login
>> > credentials
>> > at run time. These DB2 database credentials are passed to the database
>> > server and the report returns the data just fine. Works great right
>> > out
>> > of
>> > the box.
>> >
>> > What we would like to do is capture the user name that is passed to the
>> > database. This is not the Windows AD username that can be obtained by
>> > the
>> > global field
>> > User!UserID, but the DB2 specific user name that is passed by the ODBC
>> > connection.
>> >
>> > Can this be captured at runtime? We have reviewed the RS object model
>> > and
>> > haven't found what we are looking for. Are we barking up the wrong
>> > tree?
>> >
>> > Thanks in advance.
>> >
>> > Bruce.
>> >
>> > swg
>>