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