Tutorial Installing SQL Server Reporting Services for beginners
Tutorial: Getting started with SQL Server Reporting Services 2008 (SSRS)
This tutorial will help you get started with creating reports using SQL Server 2008 R2 on a local computer.
I will be installing SQL Server 2008 R2 x64 Developer Edition on Windows 7 SP1 x64. Other editions may require different steps. (Please leave comments below regarding any changes so others can follow them.) Ill then install AdventureWorks 2008R2 and create a sample report using Business Intelligence Development Studio. Note that you do not need to have Visual Studio or IIS installed to use SSRS 2008R2.
This setup is for those learning SSRS by themselves. Ideally, youd want to host the Reporting Server on a separate computer than the development machine and probably have different security/authentication modes.
Installing SQL Server 2008 R2 Developer Edition
1. Insert and load the SQL Server 2008 R2 x64 Developer Edition DVD.
2. Installation tab
-New Installation or add features to an existing installation.
3. Follow the wizard and agree to the license terms. Install the Setup Support Files to proceed with installing SQL Server 2008 R2
4. Make sure all Setup Support Rules pass.
5. For Feature selection, choose Select All to have a full-featured installation. Click next.
6. Assuming no other SQL Server is installed on the computer, we can install using the Default instance (installation). The instance ID will be called MSSQLSERVER without the quotes.
7. About 7GB of hard drive space will be required for installation.
8. For Server Configuration, click Use the same account for all SQL Server services button and select NT AUTHORITYNETWORK SERVICE. No password is necessary. Click OK and then click Next.
9. Database Engine Configuration: Leave the default of Windows authentication mode on. Youll need to add a User as a SQL Server administrator. Click Add Current User if you have administrator privileges. Otherwise, click Add, click Advanced, then click Find Now to find and select an administrator user.
Before clicking next, click on the FILESTREAM tab and enable all options for Filestream. See screenshot below. Click next.
10. Analysis Services Configuration: Click Add Current User (or a different user with administrative permissions). Click next.
11. Reporting Services Configuration: Make sure Install the native mode default configuration is selected. Click next.
12. Click next and ensure all configuration rules pass. Begin installing SQL Server 2008 R2. This process may take 15-30 minutes.
13. Upon completion, we can verify the success status of the installation by opening Reporting Services Configuration Manager from the Start Menu (Start Menu -> SQL Server 2008 R2 -> Configuration Tools -> Reporting Services Configuration Manager). In those settings, youll find these two important URLs.
*The Web Service URL is located at http://servername/ReportServer (where the reports are actually stored once deployed) (alternatively, http://localhost/ReportServer )
*The Report Manager is located at http://servername/reports (alternatively, http://localhost/reports )
Note: If a login prompt appears and youre unable to login, then make sure that your Windows user account has a password created for it. If it doesnt, go to Control Panel -> User Accounts and create a password.
Then login using the ComputerNameuser account name. For me, it was Gabriel-PCGabriel and then the password. (Right click My Computer -> Properties).
You can also open SQL Server Management Studio (SSMS) to test the connection to the Database Engine.
Installing AdventureWorks 2008R2 sample databases
1. Download the AdventureWorks setup file. For SQL Server 2008 R2, download this file.
http://msftdbprodsamples.codeplex.com/releases/view/55926 (mirror)
For other editions, go here.
http://msftdbprodsamples.codeplex.com/
2. The AdventureWorks installation should look like this. If the installation is missing OLTP or Data Warehouse (DW), then its likely you forgot to Enable Filestream for your SQL Server installation. Enable Filestream then run the AdventureWorks setup file again.
3. Once installation is complete, open SQL Server Management Studio and confirm the AdventureWorks databases exist and are populated. Here are a few: AdventureWorksDW2008R2, AdventureWorksLT2008R2, AdventureWorks2008R2.
Creating a sample SSRS report using Business Intelligence Development Studio
1. After the sample databases have been installed, open Business Intelligence Development Studio.
4. Click File-> New Project. Select Report Server Project (Another option is to use the Report Server Project Wizard, but Im not a fan of magicians, so I wont go that route)
5. The first thing is to add a new Report to the empty Report Project. Right click on the Reports folder in Solution Explorer and click Add New Item, then select Report. (Alternatively, you select Add New Report for a Wizard-like approach).
5a. The Report Data window (on the left side) should appear. If it doesnt, add it via View -> Report Data.
Creating the Data source
6. Next, create a Data Source. Click the New button in Report Data and select Data Source.
6a. A new window appears. Name the DataSource appropriately (e.g. Sales) and click on Edit to construct the connection string.
6b. Select the server and the database. For me, it was Gabriel-PC and xyz. Test the connection.
Creating the Dataset
7. Create a Dataset (a resultset from a query). In the ReportData window, click New -> Dataset.
7a. Enter a name for the Dataset (e.g. ProductSales). Select Use a dataset embedded in my report
7b. Select Data source you created earlier (e.g. Sales).
7c. Enter your query for the data that you want. You can use SSMS to create it and then copy and paste on here or use the Query Designer or whatever option is easiest for you. For our simple example, well use this one to get Product sales for January 2008. Copy this query into the Query textarea and click OK.
SELECT DimProductCategory.EnglishProductCategoryName AS ProductCategory, DimProductSubcategory.EnglishProductSubcategoryName AS ProductSubcategory,DimProduct.EnglishProductName AS ProductName, SUM(FactResellerSales.SalesAmount) AS ResellerSales, SUM(FactInternetSales.SalesAmount)AS InternetSalesFROM DimProduct INNER JOINDimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOINDimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOINFactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey INNER JOINFactResellerSales ON DimProduct.ProductKey = FactResellerSales.ProductKey INNER JOINDimDate AS D ON FactInternetSales.OrderDateKey = D.DateKey AND FactResellerSales.OrderDateKey = D.DateKeyWHERE (D.MonthNumberOfYear = 1) AND (D.CalendarYear = 2008)GROUP BY DimProduct.EnglishProductName, DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryNameORDER BY ProductCategory, ProductSubcategory, ProductName
7d. A new dataset should be created with 5 fields.
Creating the SSRS Report
8. Ok, we now have the data source set up and we have a resultset from that datasource. Lets create a simple tabular report.
8a. Bring up the toolbox on the lefthand-side and drag a table onto the report.
8b. The table only has 3 columns. Since we have 5 fields, were going to add 2 more columns. Right click the rightmost column and Insert Column. Do that twice.
8c. From the ReportData Window, drag ProductCategory into the leftmost column. Repeat with the other fields. The report should look like this.
8d. Click the Preview button right above the designer to see the results. It should look like this.
Deploying the SSRS Report
7. Lets Deploy the report to the report server. We need to set some configuration options first.
7a. In the Solution Explorer window, right click the project and click Properties.
7b. Set TargetServerUrl=http://localhost/reportserver
7c. If youll be debugging the report, set StartItem to ProductSales report.
7d. Click OK
7e. Build -> Deploy.
7f. Go to http://localhost/reports to view the reports.
Comments
Finished. I hope that was helpful. Post a comment if there are sections which are unclear.
I highly recommend you pick up a textbook to get the most out of SSRS. The one Ive found to be most helpful is Applied Microsoft SQL Server 2008 Reporting Services by Teo Lachev. It provides plenty of examples and is very thorough. The book uses SQL Server 2008 (not R2), so youll have to install the 2008 version of AdventureWorks when working with the examples. (mirror)
I had some trouble getting started with SSRS so I told myself once I was all properly set up that I would create a tutorial to help others. If you found it helpful, will you please leave a thank you comment?