What is SSRS? SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It is part of suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services). SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization. Reporting Services includes programming features that enable you to extend and customize your reporting functionality. Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components. SSRS or SQL server Reporting Services which is also called Microsoft SQL reporting, is a component of the Microsoft BI stack. The Microsoft Business Intelligence Stack mainly consists of SQL Database Engine, SSRS, SSAS, SSIS and other tools SSRS is a set of readymade tools, that helps you to create, deploy and manage reports. You can extend reporting functionality using C# and VB as programming languages. Microsoft SSRS or Business Intelligence SSRS, lets you create very rich reports (Tabular/Graphical/Interactive/free-form) from various datasources with rich data visualization (Charts, Maps, sparklines). All these reports can be viewed via a web browsers. SSRS allows are reports to be exported in various formats (Excel, PDF, word etc) SSRS allows reports to be delivered via emails or dropped to a share location in an automated fashion. SSRS provides a host of security features, in order to control, who sees which reports etc. You can download some samples from here. Next, lets look at how to install SSRS and get you started.

SSRS Tutorials - An Introduction In this set of tutorials, we will be learning microsoft sql server 2008 reporting services. This set of tutorials, is designed for anyone with absolutely no experience in business intelligence SSRS or sql server reporting services. We will also walk through sql reporting services configuration, creating your first report, deploying, security etc. we will use the adventure works database, which is available here. What is SSRS? How to Install SSRS Installing sql server 2008 sample database Creating your first SSRS report. Knowing your development environment Making your first SSRS report (non-wizard) method Creating an SSRS parameterized report SSRS Expressions Creating a SSRS tabular report Understanding the SSRS toolbox Building your first report (formatting/calculations) SSRS grouping and totals Using SSRS web services Understanding datasources More about Reporting Services security Use Reporting services API's Manage reports using caching/snapshots/subscription More about report Builder

SSRS Installation

SQL Server Reporting Services Installation Lets move forward with getting to know how to install sql server 2008 reporting services. Please note, most of the steps are the same for installing sql server reporting services 2005. Below are the steps to install SQL reporting services 2008 with express Database. Step 1 : Download the 64 bit or 32 bit version of SQL 2008 Express with advances services from here. Step 2: Right Click on the downloaded file and "Run as Administrator". Step 3: The files will extract itself. Step 4: In case you get a pop-up saying "This program has compatibility issues", click Run Program. Step 5: You should be getting a progress screen. Step 6: The SQL server Installation Center should fire up. Step 7 : Select installation on right hand tab and click on "New SQL server stand -alone installation" Step 8: SQL server will load the setup files. Step 9 : Click OK, it should take you to next page. Step 10 : Click Install. Step 11: Click next, then again next. Step 12 : Click next Step 13 : In feature selection, make sure you select 'Database Engine Services' and 'Reporting Services' Step 14 : Next, select Named Instance and give it any name (you can also choose Default instance) Step 15 : Click next Step 16 : Select Mixed Mode --> enter in any password --> and specify SQL server admins. Step 17 : Click next, and select 'Install, but do not configure under Reporting Services configuration. Step 18 : Click Next Step 19 : Click Next Step 20 : Click Install. Step 21: After Installation is done, click next Step 22 : Click Next. Your installation is almost complete. In order to start using SSRS, you need to perform a few more steps. Step 23: Click Start --> All programs --> Microsoft SQL 2008 --> Configuration Tools --> Reporting Services Configuration --> select your instance Step 24 : Click web service URL on the left hand side and click the apply button. Step 25 : Click database on the left hand side, select change database --> create a new report server database and complete the steps. Step 26 : Click report manager URL and click apply (Note down the URL displayed. It should be of the form http://machinename/reports) Step 27: open IE and hit the noted down URL Step 28 : You should get a screen like this. That's all. We are all set. Let's install some sample databases and get started learning SSRS 2008.

SSRS Access Denied

How to fix Reporting Services permissions are insufficient for performing operation (rsAccessDenied)

Learn how to configure and access SQL Server Reporting Services reports when you get The permissions granted to user 'domain\username' are insufficient for performing this operation. (rsAccessDenied)

