Dashboard Queries for Portal Framework

***This article is always under construction!

The following queries are samples for Dashboard Data Sources. Please note the text in red and make sure it matches your database naming convention. The green text should be changed to match the specific data you're trying to display on the Dashboard Visualization. 

Please feel free to post your own queries as a comment on this article! Note: Must be signed in to post a comment. 

Related Articles:

Dashboards Module 4.2 Training Guide

Dashboards SQL Authentication Info & Sample SQL Queries

Dashboard Queries for Modules

 

Conversations

# of Conversations Created between a Date Range:

SELECT count(id) as Threads FROM [Production_Portal_PortalFx].[dbo].[Post] where parentID is null AND CreatedOn between '2014-01-01' and '2015-01-01'

Portal Framework

Total number of Workgroups, Pages, Users, Roles

Select count(Activated) as numWorkgroups, SUM(activated) as numActiveWorkgroups

from Production_Portal_PortalFx.dbo.PWWorkGroups

Select count(ID) as numRoles from Production_Portal_PortalFx.dbo.PWRoles

Select count(pageID) as numPages from Production_Portal_PortalFx.dbo.PWPages

Select count(Activated) as numUsers, SUM(cast(activated as integer)) as numActiveUsers

from Production_Portal_PortalFx.dbo.PWUser

Top 10 most popular pages

Select NameOfWorkgroup + ' - ' + NameOfPage as NameOfPage, NumHits from (

SELECT top 10 NameOfPage, NameOfWorkgroup, COUNT(DateTime) as NumHits from

(SELECT   tme.PageName, tme.UserID, tmc.CategoryName,

                      tme.DateTime, cast(tme.IDPage as varchar(50)) as PageID, isnull(PWPages.DisplayText,'Deleted Page - ' + SUBSTRING(PageName, charindex(':',PageName)+2,100)) as NameOfPage, isnull(PWWorkGroups.WorkGroupName, 'Deleted Page - ' + REPLACE(SUBSTRING(PageName, 0, charindex(':', PageName)),'Workgroup Page', '')) as nameOfWorkgroup

FROM         [Production_Portal_PortalFx].[dbo].PWViews LEFT OUTER JOIN

                      [Production_Portal_PortalFx].[dbo].PWWorkGroups ON PWViews.ViewID = PWWorkGroups.ViewID RIGHT OUTER JOIN

                      [Production_Portal_PortalFx].[dbo].PWPages ON PWViews.ViewID = PWPages.ViewID RIGHT OUTER JOIN

                      (Select PageName, UserID, CategoryID, DateTime, cast(isnull(nullif(PageID,''),'00000000-0000-0000-0000-000000000000') as uniqueidentifier) as IDPage from [Production_Portal_PortalFx].[dbo].PWTrafficMonitorEntries) tme ON PWPages.PageID = tme.IDPage INNER JOIN [Production_Portal_PortalFx].[dbo].PWTrafficMonitorCategories tmc on tme.CategoryID = tmc.CategoryID

                      where CategoryName = 'Pages'

UNION

SELECT [PageName]

      ,[UserID]

      ,[CategoryName]

      ,[DateTime]

      ,[PageID]

      ,CASE [CategoryName]

        WHEN 'Headquarters' THEN [PageName]    --Headquarters

        WHEN 'Send User Alert' THEN [PageName]    --Send User Alert

        WHEN 'Group Calendar Island' THEN 'GroupCalendar Island'   --Group calendar Island

        When 'Reservations Island' THEN 'Reservations Island'    --Reservations Island

        When 'Search' THEN SUBSTRING([PageName],30,50)   --Search

        WHEN 'User Portal Settings' THEN 'Edit User Profile'    --User Portal Settings

        WHEN 'Send Group Email' THEN 'Send Group Email'     --Send Group Email

        ELSE [CategoryName]

        END AS NameOfPage

      ,CASE [CategoryName]

        WHEN 'Headquarters' THEN 'Headquarters'

        WHEN 'Send User Alert' THEN 'Send User Alert'

        WHEN 'Group Calendar Island' THEN [PageName]

        When 'Reservations Island' THEN [PageName]

        WHEN 'Search' THEN 'Search Bar'

        WHEN 'User Portal Settings' THEN 'Edit User Profile'

        WHEN 'Send Group Email' THEN 'Send Group Email'

        ELSE [CategoryName]

        END  as NameOfWorkgroup

  FROM [Production_Portal_PortalFx].[dbo].[PWTrafficMonitorEntries] tme INNER JOIN [Production_Portal_PortalFx].[dbo].PWTrafficMonitorCategories tmc on tme.CategoryID = tmc.CategoryID

  where CategoryName <> 'Pages'

) cmbquery WHERE DateTime > '2015-01-01' group by NameOfPage, nameofworkgroup order by NumHits Desc

) x

Count of Unique Visitors and Total Page Hits

SELECT top 10 left(datename(dw,convert(date, cast(month([DateTime]) as varchar) + '/' + cast(day([DateTime]) as varchar) + '/' + cast(year([DateTime]) as varchar), 101)),1) + '-' + cast(datepart(day,convert(date, cast(month([DateTime]) as varchar) + '/' + cast(day([DateTime]) as varchar) + '/' + cast(year([DateTime]) as varchar), 101)) as varchar(3)) as dateViewed, count(pagename) as numHits, count(DISTINCT USERID) as numUsers
FROM [Production_Portal_PortalFx].[dbo].[PWTrafficMonitorEntries]
where datetime < convert(date, GETDATE(), 101) and datetime > dateadd(day,-10,GetDate())
group by convert(date, cast(month([DateTime]) as varchar) + '/' + cast(day([DateTime]) as varchar) + '/' + cast(year([DateTime]) as varchar), 101)
order by convert(date, cast(month([DateTime]) as varchar) + '/' + cast(day([DateTime]) as varchar) + '/' + cast(year([DateTime]) as varchar), 101)

