Join Simon Allardice for an in-depth discussion in this video Introduction to SQL Server Reporting Services, part of SQL Server 2008 Essential Training.
Reporting Services is a separate component of SQL Server 2008. It is available in both the Standard and the Express editions and the idea behind it, not surprisingly, is to be able to create reports without requiring a developer to write a custom application. So it's really a business intelligence layer, allowing you to create a presentation of the data in your databases and then share that across an organization, and to understand it, it's best to understand there are really three different parts to it.
First is the server-side part of the platform, the engine of Reporting Services that can manage and deliver these reports, and this is considered a self-contained component of SQL Server. You can choose to either have that installed or not. If you're not going to use it, it shouldn't be installed. It even comes with its own application for managing its configuration. Now the second part is an application to help you design those reports. Well, that's where it gets tricky because there are a few different ways to create reports.
The something called Report Builder, which is a stand-alone desktop application for developing reports for reporting services and with the R2 release of SQL Server 2008 there is a new addition of this application called Report Builder 3.0. That lets you create complex reports not only including the data itself but including visualizations of the data to charts including maps, data bars and spot lines. Now there is also an application called the Business Intelligence Development Studio.
This comes with an advanced project template called Report Designer. If your developer used to Visual Studio, this might be the way that you would want to go. Now, however, this is the third part. Once your report is created, how do you view and manage these reports? We don't want to take the old-school view that the report has to be something that's printed off and handed around. So typically, the reports can be viewed by going to a web application called Report Manager. This is where you can find the different reports that have been created and view them online. If it's a large report, you can page through it, you can zoom in or search it and even all to the parameters of the report.
Reporting Services can also be integrated with SharePoint, which can be used as a location to publish a report to this. There's even a SharePoint web part so you can embed reports in SharePoint web pages. But really that's the three parts to it. We have to understand how to install and configure it, we have to then work with creating those reports and using an application to do that, and then there is the idea of managing and distributing those reports. Reporting Services and all its surrounding applications is a fairly complex technology. We could create an entire course on Reporting Services easily.
So we're going to cover a brief introduction of what it can do and the basics of how to use it.
- Using T-SQL (Transact-SQL)
- Managing databases with SQL Server Management Studio
- Understanding database normalization
- Using SELECT statements
- Building indexes
- Monitoring database size and integrity
- Backing up and restoring databases
- Creating functions and stored procedures
- Managing database permissions
- Creating and formatting reports
- Adding charts to reports
- Creating and executing a simple SSIS package
Skill Level Beginner
Q: I'm having problems installing the free Express R2 version of SQL Server on Windows XP. I tried 64-bit and 32-bit versions. In the videos, the author installs from a DVD. Do I need to do the same?
Q: The link to the installer for the AdventureWorks sample database, as shown in the Chapter 2 movie "Installing sample databases," no longer works. Where can I find the installer?
A: Microsoft has reorganized its site. The sample files are still there, but they're a bit harder to find. To install them:
1) Visit http://msftdbprodsamples.codeplex.com/.
2) Click the link to "SQL Server 2008 R2 OLTP."
3) Click the AdventureWOkrs2008R2 Data File link and agree to the conditions to download the MDF file.
4) Move the MDF file to your SQL Server Directory, usually located at C:\Program Files\Microsfot SQL Server\MSSQL 10_50.MSSQLSERVER\MSSQL\DATA.
5) Open the SQL Sever Management Studio and connect to your instance using an account with administrative privileges.
6) Attach the sample database by right-clicking the Databases folder in the Object Explorer and choosing Attach from the pop-up menu.
7) Click the Add button in the next menu and navigate to the MDF file in the Locate Database Files window that appears. Select it and click OK.
8) Remove the reference to the log file in the "AdventureWorks2008R2" database details: pane by selecting the Log entry and clicking removing.*
9) Click OK to return to SQL Server Management Studio and complete the attachment process.
*MDF files are the "data" files for SQL Server databases. They often come along with LOG files (ldf files). This one didn't so we need to REMOVE the reference to the non-existent log file. Select the second row in the lower section (it should say File Type: Log and Message: Not Found) and click the REMOVE button.
For an illustrated version of these instructions (with screenshots), click here for a PDF version.