Dashboard Queries for Modules

This article provides template queries to use as Dashboard Data Sources. Please be sure to verify you're using the correct database name and date ranges. The database text is highlighted in red and dates are green. Please feel free to post your own queries as a comment for others to use!

Description: Number of Announcements, Last Submitted Date, and Read Receipt Required Per Workgroup

SELECT [WorkGroupName], count([WorkgroupName]) as NumAnnouncements, Sum(cast([RequiresReadReceipt] as integer)) as NumReadReceiptRequiredFROM [Production_Portal_Announcements].[dbo].[PWGroupAnnouncements] INNER JOIN [Production_Portal_PortalFx].[dbo].[PWWorkgroups] on PWGroupAnnouncements.GroupID = PWWorkgroups.WorkgroupIDwhere activated = 1 AND SubmitDate between '2014-01-01' and '2015-01-01' group by WorkGroupName order by numannouncements desc

SELECT Count(Distinct([EventID])) as EventsSignedUp FROM [Production_Portal_Calendar].[dbo].[PWGroupEventSignUps]

# of Recurring Events in Calendar:

SELECT count([RecurrenceType]) as RecurrentEvents FROM [Production_Portal_Calendar].[dbo].[PWGroupEvents] where RecurrenceType > 0

Count and Last Event Per Calendar:

SELECT [WorkgroupName], count([WorkGroupName]) as NumEvents, Max([StartDate]) as LastEntry FROM [Production_Portal_Calendar].[dbo].[PWGroupEvents] INNER JOIN [Production_Portal_PortalFx].[dbo].[PWWorkgroups] on PWGroupEvents.GroupID = PWWorkgroups.WorkgroupID group by workgroupname order by numevents desc

# of Calendar Events:

SELECT count(EventID) as EventCount FROM [Production_Portal_Calendar].[dbo].[PWGroupEvents]

Select Avg(Expenses) as AverageExpense from (SELECT reportID, round(sum(Amount)- sum(Amount*(isCorpCardItem)) - 2*sum(Amount*iscashadvance),2) as Expenses FROM [Production_Portal_ExpenseReports].[dbo].[ExpenseItems] where amount > 0 and reportID in (Select ReportID FROM [Production_Portal_ExpenseReports].[dbo].[ExpenseReports] where status = 2) group by reportiD) as DB

Commonly used GL's:

SELECT GLAccountNumber, count(GLAccountNumber) as countGLFROM [Production_Portal_ExpenseReports].[dbo].[ExpenseItems] WHERE ItemDate between '2014-01-01' and '2015-01-01' group by GLAccountNumberorder by countGL desc

General Ledgers with Highest Expense Report:

SELECT GLAccountNumber, round(sum(Amount)- sum(Amount*(isCorpCardItem)) - 2*sum(Amount*iscashadvance),2) as Expenses FROM [Production_Portal_ExpenseReports].[dbo].[ExpenseItems] where amount > 0 and reportID in (Select ReportID FROM [Production_Portal_ExpenseReports].[dbo].[ExpenseReports] where status = 2) group by GLAccountNumber order by Expenses