Unfortunately, I have not found an easy way of doing this. I can give you the way I am currently using, although I wish there was somethng more simple.
Here we go:
Create a report that will be used to populate your drop-down list. In my case, I have created my own Org. View with many Org. Groups below it. I only want the Org. Groups below my Org. View that have resources.
declare @parentGuid uniqueidentifier;
set @parentGuid = N'af717479-ad45-49f8-a0f5-6c4527a8d541';--Your Top Level
declare @folders table (
ScopeCollectionGuid uniqueidentifier,
ParentScopeCollectionGuid uniqueidentifier,
BaseGuid uniqueidentifier
)
declare @OrganizationalGroup table (
ScopeCollectionGuid uniqueidentifier,
OrganizationalGroup nvarchar(1000)
)
insert @folders
select distinct ScopeCollectionGuid = fbf.[FolderGuid],
ParentScopeCollectionGuid = f.[ParentFolderGuid],
BaseGuid = ISNULL(ipf.BaseGuid, fbf.FolderGuid)
from FolderBaseFolder fbf
inner join ItemFolder f
on fbf.FolderGuid = f.ItemGuid
left join ItemPresentation ipf
on ipf.Guid = fbf.FolderGuid
where fbf.ParentFolderGuid = @parentGuid
and f.[IsFolder] = 1;
with Hierarchy
as (
select f.ScopeCollectionGuid,
f.ParentScopeCollectionGuid,
f.BaseGuid,
CAST(s.String as nvarchar(1000)) [String]
from @folders f
inner join String s
on s.BaseGuid = f.BaseGuid
and s.StringRef = 'item.name'
and s.Culture = ''
where f.BaseGuid = @parentGuid
union all
select f.ScopeCollectionGuid,
f.ParentScopeCollectionGuid,
f.BaseGuid,
CAST(h.String + ' > ' + s.String as nvarchar(1000)) [String]
from @folders f
inner join String s
on s.BaseGuid = f.BaseGuid
and s.StringRef = 'item.name'
and s.Culture = ''
inner join Hierarchy h
on f.ParentScopeCollectionGuid = h.ScopeCollectionGuid
)
insert @OrganizationalGroup
select h.ScopeCollectionGuid,
h.String
from Hierarchy h
select distinct og.ScopeCollectionGuid,
substring(og.OrganizationalGroup, CHARINDEX('>', og.OrganizationalGroup) + 2, 250) 'OrganizationalGroup'
from vItem vi
inner join ScopeMembership sm
on sm.ResourceGuid = vi.Guid
inner join @OrganizationalGroup og
on og.ScopeCollectionGuid = sm.ScopeCollectionGuid
order by 2
...Now that we have this, we can create our actual report. Go to the Report Paramters tab and add a Basic GUID Parameter. The Value Provider is going to be 'Dropdown List Value Edit Control' and we are going to select our report we previously created as shown below:
Dont forget to add the parameter to your SQL query and enjoy!!