SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Create your first SQL Server Reporting Services Report

In this SQL Server Reporting Services tutorial, business intelligence developers will create their first report using SQL Server Data Tools 2015 for SQL Server 2016 Reporting Services.

Business Intelligent developers on SQL Server Data Platform can create solutions as Analysis Services, Integration Services or Reporting Services projects created by using an IDE named SQL Server Data Tools. This SSDT aka SQL Server Data Tools is a seperate download and requires an additional installation which adds BI templates in Visual Studio IDE. For SQL Server 2016 the best solution is provided by Microsoft Visual Studio 2015. The SSDT is known as SQL Server Data Tools 2015.

To create a SQL Server Reporting Services report on SQL Server 2016 environment follow the below steps.

First launch SSDT (SQL Server Data Tools)

Create project by following menu options: File > New > Project...

create new project in SQL Server Data Tools

From installed templates, choose SSRS (SQL Server Reporting Services) template seen below as Report Server Project Business Intelligence template

Type the name of the Report Server project and select a location for project solution files including report files.

SQL Server Report Server Project Business Intelligence template

Add new report to solution by right click on Reports node in the Solution Explorer windows seen in the following screenshot.
This is a simple way to create reports for Reporting Services.

create new report for SQL Server Reporting Services project

Report Wizard will guide us to complete the basic tasks for creating a new report on SQL Server Reporting Services using SSDT (SQL Server Data Platform)

Report Wizard to create reports for SQL Server 2016 Reporting Services

As seen on the first page of the Report Wizard, following steps will be completed in order to create a report in SQL Server 2016 Reporting Services project

  • Select a data source from which to retrieve data

  • Design a query to execute against the data source
    Choose the type of the report you want to create
    Specify the basic layout of the report
    Specify the formatting for the report

    Let's start the wizard to define datasource for the report.
    This is simply identifying the database and connection properties where the data resides.

    Reporting Services report data source

    As seen above, since we have not yet created any shared data sources this option is deactivated on the screen.
    Type a meaningful name for the report data source

    Since I want to use SQL Server sample database WideWorldImporters I will name the data source as "World Wide Importers" instead of default name DataSource1

    The datasource is my local default SQL Server instance. So in the Type dropdown Microsoft SQL Server will be left unchanged

    Press Edit to configure the connection string for the report data source

    configure data source for Reporting Services report

    On Connection Properties screen, Business Intelligence developer can select the data source type, data source and authentication properties.

    For this Reporting Services tutorial, I want to display data from SQL Server 2016 sample database WideWorldImporters.
    So I leave unchanged the default "Microsoft SQL Server (SqlClient)" option on "Data Source" section.

    In "Server name" part, provide the SQL Server instance name where you want to retrieve and display data from

    I will use Windows Authentication, so did not change on "Log on to the server" section.

    One last step for defining the data source is pointing to the database. So select the database name from the combobox.

    Now we are ready to test our configuration. Just press "Test Connection" button to test the data source configuration for the report

    SQL Server 2016 Reporting Services report data source properties

    Click OK to return

    Mark "Make this a shared data source" so you can use this data source in your other reports in this Reporting Services project

    create as shared data source

    Click Next

    Use Query Builder and Query Designer to fetch data for your Reporting Services report.
    Or type the SQL SELECT script in the "Query String" section

    select top 20
     COUNT(distinct i.InvoiceID) as InvoiceCount,
     SUM(LineProfit) as Profit
    from [Sales].[Invoices] i
    inner join [Sales].[InvoiceLines] il
     on i.InvoiceID = il.InvoiceID
    inner join [Sales].[Customers] c
     on i.CustomerID = c.CustomerID
    where i.invoiceDate >= '20160501'
    group by i.CustomerID, c.CustomerName
    order by Profit desc, InvoiceCount

    As programmers can see we expect to see below SQL query output in our sample Reporting Services report.

    SQL Server query for Reporting Services data source

    As you see below, I copied the SELECT statement in the Query String section.
    I general instead of directly pasting SQL Select statements, BI developers generally call SQL Server stored procedures for fetching report data

    SQL Server 2016 Reporting Services report creation in SSDT

    Click Next button for next step in the create report wizard.

    Choose Tabular or Matrix report layout. I choose Tabular for simplicity

    report type tabular or matrix

    Click Next

    Design the data table.
    This step in report creation wizard enables developers to group data quickly for a better display format

    group reporting services table data in SQL Server Data Tools IDE

    The last step enables the developer to name the report as well as checking configuration for the last time.
    There is also a "Preview report" option

    I named report as "Top20Customers" instead of default name Report1
    Also a summary of the steps taken in the report create wizard is displayed on screen like data source, query string, grouping options, etc.

    SQL Server Data Tools report wizard

    Press Finish

    In following screenshot, business intelligence professionals can see the Reporting Services report created on SQL Server Data Tools 2015. As we have designed the data source connection properties, data is selected from SQL Server 2016 sample database.

    sample SQL Server 2016 Reporting Services report in Data Tools IDE

    As seen, in the inner section of the SQL Server Data Tools the preview of the report takes place.
    On the right section in Solution Explorer window, the BI Reporting Services project template files are seen.
    Data source file has .rds file extension.
    Reporting Services report definition files have .rdl file extension.

    In our next Reporting Services tutorial, we will demonstrate how to deploy rdl report files to SQL Server 2016 Report Server.

    I hope report developers who are new with SQL Server Reporting Services like this tutorial on report creation steps using SQL Server Data Tools.

    SQL Server

    SQL Server 2019 Installation
    download SQL Server 2019
    download SQL Server 2017
    download SQL Server 2016
    download SQL Server 2014
    download SQL Server 2012
    MacOS ve SQL Server 2019

    Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.