Oct
22
2013

How to Create Report in SSRS using Stored Procedure?

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?

Create report in SSRS using Stored Procedure

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.

STEP 1.

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

USE

PHPRING

go

Create table phpring_q_lab (

Eid int identity(1,1),

Ename varchar(20),

Location char(10)   )

go

Now, let’s Insert few rows into this table. Write this into another query and Execute it.

Insert into phpring_q_lab values

(‘Chander’,‘pune’)

, (‘pinaldave’,‘bang’)

, (‘vinod kumar’,‘bang’)

, (‘balmukund’,‘bang’)

, (‘avinash reddy’,‘hyd’)

, (‘saikrishna reddy’,‘hyd’)

, (‘venkat’,‘hyd’)

, (‘aakash’,‘pune’)

, (‘sharma’,‘pune’)

Read data from PhpRinG_q_Lab Table

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

As

Begin

Select eid, ename, Location from phpring_q_lab

Where Location=‘pune’

End

STEP 3.

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

Add New Item in SSRS

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.

Data Source properties in SSRS

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.

Connection Properties in SSRS

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.

Dataset Properties in SSRS

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.

Choose and Drag Table option to Design Pane

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

Choose fields into the table in SSRS

After choosing the fields, save the Report and then Click on “Preview button”.

SSRS preview pane

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.

Summary :-

  1. Create table Phpring_q_lab in PHPRING database.
  2. Create stored procedure to fulfill our AIM.
  3. In BIDS, create a new Report.
  4. Create a data source, Dataset and Data region (known as Table).
  5. 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.7/10 (13 votes cast)
VN:F [1.9.22_1171]
Rating: +10 (from 10 votes)
How to Create Report in SSRS using Stored Procedure?, 9.7 out of 10 based on 13 ratings
Enjoyed this post or Have Queries ? Please consider leaving a Comment below

About the Author: Avinash Munnangi

Am always a Learner. My aim is to learn something new of every single day, Learning one thing in different views is always Fun!!!

8 Comments+ Add Comment

  • Hi Avinash,
    Very nice article. Following your posts on SSRS tutorial. #Phpring.
    Thanks,
    Aanchal

    • Hey hai Aanchal,
      very glad u liked my articles on phpring.
      Thank u so much,keep continue your support .

  • Thank you very much for your tutorial. It helps me. :)

    • Hi Rekha,
      Welcome to PHPRING . i am very glad you liked my article and happy to know its help you.

      Thanks,
      Avinash

  • can you please tell how to create multiple parameterized reports…like drop down with text box..
    thanks in advance

    • Hi Sanyam,
      Welcome to PhpRing Tutorials.

      You mean what ever the parameters you have choose in parameter list , You need to display them also in Textbox ?Could you please elaborate your question?

  • hi Avinash Reddy,

    Nice Article,

    Regards,
    Bogireddy Venkat Reddy

    • Hi venkat,
      Its my pleasure to see your comment here. Thanks a lot on spending your time to visit PhpRing and read this article.

      Thanks,
      Avinash

Leave a comment

Current day month ye@r *