Create a report using Report wizard in SSRS

5
1357

Aim We will create a simple report with the help of Report wizard using Microsoft SQL Server Reporting Services (SSRS) 2008 version. With the step by step implementation of reporting services, we will also try to understand the working environment for the report making. Let’s create a report without wasting time on theoretical part as doing practical will make you understand better.

STEPS TO FOLLOW:-

Step 1. Open Business Intelligence Development Studio (BIDS) — Go to Start – All Programs – MS SQL Server 2008 R2 – Business Intelligence Development Studio.

Step 2. This will open a Start page. Go to Create project and choose Report Server Project Wizard. We need to provide Name, Location and Solution name for the project.New Project for ByReportWizard

Step 3. Clicking OK button will pop up Welcome Report wizard screen. You can tick the check-box if you do not prefer to see this screen next time.Welcome Report Wizard for ByReportWizardStep 4. Now we need to select a data source from which we will obtain data for creating this report. Either we can choose Shared Data Source (If data source is already been present) or can create a new Data source by providing data base credentials.

I will be creating a new data source so that we can understand this step clearly. Give a desired name and type for the data source. I am using MS SQL Server as my database, so will choose it from the Type drop down box. Click on Edit button.Connection properties for ByReportWizard

It will lead to the Connection properties screen containing the fields as mentioned below:-

  • Server Name – Choose the Server name from the drop down box.
  • Log on to the Server – Select the preferred Authentication method and provide valid credentials for that method (As i am using client MS SQL Server, so i will use SQL Server Authentication method to get connection with my server).
  • Connect to a Database – As the connection established, all the tables or views will be populated in the drop down box and we can choose our desired table for creating report.

NOTE :- If everything goes well then clicking on Test Connection button will result into Success else you have to configure your connection correctly.

Step 5. Now we need to concentrate on this step as we are going to design our query to fetch the desired data from the chosen table. Click on Query Builder to generate automatic query for our task. You can also write it by your own but i prefer shortcuts.

Query Builder for ByReportWizard Click on Add Table button present at the extreme right on top and choose the tables whose report you want to generate and click OK. I will be selecting a single table to perform this report creation by Report wizard method.

Step 6. Clicking Next button will lead to Report type screen. Select the desired Report type and click Next.Report type for ByReportWizardStep 7. Choose how you want to see your data in the report format according to your need. This is the screen where you can design the layout for your data. Click Next button.Table Design for ByReportWizard

Step 8. Choose the type of Layout for the table. I will be making the report using Stepped layout but you can also try Blocked layout and can analyze the difference between these 2 layouts.

NOTE:- Include subtotals and Enable drill down features will be covered later as we move forward towards the complex report generation but if you are anxious to know then you can tick the check box and can learn it from the output you get. You can select Enable drill down check box if you want to make a drill down report by Report wizard.Table Layout for ByReportWizard

Step 9. Choose a style for the table and click Next. Choose the Deployment location for the report and click Next button.Table Style for ByReportWizardStep 10. We are about to see the output for our report. Provide the name for the report and click Finish button to create a new report using Report wizard.Completion for ByReportWizardNOTE :- If we select Preview report check box, then we directly can see our report in the Preview Pane else we will land into the Design Pane.

Step 11. Clicking Finish button leads to the Design pane which is known as Workspace. Let’s understand the basics of our working environment.Design Mode for ByReportWizardIn our working Environment we will have features like:-

  1. Toolbox containing tools such as table, rectangle, line, matrix, sub-report, etc.
  2. Dataset containing columns which are generated by our SQL query which we passed in Query builder.
  3. Two types of modes named as Design and Preview mode.
  4. Solution Explorer and Properties window pane for the report.
  5. Work space, Built in fields, parameters and many other features.

Step 12. Now, it’s the time to raise the curtains and see our output for the report which we have created by spending so much of time.Preview Mode for ByReportWizardThat’s Great !!! We get the success for our first report creation using MS SQL Server Reporting Services (SSRS) by Report Services Wizard. You can also deploy your report if you have the administrative rights.Deploying your report to the server will make you to see it via web interface.I guess it was very simple for you to implement this task. We will be creating custom reports in our next part using MS SQL Server Reporting Services – SSRS 2008. Your comments and views are welcome.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.8/10 (30 votes cast)
VN:F [1.9.22_1171]
Rating: +29 (from 29 votes)
Create a report using Report wizard in SSRS, 9.8 out of 10 based on 30 ratings

5 COMMENTS

  1. Hi Chander,
    I started learning SSRS 2008 a week ago and came across your website. I must compliment you that the way of presenting is simple and clear. I am new to this report making by SSRS. So, would like to get more posts in future on the same. Good luck
    Thanks,
    Bani

  2. Hi,
    This is an EXCELLENT tutorial for learning MSBI. Your Presentation is simply superb… 🙂

Comments are closed.