***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
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
Comments
0 comments
Please sign in to leave a comment.