Landing Page Part 2: Dynamic User Report Links

Following up on the post Creating a Portal Style Landing Page, you may want to add a little bit of dynamic flair to the page.  While you may or may not agree with the case I built for the advantages of Static / Manually maintained links, I think dynamic content can live side by side with it.  Today, I’ll discuss adding two panels to the Landing Page that show the Top 5 Reports for the user, as well as the Last 5 Reports the user has run.  These will provide some quick access links for a user to access and have a personal touch to their BI experience.
The data for these reports will be coming from MicroStrategy Enterprise Manager.  You could build objects on top of the EM schema to build these, but I chose to just write the queries by hand and go with Freeform SQL reports since these are single use.  Depending on the size of your EM tables, the power of your Statistics database, and whether or not you purge, you may want to do some careful testing.  Since two queries must return before a user sees the welcome page, you’ll want to make sure they load nearly instantly.

Last 5 Reports

select top 5 ID, URL, LastRun
from (
select r.IS_REP_ID as ID, '<a href=Main.aspx?evt=4001&src=Main.aspx.4001&visMode=0&reportID=' + is_rep_guid + '&reportViewMode=1>' + dbo.CamelCase(r.is_rep_name) + '</a>' as URL, max(day_id) as LastRun
from is_report_stats f
join em_user u
on f.USERID = u.em_user_guid
join IS_PROJ p
on f.PROJECTID = p.IS_PROJ_GUID
join IS_REP r
on f.REPORTID = r.IS_REP_GUID and p.IS_PROJ_ID = r.IS_PROJ_ID
where u.EM_USER_ABBREV = 'USER LOGIN PROMPT'
and PARENTJOBID = -1
and p.IS_PROJ_ID = 2
and SCHEDULEINDICATOR = 0
and REPORTTYPE = 1
and r.IS_REP_ID > 0 and r.IS_REP_ID not in(2, 12, 13, 1639)
group by r.is_rep_id, r.IS_REP_GUID, r.IS_REP_NAME
union
select d.IS_DOC_ID, '<a href=Main.aspx?evt=2048001&src=Main.aspx.2048001&visMode=0&documentID=' + is_doc_guid + '&currentViewMedia=2>' + dbo.CamelCase(d.is_doc_name) + '</a>' as URL, MAX(DAY_ID) as LastRun
from IS_DOCUMENT_STATS f
join em_user u
on f.USERID = u.em_user_guid
join IS_PROJ p
on f.PROJECTID = p.IS_PROJ_GUID
join IS_DOC d
on f.DOCUMENTID = d.IS_DOC_GUID and p.IS_PROJ_ID = d.IS_PROJ_ID
where u.EM_USER_ABBREV = 'USER LOGIN PROMPT'
and p.IS_PROJ_ID = 2
group by d.IS_DOC_ID, is_doc_guid, d.IS_DOC_NAME
) r
order by lastrun desc

Top 5 Reports

select top 5 ID, URL, Hits
from (
select r.is_rep_id as ID, '<a href=Main.aspx?evt=4001&src=Main.aspx.4001&visMode=0&reportID=' + is_rep_guid + '&reportViewMode=1>' + dbo.CamelCase(r.is_rep_name) + '</a>' as URL, COUNT(*) as Hits
from IS_REP_FACT f
join IS_REP r
on f.IS_REP_ID = r.IS_REP_ID
join EM_USER u
on f.EM_USER_ID = u.EM_USER_ID
where EM_USER_ABBREV = 'USER LOGIN PROMPT'
and EM_RECORD_TS between GETDATE()-60 and GETDATE()
and r.IS_REP_ID > 0 and r.IS_REP_ID not in(2, 12, 13, 1639)
and IS_REPCTYPE_IND <> 7
and f.IS_PROJ_ID = 2
and IS_DOC_JOB_ID = -1 --Reports Only, not Doc data sets
and IS_SCHED_ID = -1 --no subscriptions
and r.EM_EXISTS_ID = 1
group by r.IS_REP_NAME, r.is_rep_id, r.IS_REP_GUID
union
select d.IS_DOC_ID, '<a href=Main.aspx?evt=2048001&src=Main.aspx.2048001&visMode=0&documentID=' + is_doc_guid + '&currentViewMedia=2>' + dbo.CamelCase(d.is_doc_name) + '</a>' as URL, COUNT(*) as Hits
from is_doc_fact f
join IS_DOC d
on f.IS_DOC_ID = d.IS_DOC_ID
join EM_USER u
on f.EM_USER_ID = u.EM_USER_ID
where EM_USER_ABBREV = 'USER LOGIN PROMPT'
and EM_RECORD_TS between GETDATE()-60 and GETDATE()
and d.IS_DOC_ID > 0
and d.EM_EXISTS_ID = 1
and f.IS_PROJ_ID = 2
and IS_SCHED_ID = -1 --no subscriptions
group by d.IS_DOC_ID, d.IS_DOC_NAME, d.IS_DOC_GUID
) r
order by Hits desc

Report Notes
  1. When you paste this SQL into a Freeform SQL Report, you’ll want to replace the parts that say “USER LOGIN PROMPT” with the User Login Prompt, under PromptsSystem Prompts.  This will automatically pass the User ID to the query and give the user the customized results.
  2. IS_PROJ_ID should be the project you want to run this for.  This is the Enterprise Manager Project ID, not the Project GUID.  It’ll be a single digit number, unless you have more than 10 projects.  You can find it in the IS_PROJ table.
  3. I’ve tried to filter out as much “junk” as I could.  For example, I don’t want to show Subscriptions, or Document Data Sets, or Ad-hoc reports.  I also specifically exclude <DELETE REPORT> and Custom Report Templates I’ve created.  You can tweak your own list of excluded reports and put them in the IS_REP_ID exclusion filter.  You can find those IDs in the table IS_REP.
  4. When you map the Freeform attributes to these reports, make sure to set the type of the Attribute Form to HTML so that the report will resolve to a link the user can click on in the Landing Page.
  5. Some of the reports that come back (at least for me) were in all caps for some reason.  To resolve this, I wanted to convert them to Proper Case aka Camel Case.  I found this blog post that contained a very useful function.
  6. For performance, you can convert these reports into Intelligent Cubes and schedule them to refresh at whatever frequency you find appropriate.  Simply remove the user filters from them and add EM_USER_ABBREV to the select and group bys.  Then, right click the Cube and choose Create Report, and you can add a view filter for the User attribute and set the ID equal to (Choose Prompt) and pick the User Login prompt.

You may also like...