Create a Report in SSRS using Stored Procedure
For beginners of BIDW (Business Intelligence and Data warehousing), it is always a question of great concern – How we can Create a Report in SSRS using Stored Procedure? At the time of Training sessions, we use only Normal Queries instead of Stored Procedure’s (SP’s) in data-sets to create reports in MS SQL Server Reporting Services known as SSRS. I also followed the same path and made reports in SSRS using Normal Queries. But, rightly said – “To achieve something you never had, you have to do you never did”. So, I always used to think about many questions like – Is there any other way to perform SQL operations to create report? Can we create report in SSRS using Stored Procedures?
Coincidentally, one of our members faced a similar issue and recently posted this as a Question on our Facebook Page named “PhPRinG”. To solve his Problem, we take his Question into our q Lab section of our website. You can also Like our page and post questions – We will try our best to Satisfy you with proper Solution.
Let’s solve this question practically to Create Report in SSRS using Stored Procedure.
- Open Microsoft’s SQL Server Management Studio (SSMS).
- Choose a desired Database (In our case we are using PHPRING as a Database) and Create one table in that database.
- Name that table as “Phpring_q_lab” (without quotes) with 3 columns as Eid, Ename and Location respectively.
NOTE :- Just copy and paste the below code at the time of your practice.
Create table phpring_q_lab (
Eid int identity(1,1),
Location char(10) )
Now, let’s Insert few rows into this table. Write this into another query and Execute it.
Insert into phpring_q_lab values
, (‘vinod kumar’,‘bang’)
, (‘avinash reddy’,‘hyd’)
, (‘saikrishna reddy’,‘hyd’)
STEP 2. Here, we are going to create a stored procedure to retrieve the employees, who belongs to Pune location, later we can use this same Stored Procedure (SP) to create a report in SSRS.
–Create Stored Procedure on “Phpring_q_lab” table.
Create proc sp_phpring_q_lab_forpuneloc
Select eid, ename, Location from phpring_q_lab
- Open BIDS (Business intelligence development studio) and Create report server project.
- Right click on Reports option and go to Add and then click on New item.
You can follow the below screen shot, if u has any confusion while creating a new report.
STEP 4. Create a New Data Source.
I hope all of you know are aware of what a data source is, what a Dataset is and what is a Region. I don’t want to go in depth because it is out of scope of this article.
To create a Data source, Go to “Report data” pane in BIDS (It appears at left side). Simply Right click on Data Source and Choose Add Data Source. It will lead you to the following wizard.
In the above picture, click on EDIT button and it will pop up one more wizard known as Connection Properties window. Provide the below details according to your specification to establish connection and click OK button.
- Server name :- local.
- Log on to Server via :- Windows Authentication.
- Select Database name :- PHPRING.
NOTE :- Click on test connection (It is used to check our Connection, whether it is Successfully Connected or Not?) After clicking on this, it will show Test result for our Connection. Simply click on ‘OK’. You can all see this whole thing in the picture below. Just follow the below screen shot if you have any confusion.
Click on OK button and again click OK to create connection. After looking on above picture we can conclude that our connection is correct.
STEP 5. Create a Dataset.
For doing this, again Go to the Report data and Right click on Dataset. Then choose the following options –
- Use a Dataset embedded in my report.
- Data source name :- Datasource1 (This is our data source name earlier we created for this report).
- Choose query type = Stored Procedure.
Once we choose Query type as Stored Procedure, an option gets highlighted i.e. select or enter stored procedure name. Just click on Down arrow and we can get a drop down list to choose our Stored procedure.
Choose our Stored Procedure (SP) name which is ‘sp_phpring_q_lab_forpuneloc’. Simply click on OK button.
STEP 6. Our data source and Dataset are ready. So it’s time to take Data region to display our data in a meaningful format. Here we are going to take TABLE as my Data region.
To do this, click on ‘Toolbox’ and drag Table option to designer pane. The following thing will appear.
Just click on down cell to take our column names. Repeat this step 3 times to take all 3 Columns which are (EID, ENAME and LOCATION).
After choosing the fields, save the Report and then Click on “Preview button”.
Finally by observing above screen shot we can concluded that our report was created successfully by using “stored procedure” to retrieve the data of employees who belongs to “pune” location.
- Create table Phpring_q_lab in PHPRING database.
- Create stored procedure to fulfill our AIM.
- In BIDS, create a new Report.
- Create a data source, Dataset and Data region (known as Table).
- Finally save and preview the report for our observation.
This is the end of our article on How to Create Report in SSRS using Stored Procedure. If you have any further queries, you can drop those as comments. I hope you all enjoyed this article and we will appreciate your valuable feedback.