If you are getting the following error while trying to access or deploy a SQL Services Reporting Services (ssrs) report server on your localhost:

The permissions granted to user 'domain\username' are insufficient for performing this operation. (rsAccessDenied)

Solution 1

If you are working on local system and don't need to assign role/permission.

1. Right Click Microsoft BI -> Click Run as Administrator -> either open your existing SSRS report or create your new SSRS report and then deploy your report after that complied you will be received one web URL for to view your report. Copy that URL and paste to web browser(Run as Administrator) and you will get your report view.

2. Right Click on IE and "Run as Administrator".

Solution 2

1. Make sure you have access configured to the URL http://localhost/reports using the SQL Reporting Services Configuration. To do this:
   1. Open Reporting Services Configuration Manager -> then connect to the report server instance -> then click on Report Manager URL.
   2. In the Report Manager URL page, click the Advanced button -> then in the Multiple Identities for Report Manager, click Add.
   3. In the Add a Report Manager HTTP URL popup box, select Host Header and type in: localhost
   4. Click OK to save your changes.
   5. Now start/ run Internet Explorer using Run as Administator...
2. Now start/ run Internet Explorer using Run as Administator...
3. Then go to: http://localhost/reports (you may have to login with your Computer's username and password)
4. You should now be directed to the Home page of SQL Server Reporting Services here: http://localhost/Reports/Pages/Folder.aspx
5. From the Home page, click the Properties tab, then click New Role Assignment
6. In the Group or user name textbox, add the 'domain\username' which was in the error message (in my case, I added: qaskhk-PC\qaskho for the 'domain\username', in your case you can find the domain\username for your computer in the rsAccessDenied error message).
7. Now check all the checkboxes; Browser, Content Manager, My Reports, Publisher, Report Builder, and then click OK.
8. You're domain\username should now be assigned to the Roles that will give you access to deploy your reports to the Report Server. If you're using Visual Studio or SQL Server Business Intelligence Development Studio to deploy your reports to your local reports server, you should now be able to.
9. Hopefully, that helps you solve your Reports Server rsAccessDenied error message...

Just to let you know this tutorial was done on a Windows 8 computer with SQL Server Reporting Services 2008.

SSRS Architecture

Brief Overview

SQL Server 2008 product included one of the services called as "SQL Server Reporting Services 2008 (SSRS)". SSRS is a full-featured application that provides report design, development, testing, and deployment of reports by the use of Business Intelligence Development Studio (BIDS) developer tool for all its operations.

SSRS block diagram as shown below has been taken from an online book for reference purposes:

1. Reporting Services include an HTTP listener that monitors incoming requests directed to HTTP.SYS on a specific port on the local computer. The host name and port are specified on a URL reservation when you configure the server.
2. Report Manager is an ASP.NET application that provides a browser-based interface for managing Reporting Services.
3. Reporting Services include an Authentication Layer that verifies the identity of the user or application that makes the request. The following authentication types are supported: Windows integrated security, NTLM authentication, Basic authentication, Forms or custom authentication, and Anonymous access. Reporting Services uses Windows integrated security and NTLM authentication by default.
4. The Web Service is also implemented as an ASP.NET application; it provides a programmatic interface to Reporting Services.
5. By default, reporting services creates and uses two SQL Server 2008 databases instances named ReportServer and ReportServerTempDB. The ReportServerTempDB database is used for temporary storage.

Installing Sample Databases

Installing Sample databases (Adventure Works)

Next, lets install some sample databases for us to work with. One of the most commonly used databases is the SQL adventureworks database. SQL server adventureworks database download is available here.

Once you download the database, lets look at how to setup sql database.

Step 1: Install your downloaded database (sql server 2008 sample databases download), by choosing the correct instance name.

Step 2: After installation, open SSMS --> your instance name, then check if the below databases are visible.

Step 3: You can also install some sample reports from here.

Report Creation with the Wizard

SQL Server Reporting Services with using the Wizard

In this section, we will use the Report Wizard to build a report. Let's go through the following steps:

• Open Business Intelligence Development Studio
• Launch the report wizard
• Create a shared data source
• Design a query
• Select a report type
• Design a table
• Choose the table layout
• Complete the wizard

Open Business Intelligence Development Studio (BIDS) (Start --> Microsoft SQL server 2008 --> SQL server Business Intelligence studio

Click File --> New project --> Report Server Project Wizard. Give any name and location.

Once you click OK, you will be presented with the following screen.

Click Next. Now configure your connection string. I have chosen the type as 'Microsoft SQL server'. 

The Data Source contains the information necessary to retrieve the data we pull in our report. Reporting Services can access data in relational databases, OLAP databases, and any other data source for which like an ODBC or OLE DB driver, etc.

When we create a Data Source, we can specify it as shared which means it can be used by any report in the same project. If a Data Source isn't shared, it means that its definition is stored inside the report and it cannot be shared with another report.

After launching the Report Wizard, you will be presented with the Select the Data Source dialog as shown below:

In this step, you need to enter the query for the data, that you need the SSRS report to be populated with. You can also use the query builder.

Click on query builder, and click on the right most top icon, to add tables

Select the necessary table, and verify your query, then click the (!) icon to see the result of the query after execution.

If everything looks good, click on Next

Click Next

Add the fields, as shown in the below screenshot.

Click next

Choose a style, and click next.

Choose a deployment location. It will be in the form (http://machinename/Reportserver)

Click Next

You can preview the report in Business Intelligence Development Studio.

Now, Right click the project , and click deploy.

After successfully deploying the project, open up IE, and hit http://machinename/reports

Click on your report to open it.

Congratulations! You have made your first SSRS report.

Report Creation with the Non-Wizard

SQL Server Reporting Services without using the Wizard

In my previous article, we created a SQL server report using the wizard. However, the wizard has it's own limitations towards the customization of reports and stuff. Therefore, it would be awesome if the report be generated without use of wizards. Lets get started with the same.

Most of the report development that you do using Microsoft SQL server reporting services, would be using the non-wizard method. This allows more flexibility to your report development.

Open Microsoft Business Intelligence studio, and create a report server project.

Next lets configure the datasources and datasets.

Right click on the "Data sources" folder and add a new data source. Set the connection string to point to your new adventure works database.

Once that is done, right click on the datasets folder, to add a dataset. This is basically the place, where you will configure your query that returns some data, that you want to be displayed in the report.

Select the datasource, that you just created and type in a query.
You could also use the "Query Designer" if you want.

After eveything is done, click ok

Now from the toolbox, drag and drop a table control.

From the "Report Data", drag and drop the columns defined in the dataset, that you want in the report.

That's it, click on the preview tab and congratulations, you have made your first Microsoft SQL server report using a non-wizard method.

Design SSRS Parameterized Report

Design Parameterized Report using SSRS 2008

In this article, we will see how to create a SSRS report with parameters. We will create the parameters and then add these parameters to our reports, in different ways. For this demonstration, we will use SQL Server 2008 database 'AdventureWorks' for designing the report as well as parameters.

This article is a continuation of 'SQL Server Reporting Services without using the Wizard'. If you have not gone through it, I will highly recommend you to go through it first and then start with this article.

We are going to expose the postal code as a parameter. This means, we will see the address of only the entered postal code.

Modify the query in the dataset, to make PostalCode as a parameter

Once you click OK, go to the parameters folder in "Report Data" - you will find that @postalcode automatically appears in the "Parameters" Fields.

Lets explore more about the parameter. Double click on @postalcode.
This is place, where you configure, how your parameter should behave - should it allow blank values or null values etc.
You can configure the visibility, data-types, default values etc. Lets leave it as it is for now.

Click preview, and enter in a postal code (91373 for example)

Note: Observe that the parameter appears as a text box here. If you want pre-configured values (in a dropdown), double click on the parameter in "ReportData" --> Parameters (here, @postalcode) and click "Available Values"/"Default value" and click "Get values from a query". You can choose your dataset and corresponding column. Create multiple datasets to suffice your parameter value requirements.