The report going to be designed in on SQL server Tutorial on Northwind Database, so it recommended to download the database from Microsoft website. We are going to design a Basic SSRS Report through report wizard. For that we have to write a stored procedure as given below.
Create Procedure [dbo].[RPTSP_EmpList]
( @city varchar(12) )
from Northwind.dbo.Employees where City=@city
–exec RPTSP_EmpList ‘London’
Step 1: Open Microsoft BIDS
Step 2: Choose Report Server Project from the available project type, give a suitable name to the project and click on OK button.
Step 3: After clicking on OK button the below screen will appear
Step 4: Click on the Shared Data Source – > Add New Data Source
Step 5: Enter proper information in the Shared Data Source Properties.
Step 6: After clicking on the OK button, the following screen will appear
Step 7: Next click on Reports – > Add New Report
Upon clicking on Next button, we get the Design the Query window where we will write an inline query to fetch the records from the Employee table.
Click on Query Builder button and the Query Designer screen opens up .Note that 2 options are there in the table type
- Text (where we write the normal /inline SQL query)
- Stored Procedure
Note: we can click on the Import button and can specify the SQL file that contains the script. For specifying stored procedure , we need to specify the stored procedure name with a parameter name.
Step 8: Report created. If we look now into the Project Reports folder a new file ReportSSRS1.rdl is created.
Now let us click the Preview button to get the full report
If we have specified a parameter, then at the time of preview we need to give some value to it. This is how we can create a Basic SSRS Report with BIDS.
Previous Related Post:
Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net