(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. which is the table DimProductCategory, column EnglishProductCategoryName.- datetime, which is the first day of any given month, e.g.It started with just the SELECT statement, and then I added the two parameters whose values will be passed from Excel. Excel VBA to make the Stored Procedure execute with user-entered valuesġ Create a SQL Server Stored Procedure Below is a pretty basic query I coded to return Internet Sales in the AdventureWorks 2012 Data Warehouse.Excel connects and executes the SQL Server Stored Procedure.Logging: How to stalk your users for fun and self-promotion.Connections other than SQL Server, such as Oracle, Informix, DB2..How to create a SQL Server Stored Procedure.Out of scope / Maybe I'll go here in a future article Installing SQL Server on client desktops.Any of the extra fee Microsoft Power BI tools.A report server or any reporting application other than Excel..SQL Server execute priveleges on the Stored Procedure we create.Microsoft Excel, and the free Power Query download and install. This solution has been tested in Excel 20.What is needed for users to use this solution Basic to Intermediate skills in manipulating T-SQL to create Stored Procedures.Users will not need SSMS to use these reports. SQL Server Management Studio (SSMS) to create the Stored Procedure that serves as the report's data source.The ability to create a SQL Server ODBC connection, which comes standard in Microsoft Office.This solution has been tested in Excel 20. This solution uses SQL Server 2012 and the AdventureWorksDW2012 database, so if you don't currently have it then click here to download and install.Data in SQL Server, although this solution can work with any database with a saved Stored Procedure.What is needed for you to create this solution The link to AdventureWorksDW2012 database is here, and the completed Excel doc used in this article is here. This is also a walk-through of Excel named ranges, data validation, data connections, Excel VBA, and SQL Server Stored Procedures. The finished product, which also is an Excel download at the end of this article, is here: Hey Bob, can I get a report that shows the number of aardvarks in every zoo? Hey Bob, can I get a report that shows the number of flights out of St. Hey Bob, you know that thing you ran for me last week? Run it again for last month. Hey Bob, run me my favorite report for July. Hey Bob, run me my favorite report for June. Hey Bob, run me my favorite report for April. This article targets this common scenario for anyone that can execute SQL Server T-SQL:
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |