Building a MicroStrategy Site Map

I once worked on a very large projet where there were so many users and reports, that the team decided to build and maintain a Site Map.  This provided users with the ability to quickly find reports and browse what they had access to overall.  This was a manually maintained Document, and as new reports rolled out they were considered for whether or not to be added to this Document.  And if folder structures changed, then there was a lot of work for someone to do.  This was conceived in the 8x days before the Tree Folder Navigation option introduced in 9x, but it still has some usefulness for large projects.

I immediately thought that this could be automated by simply querying the metadata.  The idea being that we could use the User Login prompt and pass it to a SQL query against the metadata and return not only an automated Site Map, but one customized for what that individual user had access to.  The rollout was a success, and it made navigation a lot easier for the users.  Today, I share that script with you!

This script was written for a SQL Server metadata, but should be mostly applicable to any other platform with just some minor syntax changes:

CREATE procedure [dbo].[sp_sitemap] (@UserName as varchar(50), @Project as varchar(50)) as
/*
declare @UserName as varchar(50) --this is the user login, not description
declare @Project as varchar(50)
set @UserName = 'USER'
set @Project = 'PROJECT' */

--Build a list of Folders under Public ObjectsReports
select o.project_id, p.object_name as project_name, o.project_id as parent, p.object_name as parent_name, o.object_id as folder, cast(o.object_name as varchar(1000)) as folder_name, o.object_name as folder_name_sort
into #Folders
From dssmdobjinfo o
join dssmdobjinfo p
on o.project_id = p.object_id
join dssmdobjinfo r
on o.parent_id = r.object_id and o.project_id = r.project_id
where r.object_name = 'Public Objects' and o.object_name = 'Reports' and o.hidden = 0

declare @x as int
set @x = 0
while @@rowcount > 0
begin
insert into #Folders
select o.project_id, f.project_name as project_name, o.parent_id as parent, p.object_name as parent_name, o.object_id as folder,
'<img style="width:15.px;height:15px;" src=/microstrategy/images/icon_folder.gif>&nbsp;<a href="Main.aspx?Project=' + @Project + '&Port=0&evt=2001&src=Main.aspx.shared.fbb.fb.2001&folderID=' + o.object_id + '" target="_new">' + o.object_name + '</a>' as folder_name,
o.object_name as folder_name_sort
from dssmdobjinfo o
join #folders f
on o.project_id = f.project_id and o.parent_id = f.folder
join dssmdobjinfo p
on o.parent_id = p.object_id and o.project_id = p.project_id
where o.object_id not in(select distinct folder from #folders)
and o.object_type = 8 and o.subtype = 2048 and o.hidden = 0
if @@rowcount > 0 set @x = @x + 1
end

--groups i'm in (recursive for group hierarchy)
select i.object_id, i.object_name
into #groups
from dssmdobjdepn d
join dssmdobjinfo u
on d.object_id = u.object_id
join dssmdobjinfo i
on d.depn_objid = i.object_id
where u.abbreviation = @UserName --<<<<<<<<<<<<< User Login to check while @@rowcount > 0
begin
insert into #groups
select i.object_id, i.object_name
from #groups g
join dssmdobjdepn d
on g.object_id = d.object_id
join dssmdobjinfo i
on d.depn_objid = i.object_id
where i.object_id not in(select distinct object_id from #groups)
end

--apply security to folders
select distinct f.*
into #securedFolders
from #groups g
join dssmdobjsecu s --objects i have security for
on g.object_id = s.trust_id
join dssmdobjinfo i --objects i have security for desc
on i.object_id = s.object_id and i.project_id = s.project_id
join #folders f
on s.object_id = f.folder and s.project_id = f.project_id
where rights < 500

----final dynamic query
----uncomment if you want to use this sp in something slick like a .net app or whatever
--declare @y as int
--declare @SQL as varchar(2000)
--declare @selectSQL as varchar(500)
--declare @joinSQL as varchar(1000)
--
--set @y = 1
--set @SQL = ''
--set @selectSQL = ''
--set @joinSQL = ''
--
--while @y <= @x
--begin
-- set @selectSQL = @selectSQL + 'f' + cast(@y as varchar) + '.folder_name, '
--
--set @joinSQL = @joinSQL + 'left join #folders f' + cast(@y as varchar) + ' on f' + cast(@y -1 as varchar) + '.project_id = f' + cast(@y as varchar)
-- set @joinSQL = @joinSQL + replace(replace(' left join #folders f
-- on f.project_id = f.project_id and f.folder = f.parent', '', cast(@y as varchar)), '', cast(@y -1 as varchar))
-- set @y = @y + 1
--end
--
--set @SQL = 'select f0.project_name, ' + left(@selectSQL, len(@selectSQL)-1) + ' from #folders f0 ' + @joinSQL + ' where f0.project_id = f0.parent order by 1,2,3,4'
--print @SQL
--exec(@SQL)

--final static query
select --f0.project_name,
f1.folder_name_sort as foldersort1, f1.folder_name as foldername1,
f2.folder_name_sort as foldersort2, f2.folder_name as foldername2,
f3.folder_name_sort as foldersort3, f3.folder_name as foldername3,
f4.folder_name_sort as foldersort4, f4.folder_name as foldername4,
f5.folder_name_sort as foldersort5, f5.folder_name as foldername5,
f6.folder_name_sort as foldersort6, f6.folder_name as foldername6
from #securedFolders f0
left join #securedFolders f1
on f0.project_id = f1.project_id and f0.folder = f1.parent
left join #securedFolders f2
on f1.project_id = f2.project_id and f1.folder = f2.parent
left join #securedFolders f3
on f2.project_id = f3.project_id and f2.folder = f3.parent
left join #securedFolders f4
on f3.project_id = f4.project_id and f3.folder = f4.parent
left join #securedFolders f5
on f4.project_id = f5.project_id and f4.folder = f5.parent
left join #securedFolders f6
on f5.project_id = f6.project_id and f5.folder = f6.parent
where f0.project_id = f0.parent and f0.project_name = @Project --<<<<<<<<< Project to check order by f1.folder_name_sort,f2.folder_name_sort,f3.folder_name_sort,f4.folder_name_sort,f5.folder_name_sort,f6.folder_name_sort --clean up drop table #folders drop table #securedFolders drop table #groups

The dynamic / static bit of the query is because if you put this into a Free Form SQL Report, you have to know exactly how many columns are going to come back.  That means you need to statically set the return columns, so do some initial research to see how deep you need to go.  In this sample, it’s 7 deep.

Alternatively, if you’re going to use this outside of MicroStrategy, you could use a dynamic approach to return the true depth of the folder structure.

You may also like...