Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Getting Started with Reporting Services

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
[SQL Server 2005 Express Edition for Beginners] >> In this video, I'll demonstrate how to install and configure SQL Server 2005 Express Edition Reporting Services, and within minutes, I'll create and deploy a couple of reports that you and others can view directly from their web browsers. Reporting Services provides the tools necessary to get great results quickly, complete with charts and graphs, with drill-down data, with export to Excel and PDF formats, and more. Let's just take a second and look at some examples of the kinds of reports that you can actually build. Here, we're using the report manager application to navigate through the reports, and the first report we take a look at there is the Adventure Works sales, where we can drill down by quarter, by year, by product category, and subcategory to see the sales for each of the quarters for a given product and subcategory of product. If we go back, we can take a look at sales order detail, so for a specific sales order number, which you can change, you can see kind of a receipt for a single purchaser. Here's another report that would get the sales for a given employee, so we select an employee from the list and click View Report, we can see some nice charts about how well that employee is performing. As you can see from these examples, with Reporting Services, you have a lot of formatting options, as well as interactivity by parameterizing your queries and a lot more, not to mention the cool web-based user interface that nicely manages the reports that you've created. Also, you can set permissions on the reports that you create to only allow those with the proper privileges to view certain reports on your server. Admittedly, the reports that we just looked at are a bit more advanced, but using a report wizard, you can create simple reports within minutes. Reporting Services also has a rich API for defining more complex reports. Reports can be built using a special version of Visual Studio called SQL Server Business Intelligence Development Studio. Those of you who are already developing applications with the other express edition tools or with Visual Studio, you can leverage your existing IDE skills here. Let's start with a quick overview of the major pieces of Reporting Services, and then we'll begin with the installation and configuration demonstration. While there are many working parts internal to Reporting Services, I'm just going to focus on the components that you're going to see and interact with the most as a developer of the reports. First of all, you'll create .rdl files, which stands for report definition language files, which is just an XML syntax that contains the definition of your reports. You'll create these RDL files using SQL Server Business Intelligence Development Studio. It allows you to find the connection to the database, the data that you'll retrieve into the reports, and the way that the data will be displayed on the report. Once you define the report, you'll deploy it to a report server, which manages the reports, and it's responsible for things like maintaining the metadata about the reports, like connection information, the the underlying data source, cached versions of the report, and so on. The Report Server kind of stays behind the scenes and manages the report and information in the reports. It keeps track of information in a couple of SQL Server databases that you'll define during the configuration steps that we'll go through in just a few moments here. Then there's the report manager, and this is what we were just looking at a moment ago, it's that user-friendly web-based user interface that you and your users will be able to interact with. It requests reports and other information from the Report Server, and then displays the results to the end user. Now again, this is a very basic breakdown to the responsibilities of Reporting Services. The main message is that you have a very sleek, well thought out workflow for creating and managing reports for your data. Okay, so that's all the overview there we're going to need. Let's go ahead and get started. First, I'm going to demonstrate the steps required to set up Reporting Services, and the key is downloading and installing the correct packages from the Express Edition website. Let's do this. Let's go to Internet Explorer and navigate to MSDN.microsoft.com /express, and that'll transfer us to the Express Edition homepage. On the left-hand side, we'll hover over SQL Server Express, and in the pull-down menu, we'll go to the download page. About midway through this page, we'll see some links on the right-hand side. We'll want to download SQL Server 2005 Express Edition with Advanced Services, and also download the Expression Edition toolkit. Those are fairly large files—take a little bit after that. We'll want to go to the SQL Server 2005 sample page. This will give us the data that we can use for the examples that we'll go through in this and the next video. First, you'll want to download the Adventure Works db.msi. This will give us all the data, and then the SQL Server samples.msi will give us some example reports that we'll also look through. Those will use the Adventure Works database. I've already taken the liberty of downloading these files for brevity's sake, and so we'll start the installation process by installing SQL Server 2005 Express Edition with Advance Services. I'm going to walk you through the installation and configuration of Reporting Services and discussed some of the options that you have whenever you're setting up your development and deployment environment. For the most part, I'm going to choose the default options, except for those that involve reporting services. Now, if you have any questions about the other options, please refer to SQL Server's help or other resources on MSDN. I'm also going to post the video recording, because the installation process can take a little while. One of the first things that the installation wizard will want to do is look at your system and make sure that there's no problems. You may have some error messages. If you do, or warnings, and if you do, you'll have a little message hyperlink. Click on that. It will give you some insight as to what the problem is and what the impact of that problem is if you don't resolve it. However, as you can see here, I'm successful, so I'm going to move on. Hit the next button. We're going to deselect Hide advanced configuration options. We are going to want to manually set up our Reporting Services where it goes on the file system, plus where it's installed within Internet information services. From here, we're going to want to make sure to install Reporting Services and the report manager. Also, we're going to select to install the client components, connectivity components, and Management Studio Express. For the next 5 screens, I'm just going to select the defaults for instance name, service account, authentication mode, collation settings, and user instances. It's on this next screen, Report Server installation options, that we'll want to make a small change. By default, it will install the default configuration. If you want to see what that entails, click the Details button, and it'll show you where it'll be installed within IIS for both the report server and and the report manager. Also, it will let you know what the default SSL settings are. However, we want to have more control over the configuration of the server. I'm going to select the second option, Install but do not configure the server, and we'll show you how to configure it then after we finished the installation procedure. I'm going to continue on with some of these options. Now that it started installing, this could take 3, 4, 5, minutes. I'm going to post the recording right now. After the successful installation of Advanced Services, we're going to want to configure Reporting Services by selecting the reporting services configuration utility. After connecting to our instance of SQL Server Express, the configure reports server utility will show us which items that we need to configure before we can start using Report Services. We'll need to specify the virtual directory for report server and report manager. We'll click on any of these items that have a red X. In this case, we'll report server virtual directory settings, and we're going to select New, and if we have multiple websites already defined within Internet information services, we could select which website we wanted to create a virtual directory for. In this case, I don't have any websites created, so we'll just select the default website, and also the virtual directory that it will be created in. We'll just keep the defaults for now. Let's go ahead and maximize this window as well. After we have set up the report server virtual directory, let's go on to report manager. Again, same idea here, where we can select the website and also the virtual directory where we'll install Report Manager. Once that's finished, we'll go on to the Web service identity, and this will allow us to change the app pool for both the report server and report manager. If you want to isolate report server and manager from the other web applications that you have on your server, you can do that right here, if you're running IIS 5, you'll see a slightly different screen where you'll be able to select the machine account that ASP.net is running under. In this case, we're not going to change the default app pool. We'll leave it as is. We will select the Apply button. After that set up, then we'll go on finally to the database set up. Now, this is the database that Report Server will use for its own purposes— for caching reports and for saving information that it needs. This is not the database that will ultimately be creating reports from. That will be a whole different database that we haven't even set up just yet. Let's go ahead and connect to our local server and our local instance of SQL Server Express, and then let it create the databases that it needs. First of all, we'll select SQL Express and we'll click Connect. Then we'll Create New, and we can give it a different name right here if we wish. It'll also create a temp db. Once that's created, we'll select Apply and click Okay on the dialog that pops up. There are some other things that we can set up. For example, encryption keys that we're going to allow, SSL, encryption of the data, email settings, and so on. We're going to go and exit out of our configure report server for now. We should have enough configured that we can test to make sure that Report Manager is accessible on our local machine, so I'm going to go ahead and launch Internet Explorer again, go to localhost/reports, and if we've done everything successfully, then we'll be able to see the report manager application. This is where we can browse through the reports that we've created, we can organize them into folders, we can set permissions on who can see them and so on. We've already talked about this at the beginning of the video. At this point, were going to go ahead and close this down. We're going to need some data to create some examples with. If you want to follow along, then you'll need to download and install the Adventure Works db.msi. This shouldn't take very long. But perhaps you've already .data that you want work with. If so, you really don't need to do this. But if you're just practicing, this is a good database to start with. Once we've installed the databases, we're going to need to attach them to our instance of SQL Server, so I'm going to go to SQL Server Management Studio Express and I'm going to connect to my local instance. As we drill down to the databases, you can see that here are the 2 databases that Report Server created. The report server and report server temp db. I want to attach the database we just installed, so I'm going to select the Add button on the attached databases dialog, going to select Adventure Works, which was installed by the MSI I just double clicked on a few moments ago, click Okay, and then you can't see it. It's off to the bottom of the screen. We're going to click the Okay button. Now you can see that we have the Adventure Works database attached, so let's go ahead and create a new query and just make sure that we have successfully attached to this database. Let's see what we called this. Salesorderheader. I misspelled something and got it right this time. You can see that we did pull data back. That's great. Let's shut this down. Now, we're going to want to install Business Intelligence Management Studio. We're going to want to go to theSQL Express 2005 toolkit and install that. Many of the installation options look similar to what we did earlier with the Advanced Services. We want to, on the feature selections, select Business Intelligence Development Studio, and that should be everything we need. It'll ask if we want to overwrite components that we already have, and go ahead and agree to that. It'll take another moment or so to set this up. Okay, so once that's finished installing, let's go and run SQL Server Business Intelligence Development Studio. You can see that we're looking at the Visual Studio IDE interface, and so this should be very familiar if you've developed applications for Visual Basic or C#. However, we're going to create a new project of type report server project wizard. The project wizard will give us the quickest results so that we can just see a very simple, basic report, and we can build this in less than a minute or 2. We're going to give our project name. Bob's Reports, and then select the Okay button. You can see that the first screen of the report wizard appears. Here on the next screen, we'll select the data source, in the data source will just keep all the connection information to our underlying data store or database. Let's give it a name, Adventure Works, and then we'll want to edit the connection string. Here, we'll use our ./SQLExpress to denote our local instance of SQL Server 2005 Express Edition. Then we'll select a database and test our connection. Select the Okay button. We have an option at the very bottom to make this a shared data source. Now, by default, whenever we create a report, it will place the connection information in the definition for the report. If we have multiple reports for project, and we wanted each to really pull from the same database connection, then it would make sense to store that in an external file and just have each of the reports point to that connection file so that if we need to make a change to the connection, we can change it in one place, and then all the reports can update as a result of that. We're going to select this Make this a shared data source, and then click the Next button. Now we're going to build the query for our report. I can type the query right here or I can use the query builder to design my report, and let me go ahead and just type in a query. We'll bring back all of the fields from the SalesOrderHeader table where order date is, let's just do between 2 dates. 8-1-2001, because I happen to know that we have data on that date. We'll just select 1 day's worth of data. From midnight on 8-1 to midnight on 8-2. I'll test the query to make sure that it runs. It does. I'll click the Okay button, and notice that it places the query I built into the query string for the design wizard's step. Just continue on by clicking the Next button. Here, we decide what type of report we want to create. For this first scenario, the simple scenario, I'm just going to select a tabular report, and then I get to select which fields will be displayed on the report. Again, I will keep this very easy, so I'm only going to use the details area of the report. However, as you can see, we can also create groupings a page level and at a group level. We'll come back and do this perhaps in a different step of our video, but for now, I'm just going to select the order date, the subtotal, the tax amount, the freight, and the total due, and then select the Next button, so this will be a simple report on a daily basis, just for this 1 date in this date range of all the sales that occurred on that day. Here, I get to select the color and the style of the report, also adjust the fonts. I'm just going to select slate. We can modify the look and feel of the report after we finished the report wizard, and I'll show you how to do that in just a moment. Now we're going to want to set the deployment settings. These not be used immediately. However, when we get ready to deploy this report to our web server, these are the settings that our project will use to deploy the report. In this case, it's going to use this report server. If we had a different report server, it'd get deployed out of that server instead, and the folder that we want to deploy to. I'll just add a space in there. Select the Next button, and we'll have to give it a name. We'll just call this Sales Report. Something very generic. Click the Finish button. Once I do that, it builds the report, and we can see it here in our main area of our designer surface. We also have a couple more subtabs within the the main sales report.rdl. That's the report definition language file, which is just an XML file format that defines the look and feel and the data that will be used within the report. On this subtab, we can see the data that's being pulled back, and change it and play with it and executed to see what type of data will be brought back. You look at the existing layout, and then we can preview what it will look like once it's built and generated. Here we go, let's shut down the output window and just move over the Solution Explorer. You can see a very simple report. It just has the fields that we defined, the date, the subtotal, tax amount, freight, and total due. If we wanted to change some simple things about this report, we go back to the layout subtab. You can select, for example, the tax amount, and we can change, for example, the background color under the appearance section of the properties window. Let's make the background color perhaps yellow. We can see the tax that will be charged. Let's say that change, then preview our report. You can see now the tax column is changed. Additionally, we can change the font, say, of this tax amount header field. We can change that by going here to the font properties, and perhaps removing the bold and increasing the size to, let's say, 13. Now let's see what that change produced. Very small difference, but difference nonetheless. It's very easy to make changes to each of the cells that we're working with within the body of our report. Over here on the left-hand side, let me close this down. We can see also our dataset. This is all the data that's being pulled back from the query that we created. If, for example, we wanted to change the order date to something else, since we already know what date range we're pulling back, let's go ahead and just delete that out and delete this. Instead, we will drag and drop the due date into that field, and you can see that it's just as easy as dragging from the dataset and dropping into 1 of the fields within our table that we've got defined. Again, we can preview this to make sure that it worked. Once we've made all the changes, and we'll look at more ways to modify our reports and even create reports from scratch, but once we were satisfied with our changes, we want to deploy this out to our report server so that other people in our organization can work with it. All we need to do is right-click on the solution name, and select deploy. We can watch the output window for the progress of the deployment. It looks like it succeeded, so at this point, we should be able to open it up. Internet Explorer. We should be able to navigate to our report manager screen at local host/reports, and we can see now we have our folder, Bob's Reports, a little new icon next to it, and we can see our sales report, little new icon next to it, and by clicking on that, we can see our report, and this was a lot quicker than trying to build something like this using ASP.net. Additionally, the report manager gives us other capabilities to manage this report, change the data source, change who can actually look at this report as well, so if we can kind of get over the learning curve and understand the basics of how to create reports and how to manage them, this becomes a very valuable tool for us and our organization whenever we're building reports from the data that we have. Okay, so let's create 1 more report using the report Wizard. I'm going to go back to Business Intelligence Development Studio and right click on reports, and select add new report. This time, I'm going to create a matrix report, which will allow us to drill down into some rolled up data. I'm going to paste a pretty complex query here, but basically, it's just going to retrieve the sales data based on the category and subcategory, and also the year and quarter. I'm going to select matrix as the type of report, and I'm going to place the order year and order quarter into the columns, the product category and subcategory into the rows, in the details section we'll put the sales information. I'm going to select this enable to drill down check box. We'll call this our sales matrix, and we'll click Finish. Let's preview the report. Look what you can do. First of all, we have sales from 2002 and 2003, as well as the categories, and we have the rolled up sales information, for example, in the year 2003 for all accessories, this was the total sales. For clothing in 2002, this was the total sales, but if we want to drill down and see more detail, now we can expand out the little plus/minus button here next to 2002, we can see by quarter what the sales were for each of our product categories, and we can even drill down on this side as well, so we can see by subcategory by quarter what the total sales were for those given subcategories by quarter. This is very cool, so let's go ahead and save what we've done here, and then again, we're going to deploy this out to our server, and once the deploy has succeeded, we'll go back and just refresh, and go to Bob's Reports, and we can see now we have a sales matrix. We can deliver this beautiful rollup data to our management team or for our own purposes, and it took less than a couple of minutes to generate this. Very nice. If you needed to take this data and export it, you can do it to Excel or export it to a PDF file. You can print it, and in the case of our sales report, you can navigate through the pages of the report using our page navigation controls. We can shrink or expand, resize the report, and we could even do a textual search, so let's look for the number 974 and click Find. Go back to 100% here, and notice when we did that, that it brings into view 974 highlighted on our report, so very nice. We can also take a look at the properties for our report, which allow us to change the name, give a description to the report, to hide it so that is not visible within the list of reports, we can change the data source, so where's the data that the report's pulling from in case that changes, we can modify it straight from here. Also, we can change the log in credentials for the report, and we can also use our security roles for network and for our local machines in order to define who gets access to these reports. While we're talking about the Report Manager, there's 1 other link that you may want to take a look at. Site settings, which allow you at a global level to change the name of the report manager that you see here. Also, you can enable my reports to enable individual users to create their own kind of cache reports. Also to define the caching settings for the reports, how many to save in history, and timeout and logging. Okay, so in this video we looked at the steps required to set up Reporting Services, how to develop reports and deploy them to our reporting server, and view the reports in the report manager. We saw how to create both a drill down enabled matrix report, as well as a simple tabular report that we made some small changes to. Finally, we looked at how to modify the settings for individual reports in the report manager and how to change the data source and the security settings for a single report, as well as how to manage the entire site settings. In the next video, we're going to take a look at building reports from scratch, and how to use several of the reporting objects from the toolbox to customize our reports, how to add sorting and grouping to our reports, how to create expressions, to add page numbers, and how to perform add hot calculations to the reports using expressions, and finally we're going to look at how to use the report viewer control to embed our reports within our own ASP.net webpages.

Video Details

Duration: 32 minutes and 51 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 7
Posted by: neudesicasp on Sep 26, 2013

SQL Server Reporting Services allows you to design and deploy nicely formatted and interactive reports representative of your data. Suitable for print or online distribution, these reports and it allows you to get professional results very quickly. This video demonstrates how to set up Reporting Services and the Business Intelligence Development Studio to build reports using the wizards and designers, and deploy them to your Report Server.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.