How to create a SQL Server Reporting Services (SSRS) report

SSRS is simply one of the components in the Microsoft Business Intelligence (BI) platform. Combined, those components offer a remarkable platform for enterprise records analysis. The Microsoft BI platform includes the subsequent:

• SQL Server: The conventional database engine, which additionally shops SSRS catalog facts.
• SQL Server Analysis Services (SSAS): A factor for online analytical processing (OLAP) and information mining. OLAP performs data aggregation and lets in customers to traverse from aggregations to details looking through the dimensions of information. Data mining allows users to discover styles in the information.
• SQL Server Integration Services (SSIS): An issue for extracting, transforming, and loading (ETL) statistics.

T-SQL Tricks for SQL-Server?

Requirements for creating an SSRS report

Before you begin using SSRS and growing reports you ought to have:

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

Create a record server task

The first issue to do is to create a report server project due to the fact we will want this later for saving report definition documents and any different documents that you want for developing SSRS reports.

The first step is to open SQL Server Data Tools. Click at the File menu, discover New and then Project.

• After this, you want to click at the Business Intelligence.
• Click on the Reporting Services and then Report Server Project.
• If you want to display the Report test project to get you started, you can type Report check-in Name
• The final step is to click on OK to finish.

Create a new report definition file

• In the View, menu finds the Solution Explorer, after which right-click at the Reports folder. In the Add, phase click on New Item.
• In the window Add New Item, click on Report.
• As you may see Report Designer has two available views. In the Design view, you outline your document layout, and in the Preview view, you may run your file.

Defining records source and dataset

Setting up a connection

To retrieve statistics from a database or some other resource, you will want to outline the source of the statistics. In the following section, you will see how you could define the information supply. We will use the AdventureWorks2012 database as an example.

1. In the View, menu find and click on Report Data, then New and after that Data Source
2. Type AdventureWorks2012 in the subject Name
3. Select Embedded connection
4. As Type select Microsoft SQL Server
5. In Connection string field type:
6. Data source=localhost; preliminary catalog=AdventureWorks2012
7. After this, click on at the Credentials after which Use Windows Authentication.
8. Click OK, and you’re done.

Define a T-SQL query for document statistics

In this phase, you will discover ways to specify a dataset. You will want a few simple SQL information for you to create a query and define what facts you will want out of your database. The question you will see is just an example, and in your functions and database, you'll have to alternate that step. Enroll now for more info at SQL Server DBA

• In the Report Data pane locate and click New, after which Dataset.
• In the Dataset Properties dialog field as Name kind DataSet1
• Make sure to check to Use a dataset embedded in my file.
• Then pick out an AdventureWorks2012 as your records source,
• Check Text as a Type, and type this into the Query input:

How to format your Report

Format currency and date

If you want to format Date subject to show simplest the date, follow the stairs below:

• In the Design tab, right-click the desired cell, then click Text Box Properties.
• Find and click on Number, then in the discipline Category click on Date
• As Type pick out date layout you want and click OK to finish.
• If in your table you've got a field that holds records about forex, however, you only see normal numbers, you may layout that discipline to display a variety of as foreign money:
• In the Design tab, right-click the favored cell, then click on Text Box Properties.
• Find and click Number, then in the field Category click on Currency
• By your need, you can alternate 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 the 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.)

Publish your Report to the document server

Finally, whilst you finished with developing your first SSRS document, you may need to put up the report:

• In the Project, menu click on Test document Properties
• Put your record server’s name inside the field TargetServerURL, then click OK.
• In the Build, menu clicks on the Deploy Test file. You get a message that shows whether you have got success or unsuccessful deployment.

Note: A trouble will happen if you don’t have permissions on the document server or if you have been the usage of SSDT with administrator privileges.

Read more info at SQL Server DBA Online Training 

Most commonplace problems

The most not unusual problem in publishing the file to the report server is configuring the target server URL.

• The first step that you want to do in the configuration is to launch the SQL Server Reporting Service Configuration Manager and hook up with the Reporting Service.
• After that in segment Web Service URL, you may locate URL to the document server.
• In segment Report Manager URL you can see URL for viewing and handling reports.
• In the Project, the menu opens the Test report Properties.

Note: Prefix “Test document” is the call of your report.

The final step in configures goal server URL is to fill the Web Service URL in the TargerServerURL property with the correct URL that you find in the Report Manager URL.

Congratulations, you have correctly created your first SSRS file!