USE AdventureWorksDW2012
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'xrpt_sales_by_category_by_month')
DROP PROCEDURE xrpt_sales_by_category_by_month
GO
CREATE PROC xrpt_sales_by_category_by_month (@dt as date, @CategoryName as nvarchar(50)) AS
/*
Source of data for the Excel report 'Sales by Category by Month'.
@dt - The month will always be passed as yyyy-mm-01
@CategoryName - Hard-coded Category name
2014-05-17 Jim Horn Original.
Personal Rant: Hey looky here! Meaningful code comments!!!
What an easy way to distinguish yourself from your peers!
*/
-- TESTING ONLY
-- Declare @dt datetime = '2005-08-01', @CategoryName nvarchar(50) = 'Bikes'
Declare @dtStart as datetime, @dtEnd as datetime
SET @dtStart = @dt
SET @dtEnd = EOMONTH(@dt) -- 2008R2 and below use DATEADD(d, -1, DATEADD(M, 1, @dt))
SELECT
s.OrderDate as [Order Date], SUM(s.SalesAmount) as sales,
pc.EnglishProductCategoryName as [Category Name],
c.LastName as [Customer Last Name], c.FirstName as [First Name], c.AddressLine1 as [Address],
cg.[City], cg.StateProvinceName as [State], cg.EnglishCountryRegionName as [Country]
FROM FactInternetSales s
-- Products
JOIN dimProduct p ON s.ProductKey = p.ProductKey
JOIN DimProductSubcategory ps ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
JOIN DimProductCategory pc ON ps.ProductCategoryKey = pc.ProductCategoryKey
-- Customers
JOIN dimCustomer c ON s.CustomerKey = c.CustomerKey
JOIN dimGeography cg ON c.GeographyKey = cg.GeographyKey
WHERE s.OrderDate BETWEEN @dtStart and @dtEnd AND
(pc.EnglishProductCategoryName = @CategoryName OR @CategoryName IS NULL)
GROUP BY
s.OrderDate,
pc.EnglishProductCategoryName,
c.LastName, c.FirstName, c.AddressLine1,
cg.City, cg.StateProvinceName, cg.EnglishCountryRegionName
GO
=TODAY() - DAY(TODAY()) +1
=DATE(YEAR(B4), MONTH(B4)-1,1)
exec xrpt_sales_by_category_by_month '2006-01-01', NULL
Sub btn_go_Click()
'Refresh the table
Call fn_execute(Range("month_value").Value, Range("category_value").Value)
ex:
On Error Resume Next
Exit Sub
eh:
MsgBox "An error occured: " & Err.Number & ", " & Err.Description
Resume ex
End Sub
Public Function fn_execute(dtMonth As Date, sCategoryName As String)
'Execute the SQL Server Stored Proc xrpt_fuel_burn with parameters entered by the user
'@dtMonth - YYYY-MM-01 of the month
'@sCategoryName
'05-22-14 jim.horn Original
Application.Cursor = xlWait
Application.DisplayAlerts = False
Dim cn As WorkbookConnection
Dim ocn As OLEDBConnection
Set cn = ThisWorkbook.Connections("xrpt_sales_by_category_by_month")
Set ocn = cn.OLEDBConnection
Dim sCommandText As String
sCommandText = "xrpt_sales_by_category_by_month "
sCommandText = sCommandText & "@dt='" & Format(dtMonth, Text) & "', "
sCommandText = sCommandText & "@CategoryName='" & sCategoryName & "'"
With ocn
.CommandText = sCommandText
.BackgroundQuery = False
.Refresh
End With
Application.Cursor = xlDefault
'ActiveWorkbook.RefreshAll
'Refresh all pivot tables
Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
For Each pt In sh.PivotTables
pt.RefreshTable
pt.Update
Next
Next
ex:
On Error Resume Next
Application.DisplayAlerts = True
Application.Cursor = xlDefault
Set ocn = Nothing
Set cn = Nothing
Exit Function
eh:
MsgBox "An error occured: " & Err.Number & ", " & Err.Description
Resume ex
End Function
Save, and close.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (12)
Commented:
Anyway, was just a thought.
Commented:
Commented:
Call fn_execute (Range("month_value").Valu
It gives message: Run-time error '1004':
Method 'Range' of object' _Global' failed
Any ideas?
Commented:
Commented:
View More