Is it possible to use one dataset to reference fields from another dataset
for Reporting Services?
Here's the example for ASP:
Dataset 1:
SELECT CUST_ID, EMP_ID, SALES_ID
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '3232'
ORDER BY CUST_ID
The second query will display the Sales_Amount base on the object from the
first query.
Dataset 2:
SELECT TOP 1 SALES_AMOUNT
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
AND SALES.ID = '"&SALES_ID&"'
I have a number of reports, migrating from ASP to Reporting Services, and
was wondering if this is possible with RS.
Use the first dataset as is and second dataset. I was thinking about
referencing it like this:
=IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
"Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
Fields!Sales_Amount.Value, "N/A")
Intended output:
CustID EmpID SalesID SalesAmount
1 2 3232 $345.00
2 2 3643 $223.00
3 6 8772 $1234.54
4 6 N/A
So Dataset 2 will check the fields and see if there's a match, if it does
then output Sales_Amount base on the output fields. Please advise on what to
do or suggestions.
Thanks.You can not do it the way you are envisioning it but it can be done. The
reason you can't do it the way you are thinking is because RS does not joing
datasets. You can have multiple datasets but they are independent of one
another. What you can do is have two reports. A main and a subreport (a
subreport is a normal report with a parameter). In your case the subreport
would have three parameters. Design the first report, then add a column to
the right of the ones you have (right mouse click, add column). Drop your
subreport into the column. Set the parameters to the fields (right mouse
click on the subreport to do this).
So, to do this make sure you understand report parameters, query parameters,
subreports. It will all fall together for you once you understand those
three things.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> Is it possible to use one dataset to reference fields from another dataset
> for Reporting Services?
> Here's the example for ASP:
> Dataset 1:
> SELECT CUST_ID, EMP_ID, SALES_ID
> FROM CUSTOMER, EMPLOYEE, SALES
> WHERE CUSTOMER.CUST_ID = '3232'
> ORDER BY CUST_ID
> The second query will display the Sales_Amount base on the object from the
> first query.
> Dataset 2:
> SELECT TOP 1 SALES_AMOUNT
> FROM CUSTOMER, EMPLOYEE, SALES
> WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> AND SALES.ID = '"&SALES_ID&"'
> I have a number of reports, migrating from ASP to Reporting Services, and
> was wondering if this is possible with RS.
> Use the first dataset as is and second dataset. I was thinking about
> referencing it like this:
> =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
> "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> Fields!Sales_Amount.Value, "N/A")
> Intended output:
> CustID EmpID SalesID SalesAmount
> 1 2 3232 $345.00
> 2 2 3643 $223.00
> 3 6 8772 $1234.54
> 4 6 N/A
>
> So Dataset 2 will check the fields and see if there's a match, if it does
> then output Sales_Amount base on the output fields. Please advise on what
to
> do or suggestions.
> Thanks.|||Thank you Bruce. I think what you suggested might be what I need. I've been
pondering this for several weeks now and can't seem to get it to work.
For the subreport, do I need to create another report for that or just the
second dataset?
"Bruce L-C [MVP]" wrote:
> You can not do it the way you are envisioning it but it can be done. The
> reason you can't do it the way you are thinking is because RS does not joing
> datasets. You can have multiple datasets but they are independent of one
> another. What you can do is have two reports. A main and a subreport (a
> subreport is a normal report with a parameter). In your case the subreport
> would have three parameters. Design the first report, then add a column to
> the right of the ones you have (right mouse click, add column). Drop your
> subreport into the column. Set the parameters to the fields (right mouse
> click on the subreport to do this).
> So, to do this make sure you understand report parameters, query parameters,
> subreports. It will all fall together for you once you understand those
> three things.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > Is it possible to use one dataset to reference fields from another dataset
> > for Reporting Services?
> >
> > Here's the example for ASP:
> >
> > Dataset 1:
> >
> > SELECT CUST_ID, EMP_ID, SALES_ID
> > FROM CUSTOMER, EMPLOYEE, SALES
> > WHERE CUSTOMER.CUST_ID = '3232'
> > ORDER BY CUST_ID
> >
> > The second query will display the Sales_Amount base on the object from the
> > first query.
> >
> > Dataset 2:
> > SELECT TOP 1 SALES_AMOUNT
> > FROM CUSTOMER, EMPLOYEE, SALES
> > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > AND SALES.ID = '"&SALES_ID&"'
> >
> > I have a number of reports, migrating from ASP to Reporting Services, and
> > was wondering if this is possible with RS.
> >
> > Use the first dataset as is and second dataset. I was thinking about
> > referencing it like this:
> >
> > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
> > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > Fields!Sales_Amount.Value, "N/A")
> >
> > Intended output:
> > CustID EmpID SalesID SalesAmount
> > 1 2 3232 $345.00
> > 2 2 3643 $223.00
> > 3 6 8772 $1234.54
> > 4 6 N/A
> >
> >
> > So Dataset 2 will check the fields and see if there's a match, if it does
> > then output Sales_Amount base on the output fields. Please advise on what
> to
> > do or suggestions.
> >
> > Thanks.
>
>|||A subreport is just a standard report. Create it by itself and test it out.
Since you want to embed the subreport you should make it very simple. Then
you just drag and drop it into an empty column in a table. Then right click
on it and set the parameters. So, the second dataset is only in the
subreport. You main report only has the one dataset. In certain cases
subreports are really the only way to solve the problem and it can be very
clean way to do so.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7D5B2A51-394C-4542-8B1A-B47D11BD4305@.microsoft.com...
> Thank you Bruce. I think what you suggested might be what I need. I've
been
> pondering this for several weeks now and can't seem to get it to work.
> For the subreport, do I need to create another report for that or just the
> second dataset?
> "Bruce L-C [MVP]" wrote:
> > You can not do it the way you are envisioning it but it can be done. The
> > reason you can't do it the way you are thinking is because RS does not
joing
> > datasets. You can have multiple datasets but they are independent of one
> > another. What you can do is have two reports. A main and a subreport (a
> > subreport is a normal report with a parameter). In your case the
subreport
> > would have three parameters. Design the first report, then add a column
to
> > the right of the ones you have (right mouse click, add column). Drop
your
> > subreport into the column. Set the parameters to the fields (right mouse
> > click on the subreport to do this).
> >
> > So, to do this make sure you understand report parameters, query
parameters,
> > subreports. It will all fall together for you once you understand those
> > three things.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > > Is it possible to use one dataset to reference fields from another
dataset
> > > for Reporting Services?
> > >
> > > Here's the example for ASP:
> > >
> > > Dataset 1:
> > >
> > > SELECT CUST_ID, EMP_ID, SALES_ID
> > > FROM CUSTOMER, EMPLOYEE, SALES
> > > WHERE CUSTOMER.CUST_ID = '3232'
> > > ORDER BY CUST_ID
> > >
> > > The second query will display the Sales_Amount base on the object from
the
> > > first query.
> > >
> > > Dataset 2:
> > > SELECT TOP 1 SALES_AMOUNT
> > > FROM CUSTOMER, EMPLOYEE, SALES
> > > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > > AND SALES.ID = '"&SALES_ID&"'
> > >
> > > I have a number of reports, migrating from ASP to Reporting Services,
and
> > > was wondering if this is possible with RS.
> > >
> > > Use the first dataset as is and second dataset. I was thinking about
> > > referencing it like this:
> > >
> > > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value
&
> > > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > > Fields!Sales_Amount.Value, "N/A")
> > >
> > > Intended output:
> > > CustID EmpID SalesID SalesAmount
> > > 1 2 3232 $345.00
> > > 2 2 3643 $223.00
> > > 3 6 8772 $1234.54
> > > 4 6 N/A
> > >
> > >
> > > So Dataset 2 will check the fields and see if there's a match, if it
does
> > > then output Sales_Amount base on the output fields. Please advise on
what
> > to
> > > do or suggestions.
> > >
> > > Thanks.
> >
> >
> >|||Thank you Bruce. That was most helpful. I was able to get it working.
Should've post it here weeks ago and got this resolve, but I was trying to
work on my own. This only took me 30 minute to get it working instead of
what took me 2 weeks.
Thanks again.
"Bruce L-C [MVP]" wrote:
> A subreport is just a standard report. Create it by itself and test it out.
> Since you want to embed the subreport you should make it very simple. Then
> you just drag and drop it into an empty column in a table. Then right click
> on it and set the parameters. So, the second dataset is only in the
> subreport. You main report only has the one dataset. In certain cases
> subreports are really the only way to solve the problem and it can be very
> clean way to do so.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:7D5B2A51-394C-4542-8B1A-B47D11BD4305@.microsoft.com...
> > Thank you Bruce. I think what you suggested might be what I need. I've
> been
> > pondering this for several weeks now and can't seem to get it to work.
> >
> > For the subreport, do I need to create another report for that or just the
> > second dataset?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You can not do it the way you are envisioning it but it can be done. The
> > > reason you can't do it the way you are thinking is because RS does not
> joing
> > > datasets. You can have multiple datasets but they are independent of one
> > > another. What you can do is have two reports. A main and a subreport (a
> > > subreport is a normal report with a parameter). In your case the
> subreport
> > > would have three parameters. Design the first report, then add a column
> to
> > > the right of the ones you have (right mouse click, add column). Drop
> your
> > > subreport into the column. Set the parameters to the fields (right mouse
> > > click on the subreport to do this).
> > >
> > > So, to do this make sure you understand report parameters, query
> parameters,
> > > subreports. It will all fall together for you once you understand those
> > > three things.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > > > Is it possible to use one dataset to reference fields from another
> dataset
> > > > for Reporting Services?
> > > >
> > > > Here's the example for ASP:
> > > >
> > > > Dataset 1:
> > > >
> > > > SELECT CUST_ID, EMP_ID, SALES_ID
> > > > FROM CUSTOMER, EMPLOYEE, SALES
> > > > WHERE CUSTOMER.CUST_ID = '3232'
> > > > ORDER BY CUST_ID
> > > >
> > > > The second query will display the Sales_Amount base on the object from
> the
> > > > first query.
> > > >
> > > > Dataset 2:
> > > > SELECT TOP 1 SALES_AMOUNT
> > > > FROM CUSTOMER, EMPLOYEE, SALES
> > > > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > > > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > > > AND SALES.ID = '"&SALES_ID&"'
> > > >
> > > > I have a number of reports, migrating from ASP to Reporting Services,
> and
> > > > was wondering if this is possible with RS.
> > > >
> > > > Use the first dataset as is and second dataset. I was thinking about
> > > > referencing it like this:
> > > >
> > > > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value
> &
> > > > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > > > Fields!Sales_Amount.Value, "N/A")
> > > >
> > > > Intended output:
> > > > CustID EmpID SalesID SalesAmount
> > > > 1 2 3232 $345.00
> > > > 2 2 3643 $223.00
> > > > 3 6 8772 $1234.54
> > > > 4 6 N/A
> > > >
> > > >
> > > > So Dataset 2 will check the fields and see if there's a match, if it
> does
> > > > then output Sales_Amount base on the output fields. Please advise on
> what
> > > to
> > > > do or suggestions.
> > > >
> > > > Thanks.
> > >
> > >
> > >
>
>
