![]() If you don't understand stored procedures very well, we can just skip this part and eventually connect to a table or view.Ģ Setting up Excel Create a new Excel workbook, and then save it with the name 'Sales By Category By Month', and save as type 'Excel Macro-Enabled Workbook'. When executed (the exec line below), it returns this set: (pc.EnglishProductCategoryName = OR IS NULL)Ĭg.City, cg.StateProvinceName, cg.EnglishCountryRegionName JOIN dimGeography cg ON c.GeographyKey = cg.GeographyKey JOIN dimCustomer c ON s.CustomerKey = c.CustomerKey JOIN DimProductCategory pc ON ps.ProductCategoryKey = pc.ProductCategoryKey JOIN DimProductSubcategory ps ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey JOIN dimProduct p ON s.ProductKey = p.ProductKey ![]() Pc.EnglishProductCategoryName as ,Ĭ.LastName as, c.FirstName as, c.AddressLine1 as ,Ĭg., cg.StateProvinceName as, cg.EnglishCountryRegionName as SET = - 2008R2 and below use DATEADD(d, -1, DATEADD(M, 1, as, SUM(s.SalesAmount) as sales, Declare datetime = '', nvarchar(50) = 'Bikes' What an easy way to distinguish yourself from your peers! Personal Rant: Hey looky here! Meaningful code comments!!! Source of data for the Excel report 'Sales by Category by - The month will always be passed as - Hard-coded Category name IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'xrpt_sales_by_category_by_month')ĭROP PROCEDURE xrpt_sales_by_category_by_monthĬREATE PROC xrpt_sales_by_category_by_month as date, as nvarchar(50)) AS Notice the use of aliases with spaces that force the use of, which is not normally a best practice, but this way the column names that will appear in Excel.Ĭopy-paste the below code into an open SSMS window connected to the server that contains your copy of AdventureWorks2012DW, and execute.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |