SSRS 2016 Part 2.1 - Shared and Embedded Data Sources

welcome to the slides our tutorial on single server reporting services 2016 this video is going to give you an overview of how to use shared and embedded data sources in your reports we'll begin with a very quick comparison between embedded and shared data sources then jump right into it and configure basic embedded data source we'll look at how to set up a connection string and give you a quick overview of the basic authentication settings will then look at how you can create data sets based on that data source and then generate basic reports from those data sets and before we finally deploy it into a report server once it done that's we'll move on and look at how you can configure a shared data source how you can then set references to that shared data source in the report and then again generate data set some basic reports using that data source before we deploy those to the report server and have a look at some of the more interesting things you can do with a deployed data source so I'll have a look at how you can modify the connection string how you can overwrite it and replace it within it with a different version and how you can also delete your shared data sources from the report server so a few things to do here let's get started now just before we get started with the main part of the video there are a few things you'll need to make sure you've done first so we have a little play list called sequel server 2015 getting started that will help you out if you need help with any of this but you'll definitely need to make sure of course you've got a copy of signal Server 2016 installs in this little video here it shows you how to do that you'll also need a couple of management - also sequel server configuration manager and sequel server management studio stop when you tell you how that works you don't need all the rest of the information all these extra videos you don't really need unless you're desperately interested but you will need to copy the wiser movies database as well and there's a link in that video description that helps you to download a copy of the script for creating the database and then finally you'll also need to make sure of course that you've got sequel server data tools installed if you've done all that already then great stuff we can head straight into the start menu and I'm just going to open up visual studio 2015 from my quick shortcut in my Start menu you can search for it as well of course or just scroll through the list of all programs and when the applications opened up what we're going to do is start a brand new report server project so when the first one we have homepage appears or the start page appears I'm going to click the new project link on the left hand side and then from the list of templates I'm going to go to the business intelligence category choose report server project it doesn't really matter where I store this for the purposes of the video but I've got got this set to it so that it will be created on my desktop we can change the name if we want to I don't really need to add estate I'm going to leave it with a boring name of report project one and then if I click OK we'll end up with a brand new report server project last thing to do just to get started before we can create a data source is we want to create a basic blank report so if I right-click the reports folder and choose add followed by new item that will just avoid going through the wizard that will be launched if we choose add new report so I'll choose add new item and then from the dialog box that appears eventually I'm going to choose report and again I don't care about changing the name of the object I'll leave it cauldre for one click the Add button and then eventually when the report has been created we'll be able to get almost creating our data sources of course the whole point of creating reports in reporting services is so that you can display some data from a database in an interesting way but of course you can't display that data until you first of all created a connection to that database in reporting services those connections are referred to as data sources there are two main types that you can create in reporting services once referred to us some embedded data source and that's one that is contained within a single report and is only accessible through that report the other type of data source is referred to as a shared data source and that's one that's created at the level of the entire project it is accessible to any report in the entire project as well as the reports that are deployed to the report server so once you deployed the shared data source any reports on the report server can reference it we're going to start with embedded data sources for this video they're slightly easier to create and deploy and then we'll move on to shared data sources a little bit later on just to demonstrate which database we're going to create our data source to point to we should have an application in stores called sequel server management studio certainly if you follow the videos in that little series that I showed you earlier on so I'm going to open up a sequel server management studio and when the application launches I'm going to connect to a named local instance of sequel server so that a named instance called SQL 2016 training which is installed on my local machine I'm going to get to a database engine and then click connect and then you'll be able to see in the list of databases if I just simply expand that folder I've got a database in there called movies along with one once you are the ones from various other courses and projects I've been working on but movies is long that we're interested in for this particular video what we'd like to be able to do is create connection to that database so we can extract some data from the various tables contained in it so let's get started by doing that by heading back to visual studio and having to look at how to create an embedded data source you can start creating an embedded data source using the report data window that window should have appeared when you first show it to create a new report but if it's not there then just make sure that you've clicked somewhere inside the report design area and then head to the View menu at the top of the screen and choose report data or just press ctrl alt + B on the keyboard anyway once the window has appeared look for the data sources folder in there and simply right-click on it and choose the only available option add data source and that will display the data source properties dialog box that helps you to configure this data source we should give this data source a simple name so just to distinguish this one from the shared data source will create later on let us call this on movies embedded you may choose what type of connection you're trying to create here so by default it will point a Microsoft sequel server of course reporting services as part of the sequel server suite of tools so that kind of makes sense but depending on exactly which providers have been installed on the machine you're working on you may have a slightly longer list of different connection types you could create we're going to be sticking to Microsoft sequel server of course for this video so let's leave it set up as Microsoft sequel server you could then if you know how to write out a connection string from scratch you could simply type it into this still text box here it says click here to type or paste a connection string but it's much more convenient for us if we use little edits button to launch the query connection string builder so I'll click Edit and then that will open up a second dialog box that helps me to configure the connection properties now the first thing we should do is specify which server our database is stored on there's a tempting looking drop-down list here but often when you click on that drop-down this about just two to do that it takes forever to try to populate itself and then I often end up with absolutely nothing in the list anyway I'm going to not responding message as well so just while that's trying to sort itself out what we're going to do instead is type in our server name if I just switch back to sequel server management studio I've got a local named instance of sequel server called SQL 2016 training and the name of my local machine is listed here that's the air bus the server name for our purposes so I could just type out that entire server name followed by a backslash and the name of the instance of sequel server I was interested in but it's because this is installed as a local server it's actually used slightly more convenient and easier to type in localhost followed by a backslash in an SQL 2016 training it doesn't matter it's not case-sensitive so I can type it in in all uppercase or a location or a mix of the two it's actually even more convenient rather than even typing in localhost I could just replace the word localhost with a full-stop which is a shortcut to the local machine so that's a fairly convenient way to to refer to the specific instance of sequel server on which your database is stored if you've done that part correctly what you should find then is that you can simply select your database name from a drop-down list you don't have to do this at this point actually you can just create a connection to the server or the instance of sequel server and then later on specify where your data come from in the data set but for the purposes of this video I'm going to select the movie database and this drop down list does actually work if I click on the drop down arrow that will show me a list of all of the databases available from that server so I can choose movies and then let's have a quick look at some of the authentication settings now you'll notice that the authentication property has been set to windows authentication by default and all that means is that when somebody tries to access this data source that if source will use that users Windows credentials first definitely the right choice for this particular example because that's the way that we configured our sequel server when we first installed it if you follow the video series about installing sequel server then you will have done the same thing if you follow my instructions and if not so you may have a different setting applied just to check on that we could switch back to sequel server management studio and then we could right-click the top node here in the object Explorer and choose to view properties on the dialog box that pops up there's a security page and that will tell you which type of authentication mode you're using so currently I'm using Windows authentication mode and that's all tied in to the logins and users configured when we first installed sequel server another option there might be sequel server authentication mode along with Windows authentication which means you can set up specific usernames and passwords to start the security settings for your sequel server I don't want to get into all the detail behind that at this point there is more information about that on as part of the sequel server 2016 getting started series a video there about logins users roles and schemas which contains information about how this works for now we're definitely going to be using windows authentication for out our particular beta source let me just cancel out of this this server properties dialog box switch back to visual studio I can click test connection if I just want to reassure myself that it's going to work luckily it does if I can then click OK and click OK one more time you'll see that the connection string has now been written out for us now before we click OK on this dialog box it's worthwhile mentioning that we've just seen that you can modify the authentication settings using the edit connection string dialog box but you also do it from the data source properties dialog box - if I switch onto the credentials page here you'll see that it's set to use windows authentication we could have also said use this username and password so if I were to choose this option here it will actually affect what we saw on the edit dialog box for the connector string by switch back to the general page and click Edit head to the authentication option you'll see that it's changed a sequel server authentication and then I need to enter a username and a password and that must match one that's been set up in the security folder for this particular sequel server as I say this one isn't the the correct option to choose in this case so much suspected windows authentication here and then click OK and so it's about two credentials on the data source properties dialog box and you'll see that it's set to windows authentication here again now there are a couple of other options you could choose here as well you choose not to use credentials soul which is no good for resume now in this particular case because of the way our sequel server has been configured you could also choose to prompt for credentials so that would mean is a dialog box will pop up every single time somebody tries to run this report asking them to enter that username and password so it can be that they windows username and password so it can still use the windows credentials it just forces the user to enter those so I could type in my own specific prompt text and that would display on the dialog box asks the user to confirm their identity alternatively it will just use some sort of default text like enter credentials or enter details username and password for this example as I say using windows authentication is definitely the right one to go for there are other reasons you might want to consider the other options which we'll talk about in later parts of this series the main one being if you wanted to a report to be set up to run automatically without anybody logged in so an unattended execution of a report that's mainly used for things like descriptions so I'll talk about that in the later parts of the series but for now hopefully you heard you're happy employed that this is definitely the correct setting to use so at that point we can now click OK and we have our basic embedded data source finally created okay so at this point we have a data source pointing to our movie database but we're still not at the point where we can actually display any data in the report simply because a data source itself doesn't return any data think of a data sources and more like a connection or a pipeline along which data can flow it doesn't actually contain data itself but it allows data to move from the database into the report to actually get some data using that data source or that connection we need to create some illegals a dataset now I'm going to cover this at a very basic level in this video we'll have a separate video that explains a lot more about how datasets work but just to begin with I'm going to right click on the data source I've just created and then I'm going to choose add dataset and then I'll get another dialog box to fill in but the important thing is that is already selected for my movies embedded data source I'm not going to bother changing the name of my data set solidly we called dataset one and I'm going to use a query designer very quickly to select a simple table or two from my main movie database if I were to click this little add table button at the top of the dialog box I can then select a couple of tables so let's go with a film table for instance and perhaps have the country table and be those on the table and that will probably do maybe the certificate table as well my self certificate and then I can close down the add table window it's going to explain a lot more about how this query works of its data set works in a later video but just for now I'd like to check a few basic boxes I'm going to have the title box the release date and then another couple of columns let's have the run time in minutes and the Oscar wins quite topical actually based on what today's date is we just seen the Irby Oscar winners announced this morning and then we can have their country column from the country table the genre column from those on the table and finally this certificate column from the certificate table the query will be written out floors down in the bottom part of the dialog box but again I'm going to focus on this in a later video so let's just click OK for now click OK one more time and now we have a data set which you can finally use to display some data in order to actually make use of the data in the data set I need to insert some sort of items into the report and we saw this in the brief sort of introductory video in this series about how to create an upload your first report so just very quickly let's insert a couple of basic items I'm going to right click somewhere in the report design canvas and then choose insert and let's start with the basic matrix once I've inserted a matrix I'm going to assign some basic fields to the various areas so for the rows area I'm going to drag the genre column from my dataset position that in the rows area I can then drag let's say there certificate column I'm going to position that into the columns area and then in the data area I'm going to drag in let's say the run time in minutes and then I see big fun what I'm going to do instead of dragging the title column I'm just going to count how many films there are for each combination of genre and certificate I get the title field filled in but it doesn't give me any sort of aggregate so I'm going to do is click on to the word title which will highlight the word in blue then I can right click on that choose summarize by and then choose count so that will simply tell me how many items there are for each combination alongside that I could insert maybe a basic chart as well so let me just right click somewhere into the blank part of the report design canvas again and choose insert and I'm going to insert a basic chart and when I do that I get to choose what type of chart I'd like I'm going to go with a basic pie chart and click OK and then I'm going to click somewhere inside the chart and I'm going to choose to specify that the category groups will be the certificates so I'm going to click on the drop down arrow here and choose a certificate and then for the values in the chart again I just like to count how many films there were so I click into the symbol table right hand corner I can choose title and that will automatically assignment count this time in the chart I could just resize the chart area just a little bit to make it a little bit more readable when we finally see the results one last thing I'd like to do if I just click and drag at the bottom of the page bottom of the report page you should to get myself a tiny bit little bit more room I'm going to right click a game into a blank part of the report and choose insert and then choose table and this time I'm going to select a few basic fields from from my data set I can choose title I can choose release date and I choose let's go for Oscar wins and then let's just change it with some of those columns so it looks a little bit more easy to read when we finally see the results and having done all of that I would just like to see what the report looks like when I run it so I'm going to hit the preview page at the top just to see what my fairly hideously basic report is going to look like so it shouldn't take too long but this is rendering the report and finally we get all the data displays based on the values stored in that data set now although this report is fairly basic looking L freely admit I don't really care too much about the formatting for this particular video what I'd like to do next is upload this report to the report server and then view it in the report manager application to a web browser just so that we can compare this one with the version that will use a shared data source later on so to do that we'll need to configure the report of the project to deploy the report to the correct report server let's start just by closing down this report the report will already have been saved when we previewed it so we shouldn't get asked to save any changes what we can then do is right-click the report project name in the solution Explorer window and then choose properties from the context menu that appears we need to make sure that we're pointing to the correct target server URL so currently we've got one that says localhost report server which isn't quite correct for the particular instance of sequel server that I've installed so what we can do to view the report server URL or get their specific report server URL is open up a new instance of sequel server management studio what I would like to be able to do here is connect to my report server if I go back to sequel server management studio and then choose to connect to a reporting services service and then click connect that will happen it will connect to the reporting services service or report reporting services the server I should say but if I try to view its properties here if I right click I'm blocked from viewing its properties because I'm not running this application as an administrator so I'm just going to disconnect from that and in fact I'm going to close down the sequel server management studio altogether at this point with no need to keep that instance open if I head back to my Start menu and then right-click on sequel server management studio choose more and then choose run as administrator confirmed that I'd like to allow it to make changes to my machine and then eventually when the application does load again I can connect this time rather than to a database engine I can connect to a reporting services instance it should have set that automatically having just manually connected to a report server in the previous instance so if I click connect what I can now do when I see my report server node I can right click on it and I can choose properties this time and the report server URL for this particular instance of sequel server will be listed right there I can simply copy that to the clipboard close that dialog box step back to visual studio and then can paste in my report server URL into that box and click OK the next thing I can do is deployed this report project but I'm going to run into a similar sort of issue that I've just had with trying to view the report server properties if I try to right-click on my project and then to deploy the process will begin but it will fail fairly rapidly immediately afterwards so that's a an error to do with permissions being granted to this particular user which is not particularly handy so I'm just going to hit the add the error to clear it and then close down that ever list and close down the output window and then what I'm going to do is close down the whole of a visual studio you can across in the top right hand corner what I can then do is head back to the Start menu and find my visual studio shortcut right click choose more then choose run as administrator click yes to allow it to make changes when the application loads into the start page or the home page I'm going to choose to open up a recently used project and so when it does finally appear there it is I should have listed my recent project so as one they're called report project one which is the one that I was just working on alternatively I could go to the file menu and choose recent projects and solutions and choose it from that list or you can just choose to open a project or solution and browse for it but far more convenient just to click the one in the recent list so if I were to do that I'll open up this enormous project with there with my whole single report in it but I should be able to do now however is right click on the report project at the top of the top of the solution Explorer and then choose deploy again and this time I should see not an error message but a list of success messages so deploying a single report no errors no warnings that's now succeeded what I'd like to do next is view that reports using the reports manager web portal sotas find the URL for that web portal application I can head to the Start menu and again if I've installed sequel server in the same way that I've described in the short video series about getting started with the sequel server I should have another application here called reporting services configuration manager I'm just going to launch this tool normally and then click yes to allow it to confirm that I want to allow it to make changes make sure I'm refering to the correct report server instance so the one I'm using here is sequel 2016 training and then click connect and then there's a separate page on the left-hand side called web portal URL I click there that will give me the URL that I need to browse to now really annoying where I can't right-click and copy this to the clipboard which is what I really want to be able to do I can't select that text in the dialog box either the reason I'd like to be able to select that text because if I just click onto it it's going to try to open up my default web browser which is Google Chrome as it turns out and then try to browse at the web portal but it's going to give me an error message saying that I'm not allowed to view this folder exactly the same reasons I couldn't feed a property to the report server or deployed a report project in the first place I'm not running this browser as an administrator so what I'm going to do is click OK to clear that message I can take that URL I can just feel that it and copy it to the clipboard for the time being what I can then do is just open up either Google Chrome or even another web browser it really doesn't matter which browser you choose to use these days for reporting services I'm going to use Internet Explorer just for something slightly different so I can right-click on the application choose more than choose run as administrator choose yes to allow it to make changes have a great look when it's loads click into the address bar paste in the URL that I've just copied hit enter to browser there and what I should find very shortly once it's loaded is a brand new folder called report project 1 and if I click into that folder I should have a single report there and if I click onto that report it will show me all of the information all the objects that I designed before the matrix the chart and that fairly boring looking table so there they all are the important thing about this having used an embedded data source is that everything that this report requires is all containing one single object there's nothing else to consider here this report just gets moved around if I wanted to move it it could be moved around and everything required to run the report would move along with it so that's one of the nice things about embedded data sources everything's self-contained in one single item now there are a couple of small downsides to using embedded data sources primarily you need to create a brand new embedded data source every single time you create a new report which gets a little bit tedious if you're always connecting to the same database also if you decide to change anything about your data source maybe you change the name of the database for some reason or change maybe the server on which it installs or maybe change some credential information then every single embedded data source that previously pointed to your original database on your original server will need to be updated as well and that can be really tedious so that's where shared data sources come in it's a single data source that lives on the report server basically and multiple different reports can point to that same data source which means that you've got a single thing to manage basically you've got one single item that you can modify and that will affect numerous of the reports so let's have a quick look at we can design a basic shared data source or connect to the same movies database but we'll do it as a shared data source if I switch back to my report project in Visual Studio I'm just going to clear the output window and then before I do anything else in terms of creating new reports I'm going to create a new shared data source and you can do that in the solution Explorer window you don't even need a report open to do this so I'm going to right click my shared data sources folder and choose add new data source as you can see the dialog box for creating a shared data sources virtually identical to one for creating an embedded data source we can give it a source of names let's call this on movies yet and then we can choose what type of connection we're going to create so of course we're going to connect it to a sequel server database but all of the other providers that are available to us as well based on which providers have been installed on your machine we could type in a connection string but it's far more convenient just to edit using the dialog box and then again type in the server name let's type in full stop back slash SQL 2016 training or if you use a slightly different instance name then of course use that instance name again the same sorts of options for credentials or authentication I'm using windows authentication for obvious reasons we can select our database names in the drop down list or that we don't have to do that but it makes sense to in this case and I can click test connection just to make sure that it's going to work I can click OK click OK again my connection string we were written out for me and then of course I've got the credentials page which would allow me to modify your credentials so for situations as I mentioned earlier on where perhaps you want to have a report that runs unattended as part of a subscription to generate emails or output files then you might want to consider a different option here again we'll talk about that in more detail in a later video in the series just for now I'm going to click OK and that will generate my new shared data source which will live in this little folder up here with the dot RDS extension just to demonstrate how to use that shared edge so let's create another report or two I'm going to right click on my reports folder and then choose add new item and I'm going to select report from the list and not going to bother changing its name reports here although it's a fairly rubbish nondescript name and good leave it as report 2 in click add the generated brand new blank report now I do need to add something to the report data data sources folder but I don't have to go to the extremes of specifying the connection string roles or credentials again all that information is stored of course in the shared data source all I'm going to do is create a pointer or a shortcut to that the shared data source by right-clicking data sources in the report data window choosing add data source I could change its name here if I like maybe let's call this one rs2 movies a shared something along those lines and then I don't need to set up an embedded connection of course what I want to do here is use a shared data source reference so I convert that option and then from the drop-down list select from all of the shared data sources that belong to this project but choose that option there is a credentials page available but I can't modify anything on here of course all that's used all that stored in the shared data source so all the credential information is set up in there so that I can browse that patient and nothing I can do there all I need to do here is just click OK so it's really duplicated selecting the shared data source from a drop-down list it's as simple as that the icon for us for a shared data source reference is slightly different to one for an embedded data sources all sort of shortcut icon indicating it's a pointer to something else but from this point on everything else is exactly the same so it's create a new data set that belongs to this data but uses this data source I can right click on that data source and choose add dataset and then I could change the name if I wanted to I don't really care about doing so in this example let's just at the query designer again and what I would like to do here is create a report that contains the film table and let's say the country table and then just close that window down I'm going to display the title and the country and then click OK and then ok again what I'm going to do then is insert a basic chart into the report so I'm going to right click and choose insert chart and I'm going to make this a basic pie chart actually tell a lie I'm going to make it a basic that column chart so that bog-standard column charts fairly boring box standard one I'll just increase the size of the charts make it a little bit more readable and then I can click into the chart and I can set up a couple of basic properties so I'm going to set the category groups sort of that set to country and then I'm going to set the values so that's a count of the film titles that would be overwhelmingly biased towards the United States of course if I just have a quick preview of the report you'll see this chart is overwhelmingly infinite of the USA I just actually listen that didn't actually note to that on the chart we're going to deal with charts in a huge amount more detail in later parts of the series but just now I wanted some that will generate some basic output I'm going to close down that report there and then I'm going to create yet another new report using the exact same shared data source so the beauty of this now is it and whenever I want to create a new report or a new item that Blanco uses that movies data source I don't need to created the game from scratch I can just create a new data source in the report data window that points to that shared data source it's as simple as that I don't even need to rename it so I can click OK then let's create another basic data set I can my chromatic sauce choose that data set and then let's use the query designer this time let's choose something like let's go with certificates and films and then press up/down I can check the certificate box check the title box and click OK and ok again and not inserting the basic chart just to temperature to principle you can carry on creating as many of these as you like by the way this is just the most basic type of demonstration to to show you what this will look like when we finally deployed the project so I've got my pie charts I'm going to set the details to be certificate and then I'm going to add in the title which will give me a counter title free certificate give that a quick preview and those might as well break down I'm not going to bother doing any formatting although I would love to because I saved a color scheme but anyway let's leave it as it is for now I'll close down report number three and there we go we have a couple of basic reports both using our shared movies data source what I'd like to do next is show you what happens when you try to deploy a report that uses a shared data source now earlier on we deployed the entire report project which would deploy every single item in there and when you've got a whole great big long list of reports and objects to deploy that's the most sensible option to choose when you're adding new reports one by one you might only want to deploy one single report at a time but we're going to have a problem with doing that for this particular example when we use a shared data source you can only deploy a report that uses that data source if the data source itself has been deployed so to demonstrate what I mean by that if I try to right click on report number two and choose to deploy it what's going to happen is it's going to fail telling me that they can't deploy the report because the shared data source doesn't exist so what we need to do here is either to deploy the entire project or deploy the data source and then the reports so of course the most sensible option would be to deploy the entire project I could right-click barron deploy the entire thing bear in mind that that would overwrite any existing copy of report number one that might be appropriate in this case alternatively you could choose to deploy the individual data source and memorable so I could right click on my movies and share data source and choose to deploy that first and that should succeed and then I can like come report number two and then deploy that and that should succeed let's do the same thing for report number three I can right click and deploy that and then that will be all the objects required all deploy to the report server just before we go and have a look at these reports and the data source in the web portal application is what if i'm mentioning that once you've deployed a shared data source once by default you're prevented from over writing that by trying to deploy it again so if i try to deploy out the entire project or just that shared data source again if i right click and choose deploy this time i'll end up with a failure message it says i can't deploy data source maybe shed server because it already exists and a specific property have not been specified so it may be the case that you wanted to have modified your shared data source and redeployed out to the server in which case you would need to modify a quick little setting of the report project so if i were to right click on the report project and choose properties in a dialog box that appears as an oculus as overwrite data sources which is set to false by default you can change that to true and then click OK and then if i right click on my shared data source again and choose deploy again this time it will tell me that it succeeded properly so there's no warning message about it not being able to be deployed okay so to see this data source from The Associated reports on the report server we could head back to our web portal application in my case Internet Explorer but you might have chosen a different web browser why wouldn't you and then I'm currently browsing into my report project folder so I just switch back to the home page first of all what I'd now like to do is refresh the home page by just providing there five or clicking the refresh button on the toolbar and what should happen this time is I ought to end up with two separate folders so there's the one with my report project reports but I've also got a separate default data sources folder and that's where the shared data sources for your entire report server will live so I click on the data sources files that will show in my movies a shared data source if I click onto that that will display another dialog box showing me all the information about that data source so you can either connect from string that's been missing out there for me and it shows me any other credential information etc I can test the connection I can modify it so I can actually change various different things about this data source even now that it's been deployed to the report server I can also see which items depend on this a source if I click dependent items it should show me that these that the share movie share data source feeds report to amber fort three so have a quick look at the what page of those display and again that will just show me those basic boring charts I could go back to the previous page and have another look at report number three instead and that was coming out slightly less hideous more hideous I'm not quite sure maybe I'm giving you two those colors and if I wanted to look at all the reports in the entire report project I can either browse directly to that folder using the brush top or I could go back to the home page and then click into the report project folder and I can see all three reports so of course this one doesn't use a shared its source I still uses embedded data source this one of these to use my movies shared data source now it's worth mentioning that once your shared data source has been deployed you can make changes to it which will then subsequently affect any reports that rely on it so for example I've actually got a couple of different instances of sequel server installed in this particular machine if I press back to sequel server management studio if I connect to my original database engine which was sequel 2016 training that's where I was basing all of my queries all my data sets and data sources on when I was still in designing my reports now that might represent that's a test server where you have some test data for creating your basic reports in the first place when you've deployed your reports and they're gone live perhaps you want to force those reports to use the production server so as you imagine I had another server which I do if I connect to another database engine and this I'm going to connect to my just an instance called sequel 2016 so without the training extension I connect there and I'll see that I also have installed in this instance of sequel server and movies database so this could be a perhaps a a production server as opposed to a test server so what I could do because there are so few changes essentially is just a change in the instance name that I need to modify here to update the app there the shared data source back in my web portal application I can browse back to the home page of the web portal and then browse the data sources and then browsing to movie shared and then all I would need to do is modify the connection string sadly it's not quite so easy to do I don't have a nice little dialog box edit to the connection string but I could because it's such a simple change I could just edit the connection so it's just called pointing to an instance called SQL 2016 and I could choose to test the connection which will hopefully work connected successfully and then apply those changes and now what will happen is that whenever I run reports to and report 3 if I browse back to let you see the dependent items in batch if I click dependent items report to report 3 will now use data stored on the other instance of sequel server rather than the original now sadly the databases are exactly the same so there's no way to see that this is the year this is happening but you can just you know just take my word for only test it out for yourself if you prefer so where's my own modify the shared data source once it's being approached the report server and that will of course affect any reports that rely on that data source and that use embedded data sources will still be using their box payment original data one final thing just to be ever so slightly careful of related back to the idea that you can choose to overwrite a shared data source when you deploy a project if I switch back to visual studio and I were to deploy my entire project this time must let's say I made some change it to all of my reports I wanted to deploy them all what would happen in this case if I right click my report project can choose deploy that will copy all three reports as well as my shared data source and of course I set the report project property to allow data sources to be overwritten so that will mean that if I switch back to my web portal application and head back to the home page and then browse back to my data sources folder and look at me be shared you'll see that this has now been overwritten again it's reverted back as sequels 2016 training instance that's not necessarily a bad thing that might be my intent all I'm saying here is just be aware that you might have that effect if you're uploading and overwriting your shared data sources just to wrap up this video I'm going to do a tiny little bit of tidying up on my report web portal application so I'm going to switch back to the home page I'm typically to to delete all of the objects both the report projects and the shared dicta sources folder you don't have to do this I'm just tidying up so that I've got a fresh version available for the next video so I'm just going to use little ellipsis between the dots at the top right hand corner of report project 1 and choose delete and then confirm that I would like to allow that and then do exactly the same thing but a shared data source so I'm going to right so just click once on the ellipsis button and then click delete and that will allow me to remove all of those objects so that leaves us set up nicely for the next video in the series I hope you found this on useful that you gained a bit of an insight into what the difference between share done embedded data sources is and how to use them to their best effects thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials and even some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 22,990

Keywords: ssrs, reporting services, sql server, report, server, data source, shared, embedded, connection string, database, deploy, report project, introduction, basic, tutorial, wise owl

Id: BviHhGb3sYE

Length: 38min 32sec (2312 seconds)

Published: Mon Feb 27 2017