Multiple Datasets and Report Design problems

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.
How do I accomplish this?
Thanks in advance.
PamHi Pam,
I'm not sure how your databases are set up (are they on the same
server?), but I would probably choose to combine the data from the
database instead of combining it at the report level - that way you
only need 1 report table and no sub reports and grouping/toggling the
data will be a piece of cake:
SELECT * from User INNER JOIN DatabaseB.dbo.CallLogs CallLogs ON
User.username = CallLogs.username ORDER BY Department, UserName
I hope this helps.
Take Care!
Michelle

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
>

Multiple Columns

Hi,
I am attempting to design an address list which will print in multiple
columns, like so:
Company One
Address Line 1
Address Line 2
Address Line 3
Company Two
etc....
This should continue down the page and fill up two columns. However, the
second column is always empty. Am I missing something? I am using a list
control to repeat the fields, and have set the Columns property to 2. Is
there anything more I need to do to get the second column to appear?
TIA,
Peter"Steffen" <Steffen@.discussions.microsoft.com> wrote in message
news:DDD47287-4639-4D1B-AB1A-3CF361C74A03@.microsoft.com...
>I had the same problem.
> it can have 2 causes:
> 1. the sum of the width of your columns and the spacing between is more
> than
> the width of the page
> 2. you need to have a Page Header (even if you set it to zero)
> "Peter Kenyon" wrote:
>
Thanks, that solved it. The Preview window still only shows one column, but
Print Preview and PDF format show two columns.
Peter

Multiple Addresses Database Design

Howdy All,

I want to design a database that has a single table that holds addresses from several different entities. e.g

Contacts
--------
ContactID

Sponsors
--------
SponsorID

Events
--------
EventID

Now a Contact can have multiple addresses, but only one is the preferred. My initial thought is to create lookup tables for each of the tables or just make the following table design for addresses
addresses
--------
AddressID EntityID Preferred(Bit Field) Street City Zip State...

I want to maximize effeciency. Any ideas are greatly appreciated.Does an address have a separate existence from the entity joined to it?

You could have the structure

Address
AddressID, ....

EntityAddress
EntityID, PreferredAddress, AddressID

Entity
EntityID, EntityType

Sponser
EntityID, ...

Contact
EntityID, ...

Then you can show that several entities can have the same address.|||You can do the following for example

Customer ID, Customer Name
Contact ID, Customer ID (FK), Address Line 1, Address Line 2, Prefered
Contact Address ID, Contact ID (FK), ..........

This will help you to make multiple addresses for each contact and multiple contacts for each customer.

Regards,

Firas arramli|||in order to minimize maintenance tasks you could have just 3 tables:

EntityMaster (EntityID, EntityTypeID, etc.)
EntityTypes (EntityTypeID, EntityDescription) -> static table
Addresses (AddressID, EntityID, EntityTypeID, PrimaryAddressFlag, etc.)

however, you need to keep in mind that by minimizing maintenance you may be stepping on your performance. on your first post you mentioned that you're after efficiency. by having a separate address table for contacts, sponsors, and events you'll achive faster performance vs. combining them all into addresses table.

multipass query many fact tables

I am new to DW. I am required to design reports which come off of DW. I found a rpt requirement which is from 4 different fact tables, each of it at a diff grain level.

The results change dramatically. I am not joining fact tables directly, but am joining them through common dimension. I am concerned about the correctness of the recs returned. I have also tried to create a view with a facttable A and its dimension tables, and linked that to the originial facttable B which looks like the report theme(In this approach the # of recs will more or less be = to B's records.

I am not finding any examples apart from multipass query mentioned. I would like to see an example to understand. Could anyone point me in the rt direction?

Thanks

Are you working with Analysis Services or just the SQL Server relational database technology?

If you are working with the relational technology, you may need to aggregate your data to a common granularity before performing your joins. This is significantly easier to handle through SSAS.

B.

|||

I am working with SQL Server Relational db(2005).

Thanks