Maybe I can help a little bit, but I am no guru, closer to a newbie, but I
have been looking at joins and reports  for quite a while now.

1) you can do a join  between two lists  on same site (MOSS) with SPD itself
2) not sure about SQL Rpt svcs, but Ensys makes a nice add-on to SQL Rpt svcs
to do joins, therefore, I assume the OOB capability might be limited.
3) Bamboo makes a nice WEB part  for SP to do joins

Are we talking  about two separate  pieces here? server  and Services
for SQL Reporting? - Requirements are that you have to install SP2 for SQL on
your SQL server. Then you need to install reporting  Services that is a component
that is a part  of the SQL Server install and then upgrade it to SP2 as well. You
can install the Reporting Server on any server in your SharePoint Farm. And then
you will have to install SQL Services Reporting Services integration  add-in tool
on all of your WFE servers. You can get the step-by-step guide to install and
configure Reporting Services and link to down the add-in component here:
http://yasirbutt.spaces.live.com/Blog/cns!A8677D5751E6B4DA!1135.entry


In a SharePoint installation  can you run  a reporting server on the
same machine  as the SharePoint_Config database, or would this be a
different server? ::: You can install Reporting Services on any WFE server in
your Farm

If on same server, is it a side  by side installation or is it part of
the SQL DB that SharePoint uses? :: As i stated above, you can use the existing
SQL Server that you are using to host your other databases for SharePoint but
you will need to install SharePoint Reporting Server on one of the servers  in
your Farm and then install the add-in on all of your WFE Servers.

Does running  SQL Reporting Server in SharePoint integration mode
allow you to consume SharePoint lists  as data  sources, specifically
having the ability  to join  them? :: I do believe you can but i have not had to
do this yet

If you are not running SQL Reporting Server in SharePoint integration
mode, can you just use a SharePoint web  part to view  the reports  you
generate on another server? :: What other servers are you referring to? As long
as you have the add-in installed on all of your WFE servers, you should be able
to view any reports on any server.

I have been looking into it for a few days and
think that the Bamboo solution might be the least painful way to handle
doing any significant joins. Has anyone in this group had experience
with this product? If so do you have any heads up that you would like to
share?

Thank you very much for taking time  out to explain this. It was very
helpful. I very much appreciate it.