Many-to-Many data

If you have ever ran across the same problem you will now what I am talking about.

The designer gave us a report to develop that displays many to many data. The traditional approach of hierarchal data reporting just will not work. Any ideas on hwo to display this data?

Here is a quick example:

Think of Musician to Albums. A musician can have many albums, and an album can have many musicians.

The report will list grouped by musician thier albums and the total sales, but when you sum up the sales it will be overstated because an album can have multiple artists. Also, if three artists contributed to the same album, it should list the three artists, then the one album, not artist/album X3.

So... what is the best approach to displayign this type of data on a report? We are stumped.

Thanks,
Eric

I was able to solve it. The trick was two have two seperate groups for Albums, then show/hide depending on conditions.

managing users

Hello,

Has anyone ever come across a reason why someone would manually create a
user table incl. permission flags and not use the inbuilt user/roles
provided by that database? The only reason that stands out for me is to
make the database that bit more portable?

Thanks,
Craig.Craig, it would depend on exactly how the table in question is
constructed and used but some applications provide application based
security. The application might connect to the database using one ID
that is the database owner or has both datareader and datawriter but
via the application limit what end-users can do.

HTH -- Mark D Powell --|||Craig H. (spam@.thehurley.com) writes:
> Has anyone ever come across a reason why someone would manually create a
> user table incl. permission flags and not use the inbuilt user/roles
> provided by that database? The only reason that stands out for me is to
> make the database that bit more portable?

The database-level can be a bit heavy-handed. In our application, our
security scheme on SQL Server is dead simple. All users are added to
a group, and that group is granted execute access on all stored procedures
and select access on most tables.

Then our database includes tables to control access to application
functions, and also access to which accounts and customers a user may
see.

True, our system started its life in the days of 4.x when the permission
system in SQL Server was far less sophistcated than today. But since
our securable entities are not SQL Server entities, I can't see how
SQL Server could help us, even if we were to make a complete restart.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx