Tutorial - How to create Parameters and Cascading Parameters in SSRS

hey guys in this video I'm gonna show you how to create parameters and cascading parameters in SSRS so to save time let's get started to save time I've already created a query here that returns this data basically a the order year total territory and territory group from the adventure works database the adventure works 2012 database is the one I'm using right here you can see that right here adventureworks 2012 that's one that I'm using so if you don't have it you can go download it from Microsoft's website and we can get started so let's go ahead and open up visual studio and create a new project let's call this one parameters so the first thing you notice is there's no report data pane down here sometimes it's there sometimes it's not you want to go ahead and add a new report first we'll just call this one new report report one leave it alone the name and when you click on view sometimes it's there sometimes it's not so if you click off of that you see it's not there anymore but if you click on the actual tab of the name of the report it shows up so if you don't have this tab that's how you get it back because when I switch from SSIS to RS a lot of times this tab just disappears so a lot of people always have that problem so that's how you get it back so let's get started let's go ahead and go to data sources we're gonna create a new data source I'm just going to leave that data source one we're going to click Edit right here your server name is if you go into your sequel and go to Connect here is your server name right here so you can always just copy it and pop it in right here we're gonna use the adventureworks test connection it's good to go alrighty we got our data source now let's add in our data set we can just leave this as name data so we want to use our data source one which is our adventure works let's grab this query now whenever I paste a query in here for the most part I go to the designer tab just to make sure that it works so there we go it works we got our main data set in now let's go ahead and grab a simple matrix and in this matrix we want to get let's say we want the territory group then we want the territory and then we want the total and we want the years as well so let's preview this data so we have all of our data here it's all jumbled up but we got it it's on the screen so now let's work with this and make this actually readable mm-hmm and also more user-friendly we're gonna add in the parameters so I guess let's do the parameters first so to put in parameters you need to modify your main data set and in here you need to add a where clause now the three parameters we're gonna choose our year the territory group and the territory name now the territory names are part of the territory groups so that's going to be where our cascading parameters come into play them but let's do the regular parameters first so let's do the year first and we're going to call this parameter let's just call it ear and we want our we want our group our territory group in a parameter called territory group and we want our territory name in a parameter called territory name okay let's see verify that you connect to this or so I spelled it wrong so let's go back here and see where we made the mistake oh that's where it is so this was supposed to be your third date there we go save it and now when we preview it we'll see it's asking for a year so I know the years in here are two thousand five six and seven so let's just say 2005 territory group we're gonna say America or let's just say Europe and territory name I want Germany there it goes so the parameters are working but who's trying to memorize all this so we want it to be where it's a drop-down list so let's go ahead and do that so we're going to add some functionality to this so in our data sets we're gonna add three more data sets the first one we're going to use is a we're gonna get a list of the years so we're going to call this year per M we're gonna use our regular data set and then we're gonna go into our query designer here we want to select the year of the sales guess we don't need this because there's no joins in here from sales dot sales order header now I'm gonna show you this on this one and I'm gonna skip through it on the others but pay attention right here if I run this if I get speller right if I run this you see how many records is pulling in literally it's two thousand five six and seven and eight I should have four records but it's giving me all these now if I went hit went ahead and hit okay and when I go to my drop-down menu I'll show you actually so we got our parameter year we're gonna go to our parameters up here and at least three should have popped up when you put them in the the main data set that where clause go ahead and double click it we want to allow multiples now the available values we have our year parameter right here expression one expression one I'll go back and rename it I'm just doing this to show you real quick what happens let's hit preview looks like it's frozen here let's try it again it's actually freezing because of the amount of 31,000 records of years so I'm actually gonna let it run should take another few seconds and should be done okay look at this all these two thousand five six seven you never want to do that so whenever you're creating the data set for the parameter you always want to use a distinct so you get individual values so let's go back to the year parameter data set here we're gonna change this to distinct let's give it a name over year and let's actually order it by two because you don't want it going in random order so we wanna order by year or date and we want it to go descending so you get two thousand eight seven six five okay but before I do that let's run it and show you what it comes up now we got four records and look how quick that was hit okay hit okay let's save it we want to go ahead and refresh this when everybody make a change let's hit preview now oh and then once I renamed it over here and gave it order year I had it expression one over here the first time so I gotta go back and change it so change to order your order year now what this is doing is this telling you hey in we're gonna use this data set to get certain values and match them up now the value field is order year and the label is order your now you could if you have different labels or different values you could always change that around but in our case right now for simplicity it's the same preview report Oh since I added this I gotta finish it up with the other ones and now so let's go ahead and just make the other parameters as well so let's go to data sets let's go to add another data set we're gonna call this one territory group parameter and for this we're going to just do a select group from sales dot sales territorial and again we're going to do a distinct here because if we don't we'll get a lot of unnecessary rows there we go and then we go to territory group here we assigned the values territory group parameter we want the group field territory group parameter group field that one's done let's do our territory name select okay yep on the 10 that's good that's good save it let's go ahead and preview okay so there's something fishy going on over here I didn't set this one to her to our name name name territory name name okay so sometimes it gets really weird like that I just closed it out and reopen it there we go so there it is you have only four values here so we fixed that over there now we have these here and these here so our parameters are working perfectly now this is a little bit messy so we're gonna fix it right here and see what happened so we have our territory group and it's only showing the Pacific now this is probably because let's see here okay so I think I figured it out here I called the parameter in here territory but then over here I had renamed it the territory name so I just change this back to territory instead of territory name and now we should be good so we're just gonna assign these again save it and let's go ahead and run it it's still not working let's see what happened here oh okay that's a pretty simple fix so in the territory parameter allow multiples and there we go so now we have it working with the years we got all the territory groups so if we just click North America it's only going to click North America if we do just North America and Pacific we got North America and Pacific and we got all of them here so I'm gonna go ahead and remove this name total to give it more clean of a look and so this is giving us the territory groups the territories the total for those countries but then I also want the total for the ears so I'm gonna click on order year and say actually that's gonna mess it up there we go now we have the totals for each year at the bottom - now this is a whole jumbled mess I'm going to go ahead and just real quick change it to the currency for both the totals and let's spread it out a little bit there we go now it looks like a report so we have the territory groups the territories got the years and we have the ability to select certain years through our parameters giving the user two options to pick whatever they want to pick and view the report as they need to now the problem you might somebody might run into is let's say I only want North America I shouldn't have Australia in here I shouldn't have United Kingdom Germany there's no point in having those in here so what a cascading parameter is is basically when I click one of when I select any of these only the territories that fall in the group with these will be selected so when I do let's say Pacific the only one that should be selected in here is Australia so let's go ahead and set that up real quick to do that we're going to go into our territory group territory name parameter and we're going to modify it so we're gonna say select the territory name and the group from sales territory where the group is actually in our territory group parameter now let's watch this now what happens so now let's say we only want Europe now you have France Germany and the UK only you don't need to worry about any of the other stuff let's say we only wanted North America so now the options are you know the other ones disappeared the only getting North America now that's cascading parameters the last thing I'm gonna do real quick is just to make it more clean as this is too much of a mess right here I'm gonna go ahead and create the drop downs the drill down report part so in the row groups for territory for the territory name we're gonna go ahead on visibility we're gonna say that the territory group can toggle this and we're gonna hide it so now when we pop open our port everything is much cleaner so if you want Europe you can drill down to Europe if you want North America you can drill down North America Pacific and so on so you now you have parameters cascading parameters and a quick drill down tutorial on this and so that's all I got for you guys thanks for watching if you have any questions or comments let me know

Channel: Mohamed Khan

Views: 843

Rating: 5 out of 5

Keywords: ssrs, tutorial, business intelligence, parameters, cascading parameters, drill down reports, reporting services, sql server

Id: nbOKOZbOECU

Length: 19min 17sec (1157 seconds)

Published: Wed Jun 06 2018