Passing Parameter Values from Excel to SQL Server

all right thanks for tuning in everyone this is Anthony smoke go and check me out on anthonysmoke.com as always if you learned something leave a comment hit that like button and make sure you ring the bell so you get a notification when I drop a new video and this week I just joined Instagram so make sure to follow me at anthonysmoke data so today I'm in Excel and I had someone leave a comment on my video showing how to connect um Excel to SQL server and Todd asked is there a way to create a dynamic query I pulled data on a monthly basis from SQL server and I need to change the reporting date to the corresponding month it would be great if I could change the date in a named range and have it linked to the query instead of having to manually edit it well I've got you covered Todd Schultz I'm going to hook you up here so we are going to in Excel create a work sheet we're going to pass in some dates we're going to change some dates and some cells and then we're going to pass that to a dynamic query which is going to go to SQL server and return those values back to excel so don't ever think that I don't read the comments and look out for you I want to give a big shout out to supporters of the channel so thank you very much esma for hitting that super thanks and contributing to the channel much appreciated and Doug you know who you are I appreciate you reaching out and letting us have that personal one-on-one talk about data and hopefully it helps solve some of your problems so I appreciate you Doug reaching out as well let's get to it okay want to get down to business here just want to show you this worksheet that I have in Excel I think this is a good intermediate data project just to show that you can connect Excel to SQL server and I have a video on how to do that if you want to get a little bit more in depth but not only connect to SQL server but use a parameter as well for a dynamic query so I've got this set up it's a little report here that's showing quantity and unit price and description of some of some items and this comes from the world the wide importers sample database that that Microsoft has I will leave a link to the description on where I got this data and you're probably it's going to be given to you as a Bak file so you can check out my video on how to import a Bak file as well but in any event here we've got our report is for New York and let's say we want to change this up for um for the great state of Georgia where a lot of New Yorkers come down to Atlanta and let's say I refresh the report it's going to run it's going to say hey do you approve running this native query I'm going to say yes run and you'll see this updates to Georgia even if I change some of the dates here so let's say I wanted to start on the 14th and go to the end of the month well now let's go into um let's go to February uh fifth here overwrite those contents refresh the report yes go ahead and run it and the dates should also update you see I've got the 14th here and if I scroll all the way down whoops a little too much you see I got some February dates uh as well so this is the functionality of the worksheet and I'm going to go into SQL server and show you how I set up that query okay we are in SQL server and I just wanted to show you that you know this is how you would set up a query it doesn't matter which query you use feel free to set up any uh where Clause that you would like just make sure that there are values that can be changed out uh when we put this SQL query into Excel like I have with Georgia and the two dates here so now uh let's get back into Excel and set this up okay so here we are in Excel um basically we have some cells here state or to begin date order end date and I've got some uh default uh values here that we're going to pass and so uh first thing you want to do and let me say this first I got to give a shout out to the the guys over at dashboard gear uh that YouTube channel that's where I got kind of the the basic underpinnings on on how uh to put this together I kind of put my own swag put my own bells and whistles on top uh for instance with the dates but got to give a shout out to the dashboard uh gear channel so we want to make sure that we have developer tab here in Excel if you don't know how to do that whoops I'm going to go down here you can customize the ribbon and if you see developer on this side you're going to make sure to add it over here to the main tabs I already have it but make sure that you do that so you can get the developer tab here okay so now that we have the developer tab what I want to do now is I want to record a macro so anything we do in Excel if we record a macro we can see the underlying VBA code behind that now it doesn't always make the most efficient code but it's always great it's great for getting a start so I'm going to go record macro macro one that's fine say okay and now whatever I do it's going to generate some code for me which is a very efficient here so let's go up to data and let's go to get data from database from SQL server and so I have to give it my server name if you don't know that you can go back to SQL Server do connect and then just copy the server name from here and let's go back to excel I'm going to place that uh there and then the name of my database is wide world importers DW let's go into advanced options and so this is where I'm going to paste my query from SQL Server that I showed you earlier you see we have the state of Georgia here you see we have dates don't worry about you know whether they look like parameters now it's okay to hard code values here right so we're going to say okay it's going to think about it and we get this you know just make sure the data looks uh generally like what you would expect and let's go to load load two and then I'm going to load this to existing worksheet I'm going to load this into let's say C8 right here and let's hit OK and it's going to think about that and you see it generates a query for me and I'm just going to rename this uh this query to something a little bit more user friendly let's call this sales order query you see I have 3 500 rows loaded so now we've got to remember to stop recording so if I go here to developer I'm going to hit stop recording and so I need to take a look uh at my uh generated code so let's go to Visual Basic and you'll see make sure you're in the right if you have multiple instances of excel open make sure you are in the right uh module here so okay so put it here on sheet1 you'll see that what I care about it it created this connection so activeworkbook.queries.added added uh it didn't it didn't uh you know put in the sales order query here but that's okay it did you know or rename it put down here did the rename action but that's fine what I really care about I care about this line right here we're going to use this um to put in our values from the uh from the sheet so stick around so as you'll notice uh that macro generated some code and it put it in a subroutine here in the uh the module in the Excel L sheet we're going to create our own subroutine I'm just going to paste in some some code here and I want to walk you through it so I called it subtest maybe we could give it a better name but that it is what it is doesn't take in any arguments or anything like that and I've just declared three variables we got our order State order start date and order end dates so we've got a string for the state and dates for our uh to date variables here again is just we're just dimensioning them okay so now here is how we access those values from the sheet order state is equal to sheet sheet1 is the name of our sheet dot range D3 that is the cell where we pass the or where we enter the state value on our Excel sheet and so likewise for the the start date and the end date D4 and D5 so we're just pass seeing the values that someone enters there into a variable it just makes it a little bit more readable for me at least anyway and so this active workbook that queries so uh remember we renamed our uh connection our query to sales order query and so we can doctor the formula so if we go back up here you'll notice that it created right that ad it created query one for us even though we renamed it and that's we we didn't need to we don't need this code here any of this code up here we don't really need any more after the first time that it ran through so what I'm going to do is I'm going to take all of this from after the formula equal I'm going to take this and I'm going to paste it here and you know I have it um commented out so it doesn't give me any uh you know craziness so it's commented out for now but for our sales order query the formula and you'll notice all these chr13s and chr10s these are Carriage returns and line feeds we don't really need this so what I'm gonna do I mean you don't have to do this it's just I don't like looking at it I'm going to just delete that let's Source okay and you may wonder what is this so all of this is uh M code that power query uses think of M code as just a way to prepare uh the data before it gets added into the the power bi query model so this isn't a lesson on M code but just wanted to let you know what you were looking at and again we don't need all these carriage return line feeds but I'm just gonna I'm gonna leave that alone for now I just fixed those initial ones so if you'll notice Let The Source equal to the SQL database that is uh the name of our server and it's in double quotes here it has to be in double quotes that is the name of our database that I want to start with and this is the query that we've that we've entered and all of these line feeds here it's just probably because when I pasted it into that initial box when we were making the connection um you know there's there's a line feed there I don't have them all on the same line but regardless we can leave them there what I really care about um I'm looking for where we put in our here we go state right so see where State Province equal Georgia and in the where Clause we need to replace that right we need to replace that with our variable that we've defined so I'm going to go up here and I'm going to do one of these here so we're going to perform a little uh surgery I'm going to in that there and then I'm going to start appending in and let's get rid of Georgia well let's not get rid of Georgia I live here but uh let's call this order state so this is our variable and then I'm going to come back in here and then we're going to uh start that up here so what I've done is um this I've ended the string I put in our variables so it'll be smart enough to change that dynamically and then we're picking back up a pending and so it's important when I'm using a character to have a single single tick marks here so this will be enclosed in single ticks and you'll see what that looks like when we run this but I need to do that for my state and then I also need to do that for the order date so same thing here I'm going to do this uh a couple more times here I'm going to close this out put that um get rid of this and I believe I call this order start date order start date pin that and there we go and then I want to come down here to this value as well this is the same thing it's going to be order in date you know what I'm just going to copy this and where are we I'm going to get rid of this and I know this needs to be in date instead of start we're going to go with uh end date here and make sure that that looks good now it's not going to give me the errors because this is a big comment but I just want to take a look at it instead of a hard-coded value here you see that we have a a variable a variable in here so now let's see what happens when we take this off of our take this off of comments and try and compile it okay Moment of Truth here let's see if we did this surgery right this will not give us any issues and if I go to debug compile it likes it okay so no errors so basically just to recap what we've done we've dimensioned or declared some variables assign the values from our Excel sheet to those variables went up into this macro here and took out the formula right basically where our SQL query is and put it into our line of code here so for our query query our sales order query.formula we pasted those values in here and did some surgery to take out the hard-coded values and put in the uh the dynamic varies the dimensions that we assigned here and so the last thing we need to do here is say sales order query dot refresh that will activate uh the query it'll fire it off and enable it to return our results and so like I said I I I can't compile it so that's good that means that Mikey likes it and so if I go back in here so let's insert a button here so on the developer tab going to go to insert a form control button and I get to draw it out here let's go ahead and draw that out and I want to assign this and so I call this sub test right that was the name of the subroutine that we created I'm going to say okay so all that means is when I click this button it's going to execute subtest and so first I want to edit the text because I just uh I like to do that first let's call this um refresh report right and so I want to make sure that I'm not in design mode so just make sure that you're not in design mode here and so Moment of Truth here let's see if this works let's change this to California I'm just going to type that out so it's California uh for 2015 Goodyear 2015 got a new job that I love very much uh 2015 was a good year okay so we're just going to debug this I like when I do these kind of uh you know live uh debugs here so let's take a look uh at our Visual Basic and let me bring this back over here all I did was have a break and so this is a real simple fix here so you'll notice I'm referencing D3 D4 and D5 and so if you look D3 four and five are not the values that I want they're an E3 E4 E5 so if I move these over here let's just move those over so now I'm getting the values in D3 D4 and D5 so if I hit refresh report now hopefully I'll get California here okay so it's going to give me this do I approve running the native query and the reason it does this is because again um you know you're sending a query over to SQL Server someone if they were nefarious could uh edit this code send like a delete uh statement over there and really screw things up so that's why I would recommend that you use this for your own kind of internal efficiencies don't package this up as a product to send out but let's go and hit run here and you'll see I get California so that's all it was I was referencing the wrong cells I copied them over so D3 four and five are there and this works perfectly now now here's something you may want to add as well notice how if I change my column with the values here whenever I run this again refresh see how it it changes I don't want it to uh to do that so where can I go to get that to change so I'm going to gonna go into table design right let's take a look at properties and let's uncheck adjust column width so let's uncheck that and so now if I change my width here and refresh the report it will stay well here you're like did you really refresh it let's go back to Georgia let's refresh the report right run it and then you'll see the width stays so that's something that you'll want to uh to check or uncheck I should say another check that you'll want to do you'll see up Georgia for the year of 2015 just make sure that your SQL query is returning what you expect here so 63 826 for my quantity and so if I go into SQL Server you'll see 63h26 is the sum of my uh my quantity I didn't Alias it but you know that is what I expect so that is good always double check your work so I'm in my fancy cleaned up version same underlying code but I want to show you this you can also go in here to the developer tab and in add-ins if you want that date picker here you see I have my add-ins but I can go to the store here and so if I search for a date here I have this mini calendar and date picker you can add that and that is what this is here if I ever wanted to again change the uh date overwrite so you just kind of select the cell you want and then it'll overwrite so that's a fancy little addition you can have here I got some uh some bands that that update simple enough to do and so as I said earlier because you know this is not something that I would hand out as a finished product it's more of an internal efficiency for you instead of having to go into SQL Server what I would do also is for my Visual Basic uh go into and this is the one that I'm working with here I can go to VBA project properties and protection and I can password protect this code so no one can come in here and alter the sequel and you know do some nefarious things so that's something that you might want to think about put a password on here lock it down as well again it's it's easy enough to crack you know you can find password crackers here on the the internet that would crack those passwords but this is really just going to keep Bob from accounting from nosing around it's not going to stop a hardcore hacker but in any event this has been Anthony smoke showing you how to create a worksheet and connecting it to SQL server using parameters so again shout out to uh Todd Schultz for leaving that uh comment you want to hit that super thanks Todd uh it would be appreciated so as always this is Anthony smoke get out there do some great things with your data thanks for watching everyone [Music]

Channel: Anthony Smoak

Views: 18,333

Keywords: Excel, ExcelDashboard, Parameters, SQL, TSQL, learn sql, sql course, sql server, sql tutorial

Id: 7xsL_gHf9Ws

Length: 21min 47sec (1307 seconds)

Published: Mon Dec 19 2022