Installing and configuring Microsoft Reporting Services

Every company, big or small has to use various tools to retrieve data from their Databases. IT departments receive many different requests for data to be presented in those reports.

There are many reporting tools on the market and some of them are very expensive. If your company is using a Microsoft product, you should not shop for any other tools. Microsoft Reporting Services can serve your needs.

Microsoft Reporting Services is a tool which is a part of Microsoft SQL Server package and can be installed and used as an enterprise reporting tool.

In this tutorial I will show you how to configure Microsoft Reporting Services (MRS).

Let’s start. First of all, ensure that MRS was installed as a part of your SQL Server Installation, if not; you will need to install it. To install MRS you have to go back to your SQL Server Media and run ‘install file’. Choose Add components and then choose Reporting Services. Follow screen menu to finish your installation. After the installation has completed, you should see 2 new databases created (ReportService and ReportServiceTempDB). Also, you will see Reporting Services under Start – All Programs - Microsoft SQL Server – Configuration Tools.

Can you see it? Great let’s start configuring this tool.

Let’s open Configuration tools under Start -  All Programs – Microsoft SQL Server – Configuration Tools – Reporting Services Configuration Manager as you can see in Pic #1

Then click Connect. Make sure that under Server Name: you put your Server Name where you installed Reporting Services, and under Instance put proper instance name if you have multi instance server.

Let’s go through each step and configure MRS. Next picture will show you your server information.

Pic #2

Click start to connect and enable configuration tools. As you can see, after we connected all options on a left side get enabled, so we can go through each one and configure our Reporting Services.

Pic #3 – Enabled Configuration Tool

Let’s go to the first tab – Service Account and configure Reporting Services Service Account

You are able to use two different types of accounts:

Build In account or another domain account the way it is showing In a picture below

Pic #4 – Service Accounts

In my example, I will use Build in option so drop down use build-in account and choose “Local Service” then click apply. Encryption Key window will pop up as showing on pic #5

Pic #5 configuring Service Account cont.

Let’s choose a file location to back up our encryption key for future use. In my example, I created new folder called Reporting Services Key where I will save my key. I created complex enough password and then save this as it showing on a next picture.

Pic #6

Next screen will allow you to select between integrated security and SQL Account. I will use Integrated Security in this tutorial.

Pic #7

Now lets move to the next tab called “WEB Service URL”. This page will allow you to configure URL for your reporting services.  Don’t forget that Reporting Services using port 80 so if your company using firewall you need to request port 80 to be opened.

Pic #8

On Web Service URL page you can set up your virtual directory, IP addresses, TCP/IP port if you want to use anything else other than port 80, and SSL Certificate.

Pic #9

Database screen will allow you to make changes to the reporting database, and reporting database credentials. In my example, I am leaving default settings and moving to Report Manager URL page.

Pic #10

Report Manager URL will let you configure your Report page URL.  In this example, we are leaving Virtual Directory and URL as default and moving to Email Setting.

Pic #11 – Email Setting

Most likely your company using Microsoft Exchange server for corporate Emails. This screen will allow you to configure email account to use it in a future for your email alerts and for emailing subscriptions.

Let’s move to Encryption Key page to backup our existing key’s.

Pic #12 – Encryption Keys

Pic #13 – Backing up Encryption Keys

After all tab’s configured we can go to the internet explorer and type reporting web page, in my example it will be

Or you can go to your report manager URL and just click on a link. This should bring you to the Reporting page as you can see in a picture below

Pic #14

Before I will get into database management systems, let me start with the basics - let's look at a simple text file example.

• Oracle
• Microsoft SQL Server
• MySQL
• Microsoft Access

• Database creation
• Tables and Tables Creation
• Adding data to your database
• Querying a database
• Relational database design

·         Microsoft SQL Server

·         Many database administrators (DBAs) use Structured Query Language (SQL) to perform many of their database tasks. To enter SQL, you need to open an interface that allows you to enter your code. For example, if you use SQL Server, you would normally use Query Analyzer.

          Example #1 Creating database using TSQL

·         Most database systems make it very easy to create a database via a user interface. Generally, it's just a matter of selecting an option from a menu, then providing a name for your database.

 

Now let’s “Right Click on TABLES” and select “NEW TABLES”

Now let’s create same table as we created as a text file previously.

 

Right click on a table and choose “EDIT TOP 200 ROW”, you should see screen similar to the one below:

After filling in information your table should look like as example below:

Open Query Analyzer and type following:

Let’s check to make sure we have 5 records in our table.