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.
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.
Posts Related:
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.