Reporting Metrics Queries for ProjectDox SQL Database

Description

Reporting metrics queries for ProjectDox SQL Database

Solution

Average Project Size

 

selectAVG(MB)from (

SELECT p.Name AS Project,

(SUM(f.Filesize)/ 1024)AS MB

FROM Projects p

JOIN Files f

ON p.ProjectID = f.ProjectID

GROUPBY p.Name) x

 

 

 

Average File Size

 

selectAVG(filesize)from Files

 

 

 

Total Number of Document Pages & Plan Pages by Year Uploaded

 

 

selectisnull(sum(PageCount),0)as [2014 Total Document Pages]

from Files

whereyear(UploadDate)BETWEEN 2014 AND 2014

and(SheetSize ='8.5x11.0'or SheetSize ='8.5x11')

 

selectisnull(sum(PageCount),0)as [2014 Total Plan Pages]

from Files

whereyear(UploadDate)BETWEEN 2014 AND 2014

and SheetSize <>'8.5x11.0'and SheetSize <>'8.5x11'

 

 

 

 

Number of Files Uploaded Per Project  (OPTIONAL: Time Period)

 

 

SELECT p.Name AS Project,COUNT(l.LogReportID)AS'# Files Uploaded'

FROM LogReports l

JOIN Files f

ON l.EntityID = f.FileID

JOIN Projects p

ON f.ProjectID = p.ProjectID

AND l.EventType ='FileUpload'

--OPTIONAL: Uncomment the YEAR and MONTH if you want results for a specific time period

--ANDYEAR(l.UsageDate)= 2014

--ANDMONTH(l.UsageDate)= 5

GROUPBY p.Name

ORDERBY p.Name

 

 

 

Number of Files Downloaded Per Project (OPTIONAL: Time Period)

 

 

SELECT p.Name AS Project,COUNT(l.LogReportID)AS'# Files Downloaded'

FROM LogReports l

JOIN Files f

ON l.EntityID = f.FileID

JOIN Projects p

ON f.ProjectID = p.ProjectID

AND l.EventType ='FileDownload'

--OPTIONAL: Uncomment the YEAR and MONTH if you want results for a specific time period

--ANDYEAR(l.UsageDate)= 2014

--ANDMONTH(l.UsageDate)= 5

GROUPBY p.Name

ORDERBY p.Name

 

 

 

Total Size of Files Uploaded Per Project

 

 

SELECT p.Name AS Project,(SUM(f.Filesize))AS Megabytes

FROM Projects p

JOIN Files f

ON p.ProjectID = f.ProjectID

GROUPBY p.Name

ORDERBY p.Name

 

 

 

Total Size of Files Uploaded Per User

 

 

SELECT u.FirstName AS'FirstName', u.LastName AS'LastName',u.Email AS'Email',(SUM(f.Filesize)/ 1024)AS Megabytes

FROM Users u

JOIN Files f

ON u.UserID = f.AuthorID

GROUPBY u.LastName, u.FirstName, u.Email

ORDERBY u.LastName

 

 

Revised 02-18-2015