Generate report from list of parameters?

I have one report with two datasets, and consequently two queries. The first
one gets a list of ReportID's for a particular day, while the second dataset
uses each ReportID to create a report.
Query 1:
SELECT ReportID
FROM Table1
WHERE Date=@.Date
Query 2:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID = @.ReportID
If I try to Union '(All)' to Query1, Query2 will return info for all
ReportID's in the system, not just what was generated in Query1. I also
tried creating a comma delimited field of all the ReportID's and using WHERE
ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
Query2 while only running Query1 once?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1On Feb 28, 6:28 am, "ana9" <u28955@.uwe> wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
If I'm understanding you correctly, you should be able to do one of a
few things:
Option 1:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID in (SELECT ReportID
FROM Table1
WHERE Date=@.Date )
Option 2:
Create a cursor (or while loop) to loop through Query 1 and execute
Query 2 in the loop for each ReportID (possibly populating a single
dataset or multiple datasets).
Ofcourse Option 1 is far less performance intensive. If I'm not
understanding you correctly, let me know.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||That was very helpful, thank you. Option1 is a variation of what I had tried
before. The only issue (which I failed to mention) is that Query2 is
actually a report that gets populated from about 20 datasets. So each
dataset may not return the exact same number of rows, in which case some of
the information will not be displayed in the correct manor. Also, for some
of those datasets I use SELECT Top # so using Option1 will not allow me to
get the correct amount of results.
Your suggestion for Option2 seems like it would be perfect for this scenario,
however I was under the impression that you can't create while loops in SQL.
Could you point me in the direction of some resources/examples? Thank you.
EMartinez wrote:
>> I have one report with two datasets, and consequently two queries. The first
>> one gets a list of ReportID's for a particular day, while the second dataset
>[quoted text clipped - 18 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>If I'm understanding you correctly, you should be able to do one of a
>few things:
>Option 1:
>SELECT *
>FROM *A whole bunch of tables*
>WHERE ReportID in (SELECT ReportID
>FROM Table1
>WHERE Date=@.Date )
>Option 2:
>Create a cursor (or while loop) to loop through Query 1 and execute
>Query 2 in the loop for each ReportID (possibly populating a single
>dataset or multiple datasets).
>Ofcourse Option 1 is far less performance intensive. If I'm not
>understanding you correctly, let me know.
>Hope this is helpful.
>Regards,
>Enrique Martinez
>Sr. SQL Server Developer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1|||You might also try
SELECT ReportID INTO #Reports
FROM Table1
WHERE Date=@.Date
SELECT *
FROM *A whole bunch of tables*
inner join #Reports
On ReportID = #Reports.ReportID
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ana9" wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>