Reporting Services (SSRS) Part 14 - Cascading Parameters

welcome to this yl tutorial in this session we're going to teach you how to create cascading parameters in reporting services what you're learning this session is how to create a sequence of cascading drop-down lists we're selecting an option from one list filters the next and so on and so on to do that you'll need to understand how the database is structured and we'll also give you a couple of hints on how to plan a report to identify which drop-down lists and which data sets you need to create well then take you through the practical aspects of creating the drop-down list parameters and then filtering each data set in turn to make sure that you get the final set of results at the end so let's get started in a previous wise hour video we talked about how to create a single drop-down this parameter to filter the results of a report the example we use was to let a user select to direct his name from a list and then to see a list of films made by that director in a table in the report in this example however selecting a director's name from the list doesn't just filter the main report itself it actually filters the next drop-down list so have what was called a cascading dropdowns for a cascading parameter searching a film name then from this list which is filtered to share only films by Steven spielberger and finally viewing the report will show me a list of people he played a role in that film and that's example I'd like to talk to you through to create our cascading parameters example when you're creating a report like this which involves cascading parameters it's very important to plan out what you're gonna do first it's also important to understand the structure of the database that you're working with so in this example I'm gonna have to separate drop-down lists and one table of results and that means I'm going to need to have three independent data sets in my report if I can take you to show you a list of a example of the database diagram on which this database is built this is in sequel server management studio here's a set of all the data that's involved in the reports I'm working in so the first drop-down list is going to be populated with a list of director names but it also has to remember what that director's ID is as well when we selected one the reason you need to do that is that the second drop-down list which is populated with a list of film names has to be filtered based on the film's director ID that drop-down list also then has to remember what the what the ID number of the film selected is finally the table of results is populated with with two tables he's got the cast character name from the cast table and the actor name from the actor table and that final data set is filtered based on the film's ID so that's the setup of the data that we're going to use let's get started with building the report so here I've created a new report and I've created a reference to my shared data source which is at the movies database and the first step then is to create a data set which will populate my list of director names so I'm going to right click the data sets folder and add a data set I'm gonna call the dataset DTS directors I can spell directors icons there we go and I'm gonna use an embedded data set referring to my shared data source and then I'm going to use the query designer to quickly build it I want to add a table to the to the to the data set so I'm going to right click in the background and choose to add one and then double click the director table and then close down the add table window now I remember that in my drop-down list I want to see a list of director names but I also need to remember the directors ID so I need to include those two fields in my data set to help my user out I'm also going to sort my directors in ascending order of their names and at that point I can simply click OK and click OK again so the first dataset is very simple I'm not going to build a parameter that's based on that data set so I can right click the parameters folder and add a parameter I'm going to call it direct ID because that's the value that the parameter will store and in the prompt I'm going to tell my user to select a director named the data type that's going to be stored in this parameter isn't text although a user will see a piece of text in the director's name what's actually going to be remembered is the directors ID which in this case is an integer to populate the drop-down list then on the available values tab I'm going to choose to get my values from a query and the data set will be DTS directors the value field is going to be the directors ID that's the one that the report the the the parameter will remember and then the label field which is the one that user will see will be the directors name and at that point I can click OK and that's my first parameter and data set created so the next step is to create the data set which will populate my second parameter drop-down list so I'm going to right click the data sets folder and choose to add one this is going to be my my list of films so I'm going to call the data set DTS films it'll be an embedded data set referring to my shared data source and I'm going to use the query designer to build it I'm going to add a table to my data set which will be the film table and then I need to think about which fields I'd like to include so obviously only to include the film name because that's what I'd like my user to see in the drop-down list but this potata set also needs to be filtered by the directors ID which is the value that's been selected in the first drop-down list finally this data set or this drop-down list at which I'll finally create needs to remember a value that can be used to filter my final table of results and that's going to be the film's ID so remember the final table of results is a list of actors in the selected film I'm finally going to sort my films in ascending order of name and choose okay and that's the data set itself created while I'm here I can apply the filter to this data set which will pick up the body for my first parameter so on the filters tab I can choose to add a filter and I'm going to choose where the film director ID and I want to say is where it where it is equal to the value of this parameter to do that the easiest thing to do I think is use the FX button to launch the expression builder find the parameters folder or category and double click the one that I want to use if I click OK and click OK again that's my second data set created now that I've created the second data set I can create the parameter which will take his values from that list if I right click the parameters folder and choose that add one I'm gonna call this one that film ID because that's the value that will be stored in it I'm gonna tell my user or ask my user to select a film name and then in the data type box I'm going to choose integer because the parameter will again store an ID number not the piece of text that the user sees back to the available values tab then and on the list of options I want to get my values from a query the data set is the one that I've just created data set films the value field I want to store in this parameter at the film ID and then the label fields which the user will see is going to be the film name I can click OK and at that point is worthwhile giving your report a quick test to make sure that that part of the system is actually working it's by previewing my reports I should be able to see that I get two parameters the second one is grayed out at the moment once I've selected a value from the first if I select a director's name I should see that the second drop-down list populates itself with the list of films made by that director that's exactly what we wanted to see so our system seems to be working pretty well at this point all the remains in this example then is to build the table of results which is filtered based on the valley selected in this drop-down list the film name so I'm going to go back to the design view and build another dataset in the report data window and I'm gonna call this one DTS actor roles and use a data set that's embedded in the reports referring to their shared data source of movies and then the query designer again now this data set needs to include two separate tables in it so I'm going to right click to add a table I'm going to include the actor table and the cast table and then closed down that window now I need to include these two tables because I want to be able to see the character name in the film but also the name of the actor who played the role I also need to be able to filter this data set and the filter will be based on the ID of the film that was selected and there's a field here called cast film ID which I can choose to use at that point I won't bother sorting this time I'm just going to click OK and then I need to add the filter to this data set so I'm going to go to the filters tab and choose to add one and I want to filter it based on when the film the cast film ID is equal to the value of this parameter so I can click the FX button the parameters category and double-click film ID and then click OK and click OK again although into now you show the results so I can double find the tool box and a book like the table tool can then include from my list of datasets someone to use the app to rolls data set so that's going to be the cast character name first and then the actor name I can delete the third column change my column widths a little so I can actually see things more clearly and finally preview my report so if I select a director let's go Goodall Steven Spielberg I can select one of his films let's go with Jurassic Park I'm gonna view my report I should see a list of actors who played a role in the selected film so that's how you create simple cascading parameters if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 110,691

Keywords: reporting services, ssrs, parameter, drop down list, cascading, drill down, filter

Id: 1oSg96XhHEI

Length: 11min 33sec (693 seconds)

Published: Thu Sep 13 2012