Step By Step SSRS in 11 Hours: Hour 3

Introduction

 

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

 

Contents

• Hour 1: What is SSRS and determine whether it is installed
• Hour 2: Report using wizard
• Hour 3: First SSRS report
• Hour 4: Report with parameter
• Hour 5: Custom template
• Hour 6: Drill down report
• Hour 7: Sub report
• Hour 8: Report with style
• Hour 9: Report with chart
• Hour 10: Report deployment
• Hour 11: RDL file to ASP.Net

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.
  
  

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.
  
  

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.
  
  

Step 4

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

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.
  
  

Step 6

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

Step 7

• The next step is to add a dataset.
  
  
• Right-click on "Shared Datasets" and click on "Add New Dataset".
  
  

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.
  
  

Step 9

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

Step 10

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

Step 11

• Now your data set file, in other words .rsd file, is ready.
  
  

Step 12

• To create a report, right-click on "Reports", select "Add" and click on "New Item".
  
  

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".
  
  

Step 14

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

Step 15

• Select the previously created data set.
  
  
• Press the "OK" button.
  
  

Step 16

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

Step 17

• Open the RptCountry.rdl file in design mode.
  
  
• Right-click on "Design Surface", select "Insert" and click on "Table".
  
  

Step 18

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

Step 19

• Insert "CountryID" as header.
  
  

Step 20

• Now click on the highlighted icon.
  
  

Step 21

• Map the "CountryRowID" as the column source.
  
  

Step 22

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

Step 23

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

Next >> Hour 4: Report with parameter