Step By Step SSRS in 11 Hours: Hour 3

In this article, we will see what is SQL Server Reporting Services (SSRS).

Introduction

 
We try to get a list of countries from the CountryMaster table.
 
Contents

First SSRS Report

 
Step 1
  • Open Visual Studio 2012 ad click on "Project…" to create a new project.

  • Expand "Business Intelligence" under the Installed Templates from the left panel.

  • Click on "Report Server Project".

  • Right-click on "Shared Data Sources".

  • Click on "Add New Data Source" to create a new shared data source.

    add datasource
Step 2
  • Select the "General" Tab.

  • Provide appropriate an name of the data source.

  • Select "Microsoft SQL Server" from the type drop-down list.

  • Click on the "Edit" button to set up the connection string.

    General
Step 3
  • Select "Server name" from the server name drop-down list.

  • Select the "Use SQL Server Authentication" radio button.

  • Provide the SQL Server Authentication a user name.

  • Provide the password.

  • Select "SSRSDemo" as the database name.

    Server name
Step 4
  • Click on "Test Connection", it shows a message box with test connection status.

    Test Connection
Step 5
  • Right-click on "Shared Data Sources" and click on "Add New Data Source".

  • Provide a data source name and connection string.

  • You can also change the credentials of your data source.

    Shared Data Sources
Step 6
  • The .rds file is shown under "Shared Data Sources".

    Data Sources
Step 7
  • The next step is to add a dataset.

  • Right-click on "Shared Datasets" and click on "Add New Dataset".

    Shared Datasets
Step 8
  • Provide dataset name.

  • Select the data source created.

  • If you want to write a query then select the "Text" radio button and provide your query.

    Text
Step 9
  • For Stored Procedure select the "Stored Procedure" radio button and provide the Stored Procedure name.

    Stored Procedure
Step 10
  • Select the fields you want to display in the report and map with the field source.

    field source
Step 11
  • Now your data set file, in other words .rsd file, is ready.

    data set file
Step 12
  • To create a report, right-click on "Reports", select "Add" and click on "New Item".

    Reports
Step 13
  • Select "Report" and provide an appropriate name for report.

  • Click on the "Add" button and it will add a .rdl file under "reports".

    Add
Step 14
  • In the report data right-click on "Datasets" and click on "Add Dataset…".

    Datasets
Step 15
  • Select the previously created data set.

  • Press the "OK" button.

    OK
Step 16
  • It will add a data set and show the fields.

    Design Surface
Step 17
  • Open the RptCountry.rdl file in design mode.

  • Right-click on "Design Surface", select "Insert" and click on "Table".

    bordered
Step 18
  • Click on the bordered area and it will allow you to add a column header.

    CountryID
Step 19
  • Insert "CountryID" as header.

    header
Step 20
  • Now click on the highlighted icon.

    highlighted
Step 21
  • Map the "CountryRowID" as the column source.

    CountryRowID
Step 22
  • We need to add "CountryName" in the same way.

    CountryName
Step 23
  • Click on "Preview" and your first SSRS report is ready.

    SSRS report