SSRS 2016 Part 1 - Creating and Deploying Your First Report

welcome to this was our tutorial on sequel server reporting services 2016 the first part of this series is going to explain how to create and deploy your very first report so the intention behind the video is to give you an overview of the entire process from start to finish we won't go into a huge amount of detail in each of the individual topics here we'll save their detail for later videos in the series but hopefully by the end of it you can have a good idea of the entire process we're going to begin with a very quick checklist of the a bits of software you'll need to have installed to follow through the series will then look at how you can use Visual Studio to create a new report server project and then add a report to it we'll look at how you can connect to a database using the data source and then write a query to return beta from that database using a data set we'll look at working with basic reports items so we'll look at basic text boxes tables and matrix in a simple chart and then we'll look at how you can deploy the reports you've created to the report server and finally view them through the web portal application so there's quite a bit to do although we won't go into a lot of detail as I say let's get started now before we can get started with creating report projects and reports you'll need to make sure you've followed a few prerequisite steps to ensure you've got all the software required you may already have done this on your own machine but just in case you haven't we have a series called sequel server 2016 getting started they'll help lead you through the various steps to get the software set up now the good news is you don't need to follow every single video in this series and there are a few key ones that you will need to make sure you followed however so part one of course to begin with getting sequel server 2016 installed on your machine this video explains how to get hold of a free edition of the sequel server and get it installed and set up it's not absolutely necessary that you use sequel server 2016 for this tutorial series reporting services is superficially similar in 2014 and 2012 and even 2008 r2 there are a few basic cosmetic differences in the interface but in terms of the tasks are going to perform that will work equally well in earlier versions anyway this video explains how to get hold of a free edition of sequel server the next video explains how to get that configured and installed a couple of the management tools that you're also going to need so that's worth a quick check of as well it's not a particularly long one just helps you get their tools installed and then once you've done that you can skip over a whole bunch of these videos unless you're desperately interested in a few sort of basic management techniques and a bit about how relational databases get designed and skip straight on to part eight which helps you to get to the wiser movies database installed so the examples database we're going to use for this tutorial series all is all based on our movies this video explains how to get that database installed there's a link in the video description for this video that allows you to install the database on your own machine and then finally part 9 explains how to install some equals sequel server data tools this is going to be so essentially the templates that are required to create report projects so part 1 part 2 part 8 and part 9 and pretty much required viewing before we can start creating report projects everything else is optional presumably follow those things we're ready to get started with creating our report project now once you've installed sequel server data tools you'll have a new application installed on your computer called Visual Studio the default edition of Visual Studio installs for sequel Server 2016 is Visual Studio 2015 slightly confusing the devotion numbers don't necessarily match up anyway regardless of which version you've got installed you can head to your Start menu or maybe just to perform a quick search to find Visual Studio you may have created a shortcut in your Start menu as I have here for visual studio 2015 alternatively you'll be able to find it in your list of all programs or all apps or whatever they're called these days if I just scroll down far enough to the visual studio section there we go so whichever shortcut you want to find or click on simply click on that icon to launch the application now eventually what will happen is I'll end up on the start page assuming I haven't changed any of the default settings you get this list start page popping up to begin with asking you what you would like to do next what we're going to do next is create a brand new project so if you do have the start page displayed you simply click the link in there called a new project alternatively you can go to the file menu and from there you can choose new and then choose project or indeed just click the keyboard shortcuts ctrl + shift + n so whichever option you'd like to choose select that option and you'll be presented with another dialog box asking you to choose what type of project you'd like to create now visual studio is Microsoft's flagship development through unless you create pretty much any type of project you can conceive of so unless you build Windows forms applications or websites or sequel server databases or video games or em or mobile phone applications iOS apps and Android apps we're going to avoid all those tempting sounding options at this point in the stick just with this category of templates at the top called business intelligence the exact list of templates you'll see depends on exactly which edition of Visual Studio installed and any additional templates the business intelligence category only appears if you have indeed been sold the sequel server data tools component so from that category we're going to choose a report server project we need to choose a sensible location to store it I'm going to store mine on my desktop although you can't use any other folder if you like and to do that you could hit the Browse button I'm also going to change the name of my project from report project 1 to something a little more sensible called movies or movies reports or movies project we prefer anything along those lines by defaults I've got an extra checkbox selected here that says create directory for solution so what I'm doing is I'm creating a single report server project that will belong to a solution whose name is exactly the same as a project as it turns out so it's movies and movies a solution is essentially in Visual Studio terms a solution is just a collection of projects so you might have a single solution that contains multiple report projects or maybe a report project in an integration services project and so on you can combine many different projects in the same solution it isn't necessary to to modify this unless you're unless you just really want to you don't even technically need one so you could always uncheck the Box angle II mind checked at this point and then simply click the ok button to create my new report a server project now assume that that worked correctly you should be looking at the screen roughly resembling this one so assuming you haven't changed any of the default settings for visual studio you'll see a window appear at the top by I'm gonna call the solution Explorer which lists out your solution items and any project listed in there so in this case with a movie solution with a movie project and any items that belong to that project it organized in separate folders what's actually happened in terms of your machine if I just browse back to my desktop briefly by pulling down the windows key and pressing D is I've ended up with a movies folder sitting on my desktop so this folder represents my solution if I double click into that folder I'll see a list of items belonging to it so I've got a file called movies dot SLN that's the solution file which keeps track of what items belong to the solution I've also got a subdirectory here called movies which represents my project so this sub movies directory here represents the movies project the main folder represents the movies solution so if I double click into this movies folder I'll see you all the items belong to it not much of interest there at this point what we're going to do at this stage is just generate a brand new blank reports and then create a connection to a database and build a basic report based on that connection so to get started what I'm going to do is right-click the reports folder it's very tempting to choose the obvious opportunity this has add new reports but what that does as it turns out is launches a simple little wizard to try to hold you by the hand and reach down the garden path to do all the very steps required to create a simple report now personally I don't tend to enjoyed these sorts of wizards I prefer to do everything by hand and there are certain things I don't want to do right at this stage that would it would force me to do - what I'm gonna do is cancel out of the ways it at this stage and then I'm going to right click the reports folder again and then in this case choose add and then choose new item instead there's also a keyboard shortcut as you can see ctrl + Shift + a anyway if I choose a new item from the list I get a dialog box displays with a list of different types of items I can insert into this project the obvious one I'm going to choose here is reports I don't want to launch the report with it although I could do that by double clicking that item all I'm going to do is select the report and I can change its name in the name box down at the bottom it's absolutely fine to change the extension here in this dialog box I can completely remove what's in the box already and type that with something like I know let's call it simple movie reports spell movie improperly that would help someone we report there we go so I'm not going to bother with the the extension that will appear automatically when I click the Add button I'll end up with a brand new report and it will be listed in the reports folder in the solution Explorer and there it sits obviously there's a big change the main area of your screen this is the report that we're about design but also we've got we've created a file here we've got to created a simple movie report dot RDL file RDL sample report definition language that's probably the least important thing you'll learn from this entire video that's a file that exists in your project folder so I just browse back to the movies folder you'll see I've got this simple movie report file sitting in that folders arm so just so you can see the organization what the effects of what you're doing when you're adding items to your movies project so there you go then created a brand new report to the server project and you've inserted a single blank report into it the next step is to start adding items to the report and learning a little bit about what you can do to manipulate it just to give you a very brief or went towards the screen just to start with when you create a new report of course it opens up into the main area of the screen so should I want to close that report down as a cross sitting just at the top right hand corner of the reports tab if I click close that will remove it if I wanted to open it again I can simply double-click on it back in the solution Explorer and now it appears again there are lots of different windows that we'll be using throughout the rest of this tutorial series but the main ones you're going to be interested in at this point of course the solution Explorer which is where we add our new reports and new items to the project there are a few other windows are opened currently called tabular model and scene Explorer and class view none of those are relevant to the type of project we're creating so you can happily ignore all of those other windows and should you want to you just close them down by selecting them and then clicking clicking acrosses in a cell right hand corner of each one there's also a properties window which responds to what items you've got selected in the report so if I were to click on two different items in the report area you'll see the items in the properties window change so you'll see that that responds to what you do in the report itself the properties window is quite important so I'm going to leave that one there we've also got something called the report data window which will use fairly shortly to create a connection to a database and there's another important window those currently tucked away in a little tab at the left hand side called the toolbox if I just click on the toolbox tab that shows me all the items I can add into the report itself the movie reports window has got a couple of extra panels that aren't controlled in exactly the same way at the very top I got a little panel called parameters and at the very bottom a little panel called groups if I wanted to hide or show either of those of course they don't have little crosses to click on the simplest thing to do is to right click in a blank area of the report so away from the report page somewhere in this blank area here I can choose view and then I can remove the parameters bar by selecting parameters and I can do the same thing to remove the grouping panel should I want to bring neither of those back I can go back to right-click view and bring them both back we're not going to be using the parameters section for this particular video but I'm so at this point I'm actually going to hide it by team view and then choosing parameters it's easy to accidentally close down a window that you didn't inventive so for instance if I could close down my report data window by clicking the cross and should I want to bring that one back you can access any window you've closed down from the view menu so I head back to the View menu and then look at the bottom of list there's the report data window now some princess menu isn't quite so obvious for instance if I want to put into the solution Explorer window first and then head back to the View menu I don't know you can see here I can't see the option to display the report data window so in order to ensure that I can bring that one back I need to have something selected in the report design area so I click somewhere inside there and then choose view and report data will be available so it's easy to get tripped up by that ass-fucking a couple of times in the last so there you go is a very brief whirlwind tour of your visual studio screen let's have a quick look now at adding some actual items to the report itself before we start connecting to a database and getting data displayed in the report from the database we can add some basic text boxes and images just to I guess show you how to add basic items and manipulate them and format them there's a couple of different ways to do this the tool boxes will not be swayed to add items to a report if I click on to the toolbox tab on the left hand side there's a list of different report items I can insert into the report so a text box is a nice simple thing to add if I just double click on text box that will insert one of the top left-hand corner at the report it'll be selected by default so you'll see the little selection handles appear at the around the borders and there's a select and move cursor that I can use to click and drag to reposition it so they're fairly standard Microsoft drawing controls I'm not going to touch nicely too much by explaining how those work up and to show you that you can resize things and move things around an alternative way to insert objects is to simply right click into the background of the report and choose insert so I could insert another text box by right-clicking and inserting and then the text box appears exactly where I've right clicked so technically you don't really need the toolbox tab which is why it took three of the left hand side to begin with should you want to delete any object in the report you need to make sure it's selected of course first so if I click somewhere inside the text box it becomes selected I can also just click and drag a selection box that at least partially encloses the object math elastic - another way to select object is using the properties window so the top of the properties window there's a drop down list that explains what you've got selected currently if I click the drop down arrow at the top there it provides me a list of all the objects in the report if I select this option called text box - it will select that object in the report for me to delete it I can simply click the I'll press the Delete key on my keyboard I think I can also probably right-click on the object and choose delete yeah indeed I can of course so basic object manipulation text boxes are designed for text as you'd imagine so to add text to a text box you can simply select it and then start typing so I don't know I'm going to type in some really simple title like basic movie reports are going to misspell movie apology's basic move the report entering the text you don't press the Enter key if you press the Enter key actually throws a new line in the same text box which isn't what I want so it backspace that to enter the text simply click somewhere away from that checkbox in the report in terms of formatting you've got all the basics and the things you might expect to be able to do so I can click onto the text box to select it it's quite important when you're selecting a text box if you want to format the object itself rather than the text inside it that you click on the object rather than the text you'll see a couple of different mouse cursors appear you can see the cursor changes when I'm hovering over the text as opposed to when I'm hovering over the actual text box so if I select the text box as a few different ways I can format it there's a basic formatting toolbar you'll recognize some name and font size all these standard Microsoft controls you've been using for years and years and years you can also format objects using the properties window as well there are citing more options available in the properties window then you'll see are available from the toolbars but there are lots of duplicate object options as well steps for instance the background color rather nanning it shuts transparent or no color I can find the background color property click on the drop down arrow and then either choose a different color from the grid or indeed choose more colors and choose a more wider range just for simplicity as I say I don't want to go into a huge amount of detail in this video and bore you I want to get the overall processes to focus for this video so I'm just going to change the background color and then perhaps font color to white and then I think I'll probably make the font bold as well probably the simplest way to achieve that is to use the toolbar so I think use to make it bold I can Center the text in the text box I could increase the font size all these fairly basic standard things you'd expect to be able to do now inserting basic objects like text boxes and formatting moves all fairly straightforward but of course what we're really interested in doing in this particular report is deploying a list of films from a database so assuming that you followed video number eight to install the wise our movie database in an instance of sequel server what we're going to do next is create a connection to that database using some we call the data source and then create a query that returns a set of plates from that database using something called the data set now you may well have spotted those two folders sitting in the report data window data sources and data sets you may also have spotted those folders sitting in a different form in the solution Explorer shared baked sources and shared data sets so the idea behind the different schema to folders basically in the report beta window anything that you add here is limited to being used just in this single individual report if you add a shared data source or a shared data set that's used or available for use for the entire projects so multiple different reports could use the same shared data source and share data set we're going to that in a lot more detail in the video that will make on those sources and note sets and now we're going to stick with just basic embedded data sources and data sets so to create a data source connection to our wise our movies database what I'm going to do is right-click the data sources folder in the report beta window and choose the only available option add data source and that provides me the dialog box to configure the data source I'm going to change its name so that it's called movies that's not necessary it just tells me to spots what basis also I'm working with when I've got multiple data sources in the report I'm going to connect to Microsoft sequel server of course and I'm not going to bother typing in a connection string I find them a bit tedious I'm not sure I could actually do it off the top of my head or what I'm gonna do instead is use the edits button to give myself a dialog box tell me to build my connection string so what you'll need to do at this stage is enter the server name sometimes clicking on this drop-down arrow takes a long time for the list of populate and often it ends up empty anyway you have to give a quick try if you like but I always find more success by typing in the server name now again if you follow the video series getting started in sequel server 2016 we're going to be referring to the sequel server instance that we created in part one essentially so in my case back in my movies project the simplest way to refer to my server name is to type in . followed by a backslash that's just a shortcut way to reference the local machine then the name of the instance of sequel by installs is called SQL 2016 training so i typed all that in at that point what i should then find is i can select from a list of databases installed in that particular instance of sequel server so from a drop-down list i should find out I've now got access to my movies database and again that's installed or you can find out how to even solve that from video number eight or part 8 of that video tutorial series ok so at that point if it makes you feel better you can click the test connection button I feel better the test connection works also cleated does basically to test credentials so if you're providing credentials or authentication for the server and again we explained various options to do with authentication in the series or the video in which we set up sequel server in the first place so all the things that I've set up work according to the settings I'm using here so all I should use at this point is click OK my basic connection string gets written out for me and then I can finally click OK again and I've now got a connection to my movies database that I can use to write queries with the next stage is to choose exactly which set of data I want to retrieve from that Movie Database I can do this in a couple of different ways but essentially what I need to do is create a data set which is the equivalent of a query if you think of it in those terms so I could click right click on the data sets folder and choose that data set although that means that I've got quite a few options to fill in on the dialog box that pops up if I can just very quickly show you if I choose a data set I've then got to choose which username and embedded data set unless I have any shared ones which I don't set up to choose embedded and then I have to click on the drop-down list to choose my data source and then I just have to fill in the rest that's slightly quicker to achieve if I cancel from this dialog box I can right click on the movies data source that I've just created instead to choose add data set that will pre-fill some of the options on the dialog box you can see that it's already selected and embedded data set and it's already selected the movie data source as that saw one that I'm using to create this one I'm going to change me my data set so it's not called dataset one let's call it something simple like I know a movie list something along those lines and then I've got a variety of choices for how I could return some data I could if I'm good enough I think I probably am by this stage I have taught enough sequel server courses to know how to write a basic select statement but you could write out a simple select statement in this great if you know how to write sequel code you don't get any help here there's no intellisense or key word coloring or anything along those lines so you have to know your database and sequel or transact sequel sequel code quite well to make this work alternatively you could choose to use a stored procedure if you've either creative on yourself or maybe a nice developers creative form for you that provides you with a list of data you can choose stored procedure and then select that from the drop-down list suddenly I don't have any custom store besiegers all these are system store procedures from the movie database and none of those are particularly useful for returning information about movies so I can't use that what I'm going to do in this case is revert to the text query and instead of trying to write it from scratch I'm going to use my query designer tool there's a nice little GUI tool graphical user interface not squidgy when I say GUI GUI graphical user interface that helps me build up my basic query I'm going to find it useful if I maximize a window here by clicking little maximize button and then I've got several different panels that I need to fill in so the first thing I need to be able to do is select some tables I can do that either by clicking the adds table button up at the top or engage is right clicking in the top panel and choosing add table I'm going to choose a few basic tables from this database I'm going to go with the certificate table first and then the director table I'm double clicking on these by the way to insert them I can also just click the tables and click outside choose to film table and then choose add that will insert it as well and then I'm also going to choose this Y on the table so I can double click on that one and I'm going to close down the add table window by clicking the close button not provide providing with access to the four tables I've just selected now you can reposition these things although it's absolutely not necessary depends on how thirsty you are I tend to be quite pedantic about arranging these things I get annoyed when things that don't line up neatly but anyway I'll try to avoid spending too much time doing max that's going to get really boring and the relationships that you're seeing here will all be based on the primary key to foreign key relationships built into the underlying database and again but you want to understand what that means in detail the entire video on how the movie state space works explains that there's also a video on designing and creating relational database that goes into a lot more detail I'm not going to bother with any detail at this stage all I'd like to do is select a few basic columns from the various tables so first of all from the director table I'd like to display the full name of the director I'd also like the name of the genre so there's a genre column I can select and then a certificate column from the certificate table from the films able I'm going to choose a title I'm also going to have the release date I'm going to have the run time in minutes and finally the Oscar wins you'll see all those options filling in the grid at the bottom or just below and then also writing out your select statement for you even further down so you could have written this out yourself in scratch if you knew your database well enough you can achieve all sort of other things here as all unifying aliases to columns so prints it's a full main column isn't particularly descriptive I'm going to give it an alias I'm going to call it director so I'm going to type in the word director in the alias box next the full name then I hit tab or enter that will rename that column as director making it slightly easier to reference I can apply sourcing some of the columns I can apply filters and all those things we'll do is add a where clause and an order by clause to your select statement but I'm not going to bother doing anything more complex at this stage all I'm going to do right now is click OK to return to the main dialog box which is written out the query for me and then finally click OK one more time to add my data set to the report but a list of all the columns just in case you need to modify either your data source or your dataset at any stage to modify the data source the simplest thing to do is just double click on the data source itself so if I double-click on movies that allows me to modify the connection string and go back to the Edit dialog box to modify anything that I wanted to modify here if I wanted to modify the query that I've just built the simplest thing to do I find is to right-click on the name of the dataset so in this case movies list and then if I want to go back to the query designer I can just choose queries and that will take me straight back to the GUI and let me modify the basic queries that I've generated if I wanted to modify anything else about the date set so for instance its name and if I cancel the query designer I can right click on the movie list data set and choose data set properties and that will take me to the main dialog box where I can modify their data set name and indeed I can just edit the text of my query here as well the lots and lots of other options available that I'm not going to go into at this stage we'll build upon these as we work through the rest of the video tutorial series so for now it's going to cancel that dialog box and look at how we can now display these values in my report so the next thing we need to do is insert a new object into the report so if I right click somewhere in the background or indeed to go back to the toolbox if I know what items I can insert I'm just going to right click similar just down below this text box and then choose insert and then select from the list now there are lots of exciting sounding control sites all report items I can insert to display data from my dataset where I'm afraid is going to stick with a very boring table just this very first simple video so I'm going to right click insert table and then look at how we can modify this to include some useful options so your table will be inserted with a couple of blank rows you've got a header row and a data row what I want to be able to do here is add different columns from my movie list they set into the table a couple of different ways I can achieve that I'd like to have the film's title in the first column so one simple way is to click and drag title and position it in that first column you'll see the column gets highlighted in blue and if I release the mouse button it will insert the title column and also insert default header for that column as well which will match the column name so I've got the title inserted on to insert the release date so again I could click and drag release date and position that in the next column one really neat thing one thing I really love about reporting services just a little searches that just make the difference I don't you can see here that the release date column as you'd expect from a sequel server database doesn't have any spaces in the name of the object you often find you either miss spaces out altogether and have capital initials for each word as I happier or sometimes you find underscore characters in place of a space both of those options are recognized by reporting services and it will automatically four column headers in a table insert a space for you which I just think is such a lovely little touch but anyway and let's move on so we've got Ruby steak there I can also insert columns into a table by hovering the mouse over a data cell and then using this little icon that appears to the right-hand side that's the field selector if I click on that it will provide me with a list of all the fields from that data set so the next one I'm going to choose the director at this point I've run out of columns so there's a couple of things I can do to add more columns I can either as you'd expect something like Excel I suppose right click on the gray box at the top of a column and choose insert column either to the left or to the right and then that provides me the blank column as you'd expect choose another field let's go with a genre this time I find it personally slightly easier just to click and drag items from the data set and position them where I want I wanted to attach this to the right-hand edge of the table if I drag just over halfway along the genre cell you'll see this vertical blue line appears at the right hand side if I wanted to insert it in between director and genre I'll position the mouse so that the vertical line appears between those three columns I'm going to attach this to the right hand side so I just released the mouse button at this point and then I'm going to do exactly the same thing that a run-time minutes and then finally the Oscar wins I'm gonna have done all that I'll have a new table containing all the columns from my dataset now at this point you know it's fairly heuristically basic it's worthwhile having a quick look at what your report looks like when it's running one key thing to get over with reporting services unlike tools like for instance Excel which you may be familiar with using reporting services is not what you see is what you get in design view this perspective ice contains 1200 films currently but all I'm seeing here in the design review is a single row representing what an individual film would look like or how it will be laid out at least if you want to see what the actual report looks like when it's running then what we should do is preview it so you'll see at the top of the report design tab there are two tabs the design tab and the preview tab so currently we're in design route design view to preview it simply click the preview tab what that will do is execute the query to retrieve all the data your query has defined and then display that in the table that you've defined it's armed but you say it's a little bit basic looking at this stage but there's lots of things we can do to modify its appearance I'm not gonna spend a huge amount of time doing it but just enough to give you the basics so a few basic things I'd like to be able to do for instance I'd like to perhaps change the column width course first of all so make sure everyone can appear on a single line I think I'd like to modify my H format I'd like to maybe format my column headers as well so all those things are relatively easy to achieve back in the design view if I switch back to design view changing column widths nice and straightforward click into any single cell in the table first of all and then just like in Excel if you're familiar with Excel hover the mouse between any two columns and then click on drag to resize that column sadly unlike Excel you can't double click to resize columns to an exact width so in Excel you can double click between two columns and the column will resize itself to fit all the data that's in there doesn't work in reporting services because in design view of course there's no possible way for the report to know how wide the data will be but having done mattify pre-dinner ovals again you'll see of course that column is now our wider formatting things like dates or formatting numbers for instance relatively straightforward to back in the design view for a specific individual cell what I really want to do is right-click on that cell in the table so for the release date cell it's important I'm using the one with the square brackets this represents the field this is the irrelevant details Royo CDs these icons here the three horizontal bars represent a detail rope that represents a rope that is simply like a template for every record in the result set so if I were to right-click on that release date cell in the row there's an option now that says text about properties now I could also modify these properties using the properties window but sometimes the dialog boxes help me out with this so for instance if I wanted to format the date for my release date then I'd have to find the relevant property for this in the properties window which would be called formats I believe I'm not quite sure exactly where it is in there somewhere there it is so there's a section goal number and a property call format if I click into that property it appears so I have a list of choices but standing when I click on the drop-down list it's not particularly helpful so for this particular case I think a much more sensible option is to right-click that cell in the table and then choose to do text box properties the dialog box is much more helpful is very much Excel style I suppose so there's a number tab on the left-hand side here and if I select that you've got a list of galleries different formatting options and numbers I've got a category they're called dates and from there I can select a basic date formats I'm going to go with this this basic UK for my day month year sorry us people missed a month you in this case rather than month day year but choose whichever is appropriate for you it really really doesn't matter at this stage so I'm going to choose that particular format and then click OK and then once again have a quick preview to see what effect that has had it's very much the process you follow when you're designing reports it's change something in design view switch to preview to see the effects and then back to design view and tweak it and so on so there we go the date formats look reasonably good it's going to change the width of my director column and perhaps a genre column as well and then a quick little bit of formatting to the column headers so that's the design view i'm going to read widen or widen my director column and a bit for the genre column to then i'm going to format the entire top row the entire um title row to do that rather than select individual cells although i could do that and do one by one i could also click into the top left cell and then hold down the shift key and click on the rightmost cell in that row and that will select all the cells alternatively I could also just click the a gray box at the left-hand side of that rope that will select the entire row as well so either of those options any of those options will work at that point I could either use the properties window to find the back color property or background color property or indeed again I could use the toolbar to change the background color but I'm going to use the properties window here the fill color I'm going to change that so that's a slightly lighter shade of blue and then the font color can remain in black I think that's absolutely fine but I want to make the font bold as well as if I use the bold button on the toolbar to do that I'm just going to modify really one of the column headings here as well so when my minutes is a bit of a long winded column name for me so having not ideally changed that one in the under lying query unlike with the director column so I can edit column headers simply by clicking into the text portion of the text box and then I can edit that I'm just going to call it run time so I'm just going to leet through it minutes using the Delete key once it's a methylene press away then preview the report one more time and that's that's not too shabby not particularly exciting I appreciate but a little bit better than it looked originally now there are a couple of other basic useful things you can do with reports and tables in particular in reports so I had the opportunity to sort my query or the results of my query in the dataset itself but I didn't do that so I can also do it at the report stage as well what I'm going to do here is sort my film alphabetically by film name and then for any film which has a duplicate name so for instance of a several different versions of King Kong in this database or them to be sorted so that the most recent one appears at the top so sorting tables is pretty straightforward if you know where to go to find the option of course so back into the design view what I'm going to do first is click in any single cell in the table and then I'm going to right click on any of the grey boxes that appear around the top and the left-hand side of that table when I do that I'll see an optional list called tablets properties publics is one of these horrible Microsoft comportment Oh words that combined a table list and matrix there are three types of tables you can insert into a report we'll have a look at those in a bit more detail in later vote later videos in the series but for now I'm going to choose publics properties and then from there I can click on the sorting page on the left-hand side and now I can add a basic sort by clicking the add button choose a column this is a column that belongs to the data set by the way it doesn't necessarily have to belong to the table itself so it's any column from the data set you created so I'm going to choose title a to Z main click the Add button again and then choose release date and say Z to a so in descending order now at that point I can click OK and then preview the report again see what effect is out and there's some you should see the films are listed alphabetically and then for any film which has our duplicate names and you'll see them in reverse order of release date so the most recent one will appear above the earlier ones okay so there's the add the basics of sorting in tables you can also filter tables as well so for instance should I want to see only the oscar-winning films again I could have done that at the data set stage I could have added a where clause to my query to only show films with one or more Oscar wins but again I can do to the table stage as well so back into the design view I need to make sure that I click somewhere inside the table and then right-click on any of the gray boxes around the outside and choose tablet's properties again the stage I can head to the filters page on the left-hand side and then click the Add button and then I can set up a basic filter by choosing a column from the expression drop-down list so in this case it will be Oscar wins and then the operator which is a fairly basic standard list of Microsoft and sequel operators like in between in this case I'm going to go for greater than or equal to and then simply type in the value of 1 in that box if I click OK again at that point and have a quick preview one more time I've got a now a list of only oscar-winning films in the list ok one last quick tiny little thing I want to change the width of that text box it's the exact same width as a table and there's a much easier way to achieve this as it turns out back in the design view I'm going to manually widen that text box with the full width of the table but of course anybody change the width of the table I've got to change the width of that text box again which gets tedious very very quickly so instead here's what I'm going to do I'm going to click into my table and then right click on the gray box on the left hand side of the first row if I right click going to insert a row above and having done that I can select all the cells in the row and then I can right click on the selected cells and I can do to merge them and then what I can do is either format that and type in the text again or I should also be able to click into the text box that I typed in that I created earlier and press ctrl X two cuts for ctrl C to copy and then I can click into that new textbook that I've just created and press ctrl V to paste so I just I just widen or increase the height of that row a little bit and the great thing about that is that now when I change the width of an individual column the width of the header cell changes automatically as well that works a little bit more neatly okay so I appreciate that's not very exciting in terms of what we created but hopefully that seems to demonstrate the basic principles of creating the new report from scratch connecting to a data source and then writing a query based on that data source and presenting it in a basic table in the reports problem we've got this sage is that this report file is just a local file sitting on your machine currently sitting in my movies folder basical similar movie report to RDL as we pointed out earlier on so currently i'm the only person who can see it what we need to do next in order to make it available to other people is to deploy this to the report server now this is one of the more technical aspects of this product so we're not going to go into a massive amount of detail at this stage you'll will reserve that for a later video again just to show you the basic steps involved in deploying your report to the report server so that other people can view it first step is to close down this report and it asks you to to save any changes you made if I click yes to save it that will close it down and it will just sit safely in my reports folder in that project what I need to be able to do in order to deploy my report project is I need to be able to specify the report server now again the report server is available have been configured if you followed video number one in a series or means sold sequel Server 2016 and installed the reporting services component as well in order to inform your project what your report server is called don't need to right click on the project name at the top of the solution Explorer and then choose to view properties so if you select properties from the drop-down list you will then be able to find a property called target server URL it may well already be filled in with the default placeholder URL but that's me no good for a particular example our URL has a unique name our report server so how do you go about finding out the name of your report server one way to achieve this is using the tool called sequel server management studio and again this is all explained in part two your video using sequel server management studio how to install and configure that application what I'm going to do at this point is just open up from the start menu the tool called sequel server management studio I've created a quick shortcut in my style menu but you might have to do a quick search for it or browse through your list to find Microsoft all Server 2016 and you ought to find them Microsoft sequel server management studio application that's in there as well now if I just click on this icon or shortcuts to open the application I'm going to run into a small problem fairly shortly I'll let the application open up what we're going to do then is we're going to connect to our reporting services server so in order to achieve that you need to make sure that that drop-down this at the top says reporting services rather than database engine or or any of the other ones so reporting services in this case the server name this again the exact name of your server will depend on exactly how you install the product but in my case is my sequel 2016 training instance on my local machine so all I'm going to do is click the connect button and then it will show me a list of all the items that belong to that report server in the object Explorer window at the top left now to find the report server URL what I should be able to do is right click on the top node of that server and then choose properties but as you can see annoyingly for me right at this point so the properties option is grayed out I don't have some permission to view that particular property which is highly annoying so a simple quick way to solve this problem just temporarily is to close down sequel server management studio and then head back to the Start menu and find a shock if it can either be the one in a long list or a quick shortcut that you've added and rather than just clicking on it right click on it and in this case in Windows 10 I can choose the option that says more and then finally choose run as administrator when I do this I'll be prompted to allow the Machine allow the application to make changes to my machine so they could yes not sir that's fine this particular example and then eventually the application will load again and there it is sitting on my taskbar I can just click the connect button to get to reporting services again and then finally when the node appears in the object Explorer I can right click on it and this time I am allowed to choose properties fortunately if I choose properties from the list I've got a simple property here called URL and I can select and copy that URL then I can cancel from this dialog box and I can close down sequel server management studio is no longer needed head back to my movies project I've still got my property dialog box displayed here if it wasn't displayed I could have simply right click to movies again and chosen viewers properties and then in the target server URL I can simply select the taxes in there and remove it and then replace all that with the URL that I've just copied at that point I can click OK unless essentially the the configuration for this done that was actually the most difficult part of the entire process the actual active deployment is incredibly simple to achieve having set up my single reports in a single project if I wanted to deploy all of the reports I've currently created all I need to do is right-click on the movies project name and then choose deploy now in this case I'm also going to run into another problem it's related to the same problem I've just had in trying to V the properties of my report server but if I click the deploy option in this menu I'm going to get a little output panel pop up and then immediately receive an error so now I don't have sufficient permissions to deploy the project now again this is kind of related to a similar issue as I've just seen and the solution is essentially the same one I'm going to hit the error option and then close down the error list and then close down the output window then I'm going to close down my entire movies project and in fact Visual Studio entirely by putting the cross in the top right hand corner once I've done that I'm going to head back to the Start menu and find a shortcut for Visual Studio 2015 and the same idea I'm going to right click on it and choose more and then choose run as administrator when I do that I'll have to confirm that I'd like to allow it to make changes to my machine and I can do that and then when the application launches this time I'll of course need to reopen my movies project so I open up back into the start page but I don't have access to my movies project currently so I should be able to open it either from the start page with a recent list so I can click movies here alternatively I can head to the file menu and I can choose recent project constellations and selected from that list I can also choose to open a project or solution from the open option and then I could browse for my movies project or solution on the desktop and then open up the solution file by double-clicking anyway any of those options will work it's entirely your choice but last thing I have access to all of my objects again and I'm just going to achieve that the same thing as I did previous I'm going to right-click on my movies project and again this time to deploy what I will hopefully see this time isn't an error message I'll get a progress list telling me that it's deploying individual items to my report server it's not a very long list of course because we only have one single report nonetheless at the end it will tell you whether the deployment is succeeded and in this case it has okay so that's all well and good we've successfully deployed our report server projects but how do we now go about viewing them well the default way that you view the reports you deploy to a report server is via a web browser Microsoft I develops a basic browser tool it used to be called the report manager application I believe they change its name now to the reports portal it miss a massive opportunity to call it just the report but anyway that's just me so what we what we need to do is find out the URL of the reports portal so to find that out there's an yet another application you can use which will have been installed that you installed sequel Server 2016 and the reporting services component so back into the Start menu again I don't actually have a quick shortcut for this yet in my Start menu so I'm going to browse through my list of other all items find sequel server 2016 on Microsoft sequel Server 2016 now in this folder there will be an option called reporting services configuration manager I'm just going to take the opportunity to right-click on that and choose him to start and then I'm going to resize it by right-clicking and choosing resize and then make it small and just drag it in to my sequel server 2016 section it's gonna do a quick little rearrangement here anyway once you've got that created I could have just of course clicked on that shortcut here or clicked on the shortcut I've just created that will open up yet another application if I click yes to allow it to make changes I've got to choose to connect to a report server instance and I want to make sure that I'm connecting to the same report server instances I've just deployed to so in my case that's not sequel 2016 its sequel 2016 training if I select that option from the list and then click connect now there are several things you can do with this in this application we'll see hopefully a few more of them a little later on this tutorial series all I'm interested in right now is where to go to find my reports I've just deployed so there you go you got a page there called web portal URL if I select this then all I have here is a quick shortcut that I can click on to try to access that web portal fire my default browser which you might case happens to be Chrome so if I click on this link knowing I can't right-click on it just a copy the URL into a different browser and you'll see why that's important in just a moment click on that link to launch the report server bottle and again another issue related to the one that we just seen I don't currently have permission to view this folder if I click OK then I should end up on the home page but I don't have the report project despite that I've just deployed so here's an alternative solution I've got the URL in the address bar up at the top of Chrome I'm going to click on - and copy that URL and then I'm going to fire up a different web browser Microsoft as you probably would expect recommends using their own particular brand of browser Internet Explorer is fully supported I believe edge is alive not really used edge that much personally I also say I tend to use Chrome more often than anything else what I'm going to do just for an example this example is I'm going to right click on my internet explorer icon here and choose more and choose run as administrator just as I have done previously allow this to make changes and then when it does fire up I can click into the arrest bar and paste in the URL that I've just copied and then just hit enter to browse to that red portal I'm going to call it a portal from now on and so here we go okay so the silikal Server reporting services portal I'm going to close up tap down in Chrome it's pointless having that they're available in chrome so you can see them in this case I've got a folder here called movies this is the project that I deployed and if I click onto that project it will show me all the items that I have deployed to it so I click on through that reports it will then load the reports in the web browser and show me all the results the beautiful thing about this of course is that any user with permissions and access can now access this report and view it on their own machine so you're not restricted to viewing the rewards just on your own personal machine you've got a few basic things you can do here you've got the basic paging control so you can navigate between the different pages and see data on different pages of course you can choose to print it directly there's a print button at the top you've got basic find search feature you can zoom in and out you'll also export it quite neat they're quite like this feature actually you can export it to various different formats so if I were to choose for instance excel and then what I can then do is to save it somewhere so and use the same as option and stick it just directly onto my desktop to make life slightly easier and once it's been downloaded you shouldn't take too long I can browse back to my desktop give that a quick double-click to open up the file and that will show me all the data listed out in an Excel workbook it's pretty neat if you ask me so I feel the very very basic things you can new once you've deployed your reports from this point on everything else is just essentially a repeat of that just learning about the intricate details of all the other objects one last quick thing I'm going to quickly talk about is how you deploy a single report rather than an entire project so I'm gonna head back to my movies project that I've still got open I've just deployed of course but I'm going to close down the output window and rather than create a brand new report from scratch I'm gonna cheat ever so slightly and just copy and paste the one that I've just created here it's easy to do I find in the solution Explorer so I can select that report and then just press ctrl C to copy and then ctrl V to paste now get a copy of simple movie report I can rename it just by using standard usual techniques click on it a couple of times alright click on rename when you're renaming it in this window it's vital T don't change the extension at the end don't change the dots RDL so I'm just going to call it on something like movie matrix and charts so we'll just use a couple of slightly different more interesting objects rather than a simple boring old table so I've renamed it and then hit enter I can hit enter to open it up or just double click on it to open it and then I returned to the main view with my basic table I get the parameters bar reappearing so I can just right click into the background and choose view parameters to turn that off and now I just want to delete the table that's already in the report so a quick simple way to do that is to click and drag a box partially around the table then just press the Delete key on the keyboard to remove it I've still got my data source and my data set I've still got access to all the data that the query is returning so all I need to do now is add a couple of different objects to the report to show information in a slightly different way I'm going to start with the simple matrix so I am going to right click somewhere inside the reports and choose insert matrix a matrix is a type of table but the interesting thing about a matrix it's got grouping applied to it automatically so just in the same way that I can drag fields into the table I can drag fields into the matrix think about things like I guess a basic pivot table in Excel terms or a basic cross tab reports in Microsoft Access terms if you're familiar with those so what I'd like to be able to do is add something to the rows group by I'm going to drag in the genre column to the rows group and then what I'd like to do it there and insert some units of columns group I'm going to put the certificate now that you into there or the certificate field into the columns group what that will do if I were to run the report at this point or preview it is not show me 1,200 rows one for each individual film in the database but it groups all the action films together or get adventure films together all the films are the 12 certificate together all of those are the top a certificate together in one single place which is quite nice so back into the design view the last thing I really need to do here is choose what data to display so as you can imagine if you're grouping busy or trying to perform some kind of aggregating so I want to either find the sum or the average or the min or the max or the count of some of the field I guess all I'd like to at this stage is just find how many films there are for each combination of genre and certificate so I simply drag in well any column it's all they'll do for counting if I drag in the title column into the data region to count that column I can click onto the text they will be highlighted in blue there then I can right click on the blue highlighted text and use summarized by and then choose from one of the lists of aggregates such to count in this case if I were to just preview the reports going now I get a list of family films there are for each combination of genre and certificates which is pretty meat back into the design view then I'd also like to do a tiny little bit of formatting so I get different header or the head of standards looks a bit cleaner so I can go back to the design view I can select those genres cell there and as we did earlier on I can just quickly change still colors and font colors I'm going to make the font bold as well if I just make sure I've got that cell selected properly and then again for the certificates and changes background color and maybe its font color as well in this case and then also make that bold as well think okay so another quick little preview that helps to read I guess again we'll go into a lot more detail on how a matrix works in later videos in the series what I've also like doing the same report is adding a quick chart that shows me the same data no I guess a more visual way back into the design view you can have as many different items to your report as you like if actually there is a hard limit to the number of items that it can contain but it's a pretty pretty big limit so we can certainly achieve it I matrix I'm a chart in the same report so I'm going to right-click somewhere down below the matrix and choose insert and then choose chart this time and I'll get a little dialogue box ask me what kind of chart I want to insert I'm going to go with this second option here a stacked column I click static column and then click OK it looks as though I've got a chart with data already inserted into it but don't be fooled by this the reporting services is not what you see is what you get if I were to preview my report at this age I get basically an empty chart there's nothing doesn't know what to display yet so back to the design view to tell it what the display I can click into the chart and then I've got this option of the category groups if I cook them a details option I can choose in this case I'm going to do Chandra so that would give me a different column for each genre in the dataset and then if I click back into the chart region again let's say I wanted to display for the value that I'd see count or film name so to display values I can click the green plus symbol just to the top right hand corner there and I'm going to choose title and that will also must be a silent count this time I don't need to change that quick preview again and just have a quick check and you see the chart is now populated but might if I saw the genre names for every single columns about the design view and then if I were to right-click on any of the headers or the column names genre a genre B I can choose horizontal axis properties everything's about properties in reporting services and then as a property they're called intervals which I'm just going to change manually to the number one and if I click OK at that point and then preview the report again I'll get one label for every one column that's basically the interval does last little quick thing I want to divide each column up by the certificate back to the design view and then again using the chart data panel on the right hand side I can choose a series group so I can click the plus symbol just the top right hand corner there and then choose a certificate and then if I preview there for one more times going to look a bit of a mess not particularly readable but a little bit more interesting I guess you can always resize the charts to give it a bit more space and make it a little tiny bit more readable it gets a little bit unwieldy when you're trying to design it at that point however so yeah I guess the same principle you make a change preview in Revolt see what is that - the change is made polities for the color scheme that's a fairly hideous and again we'll talk more about that in a later video so that's the design view there's another very very basic reports created with a couple of different types of items and matrix in a chart what we need to do now is deploy it to the report server so that other people can view it deploying a single report is pretty straight forward rather than deploying the entire project you can just right-click on a single report we should probably close this on down there first I guess and if we right-click on a single report and then choose their deploy option because the project has the URL properties stored we can just click deploy and it will tell you that it's deploying that one single report hopefully you'll see a success message and if you have done you can head back to your report all I'm going to call it Ramon and then you can head back to the home page or back to the movies page if you go back to the movies folder that should not have refreshed in just yet so if i refresh this movie's page I should end up with eventually my new movie matrix and chart so I can click on that second report there and that will show me my new report with the matrix and the chart so fairly unsurprising and you can export these to different formats as well slightly disappointingly although for good and obvious reasons if you exported this chart to excel it's just export it as an image it's not an actual excel chart even though it looks very much like one but you can export it for I guess I know creating hard copies for printing or for emailing to people if you need to be able to do that so hopefully that's giving you a reasonably good basic idea of how the process works for reporting services you know how to install the software from the previous video series you know how to create a brand new blank report projects you know how to insert new reports you know how to insert basic items into those reports you know how to connect to a database how to query the database and then how to present the results of that query using three different types of objects a basic table a simple matrix and a simple chart so of course the rest of the video series is going to go into a huge amount more detail on all of these various aspects hopefully that's good enough to have given you a basic overview if nothing else hope you found it useful thanks for watching we'll see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials needing some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 279,088

Keywords: sql server, sql 2016, reporting services, ssrs, report project, ssdt, business intelligence, report server, deploy, publish, data source, dataset, sql server reporting services, report web portal, report manager, deploy report project, wise owl

Id: 7tfrUVV_pKI

Length: 59min 37sec (3577 seconds)

Published: Fri Feb 03 2017