SSRS 2016 - Comparing Report Builder with SQL Server Data Tools

welcome to this was our tutorial on sequel server reporting services 2016 in this video we're going to compare report builder with sequel server data tools which are two separate bits of software the Microsoft release which allow you to create reporting services reports so this video is designed basically to compare the two products so that you make the best choice about which one to use we're going to start with a quick overview of the two products so you know what they're roughly designed to do in this some of the similarities between them before you move on to essentially taking you through building an entire report from scratch in both bits of software so you can see where the main differences are so start with installing the software then creating new reports creating data sources and data sets to connect to your databases and return data then look at how you work with report items in the two applications one of the big differences for me is is the way you go about writing expressions the expression builder is somewhat different particular to tools then we'll look at how you save opening import files one of the nice things about the two products is that they can use files created in either one interchangeably then finally we'll look at how you deploy and then view the deployed reports and then download these reports again so that you can make changes to them so quite a lot to do here so let's get started now just before we start talking about the differences between report builder and report server projects it is worthwhile noting that the ultimate aim of both products is to allow you to create report files with this RVL extension now will that be exact where you go about doing that is slightly different in the two different products that target audiences are slightly different the ultimate end result is exactly the same you can create these RDL files which can then upload to a report server so that anybody with access to that server computer reports there's absolutely no difference in the type of file you create in fact a file created in report builder can be opened up in a report server projects and vice versa the exact way you design them is subtly different as well that's what this video is all about of course but in practice there aren't a huge amount of differences here's a basic report design in a visual studio project a report server project and if I preview the report it shows me a simple tabular list of films from a movies database and exactly the same reports in report builder the designer looks subtly different but the end result is exactly the same if I were to run the report and if I were to upload either of these two reports in the report server the end results for the end user would be exactly the same as well so here's the reports portal viewed through a web browser Internet Explorer in this case I'm gonna grab the reports all as I called it in the previous video and if I browse through into this movies project I can have a quick look at this simple movie report that's been deployed and there's absolutely no way to tell at this point whether that reports have been created using the report server projects or report builder the end result is exactly the same it's not just about basic tables either you can create slightly more sophisticated reports using interesting matrix groups and charting data so there's the design view in reporting services or reports of a project if I have a quick preview of that one you'll see a grouped table referred to as a matrix and a not particularly attractive chart but it's not unless it's a chartered set of data and likewise the same report in report builder so there's the same matrix and the same yards if I run the report you'll see exactly the same end result and of course once again once once it's been deployed to the report server you can add usually exactly the same end result so I'm assuming the similarity is I think just as important as understanding the differences between the two products will do in this video is take you essentially from start to finish through creating this simple report using both products to show you where the main differences lie the first step of course is to download and install the software and at this stage report builder is definitely the simpler easier product to use and that's going to be a bit of a recurring theme throughout the rest of this video actually so to install report builder you can use your favorite search engine and search for report builder 2016 you might as well grab the latest version available and you should find a quick link to download Microsoft sequel server 2016 report builder if you click the link you'll be taken to a page that allows you to download and install it we've actually already got a video that explains this process in a bit more detail so I'm not going to go through the entire process right now we've got a separate video as part of our report builder 2016 series not very very fleshed out just yet but it will be don't worry so there's a little video there that explains the details of installing it it's a pretty small download it's just about 22 megabytes in size and the application itself does install this pretty small and lightweight so if you download the application and then run the install the files you will end up in your Start menu with a new folder sitting somewhere in there that stores a quick shortcut to the report builder application and want you to call that installs you can just click the shortcut to launch it now getting calls of the equivalent software for creating report server projects it's a little tiny bit more complex even understanding the name of which product you're actually installing can be a bit of a challenge to start with so what you want to search for if you're going to use Google to find it is something called sequel server data tools and you want to make sure you are searching for the 2016 version so sequel Server 2016 data tools or sequel server dates tools 2016 either those will do so that should take you to a link fairly shortly for downloading sequel server tech tools or SSD t for short and if I click that link that will take me away to another page now there are multiple different versions available and you'll see a lot of information on the page that you've browsed to there will like talk about something called Visual Studio as well so sequel server data tools isn't really a separate application at all unlike report builder sequel server data tools is really just a set of templates that are used via another application referred to as Visual Studio so you can download and install sequel server data tools in a couple of different ways you could opt to install Visual Studio first and then install the sequel server data tools templates within Visual Studio now let's just take up quite a large amount of space on a harddrive a typical base installation for Visual Studio can take a building so last time I did it was about fourteen gigabytes for the default installation so significantly more than just a 22 megabyte download for report builder the sequel server data tools adds another couple hundred megabytes I believe on top of that so it's a slightly more heavyweight application you might say now you don't necessarily need the full version of Visual Studio you can just opt to download the sequel server data tools component and that will install a lightweight version of Visual Studio refer to visual studio shell I believe but it doesn't give you the full set of tools available in Visual Studio now all these products are completely free you don't need to pay for them if you want a bit more detail on exactly how this works we've caught of course got a separate video that explains how it works so part of the sequel server 2016 getting started series because the video here that refers to installing sequel server data tools which takes you through the entire process in a little bit more detail than I'm intending to go through here so again once you've accessed or downloaded and installed sequel server data tools what you're then need to do is launch the application virtuous visual studio so from your Start menu what you can do is search for not Microsoft sequel server data tools although you might actually find a shortcut for that in your Start menu what you're looking for instead is a shortcut to visual studio so the way that you use sequel server data tools is by running the visible studio application that's been installed once you've got the software downloaded and installed you'll want to get on with creating reports as quickly as possible and again with report builder that's a remarkably simple process all you really need to do is start the application so I head back to my Start menu I've actually created another quick short to cook the application here in the main area of my Start menu so my tutor launch support builder it's an avoidable of creating reports in report builder because as soon as you launch the application if I just clear this getting started panel it's already created a basic report for me now that's no way mirror is simple to do in report server projects or with sequel server data tools what I have to do first of course is I need to launch Visual Studio and again I've created a quick shortcut forward to new circles section here at my style menu such to launch Visual Studio it's a much larger application of course it takes a little longer to load but when it has loaded I can't just create a simple report by itself in Visual Studio or sequel server data tools what I have to do is create an entire project to contain my report so to make that work I can click the new project link on the left hand side and again we've got a video as part of the main sequel server reporting services project that's a video series which explains how to do it in a lot more detail and if I've got the sequel server data tools installed when I've chosen to create a new project I'll have discipline business intelligence category here no reference to sequel server data tools somewhat strangely but sequel server data tools installs this business intelligence category in the templates for your visual studio projects so I can choose my report server projects I can choose your location to store it I should change its name as well I suppose to give it a simple name let me call mine I'm going to movies and then I'm going to store it to my desktop and I click OK that will then creates the report server project assuming that nothing goes wrong I still don't actually have a report at this stage so what I need to do now is create a report in the project I can do that using the solution Explorer window so I can right click the reports folder and I can either choose to use a wizard to create my new report which actually actually makes me create a few items that I don't want to create just yet so instead I'm going to choose the add option and then choose new item and then I can choose report from the list and then I can create a new report I can change its name if I like I'll leave it as report 1 when I fly click ad at that point eventually I'll end up with a report in pretty much the same as the one that I've just seen in report builder now of course the main thing that's different between the two applications is the interface itself although there are some similarities as a huge difference between using visual studio and report builder report build that's been designed as a basic application making it as simple as possible just to focus on authoring a report and designing it so it's designed to look somewhat like a Microsoft Office application so if you're familiar with things like Excel or Word etc then you should be reasonably at home and using the ribbon system where you can click on the different tabs to view the various different options there's a file menu should provide you with the standard options for opening and saving files and some extra options the basically the entire application is focused on just creating the reports and adding objects into it visual studio on the other hand is a little bit more complex than that of course in visual studio you have a huge number of different windows and panels open and available at any one time so I've got a solution Explorer window and a properties window and I report data window got a huge section of menus and toolbars each with huge numbers of different options available in them the the reason this is the case is that Visual Studio isn't just designed to allow you to create reports of course it allows you to create any sort of project you can conceive of it's Microsoft's flagship development application you can create websites and mobile phone applications and video games and all sorts of different types of projects in here so we're kind of shoehorning the report design into a much more sophisticated application now despite the fairly big differences in the interface between the two products the actual steps that you follow to build up a reports are pretty much identical the first step is to create a data source which is essentially a connection to a database which stores all the data you want to report on for this example what we're going to do is connect to a database in sequel server containing a list of different movies and if you haven't already got that set up we have a video series that explains all about how to install sequel server and how to install the movies database as well as part of that series so I'm going to assume that you've already completed those steps in case you wanted to follow along with this video so if you wanted to look for sequel server 2016 getting started that's the playlist which explains how to go about setting up and installing sequel server and the movies database assuming that you've done that I'm going to head into report builder first of all and have a look at the report data when on the left hand side if the report data window wasn't visible by the way you can bring it back by heading to the View tab in the ribbon and then making sure you check the report data box and then that will reappear at the left-hand side of the screen to create a data source I can right click the data sources folder and choose add data source and then I can give my data source a name so I'm going to call this one movies and then I can create a new connection that's embedded in my report now one big difference between report builder and reports Server projects is the ability to create something called a shared connection I can't do that in report builder I'll explain what a shared connection is when I talk when we go back through the report server project but for now all I can do is either refer to an existing the shared connection or create a new connection embedded in the report I'm going to use the second option there I'm going to use sequel server as my connection type and I could type in a connection string device could remember them off the top of my head so instead I'm going to teach the build option and that will display a dialog box I can type in a server name which in this case is I'm going to refer to Ares dot backslash SQL 2016 training that's just the name of the instance of sequel server that I installed in the earlier video that I like to explain sequel server 2016 getting started and then I can select a database that's installed in that instance of sequel server so I'm going to go to the movies database I can then click the ok button to write out the connection string for me and then click OK to create the data source now doing the same thing in a report server project is essentially exactly the same if I switch back to visual studio where my movies project is sitting I've got my report data window I can close this down again just like I can in reporting a report builder to bring it back it's slightly different I need to make sure that I've clicked somewhere inside the report design view and then head to the View menu at the top on the toolbar and then choose the report data window format is also a keyboard shortcut to bring your background Control Alt + D anyway creating the new data source is pretty much the same thing that can right-click data sources and choose add data source and again I can either refer to a shared data source connection or I can create a new embedded one so let's go for the embedded one be the organization of the dialog box is slightly different but the actual options are pretty much the same so I'm going to call this one movies and then I can click the edit button this time rather than build to build up my connections rink I can type in my server name dot backslash SQL 2016 and then I can set a big button 2015 training and then I can select from my drop-down this here the movies database again click OK and click OK and again I've got my data source for my report server report now both of the data sources that we've created so far are referred to as embedded data sources and all that means is that the data source is saved inside the report that you're working on and it's only available to that report now a report builder that's the standard way of working as a report builder is designed to allow you to work on one single report at a time you can open up multiple instances of report builder so you could open up report builder again and then open up another report and work on them side-by-side but they don't talk to each other they have no no sort of connection between them in report server projects in sequel server data tools or visual studio that's slightly different when you work on a report project you can work on as many different reports as you like you can carry on adding more and more reports and work on a whole set of report all in one single project now if I had to carry on building up the connection to my movies database every single time I created a new report that gets quite tedious so instead what I can do is create something called a shared data source I don't do that at the report data level I do that at the solution Explorer window so I can find my shared data sources folder and I can right click into there and choose add new data source I can then change the name of this States or some chemical this one movies shared just to distinguish it from my other movies data source then again I can click the edit button to build up my connection string and type in the same server name back slash SQL 2016 training and then I right click on the drop-down arrow I can choose my movies database and then click OK and ok again now that shared data source is available to any reports in the entire project so no matter how many reports I create I can simply refer to the existing connection rather than going through creating it each time there's still a little bit of work to do so in this new report I've just created a report to which I'm currently working on you see report 1 and reports here open at the same time I can right click on the data sources folder and choose add data source I can give it a sensible name I'm going to call it movies but this time rather than building up my connection string again I can just tell this to refer to a shared data source reference and then select that from the drop-down list so that will provide you with a list of all of the shared data sources in the entire project so it's not it doesn't save a huge amount of time but where it would save time is if you ever needed to change anything about that particular connection if I had 30 different reports in this project and they were all the embedded connections or embedded data sources I'd have 30 different reports to change if they were all using the shared data source on it and all I would have to do is double-click my movies shared data source and then edit anything that I needed to do in there and all of the reports would inherit the changes I made so that's one big advantage of using reports server projects or sequel server data tools you can create shared data sources while you're working alongside the reports once you've collected a data source in the report you men go about creating something called the data set so a data source is really just a connection to the database it doesn't actually return any data to return some data to reports on you need to create a data set which is essentially just a saved query in your report now again reporting services projects or Visual Studio sequel server data tools allows you to create both embedded data sets and shared data sets in report builder you can only create an embedded data set you can refer to shared data sets and you can refer to shared data sources but you can only do that if those shared data sources and data sets have been deployed to the report server and I haven't done that yet for this particular project so we'll come back to that a little bit later on in the video what I'm going to do in this case is while I'm in report builder I'm going to add a new data set using my movies data source so I'm going to right click on the movies data source and choose to add a data set using it I'm going to call my base set something symbol like movie data and then I'm going to create a basic query now I can do this in a couple of different ways I can write out a full sequel service you're like fomented by no sequel sequel code very well transact sequel I can write select statements directly into this box that's a little bit tedious because it's raw text I don't get any help there whatsoever if your DBA have been particularly helpful they may have created store procedures for you which allow you to select from a list I don't have any set or procedures available here these are just system store procedures that don't return anything particularly useful no movie data at least so I'm going to go back to the text query and what I'm going to do here is use a query designer instead now this is one fairly big difference between report builder and reports Server projects the query designer tool works very differently if I view the query designer in report builder what I get is a list of tables on the left-hand side which I can expand upon and then I can simply tick boxes by for the columns that I want to put into my report so let's say if I wanted some information from the certificate table I'll take the certificates column and then from the country table I can pick the country column then from the director table I can choose the full name column and then the film table I'll choose a variety to title and the release date a runtime in minutes and the Oscar wins that he met will do for the film table and I'll go for the genre table as well I can expand the genre table and choose the genre column now I'm allowed to pick columns from multiple different tables at this point because the database that I'm connecting to has a bunch of built-in relationship information it has information about primary keys and foreign keys now again if you don't understand what happens at this point I've created an entire a bit video that explains how to design and create relational databases and then a separate video that explains exactly how the wise our movie database is constructed so that's the one to watch if you want to understand how these tables are related the neat thing for us is that that I happen to know that the relationships are already built in so I don't have to do anything special here at all other than tick the boxes for the columns that I want now if that wasn't the case creating relationships is pretty awful crit in report builder if I uncheck the auto detect button what I can then do is have a look at the relationships from if I click on the drop down arrow GTO at the right hand side I can see the list of relationships that already exist so again the only reason these ones are here is because I've already set these up in the underlying database if those weren't there I'd have to create all of these relationships manually which is real real pain so if for instance I wanted to bring in let's say I wanted to bring in the studio table as I wanted to bring in the studio column if I'd choose the studio column that relationship hasn't actually been created because I've removed the auto-detect so to build it this is pretty awkward what I need to do now is I need to add a relationship by clicking the add relationship button I can choose a table from the left so it can be the film table I think that twice and look for bug in that dialog boxes always always happened from every version of report build that I've ever used and just on the table twice from the list also chosen the film table I can then choose a studio table from the list on the right and that goes in first time and then it's actually worked out for me happily the studio ideas connected to studio ID now it just so happens I named the columns in the film table t2 ID and the studio click table studio ID so the query designer automatically creates a relationship which is quite handy if that hadn't been done what I would have to do subsequently is click the Edit fields at the top and then a separate dialog box will pop up and that will allow me to select a field from the film table and the field from the from the studio table click OK and then the relationship has been created so when I finally click OK that will write out my select statement for me and I can finally click OK one more time and my data set has been created creating data sets in a report server project it's ever so slightly easier certainly when it comes to creating relationships if you if you don't have those set up already so I switch back to my movies project and I'm just going to close down report to just the time being and I'm going to choose yes to save it so if I want to go back to later on I can going back to report 1 I've already got my embedded movies data source so I can right click on it and choose to add a data set just in the same way as full report builder the dialog box is essentially identical I'm going to change the moment so it's called movies data and then let's have a quick look at building my query again I can write out a query myself by writing out a select statement or choose a stored procedure or indeed use the query designer and the query designer is slightly different in report server projects I think it's likely easier to use personally you could you get a slight sort of a visual representation of the database diagram as you add tables to it so rather than just ticking a bunch of boxes to choose the tables you can click the add table button up at the top and then you can simply double click on the tables you want to use so I start with the certificates table and the country table and the Treach table and the film table and the genre table I'm going to put in the studio table as well just for the time being and close down that window you'll see that the the diagram although it's a little bit messy because I've included so many tables but the diagram has appeared in the background showing you how all the tables are related so you don't have to worry about a civil dialogue box that report builder has writing trying to understand which tables are related you can see it visually again this is all reliance on the fact that these relationships are built into the underlying database but then I can simply check boxes for the particular columns that I want and I'll try to go with the same if I can remember exactly the same columns I selected from the from the other VIP they'd set and then I've got all those checks so that's essentially my data so all my data set now one of the really beautiful things about this particular query designer is if I didn't have a relationship built in so if I go for the studio table again let me just select that relationship and then hit the Delete key on the keyboard to remove it so there were no relationship information built into the underlying database your tables would not automatically connect columns as they have done for the tables I've added so far but creating a temporary join an ad-hoc join between tables is remarkably simple all I need to do is click and drag between the related columns or go from the studio ID studio ID and it creates an ad-hoc join for the purposes of that query much simpler if I finally click OK and then ok one more time I've got the exact same list of columns as I had for the report builder dataset cases now we've reached the stage where we've retrieved some data from our database we can get them with a process of displaying it in the reports and making it look pretty and that involves having report items to the report design canvas let's start by doing that same report builder so I set up a report builder a couple of different ways you can achieve this in report builder but all the tools you can add all the report items you can add are all stored on the insert tab in the ribbon so if I head to the insert tab the variety of different items I can display here so for things like simple titles you want might want to insert text boxes report builder actually comes along with a couple of basic default text boxes there's a little saucer that tells you when the report was executed by displaying the date and time and there's also one that allows you to quickly add a title she don't want to delete that one I can drag a selection box around it or just click somewhere inside it to select it and then impotently to key on the keyboard and then to insert a new text box I could just double click the text box tool that's one technique you by double-click text box that will be inserted to the top left hand corner another technique I could use is I could go back to the insert tab and I can click once on the text box tool and then I can click and drag or click once with the mouse to draw one in the report and a further way to insert a text box is to ignore the insert tab in the ribbon altogether I can just right-click somewhere in the background of the report and choose insert and then choose text box once it's been inserted its selected so I can choose to resize it you can establish all the techniques for Microsoft drawing tools and I can type text into it pull this simple movie report one spend too much time doing that and then I've got a whole bunch of different things I can go to format it so formatting a text box and report report builder involves selecting it first there's a whole bunch of standard formatting tools on the Home tab in the ribbon I'm sure you'll recognize many of these formatting tools from standard Microsoft products so there's a fill color tool and there's a font color tool and you make font Falls and center it etc after all the standard sorts of things you might expect to be able to do from any standard Microsoft product to actually display data from the data set the type of item that I want to insert is a table so if I go back to the insert tab in the ribbon subject can't just use a double click to insert a table in report builder because as soon as I click on the bus and I got a couple of options as a table wizard and an insert table tool the other just try to hold you by the hand and helps you out to set up the table including adding things like grouping levels I don't care about doing any of that for this particular video I just want to insert a basic table so simple thing to do here is just right click in the report and then choose insert table and then I can choose for each of the columns I can choose which field I would like to display so I can choose the title field I can also click and drag fields from the left-hand side into the table and or I can select the municipal field selector button so it's cheese run time minutes and then maybe the genre as well and then maybe just the add the Oscar wins just to wrap up you change column widths in the sort of standard way I can format cells in the table in the usual way so I can select items in the table and head to the Home tab to format them I can also format things using explore the techniques well so for instance if I wanted to format the release date so that it was a nicely formatted date rather than just relying on on the default format I could either right click on that cell in the table and choose view text box properties and that will display a separate dialog box with a number tab there I'm going to choose H from the list and then I'm going to go for this one there so is something UK date day month year if I click OK that's four months in that text box I could also use the properties window to format items in the report so to do that I would have to view the properties window first if I head to the View menu and then make sure that I can view the properties we know that will the paper at the right-hand side of the screen so for things like runtime minutes maybe I want to display that with a couple of decimal places so if I select the room time minutes column I can then find the format's property in here this is slightly more awkward way to apply formatting but I'll find the format property in there I can type in a format it looks as I've got a nice simple way to select a format from the drop-down list but sadly it's not populated with anything useful at this point so I can just type in a format of zero point zero zero and then that will format that cell as well okay so you can format things using the Home tab in the ribbon you can right-click on an object and view its properties or you can view the separate properties panel on the right-hand side now the basic principle is exactly the same in report server projects if I switch back to visual studio and if I wanted to insert a text box first of all well I haven't got a separate insert tab in the ribbon or an insert menu what I do have is this toolbox window sitting Kahler listed it left it at the left hand side of the screen so I click on this to this toolbox tab that provides me the same types of items I can add in from the report builder insert tab so if I wanted to insert a text box I can just double-click text box and that will be inserted into the report and I can resize it in the same way I can type text into it in the same way so call it's similar move the report formatting it is similar I've got a basic set of options on the toolbars so good so the summit bull's font formatting and some background because of formatting and text color form I think I finally slightly difficult to see with a new style menus as a fill color and there's a font color not particularly obvious I think you'll agree the tailor box is also a little bit poorly designed so if I choose to view the the back color it's kind of half chops off the right-hand side and I can't resize that dialog box is a little bit odd anyway I can set up the background colors in the same way and the font colors in the same way I can also use a properties window which is displayed by default that are to choose to enable it I could also right click on the text box and choose to view its properties in the same sort of way so if we wanted to go to the fonts tab I can then choose colors from the drop-down list and that the list is actually sensibly sized this time for skipper white I'll make it bold while I'm here as well I should also Center it in the in the cells I'll go to the alignment tab I can change the horizontal alignment to centered and then it's okay and of course I could have done all the same things from the toolbars or from the properties window as well inserting the table I can do that in a couple of ways of course I can go back to the toolbox I could double click table or I could click and drag table or I could just a right-click into the report and chose an insert table same techniques effectively if you've got a report builder apart from the click and drag one that's a little bit unusual so from the toolbox I can click and drag an item from the toolbox directly into the report which in report builder ISM you can't do if I go to the insert tab in the ribbon and try to drag attack box that doesn't work so one small tiny tiny minor advantage of using a report server project adding fields from the dataset exactly the same I can select them from the list like so I can drag them from the left-hand panel from the report beta panel I can use a combination of techniques I'm going to try to go for roof of the same same example as I put into the report build a project as well and the report builder report sorry I can change column widths I can change formatting all the same standard things at this point things are pretty similar which is quite nice you have to wait too much about what's different when it comes to adding items into the report I should just make sure I've formatted the date in the same way so I can right click the release date text box and choose text properties and same dialog box you saw in report builder there's a number tab there with a date option and with the same date format and it's okay again formatting numbers is the same sort of thing that can either do that you may dial a box so I can use the properties window again let's find the properties window and find the format property the same one that I just used in report builder and again I'm going to type in a zero point zero zero for the number format okay so that's the basics of adding items and formatting them I'm not going to go into any more detail about the more exotic items like charts and matrixes and all those other Styles sort of slightly more fancy advanced items or cover those in later videos in the series this is just to establish that adding items to report is pretty much the same in both products now one positive report design that is quite different between the two products is when it comes to writing expressions or calculations one common reason you're likely to do that is when you want to create calculated fields so just for example what I'd like to do is create a simple calculation which categorizes our films based on how many Oscars they've won so they've won more than zero Oscars there can be class as a winner otherwise they'll be classed as a loser so it's a bit harsh but there you go so what we're going to do is add a calculated field to this data set and the reason I'm doing that is because I'd like to show you the expression builder tool I'm going to start with in Visual Studio so in my report server project if I right click my movies data data source sorry data set and then choose to add a calculated field I'll get a dialog box which asked me to keep my field and name I'm going to call this one success and then to launch the expression builder this crops up in enormous amount of places in reporting services but there's a little effects button here could be effects button that was launched my expression builder tool now doesn't really look like much but it helps you out with writing complex calculations our calculation isn't going to be that complex as it turns out what we're going to use a simple if function now I can see a list of common functions or I could just start writing in my assumption if I know the syntax reasonably well it's actually when you start writing out functions and expressions where the expression builder in Visual Studio is much more usable than the one in report builder this is almost to the point where it's a deal breaker for me using the report builder version so what I'm going to do is I'm going to type out my F function and the nice thing about this is as soon as I start typing I got to the point amount of typed in the if function and then I open up a set of round brackets I immediately start to get help about how to complete the function so this set of parameter lists explaining how many arguments I have to fill in so I've got three there's an expression it tells me what sort of data type it is there's something called true part not massively helpful but it's an object and false part also is an object it also tells you what type of data or what type of reference the the function returns so an expression if you're familiar with their functions it isn't particularly surprising but expressions are logical tests so I want to test if the film Oscar wins column is greater than zero now I can insert that using the expression builder and we'll talk a lot more about this in a later video but I could just head to the fields list here and simply double-click Oscar wins alternatively I could start typing it in and again is when you start typing in expressions where things become incredibly helpful if I get to the point where I typed in fields followed by an exclamation mark I get a lovely little Intelli centralist there help you to work out what to do next so I can choose Oscar winds from the list and then hit the tab key to get that typed in and then I've got if I type in a full stop after that I've got another intellisense list which provides me more options and then specifically here is the value property that I'm interested in and I can then check if that is greater than zero you'll notice I've got this squiggly red underline at this point indicating now got a bit of a problem but I'll just simply haven't finished writing out the calculation yet of course so then I can type in a comma which brings back my tooltip indicating in Bowles that I'm on the next part of the function so the true part if that condition returns true then I can simply enter the answer I want to give which in this case will be winner now you'll notice that literal strings or text entered in double quotes has a subtly different color in the list it shows that it's that it's in the sort of dark brown color to negate this literal text rather than identifies or actual values like 0 for instance if I learnt have been another comma I can then type in the other answer which will be looser and then of a closer round brackets at that point the squiggly red underline should disappear most a fairly simple calculation if you have much more complex ones one of the other nice things about this expression builder is you can break a calculation onto multiple different lines so a common approach is to put each argument onto a separate line if I just click just inside the parentheses around brackets and hit the enter key that throws your new line in the dialogue box I can hit the tab key to indent that one space and then again after the first comma R I can hit enter to bring the next line down and then after the next comma so I can split up the calculation across multiple different mines to see what's happening the little squiggly underline will come back at various stages if I miss out different symbols or I put symbols in the wrong place if I put in a full stop instead of an exclamation mark there squiggly red underlines that indicates when I'm doing things incorrectly so let me just put that back in place again and when I hit ok and then hit ok again I've got this new field called success I'm just going to click and drag this into the table so that it sits at the right hand side of the table all I'm going to do now is try to achieve exactly the same results in report builder so if I switch back to report builder and then I can right click on my movie data date set and then choose out calculators field and then I'm going to get the same dialog box at this stage so I can create a new column called suppress and then I can click the FX button at the right hand side to launch the expression builder and to begin with it looks pretty much identical I get a text box I can type into I've got little categories to select from so again I could go to the common functions category I didn't show this for the expression builder in Visual Studio but I could have expanded the common functions category and then looked in the program flow category to find my if function that gives me a couple of little examples or one example in this case a girl's is just double click to insert da function but if I try to take the same approach as I took last time if I try to say equals i F and then open up some round brackets you notice at this point I don't get any help whatsoever something is like Anton zoom in to make this any clearer I should have showed you in fact let me just very quickly switch back to report or visual studio if I right click on my success field and choose field properties and then click the FX button again if I hold down the control key and roll the mouse wheel forwards I can zoom in and roll backwards to zoom out so can you show you the text that I'm writing I should have shown you that earlier apologies but I can't do that symbol builder it's just a basic plain text text box and the default font is quite small again I don't get any help if I start to write out the name of my field so of course I could go to the fields list and then just double-click Oscar wins but if I were trying to type it out from scratch I can type in fields exclamation mark no help whatsoever Oscar wins again no help whatsoever dot value no help whatsoever type in a comma and open some double quotes and stick in winner close double quotes comma double quotes loser and then close the double closing close around brackets there's no indication that the add that these are literal strings with different colors to the text compared to identify as there's no help if I miss out a commerce or puts punctuation character in the wrong place moves quickly red underlines etc etc most annoyingly if I hit enter oh that actually works this time that's amazing and if I hit enter what used to happen in older versions have report builder was that automatically click to the ok button so it was almost impossible you could insert extra lines by I think Colleen and the Alt key and pressing enter but if you just hit enter that was like hitting the OK button so that's at least one problem we've solved with this new version such as don't appear to have allowed you to tap in tech so I hit the tab key on the keyboard to attempt to invent that text it just tabs to the next available option in the dialog box so I can hit tab several times to cycle through the various options but it doesn't let me tab in a bit of text so that's a bit annoying so I can tell probably from my tone of voice I don't particularly enjoy using the expression builder in report builder it's just it's not as fully featured as the one in Visual Studio so this is for me this is almost a deal-breaker for a report builder at least you are aware of this the few limitations here the end result will be exactly the same the expression will works quite happily if I click OK and then ok I can drag the success column back into the table and then the result of that one actually finally run the report will be exactly the same so just be aware of those select limitations of the expression builder in report builder okay so now that we've designed our basic report we should really have a look at what it looks like when it's running now I appreciate we haven't spent much time making it look absolutely beautiful I haven't even bothered changing the width of this text box to make sure it fits the width of the table I haven't made sure the table is lined up with the edges of the text box either so I just shift a couple of little things around there are lots and lots of other techniques you could use here to make this look a lot better I don't really care about that at this particular point just to check what it looks like when it does indeed run in report builder head to the Home tab in the ribbon and click the Run button or you could just press the f5 key on the keyboard and that will take the report into the preview view so it kind of simulates what it will look like when it's been deployed to the report server so that you can then your end users can browse through the list of different results and then either print it or save it to a different format there's a bunch of different buttons you can click on here - to a fat tab so you can browse between different pages you can print it you can export it to a variety of different formats this toolbar doesn't look exactly like this when you've deployed the reports to the report server when you view it by the report portal there's a slightly different toolbar although the options available are the same so we took it back to the design view should i've decided that i don't particularly like the fact i've got some slightly narrow columns i can't display the film names on a single row i could head back to the design view and then make some changes to the report design and so on and so on I don't want to spend too much time doing this because this isn't really the point of this video what I can then do is just run the reports again either by clicking run or pressing f5 and then seeing it was made any improvements so that's how to do it in report builder you just switch between the run and design view by clicking that button on the Home tab in the rhythm doing the same thing in reporting services or report server projects in visual studio is pretty much the same except there's no separate run bottom what you couldn't set as a design tab in a preview tab so again just making a couple of simple little tweaks to make sure it looks reasonably sensible first if I hit the preview tab at the top of the report design view that will switch into the preview and will load all the data via the toolbar is ever so slightly different so you'll see slightly some all the buttons but the same available options you page between different pages you can print it you can export it to various different formats etc so neither of these reflect exactly what the report will look like when it's been deployed but it's close enough a similar simulation that you'll get the idea about what you'll be able to do to it switching back to design view means I can then make changes and I can preview again and design view etc etc so the same sort of idea for report builds outcome reports of the projects we haven't talked about saving reports yet and this works slightly differently between the two different products for report server projects in visual studio as soon as you choose to preview report as I've just done the report itself gets saved you can tell that if I just choose to make another design change of this report you'll see that as soon as I make a design change so this alt tab at the top of the report design gets a little asterisk symbol indicating that I've made a change that hasn't yet been saved now I can save that manually just by clicking the Save button or pressing ctrl + S and you'll see the little asterisk disappears I can also make changes if I take that table back and then change the width the gain of the report you can see the asterisk is reappeared but if I click the preview tab you'll see that the asterisk disappears so the report has to be saved before it can be previewed the mouse slightly different in report builder I haven't actually saved the report in report builder yet the reporting visual studio the one in the report server project will sit within the folder you created when you first design shows at George great the project so if I just switch back to my desktop which is where I created my movies budget in the first place I've just held down the windows key and press to be there just to do that quickly if I double-click into the movies folder there and then double click into the movies folder again we do that properly the movies folder then I'll find that like the two reports I've created so far have both been saved inside that folder they're both safe and sound now the reports builder file haven't been saved yet still it's still called untitled and haven't been saved anywhere so what I'm going to do now is to save this and you can save this in a couple of ways you can press ctrl + S or click the Save button or the file menu and choose save or save as so I just hit either any of those options I'll get a fairly standard file browser dialog box so I just point it to my desktop and I'll call this something like simple movie report okay and if I hit the Save button or hit enter that will save the file and eyebrows back to my desktop file in Windows and pressing D there's my simple movie reports sitting on the desktop closing them reopening files I know this is a little bit patronizing but it does work a little differently in the two different products so sports I'll just very quickly mentioning if I head back to visual studio first of all I'm just going to close the report itself service but the tab across at the top of the app the reports tab so I'm going to close down the entire visual studio application at that point and then if I head back to report builder I'm going to go down the entire report builder file app application as well so if wanted to reopen a file import builder I can head back to the start menu and reopen report builder of course the default getting started dialog box it will pop-up allows me to either open an existing file or go to that look at recently saved or used files if I choose to open a file again is my fairly standard file browser dialog box so I could go back to the desktop and I could open up my simple movie report alternatively I could go to my movies folder which is the one that corresponds to my visual studio project and just to prove that I can open up reports created in one application from another I can just happily open up report one by double-clicking on it and you'll see that this is the one I created initially in Visual Studio there's absolutely no way to tell that that's the case however other than that's where I went to open up the file of course because all the things I've done in Visual Studio I could have done in report builder as well so let me just close down report builder and I'll try to do the same thing for visual studio if I go back to the Start menu and I open up visual studio well I'll have to do first I can't open a report by itself I'll need to open up the project which the report belongs to so I can either click on a recently use projects in the recent list here or I could head to the file menu and choose to open or go to recent projects and solutions and open it up from that list or I could choose to open a project solution and then browse for it on the desktop so I could go to the movies folder then look for the movie solution file in there the SLN extension and then I can open that one up from sorry so tedious and boring but eventually I'll get back to the point where I've got access to all the reports in that project now if I wanted to open up the report build the reports that's a little more complex as well I can't just open it directly I have to first of all import it into the project so to do that I could right click on my reports folder and I can choose to add an existing item so that you to add an existing item and then if I browse to my desktop and I choose to import my simple movie report from the desktop you'll see that that now sits alongside the rest of the report in the project I can then just double click on that file in the solution Explorer to open it and when it finally does have been - there we go that's the one that I designed in report building here it's got that little execution time in the photo section down there so you can as I said earlier on start the video you can open reports created in either application in either other applications so they're they're completely interchangeable I'm just close down that report there and then I'm going to close down visual studio again I'm going to save changes to the projects visible dialog box hasn't changed Save Changes of the project so the project now contains an extra item that wasn't there before so I click yes that will take the project as well if I browse into the movies folder and then the movies folder in there you'll see that I've got a separate copy of the simple movement report so it's not just opening the file from its original location it's important gets into the new project making a copy of the original so that's what the one little thing to bear in mind there as well the final big difference between the two products is in how you get the reports you've created deployed to a report server so that other people can view them so currently these reports are just sitting on my local machine and they're not deployed to a report server what I would like to do first is show you the application you'll use to view the reports once they've been deployed so that's the app the new web portal or report all as I've taken to calling it which you view by a web browser I'm going to use Internet Explorer to demonstrate this if I just head to my Start menu and I'm going to right click on Internet Explorer and I going to use more and then choose to run as administrator the reason I'm doing this is because of the particular way I've got reporting services set up so I'm opening up a browsing to the reports portal web portal on the same machine that the report server is installed and there are some restrictions on exactly what you can do in that kind of situation I'm doing it this way because that's the way that I've set it up from the sequel server 2016 training video sort of training series so if you've followed the sequel server 2016 getting started series and you've installed sequel server and use the same configuration settings as I have then you'll have the same sort of issue so what I'm going to do here is as I say right click Internet Explorer choose more and then choose run as administrator choose yes I'd like to allow that to happen and I've already actually created a shortcut for me to get to my report web portal sorry switch to the favorites list I've got a report manager folder there and I've got a variety of different reports manager applications available from earlier versions of sequel server so I'm going to go for the one that's for my sequel 2016 training instance so if I select that shortcuts and that will take me to the home page of my web portal with nothing installed currently or nothing deployed so far now if you don't know the URL to use for your own particular web portal then you can find it using an application that will be installed alongside sequel server if you choose to install the report server component so again if you follow this video series exactly as I've created it then you should find it in your Start menu either through the list of all programs or maybe you've clicked it another little shortcut for it the the reporting services configuration manager application so if I open that application and click yes I can then choose to connect to my sequel 2016 training instance and click connect then the web portal URL is listed right there so you can find it in application if you don't have it already let me to take it out of that we've established where our report portal is all web portal whatever you like to call it so clearly we can see at this point there aren't any reports deployed to it now to save reports to a report server in either report builder or visual studio you need to know another URL which is the URL of the report server one way that you can find that out is again assuming you've followed the video series for installing sequel server as I have listed it's in the sequel server 2016 series you should have another application installed called sequel server management studio so again I'm going to open up the sequel server management studio by right clicking on it and choosing more and then choosing run as administrator and if I click yes I'll eventually be presented with a list of different servers that I can connect to so when the application does finally launch there it is I can choose to connect to reports reporting services an engine or a server or a database engine server I want to connect to the reporting services server and I want to make sure it's the same sequel 2016 training instance and if I've done that I can click connect it hopefully won't take too long for that to connect to that particular server and when it has what I'd like to be able to do is view it to property to that server so that you can get access to the report server URL so here we go if I right click the top node in the list and then choose properties what I can then do is just copy and paste the URL to clipboard by selecting it from that dialog box or I can just select it and then press ctrl C to copy and then I can cancel that dialog box I shall need it anymore I'll leave the application open just in case I need to get back to it later on so to make that work or to allow me to deploy a report in report builder what I'm going to do is go back to the start menu and again I'm going to right click the report builder application and I'm going to choose more I'm going to choose run as administrator then click yes to allow it to make changes to my machine and when the application has started I'm going to cancel the getting started window I'm going to open up a previously use file so I go back to the file menu I can choose to open up my simple movie reports so I can do that by clicking that option English and the list of recently used files in order to save this report to a report server I've first of all got to connect to one you know you might you might be able to see down the bottom here I've got a little status bar message that says I'm not currently connected to a report server there's a ham beetles shortcut I can click there to connect so I click back connect to all I can then paste in my report server URL the one that I've just copied ctrl V thanks connect and that will hopefully fairly quickly connect to my report server so that's great because that provides me the load of other options now but where I can save this file so actually deploying reports in report builder it's just a case of saving them to a report server location you need to make sure you've connected to it of course so if I head back to the file menu now and if I choose to view Jesus seen ours rather than a safe I'm currently pointing to my desktop folder where I open the report from but if I choose this link here that says recent sites and servers what I should then be able to do is select this report server from the list so I double click that report server I can then just simply paste or save my file in there by clicking the Save button so once I've done that now takes a little bit longer than saving it to your desktop for a folder where I can then do is switch back to Internet Explorer and if I just refresh this page I also find that my new reports eventually appears so it's my simple movie report and if I click on it now and if anybody who can access this server report portal can open up report they'll see the list of movies according to the design of the reports that we just uploaded so it's fairly long with the process appreciate it but it's actually a lot simpler to do in reports builder it's simply a case of connecting to the report server and then using that as a location in which to save your files now achieving the same results in Visual Studio is a little bit more complex but not actually that much let's start just by closing down report builder so let me just click the cross top right hand corner and then I'm going to go back to my Start menu and I'm going to right click my Visual Studio shortcuts and choose more men choose run as administrator' again and click yes to allow that to make changes when the application does finally launch what we're going to do is load our movies project again and then we're going to set the report server URL by modifying a property of the entire project so let's open up the movies project by clicking on the recent projects link left hand side and that will open up all of the reports I've created so far to set the report server URL then there's no handle it'll connect button that I can click on like in report builder what I have to do instead is right click on the movies project name and then choose views properties and it should still be copy to the clipboard but where it says target server URL if I just take away the information that's already there and then just paste in the URL that I copied earlier on I can then click OK and that's the report server set what I now need to do is deploy the project so communis in a couple ways actually I can either deploy the entire project or I can deploy individual reports in this case one of our magnitude of reports of report server projects in Visual Studio you can deploy everything all in one go rather than in report builder saving files one by one by one this is also quite important if you're using shared data sources or shared datasets because it's at this point that those items will be deployed as well and reports you although it's naturally do anything of course but reports to relies on the movies shared data source if you remember from way back earlier on in the video so let's right-click on the report project name again movies and I just choose deploy and we should see a little output window popping up and it should tell me that it's deploying all the different items not too many to deploy and hopefully that succeeds so once again back to internet explorer and if I browse back to the home page here and if I just refresh the home page again what I ought to end up with this time as well as my basic simple movie reports from my reports builder application I've also got now a movies project which contains all the reports from my visual studio project but also a data authors folder I click my data sources folder that's where the shared movies data source is stored if I just go back to the home page and then browse through to my movies folder I'll see that got report one two and the simple movie report I imported there as well so you report one that will show me the list of films again I could be report through all the film which points on say I didn't add any items Syria so just a blank reports but hey there you go now one of the nice things about having deployed this report project is that I can download files from it as well so one of the nice things about having to pull any reporting batch by the report builder or report server projects is you can download those report definitions so if I wanted to I could click on the little ellipsis icon at the top right hand corner here and choose to download the file if I then just choose to save this to my desktop I guess just quickly I'll stick it on my desktop and then click Save I can then open up that file in either visual studio or report builder to make changes to it so if I open up report builder I could then open up an existing file pointing to my desktop and open up report one and then I can make change to that and then save it back again to the report server songs I've been uploaded you don't actually need the original files at all you can just download the uploaded copies and make change to those and then reapply them again when you need to one of the neat thing now that having deployed a shared data source is that report builder can finally make use of those so just very quickly to demonstrate that if I go back to my Start menu I'm going to right-click report builder choose more and then choose run as administrator again and click yes what I can then do is once the application is loaded I'm going to create a new reports as close down the getting started window if I make sure that I've connected to my report server I should find that it's in a drop-down is because I've used it recently so I can just select the URL from the drop-down list this time rather than copy pasting it again click connect I'm going to create a new data source for the report so I can right click there and choose add data source and then this time I'm going to use a shared data source so I can hit the Browse button down here and then I can point to it's already pointing to the report server that I've connected to so I can just browse into the data source folder excuse me be shared and then that's available now to this project so that so it's a single report but every report like create now for from now on in report builder can also access that shared data source so I can create datasets based on it using this very poor version of the query designer everything from this point is exactly the same okay so there you have it our basic comparison of report builder and visual studio report server projects I'm hoping that the main thing you've gotten from this is that the end result whether you choose to use report builder or visual studio the end result is exactly the same you can deploy your reports to a report server and then other people can view them it's just the riki take to get there is subtly different in certain parts depending on which product you choose to use it really is very much a case of personal preference I find all the bits of software there you can get completely for free so you don't need any to spend any money for a particularly sophisticated bit of software you can use both products to create exactly the same sorts of reports as we've seen subtle differences in the exact where you go about doing it but basically pick the one that's right for you 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 meeting some exercises that you download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 98,513

Keywords: ssrs, sql server reporting services, reporting services, microsoft, report builder, sql server data tools, compare, vs, visual studio, business intelligence, ssdt, report builder vs ssdt, report builder vs visual studio, report, data source, dataset, deploy, web portal, wise owl

Id: LYroSet0asQ

Length: 61min 2sec (3662 seconds)

Published: Tue Feb 14 2017