SSRS Report Builder Part 4.6 - Datasets using Stored Procedures

welcome to this wisel report builder tutorial in this part of the series we'll explain how to populate a data set using store procedures we'll begin with a quick reminder of how to create basic data sets using the query designer in report builder before we then move into sql server management studio and look at how to create a stored procedure we'll show you how to use the store procedure you've created to populate a report builder dataset and then how you can edit an existing stored procedure and refresh the data set to reflect those changes so let's get started to get started i've created a new report in report builder and the first thing i'll do in here is create a data source which points to the wisel movies database just as a quick reminder again if you don't already have that installed you can use this video to help you get the database set up and you'll find a link in that video's description which you can use to download any files you'll need while i'm talking about things you'll need for the video a little later on you'll also need to have access to an application called sql server management studio if you don't already have that installed we have again a couple of playlists which have videos in there which explain how to get it set up so there's one for sql server 2016 and a separate one for sql server 2017 alternatively you can just head straight over to the microsoft docs website and download sql server management studio and install it yourself it's not that difficult a tool to install assuming you've got all that set up i'm going to head back to report builder right click my data sources folder and choose add data source i'll call it movies choose to use an embedded connection pointing to a microsoft sql server and then click the build button to help construct the connection string i'm going to type in a shortcut to my local host so dot backslash followed by the name of the instance of sql server i'm going to use for this video which is sql 2017 i can then use the drop-down list a little further down to select the movie database and having done that i can click ok a couple of times to have my data source created now that we have the data source we can use that to create a data set and while the focus of this video is on using store procedures i would like to have a quick reminder of how to create data sets in the basic way just by selecting from a list of tables and columns mainly so that we can compare the two techniques a little later on so i'm going to right click on my movies data source and choose our data set i'll change its name so that it's called films and then i can click the query designer button at the bottom to allow me to select from a list of tables i'll expand the tables folder and head to the film table first i'll select title release date oscar nominations and oscar wins then from the director table i'd like to select the full name column and then from the genre table i'd like to select the genre i'd also like a filter so that i only see films with at least one oscar win in this data set so i can click the add filter button over here and then change the field name to oscar wins change the operator to is more than or equal to and then in the value box type in the number one having done that i'll click ok once to generate my select statement one final modification is i'd like to modify the name of the full name column i want that to make that a bit more descriptive so i can see what it is the full name of so i'm going to assign an alias to that column i can do that by optionally typing in the word as after the full name column name and then the name of the new name i want to use for the column which i'm going to call director name having done that i can click ok and that's my data set created using the basic query designer creating a basic data set like this is quick and easy enough in the query designer but even small data sets like this get incredibly tedious if you find yourself recreating the same thing time and time again so what we're going to do is create a stored procedure which will define that query and store it inside our movies database and then we can simply select that stored procedure for the next data set we'll create i'll start by opening up sql server management studio so i'm going to head to my search box and type in sql server and i'll find the management studio application appears fairly quickly i can then click to launch that application i'm currently running the latest version of management studio version 18.8 which is the latest version available as of today's date when the application loads it asks you to connect to an instance of sql server and the instance i'm going to connect to is the same one that i've used to point my data source to the movies database dot backslash sql 2017 so i'll click the connect button to connect to that instance of sql server and then i'll see a list of objects appears eventually in the object explorer window on the left hand side i'll expand my databases folder and then just to show you where the stored procedure is going to end up i'm going to find my movies database and expand that then the programmability folder in there and finally the stored procedures folder in there so once we've created our stored procedure that's where we'll find it in order to begin creating our stored procedure we'll need a query into which we can write the code you can create a new query in several different ways you can click the new query button on the top toolbar or press control and n or you can right click on the movies database and choose new query from the menu which is what i'm going to do ordinarily then you could just start writing out all the code you need to create your stored procedure but this isn't really a course about teaching you how to write sql code to create stored procedures we do already have a playlist which does that by the way and if you're really interested in learning how to write stored procedures properly there's a playlist there with tons of videos that explain exactly what to do stored procedures are a much more sophisticated approach to using views as we did in the previous video in this series we're not going to do anything particularly sophisticated here our stored procedure will really just hold a single select statement but in the real world you can use stored procedures to do far more exotic fancy things particularly when it comes to working with parameters as you'll see a little bit later on in this series anyway for the time being i'm going to head back to management studio and rather than writing out all of our code we're going to rely on the query designer in management studio before i open the query designer it's important to make sure that your query is going to use the correct database so if it isn't already listing the movies database in this drop down list up here do make sure that you've selected movies first when you've done that you can simply right click in the background of the query page you've created and then choose design query in editor there's also a keyboard check for that there of course control and shift and q so if i launch my query designer i'm going to replicate the query we generated in our first data set so i need to start by selecting some tables so i'll insert my film table first by double clicking on it then the director table and then the genre table you should be able to see those tables appear in the diagram in the background i can then close down the add table window i can then start checking boxes for the columns i want to include so from the film table that was title release date and then from down at the bottom of the list oscar nominations and oscar wins from the director table there was full name and then from the genre table genre to make any modifications to the columns i've selected i can use this table area in the middle of the dialog box so for example if i wanted to change the name of the column or assign an alias to the full name column i can simply click in the alias column and type in the new name i want to use to reference it so director name i can then press enter or tab or click into another cell in that dialog box to to finish creating the alias i can also apply sorting to my results so something that's not easy to do in report builder in the query designer there's no option to apply sorting although i could type in my order by clause at the end of the select statement when generating a query in management studio we can apply sorting quite easily so i'm going to sort first of all in descending order of oscar nominations followed by descending order of oscar wins finally in ascending order of title the last thing i wanted to do was to assign a filter to the oscar wins column so that we only see films with at least one oscar win so in the filter column on the oscar wins row i'll type in greater than or equal to one and that will create my where clause when i click away from that cell in the grid so having done all that i can click ok and that will write out my select statement for me to uh to produce the results it might be worthwhile quickly checking that this does return the relevant results and i can do that by executing the query here if i click execute at the top i'll see a list of results gets written out in the table area at the bottom of the screen and it shows me how many results have been returned and all of the columns i've selected to create my stored procedure which uses this select statement as its definition i do need to add a little bit of code to my query so i'm going to click just before the select statement and in fact let me zoom in a little bit to make it easier to read so just before my select statement i'm going to hit enter to give myself a new blank line above it what i'm then going to do is write the statement create proc i could also write the full word procedure if i like but i don't know why i'd bother when i can shorten it to proc then i need to think of a sensible name for my stored procedure i'm going to call mine oscar winners you need to avoid any existing object names so if you already have a table or a view with that name do make sure to rename it as something unique and then finally at the end of that i need to say as and then everything which follows is the definition of the procedure all i have to do now is execute this script to create the entire procedure so again making sure you have the movies database selected i can click the execute button and this time rather than seeing a table full of results at the bottom i just see the lovely message commands completed successfully i don't actually see my stored procedure appear immediately in the store procedures folder not simply because the object explorer window is a little bit rubbish at refreshing itself so if i try to refresh that folder i should then see my stored procedure appears in the list eventually and there it is so i have a stored procedure called oscar winners now while it is a little bit of effort to create the store procedure the fantastic thing is just how simple it is to use to populate a data set in report builder if i switch back to report builder i'm going to right click my movies data source again and choose add data set i'll call this one films from proc or store procedure if you like i can't bother typing all that out and then rather than writing in a select statement or using the query designer all i'm going to do is choose stored procedure from the list of options for the query type and then from the drop down list which appears i can select my oscar winners store procedure i can then click ok and that's it i get my list of columns and they're immediately available to populate items in the report if we want to edit a data set to for example include extra columns this is nice and easy when you have a data set generated with a query designer in report builder a little trickier to do it using a stored procedure for the query designer i can right click on the film's data set and choose query i'll get a little warning because i made a modification to my query after generating it i change the column name for the director name column if i click yes that means i'll lose that change but it means i can now edit the the query in the query designer so for example should i want to include an extra table or an extra column i can expand the studio table for example and choose the studio studio column and then click ok to update that data set if i wanted to then subsequently modify the full name column again i can i can go back to the data set properties dialog box and then simply edit that in the query again as usual so editing it for a query designer data set is nice and simple to do the same thing for our store procedure that involves going back to sql server management studio now i will need to write some scripts to do this um i either need to modify the one that i've still got open here or i can generate a new one which allows me to alter our stored procedure if i try to execute this same script again to create my oscar winners procedure it won't work if i click into that script somewhere and then click execute i'll receive an error message telling me there's already an object with that name in the database i can modify it however and to modify it i can change the word create to the word alter having done that even though i haven't made any changes to the actual select statement itself if i click the execute button that will now run slightly confusingly this red squiggly which appears indicates that there's some kind of problem here and the problem is that the intellisense and the syntax checker hasn't actually updated to recognize that that object actually exists it's a little slow to keep up a little like the object explorer window is if you wanted to update the intellisense and refresh the syntax checker you can do that from the edit menu in management studio choose edit followed by intellisense and then the option which says refresh local cache or you can press the keyboard shortcut ctrl and shift and r it takes a few seconds for it to catch up but eventually that red squiggly will disappear so now it appears as though there are no mistakes if you didn't still have this script open that's not a problem either as long as your stored procedure exists you can generate code that will alter it and you can do that by right clicking on the stored procedure name and choose modify when i do that i get some system generated code which includes everything that i've done myself so i can see from this line here i've got my entire select statement above it is a statement which will alter the procedures definition and then above that is some more system generated code that isn't particularly important for what we're doing here if i want to make changes to the select statement itself rather than writing that out manually what i can do is highlight all of the code which comprises just the select statement so everything from the word select all the way down to the end of the the order by clause and having done that i can right click on any of the selected text and choose to design the query in the editor when the editor loads i can now start adding tables selecting extra columns to add a new table i can right click into the diagram part at the top and choose add table i'll choose the same table i've just selected in the query designer in report builder i'll go for the studio table by double clicking then i can close down the add table window tick the box next to the studio column and then click ok to add that column to my select list the final thing i must do and you mustn't forget to do this is to actually execute the code to modify the procedure itself so click somewhere inside the script click the execute button or press f5 and we should see the good or message command completed successfully again now that we've updated the stored procedure we'll also need to refresh the data set in report builder so let's head back there and i can right click on my films from proc dataset and choose to view its properties so currently of course we can see in the list we don't have the studio column but if we click the refresh fields button here and then click ok we'll find the new studio column is added to the data set we should finally just check that our data set actually works and displays some data when we try to present it in the report so let's tidy up a little bit by removing the default header text box and then right click and remove the page footer then i can right click into the body of the report and choose to insert a table i can then start adding fields from my films from proc dataset i'll drag the title field in followed by release date and oscar nominations and finally oscar wins uh i could go further there's not much point in demonstrating that it actually works i'll also make sure i don't run into the text rendering bug where often all the text in the table doesn't get displayed properly so i'm going to highlight all of the cells in the table and then change the default font to any other font and then back to the default do some basic formatting for the column headers and then we can change some column widths as well a little bit to make it a little easier to read and then if we just run the report we should see a list of films produced by the results of that store procedure so that's really all there is to it once you've created a stored procedure you can simply select it from a drop-down list when you create your data set as i mentioned earlier the real power of stored procedures comes in the extra things you can do above and beyond basic select statements and you'll get a bit of a flavor for some of those things when we cover the parameters topic a little later on in this series for now i hope you found some of that useful thanks for watching see you next time you

Channel: WiseOwlTutorials

Views: 11,344

Keywords: ssrs, reporting services, report server, paginated report, report builder, rdl, dataset, data source, sql server, sql, stored procedure, stored proc, create proc, alter proc, procedure, proc, query, select, from, where, order by, wise owl

Id: utLdXCmB3nE

Length: 18min 15sec (1095 seconds)

Published: Tue Dec 29 2020