Creating Dashboards in Microsoft Access

Congratulations Juan Soto on a great presentation this month!  I also want to thank everyone who joined the meeting and hope that you found the webinar informative and worthwhile.

The video is now available for you to review:

Here are the dashboard files: NorthwindDash

Your presence helped to make this event a great success. We wish you all the best and hope that you continue to be engaged with the Access User Group. Stay tuned for upcoming events by visiting accessusergroups.org.

NOTE: You can see the stored procedure by restoring the database in the zip file NorthwindDash above, but if you just want to see the stored procedure here it is:

USE [NorthwindDash]
GO
/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 8/14/2015 8:08:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Juan Soto
-- Create date: November 17th, 2014
-- Description: Create table for Dashboard
-- =============================================
ALTER PROCEDURE [dbo].[usp_Dashboard] 
 -- Add the parameters for the stored procedure here
 @UserStartDate DateTime
AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 Declare @Dashboard Table
 (
 myMetric VarChar(50),
 myTimeFrame VarChar(50),
 Qty Money
 )

 DECLARE @StartDate DateTime
 DECLARE @EndDate DateTime

 DECLARE @Year DATE 
 DECLARE @Quarter INT


 DECLARE @QuarterStartDate DateTime
 DECLARE @QuarterEndDate DateTime

 -- User supplies dates for analysis
 --Set @UserStartDate = CONVERT(DATETIME, '2014-11-08 00:00:00', 102)

 SET DATEFIRST 1; -- We want Monday as first weekday

 SELECT @StartDate= DATEADD(dd, -(DATEPART(dw, @UserStartDate )-1), @UserStartDate ) ;
 SELECT @EndDate= DATEADD(dd, 7-(DATEPART(dw, @UserStartDate )), @UserStartDate ) ;

 -- Calculation Quarters
 set @Year = DATEADD(day,-day(@userstartdate) + 1 ,@userstartdate)
 set @Year = dateadd(month, -month(@year)+1 ,@year)

 set @Quarter = datepart(quarter,@Userstartdate)

 set @QuarterStartDate =DATEADD(QUARTER, @Quarter - 1, @Year) 

 set @QuarterEndDate = DATEADD(DAY, -1, DATEADD(QUARTER, @Quarter, @Year))
 
 --Calc customers created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'Today', COUNT(ID) AS Qty
 FROM Customers
 WHERE DateCreated = @StartDate

 --Calc customers created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisWeek', COUNT(ID) AS Qty
 FROM Customers
 WHERE (DateCreated BETWEEN @StartDate and @EndDate);


 --Calc customers created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisMonth', COUNT(ID) AS Qty
 FROM Customers
 where (month(datecreated) = month(@UserStartDate) and year(datecreated) = year(@UserStartDate) )


 --Calc customers created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisQtr', COUNT(ID) AS Qty
 FROM Customers
 WHERE (DateCreated BETWEEN @QuarterStartDate AND @QuarterEndDate)


 --Calc customers created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisYear', COUNT(ID) AS Qty
 FROM Customers
 WHERE (year(DateCreated) = year(@UserstartDate) )

 --Calc orders created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'Today', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE [Order Date] = @UserStartDate;

 --Calc Orders created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisWeek', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE ([Order Date] BETWEEN @StartDate and @EndDate);


 --Calc Orders created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisMonth', COUNT([Order ID]) AS Qty
 FROM Orders
 where (month([Order Date]) = month(@UserStartDate) and year([Order Date]) = year(@UserStartDate) )

 --Calc Orders created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisQtr', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE ([Order Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)

 --Calc Orders created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisYear', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE (year([Order Date]) = year(@UserstartDate) )

 --Calc order dollars created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','Today',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE Orders.[Paid Date] = @UserStartDate

 --Calc order dollars created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisWeek',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (Orders.[Paid Date] BETWEEN @StartDate and @EndDate);

 --Calc order dollars created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisMonth',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 where (month(Orders.[Paid Date]) = month(@UserStartDate) and year(Orders.[Paid Date]) = year(@UserStartDate) )

 --Calc order dollars created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisQtr',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (Orders.[Paid Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)

 --Calc order dollars created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisYear',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (year(orders.[Paid Date]) = year(@UserstartDate) )

 Select * from @Dashboard

End