SSRS Report drop down parameters. Use SP or embeded SQL query for datasource

Depending on the version of Report Builder, you can only build from a single data source. I'm pretty sure this is still the case with SQL Server 2008R2, although Report Builder 3 can consume data directly from a database without needing a report model, although you need to build data sets using the individual connections and I don't think you can group items from different data sets by a common value.

You can do this gouping with SSRS (see this article for an overview), although I can't see an obvious way to do it in RB3. This article indicates that you can't nest items based on different data sets, so it's looking like common rollups aren't possible in RB3.

I'm going to guess that you are running SSRS in Native mode rather than SharePoint integrated, and that you are using the pre-defined roles to grant access to users. If you want to post more information about the version of SQL Server you are using and the security role these users are in, I can update my answer. For now, try this.

There are pre-defined security roles in SSRS:

• Content Manager
• Publisher
• Browser
• Report Builder
• My Reports
• System Administrator
• System User

I'm going to guess that these users are currently in the My Reports Role. This role is allowed to do quite a bit, including manage data sources and manage reports. This means these users can create delete, view, and modify data sources.

You might want to consider creating a new role that has only the item-level permissions you want. You can find the list of item-level tasks here. You basically want to include all of the item-level tasks included in the My Reports role, except Manage Data Sources. You could also modify the My Reports role to remove this one item rather than creating a new role.