Top 10 Most Active Users

SELECT Top 10 PWUser.FirstName, PWUser.LastName, count([LastName]) as PageHitCount FROM [Production_Portal_PortalFx].[dbo].PWTrafficMonitorEntries
INNER JOIN
[Production_Portal_PortalFx].[dbo].PWUser ON PWTrafficMonitorEntries.UserID = PWUser.UserID WHERE DateTime between '2016-01-01' and '2017-01-01' group by PWUser.FirstName, PWUser.LastName Order by pageHitCount desc

 

# of Public Pages:

SELECT count([IsPublic]) as PublicPageCount FROM [Production_Portal_PortalFx].[dbo].[PWPages] where isPublic = 1

Number of MyPages

SELECT top 25 PWUser.FirstName + ' ' + PWUser.LastName as Employee, count(Login) as NumberMyPages FROM [Production_Portal_PortalFx].[dbo].PWPages INNER JOIN [Production_Portal_PortalFx].[dbo].PWUser ON PWPages.ViewID = PWUser.PrimaryViewID group by PWUser.FirstName + ' ' + PWUser.LastName order by numbermypages desc

People Following Other People

SELECT     PWUser.FirstName + ' ' + PWUser.LastName AS PortalUser, COUNT(ResourceSubscriptions.UserID) AS NumberFollowing

FROM         [Production_Portal_PortalFx].[dbo].ResourceSubscriptions INNER JOIN [Production_Portal_PortalFx].[dbo].PWUser ON ResourceSubscriptions.UserID = PWUser.UserID

WHERE     (ResourceSubscriptions.ResourceType = 'Passageways.Portal.User')

GROUP BY PWUser.FirstName + ' ' + PWUser.LastName ORDER BY NumberFollowing DESC

People Being Followed By Other People

SELECT Left([Name],Len([Name])-15) as Employee, count([Name]) as NumberFollowers FROM [Production_Portal_PortalFx].[dbo].[ResourceSubscriptions]

  where resourcetype = 'Passageways.Portal.User' group by name order by NumberFollowers desc

Most Popular Islands:

SELECT [IslandID], Count([IslandID]) as IslandCount FROM [Production_Portal_PortalFx].[dbo].[PWPageIslands]
group by IslandID order by IslandCount desc

Public Pages per Workgroup:

SELECT PWWorkGroups.WorkGroupName, COUNT(PWPages.IsPublic) AS PublicPageCount

FROM [Production_Portal_PortalFx].[dbo].PWPages INNER JOIN [Production_Portal_PortalFx].[dbo].PWViews ON PWPages.ViewID = PWViews.ViewID INNER JOIN
[Production_Portal_PortalFx].[dbo].PWWorkGroups ON PWViews.ViewID = PWWorkGroups.ViewID WHERE (PWPages.IsPublic = 1)
GROUP BY PWWorkGroups.WorkGroupName order by PublicPageCount DESC, WorkGroupName

Role & User Counts:

SELECT PWRoles.Name, Count([Name]) as RoleCount FROM [Production_Portal_PortalFx].[dbo].PWRoleMembers INNER JOIN [Production_Portal_PortalFx].[dbo].PWRoles ON PWRoleMembers.RoleID = PWRoles.ID group by Name order by rolecount desc  

User Profile Fields:

SELECT count(ID) as NumberOfProfileFields FROM [Production_Portal_PortalFx].[dbo].[PWProfileFields]

Workgroup Managers:

SELECT PWUser.FirstName + ' ' + PWUser.LastName as Employee, count([Login]) as ManagerofWorkgroups FROM [Production_Portal_PortalFx].[dbo].PWWorkGroups INNER JOIN [Production_Portal_PortalFx].[dbo].PWUser ON PWWorkGroups.ManagerUserID = PWUser.UserID where pwworkgroups.activated = 1 GROUP BY PWUser.FirstName + ' ' + PWUser.LastName order by ManagerofWorkgroups desc

Workgroup Members:

Select WorkgroupName, count([WorkGroupName]) as WorkGroupMembers from (SELECT PWWorkGroups.WorkGroupName, PWUser.FirstName + ' ' + PWUser.LastName as UserName
FROM [Production_Portal_PortalFx].[dbo].PWRoleMembers INNER JOIN
[Production_Portal_PortalFx].[dbo].PWWorkgroupMemberRoles ON PWRoleMembers.RoleID = PWWorkgroupMemberRoles.RoleID INNER JOIN
[Production_Portal_PortalFx].[dbo].PWWorkGroups ON PWWorkgroupMemberRoles.WorkGroupID = PWWorkGroups.WorkGroupID INNER JOIN
[Production_Portal_PortalFx].[dbo].PWUser ON PWRoleMembers.UserID = PWUser.UserID
where PWWorkgroups.Activated = 1 and PWUser.Activated = 1
UNION
SELECT PWWorkGroups.WorkGroupName, PWUser.FirstName + ' ' + PWUser.LastName AS UserName
FROM [Production_Portal_PortalFx].[dbo].PWWorkGroupMembers INNER JOIN
[Production_Portal_PortalFx].[dbo].PWWorkGroups ON PWWorkGroupMembers.WorkGroupID = PWWorkGroups.WorkGroupID INNER JOIN
[Production_Portal_PortalFx].[dbo].PWUser ON PWWorkGroupMembers.UserID = PWUser.UserID
WHERE (PWWorkGroups.Activated = 1) AND (PWUser.Activated = 1)) c group by WorkGroupName order by WorkGroupMembers desc