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 SQL Server 2016 getting started that will 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 um there are a few key ones that you will need to make sure you've followed however so part one of course to begin with getting.
SQL Server 2016 installed on your machine this video explains how to get hold of a free edition of SQL server and get it installed and set up it's not absolutely necessary that you use SQL Server 2016 for this tutorial series um 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 the tasks we're going to perform that will work equally well in earlier versions anyway this video explains how to get hold of a free.
Edition of SQL Server the next video explains how to get that configured and install a couple of the management tools that you're also going to need so that's worth a quick check of as well um it's not a particularly long one just helps you get the tools installed um 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 um management techniques and a bit about how relational databases get designed and Skip straight onto part eight which helps you to get the yel movies database installed so the example datab we're going to use for this.
Tutorial series is all based around movies and this video explains how to get that database installed there's a link in the video description for this video that allows you to um uh install the database on your own machine and then finally part nine explains how to install something called SQL Server data tools so this is going to install essentially the templates that are required to create report projects so part one part two part eight and part nine a pretty much required viewing before we can start creating report projects everything else is optional but as long as you of those things we're.
In your list of all programs or all apps or whatever they're called these days if I 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 this 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 can simply click the.
Link in there called new project alternatively you can head to the file menu and from there you can choose new and then choose project or indeed just click the keyboard short cut control and shift and N so whichever option you'd like to choose select that option and you'll be presented with another dialogue box asking you to choose what type of project you'd like to create now Visual Studio is Microsoft's Flagship development tool it lets you create pretty much any type of project you can conceive of so it lets you build Windows forms applications or websites or SQL.
Ser databases or video games or um or mobile phone applications iOS apps and Android apps we're going to avoid all those tempting sounding options at this point and 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 addition of Visual Studio you've installed and any additional templates the business intelligence category only appears if you have indeed installed the SQL Server data tools component so from.
That 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 choose any other folder if you like and to do that you would hit the browse button I'm also going to change the name of my project from report project one to something a little more sensible called movies or movies reports or movies project if you prefer anything along those lines by default I've got an extra check box selected here that says create directory for solution so what I'm doing here is I'm creating a single report Ser.
A 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 desperately want to you don't even technically need one so you could always uncheck the box.
Posts Related:
- Microsoft Power Automate overview
- Excel Intermediate: Pivot Tables, Charts, and Tips & Tricks for Every Day
- Slide PowerPoint Terbaru dengan Tema Merah Putih Slide PPT Tema HUT RI
I'm going to leave mine checked at this point and then simply click the okay button to create my new report server
Project now assuming that that worked correctly you should be looking at a 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 right hand corner called the solution Explorer which lists out your solution items and any projects listed in there so in this case we've got a movies solution with a movies project and any items it belong to that project organized in separate.Folders what's actually happened in terms of your machine if I just browse back to my desktop briefly by holding 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 a file called movies. 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 all the items that 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 report 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 option here that says add new reports but what that does as it turns out is launches a simple little wizard that tries to hold you by the.
Hand and lead you down the Garden Path to do all the VAR steps required to create a simple report now personally I don't tend to enjoy these sorts of wizards I prefer to do everything by hand and there's certain things I don't want to do right at this stage that would the wizard would force me to do to do so what I'm going to do is cancel out of the wizard 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 for that as you can see control and shift and a anyway way 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 report I don't want to launch the report wizard although I could do that by double clicking that item all I'm going to do is select the report and I can change it name in the name box down at the bottom it's absolutely fine to change the extension here in this dialog box so I can completely remove what's in the box already and over type that with something like I don't know uh let's call it simple m report I could spell movie properly that would help um simple movie report there.
We go so I'm not going to bother with the uh 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 to the main area of your screen this is the report that we're about to design but also we've got we've created a file here we've got created a simple movie report. RDL file RDL stands for 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 if I just browse back.
To the movies folder you'll see I've got this simple movie report file sitting in that folder as well 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 you've created a brand new report server project and you've inserted a single blank report into it the next step is to start adding items to the report um and learning a little bit about what you can do to manipulate it just to give you a very brief worldwi tour of 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 there's 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 there 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 um there are a few other windows that are open currently called tabular.
Model and team 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 can happly just close them down by selecting them and then clicking clicking the crosses in the top 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 onto 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 we'll use fairly shortly to create a
Connection to a database and there's another important window that's 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 report window has got a couple of extra panels that aren't controlled in exactly the same way at the very top I've got a little panel called parameters and at the very bottom.A little panel called um 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 either of back I can go back to right click View and bring them both back we're not going to be using the uh.
The parameters section for this particular video but um I'm so at this point I'm actually going to hide it by choing View and then choosing parameters it's easy to accidentally close down a window that you didn't intend to so for instance if I 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 if I head back to the view menu and then look at the bottom of the list that there's the report data window now sometimes this menu isn't quite so obvious um for instance if I were to.
Click into the solution Explorer window first and then head back to the view menu um I don't know if 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 that's foxed me a couple of times in the past so there you go there 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 toolbox is one obvious way to add items to a report if I click onto the toolbox tab at the left hand side there's a list of different reports.
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 at the top leftand corner of the report it'll be selected by default so you'll see the little selection handles appear at the uh 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 patronize you too much by explaining how those work other than to show you that you can resize things and move things around an alternative way to insert objects is to.
Simp 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's tucked away at the left hand side to begin with should you want to delete any object in the report you need to make sure that it's selected of course first so if I click somewhere inside that text box it becomes selected I can also just click and drag a selection box that at least partially encloses the object and that selects it.
Too another way to select objects is using the properties window so at the top of the properties window there's a drop down list that explains what you've got selected currently so if I click the drop down arrow at the top there it provides me the list of all the objects in the report so if I select this option called textbox 2 it will select that object in the report for me to delete it I can simply click the or 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 um 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 report again I've misspelled movie apologies basic movie report um entering the text you don't press the enter key if you press the enter key it actually throws a new line in the same text box which isn't what I want so I backspace that to enter the text simply click somewhere away from that text box in the report in terms of formatting you've got all the basic.
Standard 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 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 there's a few different ways I can format it there's a basic formatting toolbar you'll recognize the font 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 slightly more options available in the properties window than you'll see are available from the toolbars but there are lots of duplicate object options as well so for instance the background color rather than having it set 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 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 the overall process is the focus for this video so I'm just going to change the background color and then perhaps the 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 can choose 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 them is all fairly straightforward but of course what we're really interested in doing in this particular report is displaying a list of films from a database so assuming that you followed video number eight to install the yal movies database in an instance of SQL Server what we're going to do next is create a connection to that database using something called a data source and then create a query that returns a set of data 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 Al also have spoted those folders sitting in a different form in the solution Explorer shared data sources and shared data sets so the idea behind the the difference between the two folders basically in the report data 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 shared data set we'll go into.
That in a lot more detail in the video that we'll make on data sources and data sets for now we're going to stick with just basic embedded data sources and data sets so to create a data source connection to our wisel movies database what I'm going to do is right click the data sources folder in the report data window and choose the only available option add data source and that provides me with a 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 helps me to spot what data source I'm working with when I've got multiple data sources in the report.
I'm going to connect to Microsoft SQL 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 well what I'm going to do instead is use the edits button to to give myself a dialog box to help 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 to populate and often it ends up empty anyway you're welcome to give it a quick try if you like but I always find more success by typing in the server name now again if you followed the video.
Series um getting started in SQL Server 2016 we're going to be referring to the SQL 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 a full stop followed by a backlash that's just a shortcut way to reference the local machine then the name of the instance of SQL that I installed was called SQL 2016 training so I type all of that in at that point what I should then find is that I can select from a list of databases installed in that.
Particular instance of SQL Server so from the drop down list I should find that I've now got access to my movies database and again that's installed or you can find out how to install that from video number eight or part eight of that video tutorial series okay so at that point if it makes you feel better you can click the test connection button I feel better um the test connection worked um or succeeded that's basically to test credentials um so if you're providing uh um 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 SQL server in the first place so all things that I've set up work according to the settings I'm using here so all I should need to do at this point is click okay my basic connection string gets written out for me and then I can finally click okay 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 movies database I can do this.
In a couple of different ways but the 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 add data set although that means that I've got um 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 add data set I've then got to choose um which uh to use an an embedded data set unless I have any shared ones which I don't so I'd have to choose embedded and then I'd have to click on the drop- down list to choose my data source and then I 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 and instead choose add data set that will prefill some of the options on the dialog box so you can see that it's already selected an embedded data set and it's already selected the movies data source as that's the one that I'm using to create this one I'm going to change the name of my data set so it's not called data set one um let's call it something simple like uh I know movie list something along those lines and then I got a variety of choices for how I could return some data I could if I'm.
Good enough I I think I think I probably am by this stage have taught enough SQL Server courses to know how to write a basic select statement but you could write out a simple select statement in this grid if you know how to write SQL code you don't get any help here there's no Intellis sense or keyword coloring or anything along those lines so you have to know your database and SQL um or transact SQL SQL code quite well to make this work alternatively you could choose to use a store procedure if you've either created one yourself or maybe a nice developer has created one for you.
That provides you with a list of data you can choose procedure and then select that from the drop down list sadly I don't have any custom store procedures all these are system store procedures from the movies 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 this is a nice little gooey Tool uh graphical user interface um not squidgy when I say gooey GUI.
Graphical user interface that helps me build up my basic query I'm going to find it useful if I maximize the window here by clicking the 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 add table button up at the top or indeed just 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 direct table I'm.
Double clicking on these by the way to insert them I can also just click the table and click add so if I choose the film table and then choose add that will insert it as well and then I'm also going to choose the genre table so I can double click on that one and then I'm going to close down the ad table window by clicking the close button and that provide provides me with access to the four tables I've just selected now you can reposition these things although it's absolutely not necessary it depends on how fussy 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 um I'll try to.
Avoid spending too much time doing that because that's going to get really boring um 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 if you want to understand what that means in detail the entire video on how the movies database Works explains that there's also a video on designing and creating relational database that goes into a lot more detail so 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 direct 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 that I can select and then a certificate column from the certificate table from the film table I'm going to choose the 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 from scratch if you knew your database well enough you can.
Achieve all sorts of other things here here as well you can assign aliases to columns so for instance the full name column isn't particularly descriptive I'm going to give that an alias I'm going to call it director so I'm going to type in the word director in the Alias box next to the full name and if I hit tab or enter that will rename that column as director making it slightly easier to reference I can apply sorting to some of the columns I can apply filters and all those things will do is add aware 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 is right now is click okay to return.
To the main dialog box which has written out the query for me and then finally click okay one more time to add my data set to the report with a list of all the columns just in case you need to modify either your data source or your data set 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.
