How To Create A Report Server

SQL Server Reporting Services (SSRS) is part of Microsoft SQL Server services since version 2000. If you are an ordinary user with a basic knowledge of SQL, this article will help you in creating your first SSRS report.

Requirements for creating a SSRS report

Before you begin using SSRS and creating reports you must have:

Microsoft SQL Server 2014 database engine
SQL Server 2014 reporting service (SSRS)
SQL Server data tools
AdventureWorks2012 database is used for this article, as data source.

Create a report server project

The first thing to do is to create a report server project because we will need this later for saving report definition files and any other files that you need for creating SSRS reports.

The first step is to open SQL Server Data Tools. Click on the File menu, find New and then Project.
After this, you need to click on the Business Intelligence.
Click on the Reporting Services and then Report Server Project.
If you want to display Report test project to get you started, you can type Report test in Name
The last step is to click OK to finish.

Create a new report definition file

In the View menu find the Solution Explorer, and then right-click on the Reports folder. In the Add section click New Item.
In the window Add New Item, click Report.
As you can see Report Designer has two available views. In Design view you define your report layout, and in the Preview view you can run your report.

Defining data source and dataset

Setting up a connection

In order to retrieve data from a database or from some other resource, you will need to define the data source. In the following section, you will see how you can define the data source. We will use AdventureWorks2012 database as an example.

In the View menu find and click Report Data, then New and after that Data Source
Type AdventureWorks2012 in the field Name
Select Embedded connection
As Type select Microsoft SQL Server
In Connection string field type:
- Data source=localhost; initial catalog=AdventureWorks2012

Note: If the database is not on the local computer, replace localhost with name of your database server instance.

After this, click on the Credentials and then Use Windows Authentication.
Click OK, and you're done.

Define a T-SQL query for report data

In this section you will learn how to specify a dataset. You will need some basic SQL knowledge in order to create a query and define what information you will need from your database. The query you will see is just an example, and for your own purposes and database, you will have to change that step.

In the Report Data pane find and click New, and then Dataset.
In the Dataset Properties dialog box as Name type DataSet1
Make sure to check Use a dataset embedded in my report.
Then select a AdventureWorks2012 as your data source,

Check Text as a Type, and type this into the Query input:

SELECT

soh . OrderDate AS [ Date ] ,

soh . SalesOrderNumber AS [ Order ] ,

pps . Name AS Subcat , pp . Name as Product ,

SUM ( sd . OrderQty ) AS Qty ,

SUM ( sd . LineTotal ) AS LineTotal

FROM Sales . SalesPerson sp

INNER JOIN Sales . SalesOrderHeader AS soh

ON sp . BusinessEntityID = soh . SalesPersonID

INNER JOIN Sales . SalesOrderDetail AS sd

ON sd . SalesOrderID = soh . SalesOrderID

INNER JOIN Production . Product AS pp

ON sd . ProductID = pp . ProductID

INNER JOIN Production . ProductSubcategory AS pps

ON pp . ProductSubcategoryID = pps . ProductSubcategoryID

INNER JOIN Production . ProductCategory AS ppc

ON ppc . ProductCategoryID = pps . ProductCategoryID

GROUP BY ppc . Name , soh . OrderDate , soh . SalesOrderNumber , pps . Name , pp . Name ,

soh . SalesPersonID

HAVING ppc . Name = 'Clothing'

Source: Defining a Dataset for the Table Report (Reporting Services)

Note: soh, pps, sd, pp, ppc are just shorter names for tables that we have in AdventureWorks2012 database.

How to add a table and fields to a report layout

After finishing with previous technical details, we can start with more interesting things, like designing your first SSRS report. This part is easy because instead of writing code, you can drag-and-drop graphic icons into the report format. This section will show you how to add Table and Fields to your report.

In the View menu click Toolbox, then find and click Table and drag the mouse to the design area.
In the left pane, you can expand the dataset Dataset1 in order to see all the fields.
Drag one of the field (e.g. field Date) from Report Data to the column in the table
You can continue adding fields, table will automatically add more columns.

Preview report

If you want to preview your report to see how it all looks, to correct errors, to correct issues or to verify design and data connection, click tab Preview.

Tips and tricks

An easier way to add a table is to right-click on the design surface, click Insert and then click Table.

How to format your Report

Format currency and date

If you want to format Date field to show only the date, follow the steps below:

In the Design tab, right-click the desired cell, then click Text Box Properties.
Find and click Number, then in the field Category click Date
As Type select date format you want, and click OK to finish.

If in your table you have a field that holds information about currency, but you only see ordinary numbers, you can format that field to display a number as currency:

In the Design tab, right-click the desired cell, then click Text Box Properties.
Find and click Number, then in the field Category click Currency
In accordance to your need you can change defaults
Click OK to finish.

Changing column width and text style

You also have an option to change text style (font, size etc.), and to change column width. Column width you can change by simply dragging the columns to the desired size.

Text style you can change by clicking the Format menu. In Format menu find Font, then click whatever you need (bold, italic, etc.)

Adding Grouping

If you want to make data set in your SSRS report do the following:

Click Design tab, and then choose pane Row Groups
Drag the field you want to group to the pane Row Groups
From your report pane drag some other field you want to group.
Delete the old columns to the double line.
If new columns need to be format, just right-click the cell and then click Text Box Properties, the next steps are the same like in the formatting report section.

Tips and tricks

You can do the same by right-clicking on the surface and clicking View, and then Grouping.

Adding totals

Total is the sum of numeric, non-null data in the data region, and if you want to add totals for a group, you can do that by clicking Add Total for the group in the Grouping pane, and if you want to add totals for an individual cell just click Add Total for the cell.

Add a daily total and grand total

Right click the cell [Order] and choose Add total, then click After .
Type Daily to format a new name, Daily Total
After that, select the new cell [Daily Total], two Sum cells and the empty cell you see between them.
In the Format menu, choose background color. We chose color orange.
Right click the cell [Date] and choose Add total, and then After .
Type Grand to format a new name, Grand Total
Select the new cell [Grand Total], the two [Sum] cells and the empty cell you see between them.
In the Format menu, choose background color. We chose color light-blue.

Tips and tricks

After you add total, you can change the default function Sum. There is a list of different function you can use (avg, count, etc.).

Publish your Report to the report server

Finally, when you finished with creating your first SSRS report, you may want to publish the report:

In the Project menu click Test report Properties
Put your report server's name in the field TargetServerURL, then click OK.
In the Build menu click Deploy Test report. You will get a message that indicates whether you have successful or unsuccessful deployment.

Note: A problem will occurred if you don't have permissions on the report server or if you have been using SSDT with administrator privileges.

Most common problems

The most common problem in publishing the report to the report server is configuring the target server URL.

The first step that you need to do in configuration is to launch the SQL Server Reporting Service Configuration Manager and connect to the Reporting Service.

Create SSRS report

After that in section Web Service URL you can find URL to the report server.

Creating SSRS report shot

In section Report Manager URL you can see URL for viewing and managing reports.

Creating SSRS report shot

In the Project menu open the Test report Properties.

Note: Prefix "Test report" is name of your report.

Final step in configure target server URL is to fill the Web Service URL in the TargerServerURL property with correct URL that you find in Report Manager URL.

Creating SSRS report shots 1

Congratulation, you have successfully created your first SSRS report!

References

Reporting Services (SSRS)
Reporting Services Concepts (SSRS)
Reporting Services Backward Compatibility
Cause and Resolution of Reporting Services Errors