Multiple Datasets and Report Design confusion

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
Any ideas on how to accomplish this scenario?
Thanks in advance.If both these databases are in the same SQL Server then it is a piece of
cake to do this in a Stored Procedure, just join the two tables. If they are
in different servers it is a little more difficult, you would need to use
linked servers. Same strategy though, you need to look at creating a stored
procedure. Based on your description it does seem to me that a subreport
will not work for you.
Oh, another idea. You don't even need a stored procedure if they are on the
same server, use the generic query designer and create the sql:
select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
b.somedatefield > @.startdate and b.somedatefield < @.enddate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"pb" <pbrechlin@.hotmail.com> wrote in message
news:%23v$M9Z7AFHA.2112@.TK2MSFTNGP09.phx.gbl...
> I have a table in Database A with users and a table in Database B with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally started
> with a subreport to print the call detail for a user. Unfortunately, I
> need to be able to total and avg call detail for each user, dept which I
> believe must happen in the main report. By using a sub report I don?t
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||I believe that your best bet would be to either have some process that
creates an intermediary resultant table combining the abstract data
relationship between the two tables, or design your stored procedure so
that you are joining the two together.
As a design key I always try to leave the real work to the best of
breed. In this case you are going to be better off doing your real data
work in the database layer(SQL Server), not in the application layer
(Reporting Services).
-Brian
pb wrote:
> I have a table in Database A with users and a table in Database B
with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally
started
> with a subreport to print the call detail for a user. Unfortunately,
I
> need to be able to total and avg call detail for each user, dept
which I
> believe must happen in the main report. By using a sub report I
don't
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||THanks Brian, Bruce and Michelle. I have chosen to link my servers (data
on two different servers). I created some views and stored procedures.
All is woking great. Thanks for the direction!
Bruce L-C [MVP] wrote:
> If both these databases are in the same SQL Server then it is a piece of
> cake to do this in a Stored Procedure, just join the two tables. If they are
> in different servers it is a little more difficult, you would need to use
> linked servers. Same strategy though, you need to look at creating a stored
> procedure. Based on your description it does seem to me that a subreport
> will not work for you.
> Oh, another idea. You don't even need a stored procedure if they are on the
> same server, use the generic query designer and create the sql:
> select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
> innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
> b.somedatefield > @.startdate and b.somedatefield < @.enddate
>